Notify when file's dates are inconsistent
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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) ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Fantastic! Thanks a lot @RolandSchubert for your help 🙂