topic Re: Help with Formula- in Workato Pros Discussion Board

Help with Formula-

tstack — Sat, 09 Jul 2022 01:05:07 GMT

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.

Re: Help with Formula-

jeff-allen — Sat, 09 Jul 2022 01:44:03 GMT

Try adding the & to your Lookup function as well. This would apply safe navigation to both calls.

Re: Help with Formula-

rachelnatik — Sat, 09 Jul 2022 05:27:57 GMT

I don't think you can have 2 "ELSE" statements. You would need to use a different formula such as elsif to carry this out.

Re: Help with Formula-

roland-daane — Sat, 09 Jul 2022 19:59:47 GMT

Create a variable, and if datapil 1 is not present set it to 0.

Then use that variable in the formula.

On top of that keep a reference of the received null values and use that to update the source.

Get Outlook for iOS

Re: Help with Formula-

meghan-legaspi — Mon, 11 Jul 2022 19:05:10 GMT

Hi Tracy Stack! Did any of these suggestions work for you?

Re: Help with Formula-

tstack — Mon, 11 Jul 2022 22:45:30 GMT

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.

Re: Help with Formula-

jeff-allen — Mon, 11 Jul 2022 23:54:24 GMT

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,

Re: Help with Formula-

nikhitha-thatra — Wed, 13 Jul 2022 12:28:53 GMT

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

Re: Help with Formula-

nikhitha-thatra — Wed, 13 Jul 2022 13:11:49 GMT

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

Condition1?(condition2?exp1:exp2):exp3

Re: Help with Formula-

sanjay-rathod — Wed, 13 Jul 2022 19:43:56 GMT

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])

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] + ""

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]

Re: Help with Formula-

nikhitha-thatra — Wed, 13 Jul 2022 19:53:23 GMT

Let me know if it works & satisfies your usecase or not.