Accomodating ad hoc adjustments in a workflow

[tniceanm_10]

Hi,

I have a workflow that calculates a value based on the sum of various columns, with each column representing an input. Some months there are new adjustments which are new input sources.

If there any suggestion on how I can just design an input step in the  workflow that would accommodate all of the inputs easily so that a one time adjustment could be added when needed ?

Thanks

[JoBen]

Hi @tniceanm_10, would you be able to attach your current workflow or a screen picture of how it is currently configured?

[tniceanm_10]

Hi @JoBen,

In the capture screenshot want to figure out a way that I could better accommodate the adjustments. Some of the adjustments occur every month but then there are new ones that are not in the green adjustments section that come up and are not programmed for in the workflow so I would have to add any new adjustment as a new input if one should occur. I was trying to figure out if maybe there is a better way or would it be best to create some sort of  separate work flow or step that loads up new adjustments in a file and send them to designated columns.

[MarqueeCrew · Accepted solution]

One option would be to store adjustments into a field that you either lookup with a FIND REPLACE tool or use a JOIN tool if there are multiple keys to the calculation.

Then adjust your formula to include the adjustment.  When adjustments are not present, the formula needs to be smart enough to accept no value.

Example.

[Field1] * [Adjustment] 

When .85 is present for the adjustment, that makes sense, but when null is present, you'll have a problem.  So this is better:

[Field1] * IIF(IsEmpty([Adjustment]),1,[Adjustemnt])

cheers,

Mark

[JoBen]

Would all the adjustment excel files come from the same directory?

[tniceanm_10]

Hi @JoBen,

Yes all would be in same directory.

Thanks,

tniceanm_10

[JoBen · Accepted solution]

Okay. I think your problem could be solved by using a directory input with a *_Adj.xlsx for the file specification. From there you could use a dynamic input tool and set it up to read the full path. I've attached pictures below. Would this help?

Directory Setup

Dynamic Input Setup

[tniceanm_10]

Hi @JoBen,

I will try and see if it works.

Thanks !

[tniceanm_10]

Hi @MarqueeCrew,

Will try and see if it works.

Thanks !

[JoBen · Accepted solution]

Okay. Sounds good. If your sheet names vary, you can always use the Input Data tool with the wildcard (*). It will then let you select the sheet names, which you can append to your original stream. You can unite the file path with the sheets with a "|||" in a formula tool, and then run through the dynamic input tool to see what you get. I've attached an example below. Because I had multiple sheets, I created a batch macro to capture every sheet on my excel file. Anyway, good luck!