Parent Child dynamic LookUp

  • Resolved measy

    (@measy)


    1 year, 9 months ago

    Hi Peter,

    in the last couple of days I’ve been trying to replicate your Advanced LookUp filter Advanced lookups | WP Data Access , unsuccessfully.

    My business case is pretty much similar to your SAS Demo:

    A teacher has to record Absences / Delays for his/her Students.

    Ideally, the Teacher logs on, and is presented the list of his/her Classes (group of Students), and select a student to mark him/her as Absent/Partially Present.

    My dataModel is : Class (1) –>Subscription (n) –> Presence (m)

    Student (1) –> Subscription (n)

    I need to create a LookUp on Presence.student_id to return Students enrolled in the Class they subscripted.

    What I did is:

    CREATE VIEW students_enrolled AS:

    SELECT subscr_id, student_id, class_id

    FROM Student

    INNER JOIN Subscription ON Subscription .student_id = Student.student_id

    INNER JOIN Class ON Class.clas_id = Subscription.clas_id.

    (used inner join to exclude class without enrolled students and viceversa, If run in SQL Query, the select works fine).

    Then, I created 2 template sets:

    one for Subscription, one for Absences, which includes the Relationship: LookUp (based on students_enrolled view) ON Absences.student_id to return Subscription.student_id in (P). I avoided to return class_id, by now, to check incrementally

    Finally, I created a PC-ParentChild ProjectPage: Subscription (P)->Absences (C).

    The LookUp returns full set of Student (not filtered by the subscription_id in Parent record selected.

    In Advanced lookups | WP Data Access you mention order of lookup columns, can you please explain its importance?

    Quite frustrating not being able to directly reference the subscr_id in the child record…

    Thanks in advance for any contribution/help.

    • This topic was modified 1 year, 9 months ago by measy.
Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Marko,

    It looks like you are selecting the wrong columns in the wrong order. If you compare your use case with the example you are referring to, this would be about you need:

    Our example:

    module course_id, module_name
    course
    class student_id

    Your use case:

    class class_id, class_whatever
    subscription
    presence student_id

    You need to select the correct columns in the correct order. The lookup code column that is store in your presence table first, then the lookup description column you want to show in your lookup (this is what the user sees in your lookup) and finally the column presenting the relationship.

    Hope this helps,
    Peter

    Thread Starter measy

    (@measy)

    Hi Peter,

    thanks for your reply.

    Couple of tentatives run this morning, no result.

    Reading carefully your reply, I noted in “The lookup code column that is store in your presence table first“:

    store, but I need to insert presence table (new record) data.

    Is this the issue?

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Parent Child dynamic LookUp’ is closed to new replies.