Dynamic Input (with few rules)

[mohit9garg]

Hi,

I need your help to construct a Dynamic Input tool with few rules & conditions.

• In a sharedrive path (a fixed directory)
• we would have a folder (somewhere there) for last working day and that date would be the folder name; for example, if today's date is 25th May then folder name would be 20200522. But date format is not fixed.
• the file name would have 'xyz' but should not have 'pqr'; file name would also have the folder date. like 20200522; but again date format is not fixed.
• File format could be any excel format (.xls , .xlsb , .xlsx..)

Then workflow should process that file.

Kindly guide me.

Mohit

@grossal @DavidP 

[grossal]

Hi @mohit9garg,

I think the first thing you need is the directory tool for the shared drive - make sure to Include SubDirectories.

After this we can use a Filter-Tool to figure out the right file. The filter probably needs something like:

Contains([FileName], Replace(DateTimeToday(),'-','')) 
AND NOT Contains([FileName],'second condition')

You could pass this information into a Batch Macro that reads in the file. This should look like:

I would probably tick "Auto Configure by Name" - just in case:

The main workflow would look like this:

Let me know if this helps. I won't be able to build an exact workflow because I don't have access to your drive etc, but this should give you a good idea how to continue.

Best

Alex

[danilang]

Hi @mohit9garg 

@grossal gave you a great start on this.  The biggest remaining issue is your statement that says "the date format is not fixed"  The suggested solution gives you the date part of the directory and file names in yyyymmdd format.  Which other formats are possible?  If the format isn't fixed, how do you handle ambiguous dates?  20200102 could be Jan 02 or Feb 01

Dan

[mohit9garg]

Hi @danilang - Thanks for noticing. I am putting this ask aside for the time being. Let me try to build a workflow with fewer conditions. 🙂

Thanks @grossal for the solution. But could you please advise how could I achieve below-

• we would have a folder (somewhere there) for last working day and that date would be the folder name; for example, if today's date is 25th May then folder name would be 20200522. But date format is not fixed.

I need to find the folder fist in the main Directory which contains the date of last working day; which is in yyyymmdd format. Because actual excel file won't have the date.

Thanks,

Mohit 

[grossal]

Hi @mohit9garg,

I'd do this in a three step process:

What happens:

1) I create a helper column with the Weekday as a String

2) We correct the date for weekends

3) We create the string in your format.

I'll post the formulas below for easy copy paste.

//First formula
DateTimeFormat([Date],'%A')

//Second formula
IF [Weekday] = 'Saturday'
THEN DateTimeAdd([Date],-1,'days')
ELSEIF [Weekday] = 'Sunday'
THEN DateTimeAdd([Date],-2,'days')
ELSE [Date]
ENDIF

//Third formula
DateTimeFormat([Date],'%Y%m%d')

Best

Alex 

[mohit9garg]

Thanks @grossal for your prompt response.

To get the last working day, I think our formula should be like-

If Today ="Monday"

Then [Date}-3

ElseIf Today ="Sunday"

Then [Date}-2

Else [Date]-1

To get the required folder Should I do like this (please excuse my ignorance)

[mohit9garg]

@grossal @danilang  Could you please help me here.

[grossal]

Hi @mohit9garg,

sorry for the late response.

Just add another Formula Window add the top, name the column 'Date' and use the Formula DateTimeToday(). Make sure the Type is set to Date.

I am not quite sure why you would want that Monday is replaced with Friday or I understand you wrong. I thought you'd want to always have the last workday based on the current date. Therefore it should look like this:

Current Day	Last Workday
Monday	Monday
Tuesday	Tuesday
Wednesday	Wednesday
Thursday	Thursday
Friday	Friday
Saturday	Friday
Sunday	Friday

Best

Alex

[mohit9garg]

Hi Alex @grossal 

Hope you are doing good. Thank you (as always) to look into my query.

I mean with last working days as below-

Today's day	Last Working Day
Monday	Friday
Tuesday	Monday
Wednesday	Tuesday
Thursday	Wednesday
Friday	Thursday
Saturday	Friday
Sunday	Friday

Could you please build a workflow for me as I am getting the errors.

Regards,

Mohit

[grossal]

here is a minimal example: 

Formula:

Result:

Workflow attached.

Best

Alex