How to autofill listbox columns from one table to another

  • Resolved Mushfiqur

    (@aa11plus)


    5 years, 3 months ago

    I’m drawing 3 columns from a table called customers, into a new table called preorders. The 3 columns I am drawing on are first name, last name and email. These three columns will populate columns with the same name in the preoders table. Now, I know how to make a list box in preoders and populate with the same column in customers, and have done so for the first name column. This means that when I go into preorders, I can select the first name of my customer. My question is:

    How can I get the other two columns to autofill (in preorders) with the correct details (in customers), based on the first name I pick? I can’t figure it out. Would really appreciate your help.

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

    (@peterschulznl)

    Hi aa11plus,

    I suppose you are using a lookup? At this time a lookup listbox can only take 1 column from the list. There is an item on my to do list “Display multiple lookup columns” which came from another plugin user. I’ll leave this topic open and let you know when it is available.

    Best regards,
    Peter

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi aa11plus,

    The new version is out and allows you to create lookups with a reference to your parent key. You can find more info here:
    https://wpdataaccess.com/2020/01/29/lookup-queries-on-child-column-using-parent-filter/

    Let me know if you need help.

    Best regards,
    Peter

    Thread Starter Mushfiqur

    (@aa11plus)

    Awesome! Thanks ever so much. I’ll update my plugin and try it out.

    Thread Starter Mushfiqur

    (@aa11plus)

    I’ve tried and I can’t get it working…

    So, I’ve got one table (aa_student) and another table (aa_test).
    aa_student has many colums three of which are stud_id, wp_user_id and full name.
    aa_test has 4 columns: stud_id, wp_user_id, full name, note.

    I gave both tables the same option set name (test01):

    Here are the tables

    and created a parent/child page in Projects with the aa_test table being the chosen table:

    Here are the settings

    I added a lookup relationship. This worked and gave me a dropdown of all the students’s fullnames:

    Here is the lookup setup

    But when I add the additional column lookups (as below) then the dropdown becomes empty and although I can click on a dropdown link, there are no student names in there.

    Here are all the lower lookup settings

    What am I doing wrong? All I want to do is select a student name and have the data in that row to pipe across automatically and populate the same columns in the new table.

    As always, your advice is most appreciated…

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Mushfiqur,

    I presume the student_id uniquely identifies a student. Is that correct? I also presume the student_id is the primary key of table aa_student. I that correct as well?

    Which column is the primary key of table aa_test? And what is the functionality of that table? Do you need to have wp_user_id in both tables?

    Do you have a data model?

    Best regards,
    Peter

    Thread Starter Mushfiqur

    (@aa11plus)

    Yes, so the aa_student table has stud_id, wp_user_id and stud_fullname columns of which stud_id is the PK.
    The aa_test table (just for testing and learning) has test_id (PK), stud_id, wp_user_id and stud_fullname columns in there.

    The reason I’m trying to pipe those columns from aa_student table (to all other tables) is so that, when I create any new table, the login user is able to view only their data and I don’t have to manually select the student’s/teacher’s wp_user ID every time I add an entry.

    Because, the way I have understood it, the login user’s wp user ID must be present and matched to the WPDataAccess table PK of the student. Is that correct or is there a better, more efficient way?

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘How to autofill listbox columns from one table to another’ is closed to new replies.