Time to enjoy the fruits of your hard work by maximizing the potential of Template Helper.
Last week, we explored how to build the Template Helper to detect and replace keywords in your template. This week, we would discuss ways to maximize the potential of Template Helper by designing a convenient user interface.
Repetitive input is a waste of time
Sometimes, the input for a certain parameter would always be the same. For example, [PHARAMCIST] would always be Chris Chan. [DD/MM/YY] would always be the today’s date, e.g. 31/12/2021. When we create tons of templates repetitively, the system would prompt you to input value for [PHARMACIST] and you would type the same input, i.e. Chris Chan, every time. This is incredibly time consuming. If we could pre-define values for parameters, that would save a lot of time.
Note: Entering Chris Chan manually every time is a waste of time.
1. Pre-define values for parameters
Let’s insert a new sheet called “Default Value”. We put the parameters in Column A and the pre-defined values in column B. In cell B2, we can input formula =TODAY() to return today’s date. In cell B3, we can input formula =TEXT(NOW(), “h:mm”). The NOW() function returns the current date and time, e.g. 31/12/2021 23:56. The TEXT() function lets you change the way a number appears by applying formatting to it with format codes. By specifying “h:mm”, it would returns the time only, i.e. 23:56.
2. Assign available pre-defined values to parameters
In the previous example, we asked for user input for every parameters we detected. Now, we would like to loop through the pre-defined parameters before prompting user input. If a parameter is pre-defined, we use the pre-defined value. Otherwise, we prompt user input for that parameter.
Once again, we utilize For-Loop to loop through rows in the sheet “Default Value”. We check whether the current parameter in array matches the parameter in sheet “Default Value” column A. If yes, we check whether the column B in sheet “Default Value” has pre-defined value; if yes, we can assign the pre-defined value to the ParametersInput(i); otherwise, we prompt user input for that parameter.
When all parameters in sheet “Default Value” column A are looped through with no matching results, that indicates no pre-defined value for that parameter and we prompt user input.
Exit For is used when we gather our desired input for a parameter. This saves time by avoiding further unnecessary looping.
Let’s look at the result:
3. Drop down menu – the convenient, user-friendly tool to save users’ time
We can further optimize our user interface by using drop down menu. Let’s use another template called Medication Review template as an example.
Medication Review template
sheet “Default Value”: to save commonly used inputs in a drop down menu
For example, the point of contact (cell B4) would always be patient or patient’s family members/caretaker. Awareness of medication change (cell B5) should always correlate with the point of contact (cell B4) by using this formula:
Instead of typing twice upon system prompt, we can pre-define values in the same row from column C to column H and create a drop down menu.
Press “Data” tab –> “Data Validation” –> “Data Validation”.
Choose List and manually type in the source “=$C4:$H4”. Since we are in cell B4, we want our drop down to have values from C4 to H4. Using a $ symbol before columns lock the specific columns C to H. We deliberately not to include $ symbol for rows so that we can drag and AutoFill for other cells illustrated in the video below.
Upgraded Template Helper illustration
Let’s see the how the upgraded Template Helper works:
For parameters with common values, using drop down lists is the preferred method. On the other hand, for parameters without common values, we can leave pre-define parameter values blank and input values when the system prompt for inputs.
Summary
The upgrade Template Helper saves time by recognizing and using pre-defined parameter values instead of prompting user input. Utilizing drop down lists for common values can further save time. With the core codes built, we just need to alter a few lines of code and design the user interface to suit the user’s need. This is how we can maximize the potential of core codes.
Once again, thank you very much for your support. Please feel free to leave any comments down below. 🙂 Your comments are greatly valued.
Key takeaways
- Think how to design interfaces to tailor users’ needs so that the value of your core codes is maximized
- In-cell drop down lists is a convenient, user-friendly tool to save users’ time
Full code
