Adding user entered dates to an existing dataentry script to batch transactions through a repeat loop -- Looking for ideas on methods

[Jason_ME]

I have been using Macro Express for almost two decades now and have been very pleased.  We have a set of scripts that automate a date based series of data entry.  Today the script requires the user to enter the date and a couple of other variables.  These are then used to calculate other values and look up values based upon a matrix.  The script takes about 30 seconds per transaction to calculate and then cycle through the half dozen screens of a legacy application entering the values in various fields based upon the decision tree.

 

We are looking to modify the script to accept a list of user entered dates to then drive a repeat loop through this existing script.  Adding this batching will greatly enhance the utility of the script.

 

We assume it will be using the "repeat with variable" function combined with the "date" variable.  I am looking for ideas on how to do this.

 

Some important notes.  The dates are not linear so its not just simply a start date, end date and then an iteration or offset between them.  The dates could be bi-weekly, twice monthly or once a month depending upon arrangement.  If the date falls on a weekend or holiday, it moves forward to the next working day.  We have another job that can create the date list already to be systemically consumed or manually entered

 

Different solutions could be

 

- Allow the user to copy paste the list of dates into a dialogue prompt and then systematically know to repeat one line per date entered (or have the user enter another variable with the number of dates to drive the repeat loop.)

- It could be a comma (or other delimited set of values) entered into a field and then string parsed.  (or could even say repeat nine times and the user would be responsible for entering 10 dates.).   
- Even if it is a prompt to have the user manually enter the dates into fields in a dialogue box (field var 1 = date 1, field var 2 = date 2) and then enter the number of dates for it to loop, that would be a great improvement over what we have now.  If a user could take less than a minute to enter ten or twenty dates and then let the script run unattended for five to ten minutes, that would be a huge win. 

 

Thoughts on the best way to tackle this?  The folks doing the data entry are seasoned and know the business very well.  It does not have to be perfect.  Good enough to accomplish the task will work and they would appreciate being able to use the tool that way knowing the benefit they will get.

 

Thanks for any ideas to help me out.

 

Jason

 

 

 

[rberq]

Here are a couple initial thoughts:

 

1) Drive the Repeat loop via a file, using Text File Begin Process or ASCII File Begin Process.  Get the dates into the file by any of the methods you have described; or by entering them into a spreadsheet and exporting that (with your macro) to a TXT or CSV file; or just by using something like Notepad.  Then visually review the list of dates and start your macro to process them.  Using a file has the benefit of flexibility, where the set of dates can be very small or very large depending on the user's time availability.  Think about how you will control the process -- that is, how will you keep track of which file(s) of dates have been completed, which are pending, and so on.  Have the macro produce a text file log of each date successfully processed.   

 

2) It sounds like your primary goal is to save on user idle time.  That is, type a date then sit there idle for 30 seconds while the macro fills in a series of screens.  Then repeat until the user falls asleep from boredom, all while there are other important things he/she could be doing.  But is the screen-filling process so reliable and trouble-free that it can run for 5 or 10 or 30 minutes without ANY failures that put the screens out of sync with the list of dates?  How often, now, does the user catch a problem with the screens and have to take corrective action?

[Cory]

You description is verbose, but I'm still left with a vague understanding of what the environment is and what exactly you're doing, and those details would greatly affect how I would approach the job. This is the kind of thing I do professionally and I never get it by the potential client explaining in a message. One reason is the writer often has context already in their mind and doesn't realize that I don't have the some contextual understanding. Second is that often the client has already made up on their mind what for the solution shoudl take despite that usually not being the best way because they don't understand the full capabilities of MEP or other tools. EG I often find that users automate from a table in Excel using arrows and clipboard, when it would be better to link that table to a tab delimited file and link the spreadsheet to that file instead so that the Macro can use ASCII file process. 

I recommend taking my up on a free consultation. There's no obligation at you're likely to learn some things that will benefit you at no cost.

Another would be to break this down into parts I can understand and help you with here. 

I agree with what rberq suggested. I also think this might be something that would be better as a set of macros that either trigger each other or has a master macro that drives them. Also easier for development. 

[rberq]

1 hour ago, Cory said:

this might be something that would be better as a set of macros

 

Yes.  It sounds like you have competent and knowledgeable users.  So you can break down your procedure and give them macros wherever macros can help.  My biggest problem with processes like this was when a manager would say, "My people don't really understand what is going on, just make the computer do everything from beginning to end so they can't mess it up." 🫢

[acantor]

If the users of this system must enter a list of dates during the process, there are ways to reduce their effort. Off the top of my head, I think it would be simpler to standardize the date format, e.g., "DD-MM-YYYY".

 

Let's say the date is stored in string variable %Date%.

 

1. If length (%Date%) does not equal 10 Then signal a problem.

 

2. Ignore characters in position 3 and 6. Then it doesn't matter if the user has used hyphens, spaces, slashes, or some other character as a separator.

 

Alternatively, parse %Date% to delete anything that is not a digit. That leaves an eight digit string, e.g., "01012024." Characters 1 and 2 are the day of the month, 3 and 4 are the month, and 5 through 8 are the year.

 

3. Allow users to omit the year if it's the current year. Let's say %Date% = "0101." If length (%Date%) = 4, then %Year% is Current Year. This shortcut can reduce typing volume by 50%, but may or may not make sense in your work environment.

[Cory]

One of the limitations of MEP is it's designed for one prompt, one variable. Often we need a simple form. However I will say when it gets to that point, it should be a program and not a macro. But I'll toss out some ideas.

Have me write a simple WinForm program for you which you can use with controls from MEP. It takes me no time to do this. 

There's a post here from long ago of using forms in active MHT files. 

Prompt for text in multiline but pre-fill it with things like "Start date: " and another line "Finish date: " and then manipulate the text in MEP to get the desired dates. But you need to make sure and validate the user input carefully. 

 

I've written many simple programs that are a springboard for macros, user interface, or are an extension. For instance i created one that takes a string and executes RegEx on it and outputs tabular data. This way MEP can benefit from RegEx, something not native, easily using the Windows Controls commands in MEP. Imagine it being like Windows Calculator where MEP can access math functions not available natively. 

[Jason_ME]

On 10/5/2024 at 8:01 AM, rberq said:

Here are a couple initial thoughts:

 

1) Drive the Repeat loop via a file, using Text File Begin Process or ASCII File Begin Process.  Get the dates into the file by any of the methods you have described; or by entering them into a spreadsheet and exporting that (with your macro) to a TXT or CSV file; or just by using something like Notepad.  Then visually review the list of dates and start your macro to process them.  Using a file has the benefit of flexibility, where the set of dates can be very small or very large depending on the user's time availability.  Think about how you will control the process -- that is, how will you keep track of which file(s) of dates have been completed, which are pending, and so on.  Have the macro produce a text file log of each date successfully processed.   

 

2) It sounds like your primary goal is to save on user idle time.  That is, type a date then sit there idle for 30 seconds while the macro fills in a series of screens.  Then repeat until the user falls asleep from boredom, all while there are other important things he/she could be doing.  But is the screen-filling process so reliable and trouble-free that it can run for 5 or 10 or 30 minutes without ANY failures that put the screens out of sync with the list of dates?  How often, now, does the user catch a problem with the screens and have to take corrective action?

 

Thank you for the thoughts.  I like this idea since it cleanly solves how to handle a variable number of dates.  The screen-filling process is stable and reliable using a combination of delays and windows activate commands.  Its a legacy program that does something specifically well for our purposes and it is rather hard to replace.  (We have looked!).  Its a combination of save time and quality control / error reduction.  It's some rather tedious data clean up and ongoing data entry so removing friction and aggravation from tasks is also important.  Today it is built from multiple interconnecting smaller macros (I have always preferred microservices to solve complicated issues in code).  The users can preview the results before hitting the go button or can cancel out the macros. 
 

While there are multiple ways of doing this, my criteria for success is something stable and that works well in our environment and culture.  That does not mean its for everyone or every answer will work for us. 

[Jason_ME]

On 10/5/2024 at 9:44 AM, rberq said:

 

Yes.  It sounds like you have competent and knowledgeable users.  So you can break down your procedure and give them macros wherever macros can help.  My biggest problem with processes like this was when a manager would say, "My people don't really understand what is going on, just make the computer do everything from beginning to end so they can't mess it up." 🫢

 

They are and have been doing this a long time.  They know the subject matter and the processes very well.  When something does not make sense, we have a culture where that is brought up.  If there is a better way that makes sense we try it.  These are work aids to assist not a prescriptive controlling tool.  Appreciate the help.  Will update on progress 

[Jason_ME]

I believe I have a solid method to do this using Text File Begin Process / Text File End Process.  Is there an easy way to systematically grab the current system date and time within MacroExplorer?  I want to save off a copy of the source text file with this concatenated to the end of the file name before replacing the source with a blank one as a safeguard.  I would have expected this as an environmental variables for the Copy File/Files command.  It seems like I need to capture this separately, save it to a variable and then add the variable to the file name.  Same question.  What is a good method to do this?

Having this as a supplement to the logging adds another layer for troubleshooting as well as helps avert accidental reuse of the same dates.  Obviously going to use this a smaller macro to add another layer to this.  I see all sorts of uses for this as a reusable subroutine. 

 

Thanks,

 

Jason

[Jason_ME]

On 10/5/2024 at 8:01 AM, rberq said:

 But is the screen-filling process so reliable and trouble-free that it can run for 5 or 10 or 30 minutes without ANY failures that put the screens out of sync with the list of dates?  How often, now, does the user catch a problem with the screens and have to take corrective action?

 

Meant to reply to this earlier.  It is stable.  As long as the user initiates the macro on the proper screen, it works well and no user action required.  If they initiate it in the wrong place, it essentially hangs trying to activate a window that is not open.  The macro uses a series of delays as well as window activate commands to ensure focus.  It is generally the only program running within that VM at the time.  Again, its a simple program but vital.

[rberq]

1 hour ago, Jason_ME said:

As long as the user initiates the macro on the proper screen, it works well and no user action required.  If they initiate it in the wrong place, it essentially hangs trying to activate a window that is not open. 

 

You can double-check the screens and tell the user explicitly what is wrong:

 

If Window "Microsoft Excel - ComputeMF" is running
  Window Activate: Microsoft Excel - ComputeMF
  Delay: 500 milliseconds
  Text Type (Simulate Keystrokes): <CTRLD><HOME><CTRLU> // Go to top of spreadsheet
  Delay: 500 milliseconds
Else
  Text Box Display: Error // Tell user why we are aborting the macro
  Macro Return
End If

 

You can also double-check that the screen activation has succeeded:

If Window "Microsoft Excel - ComputeMF" is focused

 

I have never written a macro that would successfully interact with changing screens for 30 minutes, without SOMETHING going wrong occasionally.

[rberq]

2 hours ago, Jason_ME said:

Is there an easy way to systematically grab the current system date and time within MacroExplorer? 

 

Look at the Date/Time command.  The example below stores yyyymmdd in a text variable, but I believe there are formats that also include time.

[Cory]

VMs are tricky and not ideal for MEP where GUI interaction is needed. But most times it will work as long as the user is active, has the client window visible and focussed in the client software on the client machine. You see if the VM decides no one is there, many events, controls, and maybe more, go into a state of suspension. I suppose to conserve resources. I've had it before when the user would have the VM app open on one screen and then was doing Facebook or whatever in the client machine in another window, and whenever the VM client app lost focussed, things would go haywire.

One thing I found that would often help using any kind of RDP client software like Remote Desktop is to connect to the console session. By default an RDP session will connect to a remote session. There was a command-line parameter to connect to the console session. That's the session a local/normal user connects to. See here a better description. It will likely be different for your service.

Another thing I used with great success many years ago, was VNC. It is simpler and doesn't interact at the control/signal/message level like good RDP or VM client software, instead it transmits all of the visual data graphically and emulates the actual keyboard and mouse commands like a user sat at the console session. This crudeness was a blessing for MEP macros. I don't know if it will connect to a VM. Doubtful. 

Many years ago I had a big client which was a hospital and was running my macros to port data from a billing system to a medical system and back. We could only get it to work reliably with a real computer and VNC. So they grabbed about 20 old outdated surplus retired machines from the IT desktop support department, put them on rolling wire racks in a old supply room, and ran them 24/7. It was humorous to watch them all running. Seemed silly, but it was cheap and effective. 

[Jason_ME]

On 10/15/2024 at 8:05 AM, rberq said:

 

You can also double-check that the screen activation has succeeded:

If Window "Microsoft Excel - ComputeMF" is focused

 

I have never written a macro that would successfully interact with changing screens for 30 minutes, without SOMETHING going wrong occasionally.

 

 

Good advice. 

 

Understood on the length and the issues.  The only hang up at this time is inflexibility in the date mask being used by the program.  Frustratingly it will not accept a paste full data in the MM/DD/YYYY format.  Instead it makes you enter the MM, tab or arrow over, enter the DD, and then tab or arrow over and enter the YYYY.  Existing method worked because it was captured from the user as 3 variables.  This causes an error when it tries to pull it from the file into one variable.  Not sure there is going to be an easy way to do this unless the value from the file can be string parsed.  That starting to get fragile.  Will keep mulling it over.

 

Jason

[rberq]

1 hour ago, Jason_ME said:

This causes an error when it tries to pull it from the file into one variable

 

When you have the date in one variable, parse it with command VARIABLE MODIFY STRING to extract substrings into three separate variables.

For example, copy the first two characters into %var_mm%, the next two into %var_dd%, next four into %var_yyyy%. 

Then TEXT TYPE %var_mm%<TAB>%var_dd%<TAB>%var_yyyy%. 

You may need to use the KEYSTROKE SPEED command prior to typing, to avoid sending keystrokes too quickly -- depends on the receiving screen.

[Cory]

I've never had a problem with the date format order. Can you give me an example? 

And why can't you use the "Choose a Date/Time Format from the Date/Time command, Operation dialog?

A few tricks too. It's been a while and I don't have time to test them out now, but one can be tricky by using only part of the format. IE type or convert the same Date/Time variable by "YY" then another like "MM". I mean imagine doing the command twice or more but only doing the month or other portions. Imagine right now I created a DT variable and had it type it out or save it to a variable with "dd". Only "28" would be output. 

Reverse Date String This is an old article I wrote you might find interesting. I'm not sure if it's relevant anymore. 

[rberq]

Another way to fix the date for entry to your screen -- when variable %dat% contains MM/DD/YYYY -- not exactly parsing,

but same result as my previous example:

// Tab character ascii 9
Variable Set to ASCII Char 9 to %TAB%
Variable Modify String: Replace "/" in %dat% with "%TAB%"  //change %dat% from MM/DD/YYYY to MMtabDDtabYYYY
Text Type (Simulate Keystrokes): %dat%

 

[Jason_ME]

21 hours ago, rberq said:

 

When you have the date in one variable, parse it with command VARIABLE MODIFY STRING to extract substrings into three separate variables.

For example, copy the first two characters into %var_mm%, the next two into %var_dd%, next four into %var_yyyy%. 

Then TEXT TYPE %var_mm%<TAB>%var_dd%<TAB>%var_yyyy%. 

You may need to use the KEYSTROKE SPEED command prior to typing, to avoid sending keystrokes too quickly -- depends on the receiving screen.

 

This should work as you can see from the below date was parsed in variables N1, N2 and N3

The issue is the program the data is being put into and how that field accepts data entry though its mask.  (Seems the "/" are hard coded to the UI, silly).  We run the macro at normal speed, calculated values are  done at machine speed. When we have data entry we interspace 250ms delays at key areas .  System really only needs 80 ms so there is a safety factor. Additionally we wait on window titles to ensure focus.  

For example, the system currently has

 

<COMMENT Value="Start repeat here (Original repeat iterating the transactions for a given date)"/>
<REPEAT START Start="1" Step="1" Count="13" Save="TRUE" Variable="%N[20]%"/>
<DELAY Flags="\x02" Time="250"/>
<TEXT TYPE Action="0" Text="<ENTER><ARROW DOWN><ENTER>"/>
<COMMENT Value="Date Entry"/>
<IF VARIABLE Variable="%N[20]%" Condition="\x00" Value="1" IgnoreCase="FALSE"/>
<DELAY Flags="\x02" Time="250"/>
<TEXT TYPE Action="0" Text="%N[1]%"/>
<TEXT TYPE Action="0" Text="<ARROW RIGHT>"/>
<DELAY Flags="\x02" Time="250"/>
<TEXT TYPE Action="0" Text="%N[2]%"/>
<TEXT TYPE Action="0" Text="<ARROW RIGHT>"/>
<DELAY Flags="\x02" Time="250"/>
<TEXT TYPE Action="0" Text="%N[3]%"/>
<TEXT TYPE Action="0" Text="<ARROW RIGHT>"/>
<VARIABLE MODIFY DECIMAL Option="\x05" Destination="%D[1]%" Text_Variable="%T[20]%" Places="2"/>
<END IF/>

 

Further we have added our standing logging microservice by appending to log files using variations of the following,

 

VARIABLE MODIFY STRING Option="\x12" Destination="%T[4]%" Filename="G:\\#############.txt"

 

There is another set of macros that save them off at regular intervals that are datastamped to avoid them becoming unweildly.

 

Appreciate the help. Will relay information on progress.

 

Jason

 

[rberq]

1 hour ago, Jason_ME said:

Will relay information on progress

 

I didn't follow everything you just said, but:

If you parse file data into variables, it's probably safer to put mm, dd, and yyyy into text variables rather than into integer variables.  That way you can be sure single-digit months and days will contain the leading zero.  I'm not sure that will always be true with integer variables. 

[Jason_ME]

It was probably too much in one message.  Sorry about that.  We have added a lot of logging, time delays and window confirmations to help macro processing and troubleshooting.  Will offer some of that up in separate, clearer messages later to help give back.

The command VARIABLE MODIFY STRING seems to work, once I remember to skip over the "/" in the source data.  i.e. MM starts on the first position, DD starts on the 4th, YYYY starts on the 7th.  (That is a very powerful command by the way!)

Also good call on changing the variable to text and not a number/integer.  I agree that could easily drop the leading zero.  I will try some testing and report back the results.

 

[Cory]

TBH I skipped it. I'm exceptionally busy and didn't have time. These days I am more likely to respond to small, considered, succinct bites. 🙂 

[Jason_ME]

rbheq  That worked very well.  I was able to get this to work as you prescribed.  A few rough patches but otherwise clean.  (I missed adding brackets to one of the new variables and that took a minute to troubleshoot.  I also accidentally reused a variable as part of looping, I forgot to clear it, and resolved that by updating the list we keep of all variables that are used by a given set of macros.) 

The most number of dates we have attempted to loop at once is 40 which takes about 30 minutes.  Works well as we just spawn the work into VMs or on second computers that user access with a KVM switch.  Logging has shown no issues to date.  We were able to process more than a thousand transactions per person in the first morning.  That is definitely an improvement and appreciated by all.  Thanks!

 

Jason

[rberq]

3 hours ago, Jason_ME said:

That worked very well.

 

Glad to hear it is working.  Sounds like a big boost in throughput.