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

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


Just a thought.

SELECT <?php echo $TABLE_PREFIX ?>locations.num, CONCAT (<?php echo $TABLE_PREFIX ?>state.name, ' - ', city, ' - ',address)
  FROM `<?php echo $TABLE_PREFIX ?>locations`
  JOIN <?php echo $TABLE_PREFIX ?>state ON <?php echo $TABLE_PREFIX ?>locations.state = <?php echo $TABLE_PREFIX ?>state.num

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

Something to do with JOIN?

screenshot attached.

...unless someone knows a better way, plus I'm curious if you can pre-select things in this way (multi lists, pre-checked pulled from another table).

I'm doubting now that it's possible in CMSB. I mean, next would be wanting to pre-select only certain locations instead of all of them. i don't see that you could do that.

For the checkboxes, like the list that appears for an admin in the screenshot I previously posted  with the multiple "Austin" locations, I want all those checkboxes pre-checked. Imagine one table for locations, another for promos. The idea being that when creating a new record for a promotion, the admin can select which locations are running that promotion. Most of the time, it will be all of them. Sometimes, one or two may opt out. In order for an admin to allow a location to opt out, i figured there needed to be checkboxes. Having to select all the locations with individual checkboxes seems ridiculous after pressing "create" to create a new promotion. It would make more sense in this situation to load them all pre-checked for an admin. Sometimes the admin will have to uncheck one if a location is opting out of the promo.

In another table, there is a situation where most would need to be unchecked. 

Maybe for the promos i should just make the checkboxes "checked = opt out". I was just hoping to keep it consistent with the rest of the site where you check a box if a location is included.

i have attached another screenshot to show where i am using checkboxes in these lists. As another example, think of how you are showing me a screenshot using a dropdown as opposed to checkboxes. What if you were using a multi value dropdown and knew that 9 times out of 10, the same multi selections would be made? You may want to pre-select them. Especially if you are talking about 100 locations.

For the check boxes, I'm using them as you use a dropdown in your screenshot, in the advanced list options (multi value). Unfortunately, the default checked option doesn't appear to be there. 

First, you need to change the location lookup field List Options to Get Options fro MySQL query (advanced)  Then enter the following in the query. (I am assuming that the table and field names match your database)

SELECT num, CONCAT(name, ' - ',address)
  FROM `<?php echo $TABLE_PREFIX ?>locations`

This will concatenate the name, a spaced hyphen and the address in the lookup dropdown list.  I attached a screenshot.

As for default check boxes ... very simple .. set default to checked.  See attached screenshot.

Hope this helps!

I don't know enough to understand how to remove the depth check or why CONCAT is on the same line. If anyone can help with details while i try to learn more, I'd appreciate it.

• It's a simple table named "locations". All should be included.
• I'd like to display the "city" and "address" fields for each checkbox

Here is another question. I will be duplicating this for two different editors. When pressing "create" to create a new record in the "locations2" table editor, the admin should see the checkboxes / locations all selected already as default. More often than not, an admin will only uncheck one or two in locations2, then save.

I don't see where i can make checkboxes pre-selected. The only thing i can think of is making the checked value mean "NO" as opposed to it usually meaning "YES". However, the above table "locations" would mostly be unchecked by default. Setting the checkboxes up to mean the opposite when checked would be very confusing when dealing with identical looking editors for the same locations. How can I make checkboxes pre-selected in one table or not selected in another when creating new records in an editor?

thanks! 

Here is where I got the solution when I needed it

http://www.interactivetools.com/forum/forum-posts.php?postNum=2233847#post2233847

You will just need to concatenate the city and address or whatever to suit your needs.

i don't know if this is possible with the "get options from MySQL query (advanced)" or if that has nothing to do with display.

I hope that's clear. I attached a screenshot of the problem. Something like the following would be helpful. I guess i could make a 3rd, redundant  label in my store locations table but thought there must be a better way. 

_ Austin,  123 West Canyon Ridge Drive

_ Austin,  456 S. Lamar Blvd

Thanks for any help.