Need to create multiple records in a table that resolves a many to many relationship
I have this basic relationship
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.