advanced lists

14 posts by 2 authors in: Forums > CMS Builder
Last Post: March 27, 2015   (RSS)

By rez - March 23, 2015

I'm using a list in an editor, pulling from a table of store locations. I am using checkboxes with num as the value and city as the label. However, there are multiple locations / addresses in each city. So when viewing these checkboxes as an admin, it would be helpful to see the city and the address. 

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.

Attachments:

Capture-IT.JPG 45K

By rconring - March 24, 2015

I have had to do this several times for basically the same reason but cannot find an example in my own code.

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.

Ron Conring
Conring Automation Services
----------------------------------------
Software for Business and Industry Since 1987

By rez - March 24, 2015

Thanks Ron. That looks useful. It must be time to learn some MySQL.

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! 

By rconring - March 24, 2015

I'm sorry ... too much assumption on my part.  I was in your same shoes when I first purchased CMSB and knew NOTHING about PHP MySQL or web development in general.  I learned all I know from this forum and online documentation.

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!

Ron Conring
Conring Automation Services
----------------------------------------
Software for Business and Industry Since 1987
Attachments:

dropdown.jpg 41K

defaults.jpg 20K

By rconring - March 24, 2015

OK, if I understand the first part of your question, it should be solved with the concatenation of the address to the city name in order to distinguish multiple locations for the same city, but without a better understanding of what you are trying to set the defaults to, I cannot offer a solution.  I guess I am not understanding why there would be a default on creation of a new record to a list of locations within that record. 

Ron Conring
Conring Automation Services
----------------------------------------
Software for Business and Industry Since 1987

By rez - March 24, 2015 - edited: March 24, 2015

Although I haven't actually tried it yet, yes the first part looks resolved.

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.

Attachments:

checkboxes.JPG 50K

By rez - March 24, 2015 - edited: March 24, 2015

Actually, i guess i answered my own question. Using a multi selection dropdown for 100 locations, pre-selected, would be ridiculous.  I should go with the reverse and make "selected = opt out" since its only a few that would opt out.

...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.

By rez - March 24, 2015 - edited: March 24, 2015

Ron, thanks so much for the MySQL. It works great. I look forward to exploring more. :) I learned very little, years ago, with the command line. Hated it.

By rez - March 24, 2015 - edited: March 25, 2015

How would I show the state if that's in another editor as an advanced list (another table)?  Unfortunately, it displays as a number. Much like on a page where you would have to use state:label.

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

Something to do with JOIN?

screenshot attached.

Attachments:

numbers-state.JPG 22K