advanced lists

Glad to see you got the join working.  I have always used the state abbreviation in the state validation table as a key field.  Then, instead of storing the state record number in the locations file, you could store the 2 character state abbreviation.  That would make the state available without the join to the state table. 

SELECT num, CONCAT(city. ', ',abbreviation, ' - ', address)
  FROM `<?php echo $TABLE_PREFIX ?>locations`
  ORDER BY 'abbreviation, city'


Just a thought.

Oh, that is convenient. However, in CMSB, the keys are always automatically num, right? How are you getting the abbreviation into the locations table as a key somewhere before this (if i am understanding correctly)?

You do not have to always use the record ID as the value for options.  Just make sure that the abbreviation field is not editable in the states master table. 

In the locations table list, Get options from database (advanced)

Section Tablename -states

Use this field for option values - abbreviation

Use this field for option labels - name

I tried to upload a screenshot but something is wrong.  It refuses to accept the file. 

I am late for an appointment and can continue this later if you need more assistance.  I could send you the states table schema and data but the file upload is not working. LOL