Formulas in Lookup search criteria

Answered

Wade Shelton

• January 27, 2021 16:41

I'm trying to set up a Lookup against Netsuite and would like to be able to filter using a text formula as I need to parse values out of some Netsuite fields and compare them against my incoming data.

I haven't found any documentation on how to use text formulas from Celigo in lookup search criteria.  Is this even possible?

0

• Official comment

  

  Courtney Jordan Experience Strategy & Design Director Community moderator

  • February 19, 2021 17:49
  • Edited

  

  

  

  Hi Wade Shelton,

  The join handlebars helper can be used to concatenate the "Meeting Type" and "Expense Type" field values, which can then be used as the value to search in NetSuite. From your substr example above, you would instead use: {{join " : " data.[Meeting Type] data.[Expense Type]}}. Let us know if this works!
• 

  Courtney Jordan Experience Strategy & Design Director Community moderator

  • January 27, 2021 17:41

  

  

  

  Hi Wade Shelton,

  Can you provide more information or an example of the type of text formula you're referring to? What other applications are you using and what problem are you trying to solve? Thanks!

  0
• 

  Wade Shelton

  • January 27, 2021 18:20

  

  Courtney

  I'm trying to make some adaptations for the Concur Expense Reports Integration.  The OOB template doesn't handle some master data very well (like Concur Expense Categories), so I'm trying to update the flows to accommodate them.

  In this case, I have an expense Category Name and "Meeting Type" in Concur.  The intersection of those two values should yield an expense account.  

  I've set up the Expense Categories in Netsuite to hold both sets of information using a standardized format of "Meeting Type : Expense Category".  For example... "NORMAL EXPENSE : FOOD".

  The JSON returned by the API has these as separate fields, though.  i.e. - Meeting Type: Normal Expense; Expense Type: Food

  I tried using a search criteria using "contains" with both values looking to Category Name, but there is overlap in the expense types.  One may be "Food" where the next is "Food Samples".  Contains will match both, which causes a failure.

  So, I'd like to use a formula to parse the meeting type out of the category from netsuite and compare it to the fields from the JSON.  Something like this, but I can't find any way or documentation on how to make it work.

  SUBSTR({name},0,INSTR({name},' : ')) = {{data.[Expense Type]}}

  0
• 

  Courtney Jordan Experience Strategy & Design Director Community moderator

  • January 27, 2021 19:48

  

  

  

  Hi Wade Shelton,

  Thanks for this context! I'm checking on this with our experts, but for faster resolution, I'd recommend that you either attend Office Hours or submit a support ticket (or both).

  0
• 

  Courtney Jordan Experience Strategy & Design Director Community moderator

  • January 28, 2021 00:31
  • Edited

  

  

  

  Here is more info on the join handlebars helper. I updated the above comment, but you won't likely see an updated comment, so including this comment for your visibility.

  0

Please sign in to leave a comment.