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:
- 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
- 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.
- Repeat the process until the six existing records were duplicated.