4 Replies Latest reply on May 19, 2014 4:58 PM by deathrobot

    How to relate tables to a multi-line form

    deathrobot

      Title

      How to relate tables to a multi-line form

      Post

           I have a table whose sole purpose is to fill out an extensive form. Its records are children of a table called "Projects":

           Projects ---< Forms

           On each form, I have 15 places where I have to list People. The form looks like this:

           Person 1 (LEADER)
           Person 2
           Person 3
           …etc…
           Person 12
           Person 13 (ARRANGER)
           Person 14 (ORCHESTRATOR)
           Person 15 (COPYIST)

           I always list someone as LEADER and someone as ARRANGER. For Persons 2-12, there could be any number of people. And, each person can be listed more than once (e.g. person A could be listed as LEADER and also 1-4 additional slots).

           I assumed I would connect this Forms table to my People table with a join table, like this:

           Forms ---< Listed >--- People

           But I'm not sure if this is the best way to handle this. I would like to be able to see Forms associated with each person when looking at a layout in the People table, but…

           1) I will have multiple/duplicate entries for each person, so would need to deal with that when viewing the Listed records from the People table

           2) Would I need to have dedicated fields for LEADER, ARRANGER, ORCHESTRATOR, and COPYIST, and use a portal for Persons 2-12?

           3) Or is there some better way to do this?

           Thank you,
           Michael

        • 1. Re: How to relate tables to a multi-line form
          philmodjunk

               1) this should not be a problem. A portal to the Join table or a list view layout based on the join table can list the same person more than once as you will have one join table record for each time that the same person is listed for a project. In either portal or list view layout, you can include fields from People to show their name and other data and it will appear once for each join table record that links to that same People record.

               2) You have multiple options. You could set up a single portal where one field identifies the "role" (arranger, leader, etc.) and one links that join table record to the selected people record.

               You can also set up one row filtered portals with a script trigger as a "dedicated" field for certain roles if you wish. The script trigger performs a script to enter the needed "role" text for that record so that the portal filter won't omit the record after you have selected a People record for that specified role.

          • 2. Re: How to relate tables to a multi-line form
            deathrobot

                 Thanks, Phil. A few questions about this.

                 

            In either portal or list view layout, you can include fields from People to show their name and other data and it will appear once for each join table record that links to that same People record.

                 If I am in the People table, and there is a form (e.g. Form 001) where that person is listed 4 times, is there a way to have a portal only show "Form 001" for that person once? And not 4 times?

                 

            You could set up a single portal where one field identifies the "role" (arranger, leader, etc.) and one links that join table record to the selected people record.

            One issue is that, assuming a 15 line portal, LEADER needs to always be in row 1, ARRANGER needs to be in row 13, ORCHESTRATOR needs to be in row 14, and COPYIST needs to be in row 15. Rows 2-12 could be blank. This probably wouldn't work for that requirement, correct?

                 

            You can also set up one row filtered portals with a script trigger as a "dedicated" field for certain roles if you wish. The script trigger performs a script to enter the needed "role" text for that record so that the portal filter won't omit the record after you have selected a People record for that specified role.

            This sounds like it might be the best choice, but I don't completely follow. So, in my join table, there would be a "role" field, and on my Form layout I could set up 5 portals (with the same relationship) to that join table; a single line for LEADER, an 11 line for People 2-12, and single line each for ARRANGER, ORCHESTRATOR, and COPYIST? And the filter would be used to only show the appropriate related records? For the script trigger, would that be an onObjectSave in the id_People field that would set the role to ORCHESTRATOR, ARRANGER, etc. depending on which portal I was in?

            Michael

            • 3. Re: How to relate tables to a multi-line form
              philmodjunk
                   

                        If I am in the People table, and there is a form (e.g. Form 001) where that person is listed 4 times, is there a way to have a portal only show "Form 001" for that person once? And not 4 times?

                   A portal to Forms instead of Listed, will list Form 001 only once. But keep in mind that you can't use such a portal to manage the actual links between the current People Record and the Forms records.

                   

              This probably wouldn't work for that requirement, correct?

                   It's one of the reasons why I also mentioned a filtered portal with a script trigger, but no, you could still do this if you specified a custom sort order for your portal that automatically arranged the records by Role into this arbitrary order.

                   

              This sounds like it might be the best choice, but I don't completely follow.

              This sounds like you follow perfectly. I was thinking in terms of OnObjectEnter, but OnObjectSave should work just as well. Either way, you can pass the text you want to enter into the Role field as a script parameter and thus use the same script, but with different parameter text set up for the script triggers with each such dedicated role portals.

              • 4. Re: How to relate tables to a multi-line form
                deathrobot

                     Thank you, Phil.