Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Filter Tool No Records - Formula Tool Fais

Kristie_Pires
7 - Meteor

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!

10 REPLIES 10
apathetichell
16 - Nebula

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
7 - Meteor

Thank you @apathetichell

 

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

- Select tool before the formula tool

Kristie_Pires_0-1634908731903.png

 

Here is the conf. for all the formulas:

Kristie_Pires_1-1634908801986.png

 

atcodedog05
21 - Polaris

Hi @Kristie_Pires 

 

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

Kristie_Pires
7 - Meteor

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
16 - Nebula

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
18 - Pollux
18 - Pollux

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. 

danilang_0-1634999334759.png

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. 

danilang_1-1634999925820.png

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
21 - Polaris

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.

atcodedog05_1-1635001109732.png

 

Hope this helps : )

danilang
18 - Pollux
18 - Pollux

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
7 - Meteor

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

Labels