Applying formulas dynamically

[MKanchwa]

Hi Folks,

I have a data set 

I have added 3 new columns used a formula tool with the below formulas in it

But if any one of the column is removed from the Data set, the formula tool should not use the formula related to that column.

For Eg - If column "Item No C" is removed from data set then the formula tool should drop the third formula which uses column "Item No C" otherwise it will throw an error. It should be done dynamically.

Thanking you in advance.

Mustafa

[AngelosPachis]

Hi @MKanchwa ,

What you can do to make this dynamic is to first transpose your columns to rows. Then that error won't appear if one of the columns is not present

Hope that helps, let me know if that worked for you.

Regards,

Angelos

[echuong1 · Accepted solution]

A more dynamic method of achieving this is with a multi-field formula. Essentially, this will allow you to deploy a formula to multiple columns at once - simply check off the fields that the formula should be applied to at the top. There is an unknown fields as well, so you can account for any new fields that show up later on. If a field is removed from the dataset, it will just ignore it, similar to a Select.

[AngelosPachis]

@echuong1 your way is much simpler I think.

The only drawback that I can think is that if a column is not present, then the multi-field formula tool will throw a warning to you which might be disturbing if you are working with a big dataset.

On the other hand, this might also be a plus because the tool gives you a heads up. Really cool and it didn't cross my mind.

EDIT : the warning was not a cause of the multi-field formula. Advice of the day is to read the whole warning sentence 

Thanks

[echuong1]

The multi-row will actually not throw a warning when a field is removed (a little different than the Select)!

In the screenshot below, I removed the column for item B and re-ran. As you can see in the messages of the multi-row, no warnings! There was an error for the hardcoded formula though 😉 

[AngelosPachis]

Hi @echuong1 ,

That's strange, when I ran it on my workflow (by just adding that extra multi-field formula tool) I do get a warning.

However, in your workflow, when I tested it and removed a column, I didn't get any warning although the tools in your workflow and mine are configured exactly the same. That's very strange.

I'm attaching my version of workflow so you can see for yourself. What do you thing causes this?

The 1 million dollar answer here is... the transpose tool 🙂 

[AngelosPachis]

Ignore me please @echuong1 , it's the transpose tool, I take everything back.

[SeanAdams]

You could also achieve this using a batch macro; after transposing the data.   The benefit of the batch macro approach is that you can so something like "if [name] = 'field c' then..." to have different formulae.

You may also be able to achieve this with a dynamic replace tool - although these can be a little tricky to use.