How should I structure and optimize my workflow to accept a new input for each day?

[hellyars]

Each day I get an Excel file of potential opportunities.    The Excel is the input to a workflow that attempts to filter to only those opportunities that meet certain criteria.

The structure of the Excel file remains the same each day.  Each Excel input has exactly the same fields.  Only the number of entries vary each day.  The only other exceptions are the filename and sheet name, both of which reflect the date they were created. 

As started, the workflow only accepts one input. 

Questions:

1. How can I update the workflow such that I can select the file I want to run through workflow?
2. How can I update the workflow such that it automatically runs the most recent file added to the source directory through the workflow?
3. How can I most efficiently combine all the workflows into a single file and append (join) each new day's file to that master file?

I have attached a crude sample of the workflow as well as 3 sample input days. 

Help and thank you.

[CharlieS]

If you haven't found the Directory Input tool, that will be your friend in this process. Point that to the file directory and it will return the names and other file properties of the files within. This information can be used to open the desired file with other input tools.

Now if the sheet names were all the same, a Dynamic Input after the Directory Input would be all that you needed to complete this task. Since the sheet names differ, there will be a few more steps. Would it be possible for the sheets to share a consistent name, while the file names continue to have the dynamic date name? 

It's also worth mentioning that you can reduce the number of filters in your workflow by combining multiple conditions in one filter. For example, one Filter can be configured with the following custom filter to replace 5 tools.

[CompetitiveGroup] not in (1,2,3,4,5)

An alternative is to use multiple conditions in one custom filter:

[CompetitiveGroup] != 1

AND

[CompetitiveGroup] != 2

AND

....

[hellyars]

I figured how to setup the workflow to automatically run the most recent file using the following example.

You can't select a specific file, but it is one the right. path and something I will use once I have everything sorted out.  

[hellyars]

I am new to the Dynamic Input tool.  I tried using it and ran into the issue you point out.  The Excel inputs are auto-generated by a 3rd party system, so I don't know if I can get the sheet names to change without manually doing it. 

I hear you on the filters.  I had been playing with different outputs for each step thru the filters.   

[CharlieS]

This is still possible, but the next step I would take is to build a batch macro. Use the Directory Input tool and configure the Dynamic input to return the list of sheet names. Combine that information into a string that can be input into a batch macro that reads that file|sheet specified.

[neilgallen · Accepted solution]

Agree with @CharlieS on the idea of using a batch macro, although it seems like that might be a step too far, so I built this out instead. See the attached version.

This will read the directory, parse out the date from the filename and then convert the date to a proper format (because sorting on the date in string form can cause problems). The sample tool selects the most recent date file and then creates a formula to feed into the dynamic input tool because the sheet names change based on the date.

The back end of the workflow is one way you could have a master file. The key here is to use the block until done tool.

Good luck!