Help with Formula-

[tstack]

Hello! I'm struggling with an error in one of my formulas 'Formula received a nil value: undefined method `+' for nil:NilClass' After doing some research, I was thinking that using "&" for safe navigation, but still getting the above error.

This is the formula that I am using:

[DATAPILL1]&.presence ? lookup("TABLE_NAME", "FIELD_NAME": [DATAPILL2) ["FIELD_NAME"]+[DATAPILL2]: [DATAPILL3]

the Datapill1 that I'm using to look for the Lookup Table, comes in blank or null every once in awhile. In that case, I want to populate it with Datapill3. However, I am receiving the error rather than the data being replaced with Datapill3 which should be the second condition. Any advise would be great.

[tstack]

I couldn't get any of the above suggestions to work. Basically, I'm trying to use a conditional with a lookup table. I was able to work around it by creating another lookup table with the data that I need rather than using the formula.

[jeff-allen]

Hello Tracy,

Depending on where the data is coming from that you are using in your lookup table, you may want to condider using Collections. We use Collections for some of our more complex lookups and joins. It allows you to work with the various data sources as SQL tables,

[nikhitha-thatra]

Hi Tracy Stack,

You can use ternary conditions for this logic "Condition?expression 1: expression 2"

When using Datapills it can be defined as:- "Datapill1.present? ? exp1:exp2".

Docs Link: https://docs.workato.com/formulas/conditions.html#conditionals

[nikhitha-thatra]

If you have multiple conditions to check, you can use ternary conditions inside a ternary.

Condition1?(condition2?exp1:exp2):exp3

[sanjay-rathod]

From the error message it looks like lookup("TABLE_NAME", "FIELD_NAME": [DATAPILL2]) this returns null(blank) value

1. Make sure that you have data for lookup("TABLE_NAME", "FIELD_NAME": [DATAPILL2])

OR

2. Add the quotes and contact before and after lookup to convert expression result to string. New Redefined expression should be like following:

[DATAPILL1]&.presence ? "" + lookup("TABLE_NAME", "FIELD_NAME": [DATAPILL2]) ["FIELD_NAME"] + "" + [DATAPILL2] + "" : "" + [DATAPILL3] + ""

OR

3. Add variable with following expression and use this variable in your expression:

Variable Temp1:

(lookup("TABLE_NAME", "FIELD_NAME": [DATAPILL2]) ["FIELD_NAME"]).present? ? (lookup("TABLE_NAME", "FIELD_NAME": [DATAPILL2]) ["FIELD_NAME"]) : ""

Your expression will be:

[DATAPILL1]&.present? ? [Variable:Temp1] + [DATAPILL2] : [DATAPILL3]