3 Replies Latest reply on May 7, 2015 1:58 PM by philmodjunk

    Portals to move (add or remove) linked files among a join table

    miw

      Title

      Portals to move (add or remove) linked files among a join table

      Post

      I am looking for a more elegant solution to adding or removing a record from a table to a related join table.  As per the image (Capture.png) attached, I have the following main tables:  Country and Mint.  I have a join table named CountryMint and an unaffiliated table named UnaffiliatedMint.  Multiple Countries can have multiple Mints in the CountryMint join table.  Currently, to add and create a record in the CountryMint join table, in the Country layout I have a one-line portal for CountryMint with a pop-up menu field for CountryMintID with pop-up menu values being pulled from the UnaffiliatedMint table.  When I select a unaffiliated Mint in the pop-up field, the new CountryMint record is created without a problem.  But, it is not too elegant a solution with the one-line portal and pop-up field. 

      What I would like to do is have a multi-line portal which shows ALL remaining UnaffiliatedMints for the Country and select from that portal an Unaffilitated Mint to create the record in the CountryMint join table.  My first problem is figuring out the table to "show related records from" for the portal and the proper fields in the portal (if the field source is different from the "show related records from" table for the portal itself).  Then, for a button on the selected portal row, the script to add the selected Mint from the portal to make the CountryMint join record.

      I have had success in such design if a Mint can only be assigned to one Country (ie once a Mint is assigned to a Country it no longer is on the UnaffiliatedMint list for selection by any other Country).  But what I am trying to accomplish is a portal whereby any Country can be assigned any Mint that has not already been affiliated to it and whereby Mints can be assigned to any and multiple Countries.

      Capture.PNG

        • 1. Re: Portals to move (add or remove) linked files among a join table
          philmodjunk

          Currently, to add and create a record in the CountryMint join table, in the Country layout I have a one-line portal for CountryMint with a pop-up menu field for CountryMintID with pop-up menu values being pulled from the UnaffiliatedMint table.  When I select a unaffiliated Mint in the pop-up field, the new CountryMint record is created without a problem. 

          I think you left out some details. That won't work without using a script trigger and script that you don't mention here.

          I don't think you need the join table at all from what I can see here but maybe you know more than I do. I would think that every mint can only link to a single country. If so, there is no need for the join table. but maybe a mint can strike coins for more than one country? If you don't need the join table, you can simplify your data model by getting rid of the join table and adding a country ID field to the mint table to use in a one to many relationship from Country to Mint.

          "ALL remaining Unaffiliated Mints" sounds like what is sometimes called a "dwindling" or "diminishing" value list. You can find an example of such in:

          "Adventures in FileMaking #1 - conditional value lists".

          The same value list method (which is based on a relationship) can be used to set up a diminishing portal.

           

          • 2. Re: Portals to move (add or remove) linked files among a join table
            miw

            For the quote for what I currently have that is working, I do have a script trigger which simply will Commit Records/Request [No dialog] when I pull an unaffiliated Mint from that single line portal.

            As for what I am trying to accomplish (a multiline portal showing any unaffiliated Mints to the current Country record), I use the CountryMint join table because a Mint can be used by more than one Country.  I want to use the UnaffiliatedMint table as a diminishing list so that when I am in a particular Country record, only those Mints not already assigned to that Country show up in the multiline portal for possible selection. 

            How do I set up my portal to show the remaining unaffiliated Mints to a Country and what is the script to create the new CountryMint join table record?  I can't seem to get the portal or the script set up properly.

            • 3. Re: Portals to move (add or remove) linked files among a join table
              philmodjunk

              I really don't see how your current design works at all given that setup, but since you are trying to change that design, it doesn't matter.

              See the demonstration file that I recommended. As I have already stated, the value list example in there can be used to show you how to set up a portal for the same reason. Both the value list and the portal would use the same relationship demonstrated so once you get the value list to work, you also have what you need to set up a portal.