Thinking About Value Lists, part 3

Welcome to the third installment in this series. We’ve been exploring various issues and behaviors in connection with 2-column value lists (VL’s), and today we’re going to look at, and propose a work around for, an issue with filtered value lists in Find mode.

All of today’s demo files feature a basic expense submission system, with each portal row representing a receipt that the submitter would like to be reimbursed for. Here it is in Browse mode:

And here’s the Relationships Graph:

And of course you are invited to download and explore the demos if you are so inclined.

• Thinking About Value Lists, part 3 demo 1
• Thinking About Value Lists, part 3 demo 2
• Thinking About Value Lists, part 3 demo 3

When adding a receipt, the user typically works from left to right, inputting the receipt date, choosing a department from the popup menu…

…and then choosing the account number from a filtered value list (based on department):

At this point the related description appears, and the user types in the receipt amount.

I should point out that we also have a “power-user” feature, where the user may type or paste an account number directly into the account number field, in which case the department will be looked up (auto-entered, actually). In other words, if they already know the account number, they don’t have to first choose the department from the popup menu. The need to support this feature guided some of the choices I made re: 2-column dropdown lists vs. the popup menus we saw in part 1 and part 2 (which were also 2-column, but with only the second column displayed).

But most users choose the department first, and then pick the account number from the filtered value list. And (at the risk of stating the obvious) since the value list on the account number field is dependent on the department value, it will not “drop” if the user has not first made a valid entry in the department field. However, assuming the user has made a valid department selection, the account number value list will display properly.

In Browse mode at any rate.

In Find mode things aren’t quite as clear cut. If we head over to the Receipts layout, which conveniently has the same value list setup enabled (but in Find mode only, since we don’t want users editing data here)…

…and if we enter find mode, make a choice from the department popup, and then click into the account number field, sure enough, the filtered value list displays:

But returning to the Expenses layout, and attempting to perform a similiar search in the Receipt portal, we discover that the account number VL does not display.

So we need to come up with a work around, which brings us to demo 2… and before we dig into how it works, let’s just confirm that it really does:

Okay, it does work, but at the cost of having to add some new “moving parts” to the system. They are:

1. A global field, Receipt::g_department
2. A new table occurrence, Account_by_gDept
3. A relationship between them…
4. A 2-column conditional value list based on the preceding
   
5. A second “instantiation” of the Receipt::account_number field, to attach the above value list to, overlaid on top of the original one. The original will be accessible in Browse mode and the new one will be accessible in Find mode.
   
6. A script to populate Receipt::g_department
   
7. …which is attached to the department popup menu and triggered in Find mode only:

And with all that work, surely we’re done… right?

Almost. There are a couple minor Find mode issues remaining in demo 2:

• Once the account number has been chosen, the description field should not be empty:
  
• The value in g_department is not being cleared, so on subsequent finds, if the user clicks into the account number field, the filtered value list will display (we don’t want anything to display here unless the user has first chosen a department):
  

And these issues have been resolved in demo 3.

To address the “missing description in Find mode” issue, we’re going to leverage the store-the-first-column, but-display-the-corresponding-value-from-the second-column behavior we examined in Thinking About Value Lists, part 1. Here’s what needs to happen:

1. We want users to be able to do ad-hoc searches in the description field in the portal, so we’re going to leave it enterable in Find mode. But let’s turn its text invisible via the 500 point conditional formatting trick.
   
2. Next, let’s define a new value list, “All Accounts 2nd Column”
   
3. And now we can format it as a transparent popup menu attached to a new instantiation of Receipt::account_number, and overlaid more or less on top of Account::account_description — I had to vertically offset it a point or two to get things to look good. Also, since this is for display purposes only, do not allow any field entry.
   

Okay, that takes care of the first issue. What about the second one (clearing out g_department)? That’s less work. All we need to do is…

1. Define the following script…
   
2. …and attach it as a trigger to the Expenses layout, like so:
   

And those two issues have now been resolved.

Before we call it quits, I should point out that the count and amount at the bottom of the Expenses layout are related summary fields from the Receipt table. And to get them to refresh properly, I embedded them in a one-row invisible Receipt portal. You can read about this technique in more detail here (Summary Report in a Filtered Portal).

Well, that’s it for today. In part 4, we’ll see if we can find a way to get 2-column VL’s — or a reasonable facsimile thereof — to play nicely with auto-complete.