Dynamically create new fields with null values

Question

I am comparing values for a few dozen fields, this month vs. last month.

When there's a new item this month, there is no "last month field" to compare it to.

This crashes the flow when the logic tries to compare "Sales" with "Sales Previous Month"... since the Sales Previous Month field does not exist for new items.

For items with no previous month, how can I create a dynamically changing set of fields for last month, with null or zero values?  I'd Append this empty set to the current month's fields.

I'd like this to be dynamic because the users likely will decide to monitor different fields as time passes.

Thanks for any help!

aatalai · Answer

You can also try using the ensure field tool from the crew macros

dreldrel · Answer

I like your idea @Yoshiro_Fujimori! I used to use Python tool to address this kind of task, but now I have a much easier to do it :)

Yoshiro_Fujimori · Answer

Hi @Newt ,

If the column names follows a specific rules like [name] vs. [name_Previous], you can use Cross Tab as below.

Input Data

RecordIDItemAItemA_PreviousItemBItemB_PreviousItemC110112022102202240422033033606230440448082405505510010250

Output Data

RecordIDNameCurrentPreviousDiff1ItemA1011-11ItemB2022-21ItemC10  2ItemA2022-22ItemB4042-22ItemC20  3ItemA3033-33ItemB6062-23ItemC30  4ItemA4044-44ItemB8082-24ItemC40  5ItemA5055-55ItemB100102-25ItemC50

Workflow

Formula Tool
[Cur_Prev] = IF EndsWith([Name], "_Previous") THEN "Previous" ELSE "Current" ENDIF
[Name] = REGEX_Replace([Name], "_Previous$", "")

CompareCurPrevWithNull.yxmd