1 Reply Latest reply on Aug 22, 2011 4:42 PM by philmodjunk

    DropDown populated by multiple (joined) tables

    TroyKelly

      Title

      DropDown populated by multiple (joined) tables

      Post

      I need to populate a DropDown with an inteliable list of options.

      It is super clear / easy to populate it from a single table, but I need data from three tables.

      Ultimatly I need tblFlyLines::ID but the user needs to be presented with a concatenation of 

      tblFlyPositions::Identifier & "-" & tblRiggersPosDSUS::Identifier & tblRiggersPosPOP::Identifier

      I have tried creating a "Query" type table that has a calulated field with the above content - but - if any of those tables are changed - the "Query" table is not updated.  The dropdown really should be generated on the fly - to prevent storing data that could (and should) be calculated.

      Any advice / recomendations would be really apprecaited.

      Screen_Shot_2011-08-20_at_3.39.21_PM.png

        • 1. Re: DropDown populated by multiple (joined) tables
          philmodjunk

          You'll need to figure out a way to pull the date from just one table. There are ways to copy the data via a looked an auto-enter setting on a field in tblFlyLines and then use script triggers on the three fields in the three tables to update this value anytime you edit one of them. This is messy, denormalizes your data and can produce noticeable performance issues if a change in any of the three tables forces an update of a large number of related recors in tblFlyLines. (A calculation field that combines data from all three tables can be set up and will update automatically, but the field is an unstored calculation field and these cannot be used in value lists due to the lack of an index--needed to provide the list of unique values.)

          Perhaps there is a better way.

          If you want, spell out the purpose of this value list and the purpose of each of these 4 tables and maybe I can suggest an alternative approach.