Note, reading the first two articles will help your understanding of part three greatly. If you already know what value lists are then you should probably be able to follow this article. In part one, we built a couple of basic conditional value lists to select sport names & equipment items. Part two demonstrated how the conditional value lists could be tweaked for use within a portal. The last part in the series gives some basic techniques to help the selection process from your drop-downs, and ensure that the chosen values remain consistent and correct.
A Possible Scenario…
Typically when using conditional value lists, the whole idea is that the user will select values in a specified order. In this case, the user should select a sport category first, followed by a sport name, and finally a sport equipment item. If the user were to try and choose a sport name without a category, the value list would not be present, given that no category was chosen. The same applies when choosing a sport equipment item without a name or category chosen.
By placing the fields in order on the layout, this provides an intuitive order for the user to select items, and in most cases this is sufficient, the user will quickly learn the order in which to select items.
Lets assume now that a user has chosen a category, name and equipment item. What happens now if they decide they chose the wrong item, and have to repeat the process?
If the user now chooses a different sport category, this may happen:
The new category is chosen, but the sport name & item remain unchanged. The name & item no longer match the chosen category.
Because the user is going to proceed and select a name & item in most cases, this may not seem like an issue. But we can automatically clear the name & item fields to ensure they do not leave it as it stands. This can be done with simple auto-enter calculations.
Putting it into english, we want the following to happen:
• When Sport Category changes, clear Sport Name
• When Sport Name changes, clear Sport Equipment
By way of inference, when sport category changes, we would expect sport equipment to also clear.
Auto-Enter calculations are calculations which will re-evaluate themselves when any referenced field within the calculation changes it's value - so long as the referenced field is a) in the same table, and b) is a stored (indexed) value.
The auto-enter calculation for the Sport Name field looks like:
Let ( Trigger = Sport Category ; "" )
In this calculation, the referenced field is set as Sport Category, and the result is to set the field (Sport Name) to blank. Note that a Let statement has been used, for the sole purpose of referencing another field, without having to use that field in the calculations result. this is a great technique for any auto-enter calculation you want to trigger.
Similarly, the Sport Equipment fields auto-enter calculation is:
Let ( Trigger = Sport Name ; "" )
Whenever sport name value changes, sport equipment is cleared.
By way of inference, changing Sport Category forces Sport name to re-evaluate, clearing itself - and this action forces Sport Equipment to re-evaluate, also clearing itself.
To see this in action, check out the example file.
Script Triggers to Ensure Correct Selection of Fields in Order…
Script triggers can also be used to ensure that the user selects fields in the specified order. Imagine a simple layout with our 3 fields on it. The user must choose the category, name, and item in that order.
If the user clicks into either the sport name field, or the sport equipment field, without having a category chosen, this action should be disallowed.
A simple script can be written to achieve this, and attached to the onobjectEnter trigger for each field. Consider the script for the Sport Name field:
If [ isEmpty ( Sport Category ) ] Go to Field [ Sport Category ] End If
If the user tries to enter the sport name field, without there being a value in sport category, the script will automatically put the user into the sport category field, in the hope they will then select a category.
A similar script can be written for the Sport equipment field:
If [ isEmpty ( Sport Category ) ] Go to Field [ Sport Category ] Else If [ isEmpty ( Sport Name ) ] Go to Field [ Sport Name ] End If
In this case, the script first ensures a category is chosen, and if not, the user is taken to the category field. If a category is chosen, but a name is not, the user is put into that field. If both category & name are chosen, the user is allowed to proceed and choose a sport equipment item.
Please find attached an example file. This file was used for all the screenshots in this article, and is fully commented to help you fully understand what is going on in the file.