5 Replies Latest reply on Mar 30, 2016 2:46 PM by Mike_Mitchell

    Duplicate a join table into another join table

    peter.martin@sinclairdesign.com.au

      I’m trying to replicate a bit of Access VBA code into FileMaker.

       

      I have two relevant join tables: “Job Plant Join” and “Maintenance Plant Join”.

       

      I have set two variables $JobID and $MaintenanceID which reference to the current records on (a) Job Layout and (b) Maintenance Layout respectively.

       

      I wish to duplicate a set of records from the “Job Plant Join” table where JobID = $JobID as new records in “Maintenance Plant Join”, but with reference to Maintenance records, not Jobs.

       

      Both these join tables only appear within portals on the Job and Maintenance layouts. New records are manually created via these two portals.

       

      The only way I could find to loop through the Job Plant Join records (say six separate records where JobID = $JobID) was to create a special layout and access that layout (using script) from the Job Layout using Go To Related Record. The script then Loops through these six records but I was unable to:

      1. Test whether the resultant MaintenanceID/PlantID combination (both these fields are mandatory) already exists within the Maintenance Plant Join table before creating a new record, and
      2. Create a new Maintenance Plant Join record using (a) Maintenance Plant Join:: PlantID = Job Plant Join::PlantID (i.e. the current record within the Loop) and (b) Maintenance Plant Join:: MaintenanceID=$MaintenanceID.
      3. Repeat the process until the six existing records were duplicated.
        • 1. Re: Duplicate a join table into another join table
          Mike_Mitchell

          Hello, Peter. There are several ways to accomplish what you're after.

           

          • Create a new TO pointing to the target join table. Instead of using variables, use global fields to store the desired job and maintenance ID values. Create a relationship between those two globals and the new TO, where JobIDGlobal = JobID and MaintenanceIDGlobal = MaintenanceID. This will allow you to test to see if the record already exists, and to create it if not.

           

          • Create a calculation field in the target join table equal to JobID & "_" & MaintenanceID. Validate this field as unique. Then test for validation failures inside your script; revert the new record if one occurs.

           

          • Perform an Import from the source table to the destination table (using method 2) and include validation in the import. Records that fail validation will not come in.

           

          HTH

           

          Mike

          • 2. Re: Duplicate a join table into another join table
            peter.martin@sinclairdesign.com.au

            Mike

             

            Thanks. I think that I now understand how to create a calculation field with a unique validation to create an error message when creating new records via script. I presume an error trap loops to the next record when such an error arises.

             

            But (and this is a basic misunderstanding on my part) what script option do I use to (a) create a new record in a table other than the "current" table (i.e. in the Maintenance Join table rather than the Job Join table I'm duplicating from) and (b) 'save' that new record before looping to the next Job Join record?

            • 3. Re: Duplicate a join table into another join table
              Mike_Mitchell

              peter.martin@sinclairdesign.com.au wrote:

               

              Thanks. I think that I now understand how to create a calculation field with a unique validation to create an error message when creating new records via script. I presume an error trap loops to the next record when such an error arises.

               

              Not unless you tell it to. By default, the error will be thrown and the script will ask for user input as to what to do (depending on your validation settings). If you want to trap it and move on, then you'll need to use the Set Error Capture script step and tell the script what you want it to do.

               

              But (and this is a basic misunderstanding on my part) what script option do I use to (a) create a new record in a table other than the "current" table (i.e. in the Maintenance Join table rather than the Job Join table I'm duplicating from) and (b) 'save' that new record before looping to the next Job Join record?

               

              You have two options: Create the record through a portal by using a combination of Go to Field, Go to Portal Row [ Last ], and Set Field (assuming the relationship allows the creation of a related record). Or, move to that layout and use New Record / Request.

               

              As for saving, by default, FileMaker automatically commits records when the user (or script) performs any of these actions:

               

              • Clicks outside a field
              • Presses the "Enter" key (numeric keypad)
              • Navigates to a different layout
              • Navigates to a different record (including creating a new record, as this automatically moves the record pointer to the new record)
              • Changes modes (e.g., Browse to Layout)
              • Closes the active window
              • Closes the database (which is an extension of the previous item)

               

              For more on record committing behavior, see Committing data in records.

              • 4. Re: Duplicate a join table into another join table
                peter.martin@sinclairdesign.com.au

                MIke

                 

                Again, thanks.

                 

                I'm still trying to get my head around FileMaker's need to use layouts within scripts (against VBA's use of recordsets) but your response encourages me to learn.

                 

                Peter

                • 5. Re: Duplicate a join table into another join table
                  Mike_Mitchell

                  Peter -

                   

                  Yes, that's a sticking point for a lot of folks coming to FileMaker from other RDBMS's. The key is this concept:

                   

                       Context Is King

                   

                  Yeah, nice. What the heck does that mean?  

                   

                  In order to understand context, it's necessary to understand the Relationships Graph. It's not an ERD (nor does it play one on TV). It's a combination ERD and querying tool, and it forms the foundation for everything you do in FileMaker. I find it helpful to think of the Graph like stepping stones in a river. Every layout is based on a Table Occurrence (TO), which is not the same thing as a table. (The development team refers to them as Table Aliases, if that helps.) They're objects that point back to a base table, but since you can have multiple occurrences of the same table on the Graph, it's not a table (even though the literature uses that term sometimes).

                   

                  Now, once you determine which layout you're on, you can see paths through the Graph via the various relationships. These define what I like to refer to as an "implied found set". When I go from TO A to TO B, the "implied found set" is the set of matching records, based on the relationship criteria. It's sort of like a query, except if I were to navigate to a layout based on TO B, I would see all the records (so the records in any TO context always consist of all records in the table).

                   

                  As I "step" from TO to TO on the Graph, I will further filter (or extend) the implied found set. Again, this is based on the relationship criteria.

                   

                  This is why every action you take is taken against a layout: It determines the context for that operation - and the path through the Graph you will take when addressing related records. This gives FileMaker the ability to address the correct sets of related records (and is the reason why you can't have a closed loop on the Graph).

                   

                  HTH

                   

                  Mike