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.