4 Replies Latest reply on Oct 24, 2011 10:35 AM by MikeF

    sort calc'd field in value list



      sort calc'd field in value list




      tblCities fields: CityID [number] / City [text] / StateID [number] / MetroID / CityState

      tblStates fields: StateID [related to tblCities::StateID] / State [text]


      The calc'd field in tblCities named CityState is the [text] City field from Cities and the [text] State field from tblStates.


      Have a value list named vlCityState.

      First field is CityID [number] from tblCities, second is CityState [calc'd].

      However it won't sort on CityState because "the field can't be indexed".


      So when using the vlCityID value list in other table's drop-down lists, the drop-down is sorted by CityID, ie in the order the cities were entered.  Consequently if Atlanta GA is CityID 901, and Vancouver BC is CityID 203 --- Vancouver shows up before Atlanta in the drop-downs.   This could obviously lead to all kinds of problems with inexperienced users.

      How can the value list, or at least the drop-downs, be sorted by the CityState field ????


       - Mike



        • 1. Re: sort calc'd field in value list

          Modify tblCities to use a looked up value field option to copy the state text from tblStates into a matching field in tblCities.

          Now your CityState calculation can use this field defined in tblCities for the state text to produce a result that can be stored and indexed.

          • 2. Re: sort calc'd field in value list

             I just created a State field in tblCities, then in Field Options had it lookup to tblStates::State.

            Was expecting all the records to fill in, but nothing happened.

            Do I need to export tblCities to Excel, do some vlookups to State, then re-import it?

            • 3. Re: sort calc'd field in value list

              Any record where you edit the ID field on which the lookup's relationship is based will automatically copy the data for you. It's only with existing records where you'll have this issue so you can do a one time fix to correct it.

              You can use Relookup in the records menu to update all your existing records:

              Put your cursor in the tblCities::StateID field on a layout based on tblCities after doing a show all records on this layout.

              Choose Relookup from the records menu and it'll update them.

              • 4. Re: sort calc'd field in value list

                 Wow! Ok ..!

                Thanks Phil ..!!

                Had already exported to Excel and done vlookups, but deleted all that, rebuilt everything, and did the Relookup Field Contents.

                It worked.

                Thanks again.

                 - Mike