AnsweredAssumed Answered

Relational Drop Down Lists (ID Hidden and Auto Updating On Not In List)

Question asked by ezz on Sep 30, 2014
Latest reply on Oct 1, 2014 by philmodjunk


Relational Drop Down Lists (ID Hidden and Auto Updating On Not In List)


What is the best user friendly and simplest method you use for:

a) Completely hiding the ID value and using/showing another value

b) Auto updating new values into the list on the fly when not already in the list



- Not using new windows or any other method that causes disturbing hypnotic surprise screen flashes that the freeze window script step fails 'freeze'.

- Not requiring any more user input than a simple dialog confirmation such as 'not in list do you want to add it'.

- Not using PopUp Menus... they are not Drop Down Lists wink



- The least cumbersome and most effective method (the cleanest/shortest code).



- Fruit (Parent) (FruitIDpk + FruitNameText)

- Cart (Child) (FruitIDfk)

- On the Cart layout we only want to use and ever see FruitNameText in the Drop Down List (but store the FruitIDpk)

- If FruitNameText does not pre-exist in the list we want to auto add to the Parent table on confirmation from the user and then use that new value in the Drop down list in the Cart layout.

- In its simplest form... FruitNameText is also a unique value and no other fields in Fruit are required values.


The best I have come up with on my own and/or using other samples floating around are:

Completely Hidden ID's

The method of using an extra TO for the Fruit Drop Down list linked by FruitNameText (single text list). With a lookup calculation in the Cart table to fill in the FruitIDfk into one field and FruitNameText into another field (solely to keep the onscreen drop down list matching the text if its changed in the Parent (onrecordload). To make this also work in list view (the text matching any changes to the parent text) a related table field overlapping the Drop Down List is used too Cart::FruitNameText. I hope that makes sense to most of you but if not I can explain better perhaps (forget where I picked bits of this from originally).

Auto Updating On Not In List

Using a goto layout method (Fruit) and adding the new record in and coming back to the Cart layout and populating the new value into the Drop Down List. Avoids the screen flashes but might not be the simplest method out there. The steps to do it this way involve:

- Error is trapped (on validate trigger) if not in list (tip: with an exit with false required to cancel the event).

- Dialog prompt if they want to add it

- Store the new value temporarily (extra logic around new records or modified... ie. if new the record would need to be deleted and all fields filled in so far stored into temporary data and re populated after coming back)

- Undo the field change (or delete the new non committed record) so we can change layouts without further error

- Create the new record in the parent layout

- Come back and populate the Drop Down List with the new value (and other fields if a new record was in progress).

- Goto the Drop Down List to continue from where the user was before the confirmation.