Data entry dropdown list showing two columns

  • Resolved joncast

    (@joncast)


    5 years, 6 months ago

    Thanks for your help last week with the Auto-Increment option, and hopefully you will be able to figure out, or help me figure out, why my tables cannot be altered in a remote database. Although I’ve been tinkering with database development for a couple of decades, and have used a little SQL scripting over the years, I am still just a novice. Still, being a server admin for 25 years, including installing and managing SQL servers, I’m certainly familiar with lots of scripting. That said, I can use all the help I can get.

    Beside my IT experience, several years ago I went back to college to become a historian, and I am using your plug-in to create a database to store and publish primary source data from my research of the San Diego, California Common Council actions between 1850 and 1875. I’ve been successful at creating my tables, the relationships between them, and have created parent/child relationships in the Data Projects module. I created a view to use for a lookup, but I don’t think I even need to be that complicated with what I’m trying to do.

    My problem is that I want to create a data entry dropdown that shows more than a single column. My database has a table for City Ordinances where I want to enter who proposed each ordinance. In that Ordinance table I have a column for Citizen ID (Cit_ID), but when I enter the data I want a drop down that lists the Citizen’s First and Last Names. I also have a Citizens table that has Cit_ID, FName, LName, and other related citizen info. In Data Projects I created a table relationship type “Lookup” and chose Cit_ID as the link between the Ordinance table and Citizens table. In the “Manage Columns for data entry” I selected the Lookup to be LName. So now when I go to enter data in my Ordiance table it shows the citizens Last Name. The problem is that if I have several citizens with the same last name I don’t know which one to select. How do I show both Fname and Lname in my lookup dropdown?

    I’ve searched for discussions on this, but I don’t even know what to search on to limit my results for this problem.

    Thank you for your help.

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 6 months ago

    Hi Jon,

    Good to hear you have SQL experience! That will make the implementation of a solution much easier. 🙂

    To show multiple columns in a lookup, you can use a view in which you merge multiple columns into one column, and then use that view (instead of your table) to create a lookup.

    What do you think? Does this help?

    Best regards,
    Peter

    BTW, the plugin does (not yet) support creating views, but you can write your own SQL script and run it from the Data Explorer. You can use this feature to create or alter your tables as well.

    Thread Starter joncast

    (@joncast)

    5 years, 6 months ago

    Thank you so much!!! That was enough information for me to figure out how to make that work.

    I was also thinking that your SQL script import feature was not working for to create a view as I was able to get my script to work in phpMyAdmin, but then after creating and recreating my tables several times in phpMyAdmin over the past couple of days, I finally remembered that your plugin required each line to end with a “;”. I used that on my last table, and it worked. Imagine that, it was my mistake.

    Thanks again for your help, and your quick response.

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 6 months ago

    Good te read it works! 🙂

    I will add a comment to the import about the ; character. This is problem more plugin users had in the past. Thanks for reminding! 😉

    Have a nice weekend,
    Peter

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘Data entry dropdown list showing two columns’ is closed to new replies.