2 Replies Latest reply on Apr 14, 2014 1:08 PM by simon_w

    Many-to-many relationship/scripting query



      Many-to-many relationship/scripting query


      I'm fairly new to FMP so apologies if this question has an obvious answer or has been answered elsewhere.

      I have a many to many relationship between two tables. The first is called "Plant Species Table" and the second is called "Reference Table" and they are linked by a join table: "joinPlantReference Table".

      Using a portal on the Plant Species Table, I want to be able to create a new entry in the joinPlantReference Table which links a pre-existing entry on the Reference Table to the current Plant Species entry that I am working on. I also want to be able to add new entries to the Reference Table via this portal.

      My current approach has been this: using the approach outlined here - http://techfornonprofits.com/styled-3/downloads-2/files/FMPM2Many.pdf - I have been successfully able to create new entries in the joinPlantReferences Table which links pre-existing entry on the Reference Table. While this has worked perfectly in being able to link pre-existing entries it (seemingly) does not provide a solution for creating new entries in the References table.

      I've tried adding a button which creates a new entry using a script. I can get it to create a new entry on the Reference Table, but I cannot script it correctly to create a new entry on the joinPlantReference Table which is linked to the newly created Reference Table entry and links back to to the original entry I was working on the Plant Species Table.  

      What would be the best way to script a way to create a new entry in the joinPlantReference Table which is linked to the newly created reference and links back to to the original entry on the Plant Species Table?

      Alternatively, is there a better way to approach this problem altogether?

      I hope that all makes sense. Many thanks


        • 1. Re: Many-to-many relationship/scripting query

               Say you have these match fields:

               PlantSpecies::__pkSpeciesID = joinPlantReference::_fkSpeciesID
               Reference::__pkReferenceID = joinPlantReference::_fkReferenceID

               Then the following script will add a new record to Reference and also a correctly linked join table record linking it to the current record in PlantSpecies:

               Set Variable [ $PlantID ; value: PlantSpecies::__pkSpeciesID ]
               Go to Layout [ "Reference" (Reference) ]
               New Record/Request
               Set Variable [$ReferenceID ; value: Reference::__pkReferenceID ]
               Go to Layout [ "joinPlantReference" (joinPlantReference) ]
               New Record/Request
               Set Field [ joinPlantReference::_fkSpeciesID ; $PlantID ]
               Set Field [joinPlantReference::_fkReferenceID ; $ReferenceID ]

               Go to Layout [ //you have two choices here, either plantSpecies or reference]

               If the last step returns you to the PlantSpecies layout, you'll see a new blank layout. If there are a limited number of fields in Reference, you may be able to put all of them in your portal to the join table and simple enter the data in your portal--in which case your script can return you to the original layout or you may want the script to go to the Reference layout so that you can fill in all the needed details for that new reference record.

          • 2. Re: Many-to-many relationship/scripting query

                 Fantastic! Thank you so much Phil, that worked perfectly.