A Value List Experiment

Demo file: a-value-list-experiment.zip

Note 1: today’s demo is recycled from the one that accompanied Virtual Portal, part 2. All we care about this time around is how we assign the parent company; the remainder can be ignored.

Note 2: if you’re unclear on value list basics, check out Thinking About Value Lists, part 1.

“I want it to work like it does in Quickbooks.” Based on a client request I recently implemented a Quickbooks-like picker… shown here enabling the user to easily assign a parent company via auto-complete as the user types the first few letters of the company name.

To be clear, we aren’t actually storing the parent company name, merely using it as a selection mechanism. When a name is chosen via this interface, the corresponding foreign key is immediately auto-entered into “id_parent”…

…via the highlighted relationship.

Here’s the single-column value list…

…here’s how it’s implemented on the layout…

…and note that auto-complete only works on single-column value lists.

The global field is populated or cleared as necessary via script trigger when the user clicks into it in browse mode, so they never see a wrong parent name.

Once this operation has taken place, the user then sees (and can search on) the parent company name…

…via this relationship.

Note that in the company table, validation settings for the name field are “unique”, “not empty”, and “always”.

Incidentally, when preparing this demo I decided to add more company records by importing them from another file, and, thanks to the preceding, ten of those records were rejected due to having the same company name as records already in the demo.