4 Replies Latest reply on Jan 8, 2014 4:54 AM by john.s

    Need to create multiple records in a table that resolves a many to many relationship

    john.s

      Title

      Need to create multiple records in a table that resolves a many to many relationship

      Post

      Hi folks,

            

      I have this basic relationship

            

      Schools2---<JoinCustomersJobs>---Jobs>---JobType

            

       and when a Job is created a record is created in the JoinCustomersJobs table via the relationship to resolve the many to many relationship. Thank you PhilModJunk for helping with that. My next challenge is that I have two job types (4 and 14) that when they are created I need to determine if multiple records need to be created in the JoinCustomersJobs table.

            

      When I create an Action Sports Job (jobtype 4) the layout has a field for the  fk_SchoolCode. It is a lookup from Schools2 and the field is populated on the JoinCustomerJobs table (along with the Jobs:pk). The field for the opponent is stored on the Jobs table.  That too is a lookup from the Schools2 table with the option to “allow entry of other values” set.  So what happens is the Jobs::Opponent field has either a fk_SchoolCode (if the team being played is a school we contract with) or a value entered by the photographer for the opponent.  What I need to do is check to see if the Jobs:Opponent is in the table Schools2:_fk_SchoolCode and if so create a second record in the JoinCustomerJobs table with the same Jobs:pk as the first record (which was automatically created by the Relationship) and populate the _fk_SchoolCode with the value from the Jobs:Opponent field.  If the Opponent is not in the schools2 table then only the one record, that is created by the relationship, is needed in the JoinCustomersJobs table.  I need two records for that Job in the Join table because I need to be able to report out to each school that we covered that game.

            

      When we create a job for a swim or track meet type of event (jobtype 14) the Layout creating the Job will have a Checkbox field for the _fk_SchoolCode that does a lookup from Schools2 and again it will be populated on the JoinCustomerJobs table.    The checkbox field will be checked for each school at the meet (only schools in the Schools2 table will be included ie NO option to allow entry of other values.  I need to create a JoinCustomerJobs table record for each school checked off.   Each record being created in the JoinCustomerJobs table will have the same Jobs:pk and one of the schoolcodes in the fk_schoolcode field. So if four of our teams are at a swim meet  I will have four records in the JoinCustomersJobs one for each team all with the same Jobs:pk

            

      I’ve learned a lot in the past few weeks but Lookup and looping are two things that still confuse me.  Any and all help is greatly appreciated.

            

      John

            

        • 1. Re: Need to create multiple records in a table that resolves a many to many relationship
          philmodjunk

          Instead of selecting the opposing school(s) in fields defined in Jobs, you'd select the each opposing school from the _fkSchoolID field in JoinCustomerJobs, using a portal to that Join table located on the Jobs layout. This both selects an opposing school and creates a new record in the Join table in a single data entry action. And the same layout design works for both Football games and Swim Meets.

          And a check boxes type format for selecting the schools is possible if you want that.

          See the check boxes demo layout in this many to many demo file: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
                

          • 2. Re: Need to create multiple records in a table that resolves a many to many relationship
            john.s

                 Hey Phil,

                 This works great for the Multi team meets because the JobName that I generate just needs to say it was a Swim Meet on a given date.  The Sports events with two teams need a different format for the Jobname  basically it is YB_01062014_Football_bfd_vs_glfd  where the bfd and glfd are pulled from the _fk_SchoolCode and Opponent.  I'm not sure how to create that Jobname if I use the checkbox to create the Join table record.  All of these jobs run a script to create folders on the desktop with the job name.  Right now I have one script that runs for all single folder jobs like portrait, School Proms, Weddings etc and a different script that creates folders for sports with two teams playing each other.  That script creates one folder for the Job and then two folders inside that one for each of the two teams.

                 The business rule for the Job is the home team is always listed first and I don't see a way to make that determination if I use the checkbox.  I'm thinking  there might be a way to create the second record when the script is run to create the three folders.  I don't know how to capture the jobs pk and other info

                 this is what that script looks like.

                  

            • 3. Re: Need to create multiple records in a table that resolves a many to many relationship
              philmodjunk

                   The check box format is simply one option. Other options for creating join table records are also illustrated in that demo file. But the school data that you want for this JobName can still be accessed from the layout I am describing. I must assume that out of your example: YB_01062014_Football_bfd_vs_glfd

                   The number is based on the date (Jan 6 2014?) and it is the bfd and glfd text that must be extracted from text fields in the school table. While Football comes from the job record. Don't know where YB comes from though it looks like a code for "YearBook".

                   Let's say that you have a field in school named "Abbreviations" that holds that text. Then this calculation:

                   Substitute ( List ( Schools2::Abbreviations ) ; ¶ ; "_vs_" )

                   will return: bfd_vs_glfd

              • 4. Re: Need to create multiple records in a table that resolves a many to many relationship
                john.s

                     Thanks Phil…It took me some time but I got this working and all Job Names, Reports and Folders are correct.  Now I'm going to try to add some scripts to be sure folks don't check more boxes than they should.  i.e. football only has two teams playing against each other.