Recently I was tasked with implementing a picker list whereby the user could assign people to a project, and indicate each person’s hourly allocation to that project as that assignment was made. How can data be associated with names in a picker list, before those names have actually been stamped onto their own records?
The Challenge
The workflow:
- User clicks a button to add one or more people to the selected project.
- The user is presented with a list of people to be added.
- For each person in the list, the user may input a number of hours.
- If the user saves, people with hours are assigned to the project, with the specified number of hours (specifying hours is synonymous with selecting the person to be added).
Here are some clarifications of the challenge:
- The user must be permitted to input values for each name to be selected, before actually saving that selection.
- This is a multi-user environment. Two users should be allowed to assign the same person simultaneously without their allocations cross-pollinating.
Here’s an example of the picker list:
And of the project record after the specified people were assigned:
Note: this post doesn’t attempt to make the picker list itself as usable as possible. E.g., it doesn’t account for filtering the list of people in the picker list, nor does it trap for keystrokes to quickly tab between rows.
Method 1 – Editable Field on the Person Table
My first solution was to create a data field in the person table, to be displayed in the picker list, to serve as a temporary holding cell for the user to input their value into. The script would then clear this field when the user closed the picker list.
This didn’t satisfy the multi-user environment requirement, as two users trying to assign the same person would be trying to edit the same holding cell.
Additionally, there were performance issues as the user opened/committed records as they input values in the picker list.
Method 2 – Create Records to Hold the Data
My next thought was to create temporary records to hold the input value. A user would click into the field to add a value, and at that moment FileMaker would quickly create a record to hold the value. With some carefulness perhaps that could be the very same record that would eventually be committed if the user saved.
I didn’t actually work up this approach, as it seemed to have too much overhead to be creating records, and then still incur the cost of editing/committing those records as the user tabbed quickly through the list.
Method 3 – Repeating Global Field
What I really wanted was a temporary holding bucket, which lead me to globals. Unfortunately, globals aren’t tied to a record. So I came up with a crazy interface with repetitions of a global field stacked on top of each other, using conditional visibility to hide all but the repetition corresponding to the row in the list (e.g., only repetition 5 would display on the 5th row in the list).
Aside from being a pain to work with in layout mode, the repeating globals also forced me to commit to a maximum number of supported items in the list, which wasn’t feasible for my client, who could have thousands of people in the picker list. Also, it didn’t play well with filtering/sorting the picker list, which shifted the names relative to their row number.
Method 4 – Virtual JSON Field + Button Bar Calculation
But the repeating global field got me thinking, what I really want is just a temporary bucket, with individual values tied to individual records. My ‘a ha’ moment was realizing that the user’s cursor is only in one field at a time, so I really only needed a single input field; the values for all the other rows could be stored in a single JSON object, and modified/retrieved as the user navigates from row to row.
Structure
- A single global field, PERSON::INPUT. This is the field the user edits each time they want to input a value, for any person in the picker list.
- On the picker list, make that global field enterable, but stack a button bar calculation on top of it.
- When the user clicks the field, they click through the button bar to the enterable field, triggering an OnObjectEnter script to set the global field to value associated with that row, if any. While the editable field is active, the button bar calculation is invisible.
- When the user clicks out of the field (e.g., to click into the same field on another row), it triggers an OnObjectSave script to insert the input value into a global JSON variable.
- The button bar calculation then displays its row’s corresponding value from that global JSON variable. This gives the appearance of each row retaining its input value, yet only one of these is made available for editing at any given moment.
- If the user clicks the Save button, the data in the global JSON variable is harvested to create the related data.
Again, though this model doesn’t show it, I’ve implemented this in production with a version of the picker list that allows the user to quickly tab from row to row, and I’ve found it to be performant and to behave as expected. In that version, it’s also enhanced such that the picker list displays people already assigned to the project (for reference), but hides their entry field, thus disallowing them from being added multiple times.
Also, because the JSON object uses the PERSON::ID primary keys as its keys, the user is free to sort/filter the picker list without fear of values becoming disassociated from their records.
Gotchas
- When the user inserts their cursor into the field there can be a momentary flicker of a value from a previous row, as the script catches up to set the global. I notice this on LAN, but not on WAN. I’ve tried to clear the global when I’m done with it, to avoid this flicker, but so far haven’t been successful.
- WebDirect doesn’t like stacked objects. The enterable global field doesn’t have to be beneath the button bar, but I prefer that approach because it allows me to give that field a white background. If it is on top, with a transparent background, you’d need to jump through extra hoops to hide the button bar behind it when the record is active, and to ensure you like the background color of the body part bleeding through into the enterable field.
Sample
You can download the sample file here: Editable Picker List.fmp12