Accomodating ad hoc adjustments in a workflow

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

one time adjustments.xlsx

Workflow

Input

Best Practices

Accepted answers

MarqueeCrew

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

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

JoBen

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!

All comments

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.

Capture.PNG

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703