This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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.
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
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.
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.