ConradWilkie20
Canvas Initiate

Nintex Form Lookup Formula to return Person or Group

Jump to solution

Hi All, 

 

Hoping for some help here. 

 

I am creating a form for our Finance department to deal with incoming purchasing requests. The form writes the data to a SharePoint List.  

In the form, we have Three Lookup fields pulling data from two config SharePoint lists. These lists are our list of account codes and cost centres. 

 

Both lists contains the account codes/cost centres and their respective budget holders. 

 

Account Code List properties (Named "R1 Account Codes Cost Centres and Budget Holders": (Parent Category and AccCode are the fields getting looked-up in the form.)

NOTE Budget Holder AC allows multiple Values

r1 Form query #2.png

 

Cost Centre List Properties (Named "R1 Cost Centres"): (CostCen is the field being looked-up in the form)

NOTE Budget Holder CC allows multiple Values

r1 Form query #3.png

 

Within the form, the first lookup retrieves a list of categories relating to type of purchasing, the next two fields are the account code and Cost centre lookups. Pictured below. (NOTE the Budget Holder Field was just a test thing, it doesn't work as intended. 

R1 Form query #1.png

 

What our Finance department has asked is that, in the form, once the Account Code and Cost Centre has been chosen, who the Account Code budget holder is, and IF there is no budget holder against the account code, then who the Cost Centre budget holder is. I'm trying to create a lookup formula that would do what I need but I'm really struggling. If it is not possible within Nintex Forms, then I will use Workflow to complete this.

 

How I imagine the formula will look like:

IfElse
    (lookup("R1 Account Codes Cost Centres and Budget Holders","AccCode",parselookup(convertToString([Form].[AccountCode]),true),ConvertToString("Budget Holder AC")ISNullOREmpty,
THEN
        lookup("R1 Cost Centres","CostCen",parselookup(convertToString([Form].[Cost Centre]),true),"Budget Holder CC"),
ELSE
        lookup("R1 Account Codes Cost Centres and Budget Holders","AccCode",parselookup(convertToString([Form].[AccountCode]),true),"Budget Holder AC").
 
The Main issue I'm having here is the original lookup of:
lookup("R1 Account Codes Cost Centres and Budget Holders","AccCode",parselookup(convertToString([Form].[AccountCode]),true),"Budget Holder AC". 
 
I have tried multiple versions of the formula and it never seems to return the value from "Budget Holder AC". I have broken down the formula and tried to trouble shoot, but the only part I was able to confirm is definitely working is the "parselookup(convertToString([Form].[AccountCode]),true)" part of the formula. I have tried actually manually entering the filter value instead of using Parse Lookup and I still don't get the "Budget Holder AC" Value. 
 
I completely understand that this is a lot to read, apologies. 
Labels: (1)
0 Kudos
Reply
2 Replies
kchaluvadi
Workflow Veteran

Re: Nintex Form Lookup Formula to return Person or Group

Jump to solution

Hi,

The formula looks correct, but its limitation in Nintex for O365 - New Responsive that the lookup() function will not support "Person or Group" columns at this time. Please see this link for more info on limitations.

 

View solution in original post

0 Kudos
Reply
ConradWilkie20
Canvas Initiate

Re: Nintex Form Lookup Formula to return Person or Group

Jump to solution

Hi,

 

Thank you for your reply and for the link to the current limitations, that's helped a lot. 

 

Looks like it's going to be a workflow then 😂.

 

Thanks again.

0 Kudos
Reply