Notify when file's dates are inconsistent

[anhdo92]

I have the following two excel files to reconcile 

File 1 :

File’s name System A 20210913 where 20210913 is date formatted as YYYYMMDD

File 2:
File’s name is System B 13092021 where 13092021 is date formatted as DDMMYYYY

I would like to set up a scheduler for my workflow to run automatically following the rules below:

• If the dates from two files are different, the workflow will stop running immediately and will send an email to notify recipients of the inconsistent dates
• If dates from the two files are the same (13 Sep 2021 in this example), the workflow will be able continue to run and reconcile the two files. The reconciliation result would then be output into an excel file consisting of 2 sheets
  Sheet 1: Matched records from the two System A and System B files
  Sheet 2: Unmatched records (anything records from System A but not found in System B)
  Finally, the workflow will send out an email with output file attached to relevant recipients.

Any idea is much appreciated! 

Thanks

[RolandSchubert]

Hi @anhdo92 ,

you can use Directory tools to find the filenames (I think, only the "date part" is changing). You can extract the date from the filenames, convert both dates to  date format and compare.

 

To  execute the workflow only if both dates are the same, you can use a macro with a Condition tool and a Tool Container. If check of dates isn't successful, the container will be disabled and the steps are not executed, if dates are the same, the container is enabled, next steps are executed. You can add the required mail functionality in a similar way (mail to warn, that dates do not match) and add an Email tool (use Block Until Done - anchor 1 for Output tool, anchor 2 for Email).

 

What do you think? Let me know if it works for you.

Best,

Roland

[anhdo92]

@RolandSchubert  thanks a lot Roland. I can't seem to open up your workflow to have a closer look tho... Upgrading to latest version will take a couple of days for me to sort out governance paperwork.. Is there any chance to convert it back to the older version I'm using (2019.3 x64) ?

[RolandSchubert]

Hi @anhdo92 ,

I've attached the workflow and the macro in version 2019.3, but you can modify the version by editing the file using a text editor (Notepad, Notepad ++). Replace the version I used to create the file with the one you currently run.

Does it work for you?

Best,

Roland

[anhdo92]

HI @RolandSchubert 

Is it possible to get the workflow to send email in both cases

When the dates are not consistent - send email to notify 

When dates are consistent - continue to do the reconciliation and then send another email with recs result attached ?

And after the macro, what I would like have the output in excel. The current macro generates 2 separate workflows

Thank you

[RolandSchubert · Accepted solution]

Hi @anhdo92 ,

I've added the EMail tools now, both are not configured, but I think it may help to clarify the logic of my approach.

There are 2 Tool Containers, depending on the value of "Check" (dates are different or the same), one of the Containers is enabled, the other one is disabled and either the join is executed and result mail is send or the error mail is send without executing join.

Best,

Roland

[anhdo92]

Fantastic! Thanks a lot @RolandSchubert  for your help 🙂