Filter Tool No Records - Formula Tool Fais

[Kristie_Pires]

Hi everybody,

I've noticed that when the filter tool gives me zero records, the formula tool fails to work. I get the following error: "Parse error at char (11): unknown variable "Amt_Requested".

Is there a tool that can stop that section of the workflow from running if I get zero records after the filter tool?

Thank you so much for helping me out!

[apathetichell]

I think you are getting that error for a different reason. Can you post the config of your formula tool and a select before it?

[Kristie_Pires]

Thank you @apathetichell. 

I can't share the actual workflow but here is a screenshot

- Select tool before the formula tool

Here is the conf. for all the formulas:

[atcodedog05]

Hi @Kristie_Pires 

I dont see Amt_Requested column in the select tool which is used informula and giving the error.

[Kristie_Pires]

You are right that the Amt_Requested column is not in the select tool. However, even if it is not present sometimes the workflow works without any issues as long as I have records coming from a filter tool. As of right now, there are no records from the filter tool. So, I'd like to know if there is a formula or tool, to stop that section of the workflow from running if no records are present.

[apathetichell]

If Amt_Requested is not null it might not check for [amt requested] - if it is null this will crash. there is no value there - unless it's a dynamic field. You need ot have this field you are testing for.  If you have a filter - this part of the workflow won't run. But perhaps you can just correct this formula tool so that it references a field that actually exists? That would be my strategy. I don't want to speak for @atcodedog05  - and it's 11 p.m. on a Friday night his time - but I have a feeling he would agree.

[danilang]

Hi @Kristie_Pires 

The formula that you're using for amt_requested and unnassigned_apps will never work unless the field actually exists when the formula tool was originally created.  The reason for this is that you're referencing the field itself in its definition. 

Here my Input data only contains the field [amt requested] so we get the error that [amt_requested] is unknown.  

In my second example the [amt_requested] is in the input but the filter is configured to not allow any records. 

In this case the formula is correct, since the field is defined before the formula tool even though no records pass through the filter 

As to why this might work sometimes and not others probably has to do with your source data.  I would hazard a guess that sometimes [amt_requested] is actually in your source data and that it was available as a field when the formula was defined.  The preceding Select tool has Unknown checked and will pass [amt_requested] if it's there so the formula will be valid.  If the field isn't there the workflow will fail.

Dan

[atcodedog05 · Accepted solution]

Hi @Kristie_Pires 

As you mentioned that columns might be missing sometimes and as @apathetichell & @danilang mentioned this issue is being caused by missing columns referenced in the formula.

What you can do in this scenario is something like below

Input Data: Lets say input data doesn't have amt_requested column

amt requested

100

300

Workflow: You can design your workflow like below. You can have one text input tool with the all the required headers we will union that with the data. What this does is even though column is not present in input data the headers will add the all-required column. This way your formula should still be intact and not cause errors. Refer below ran with no errors.

Hope this helps : )

[danilang]

Good suggestion @atcodedog05 

I would follow up the Header template with a Select tool to force the proper field types.  Since there are no records in your header, the fields default to strings which then changes the types of all the fields to strings after the union.

Dan 

[Kristie_Pires]

Thank you all for helping me understand what the issue was and also for providing a solution!! 🙂