3 Replies Latest reply on Jan 6, 2014 9:28 AM by philmodjunk

    Lookup data from one table to another that share a common third table.

    miw

      Title

      Lookup data from one table to another that share a common third table.

      Post

           I have three tables, Coins, Type and Mintage.  My relationships are as follows:  Coins::TypeIDLink >--- Type::TypeID. Through this relationship, individual coins of various and duplicate years (multiple coins with the same years for a particular Type are possible due to other specific coin attributes other than year) can be linked to a particular type.

           I also have the relationship of Mintage:: TypeID >--- Type::TypeID.  Through this relationship, various unique mintage years and their unique mintages (duplicate years are not possible for a particular type - the mintage for a particular type and year is only one amount) for a particular type are maintained.

           Lets say I have four coins with unique attributes (other than year) linked to TypeA, dated 1922, 1923, 1923 and 1925.  In a similar manner, lets say I have five coins with uniques attributes (other than year) linked to TypeB, dated 1922, 1926, 1926, 1926 and 1928.

           Lets say I have in the mintage file a series of unique years and mintages for those years (and many others) linked to TypeA and also for TypeB types.

           When I am in the Coin record for TypeA with a year of 1923, how can I have my layout automatically lookup what the mintage was for TypeA, year 1923 from the Mintage table?  The same for any other year and type for any other active coin record?

           I thought a join table might be what I need but I couldn't figure that out.  Any advice or even better, a demo file, would be appreciated!

           I am using FM13 Pro Advanced on a Mac.

        • 1. Re: Lookup data from one table to another that share a common third table.
          philmodjunk

               Mintage>-----Type----<Coins

               If I understand correctly, you have a record on the Coins layout and want to see the Mintage record specific for that Type and Year. Correct?

               One method would be to place a portal to Mintage on your Coins layout and specify a portal filter to limit the related Mintage records to just the one for a specific year.   Mintage::Year = Coins::Year might serve as that portal filter expression.

               Another method is to enter Manage | Database | Relationships and use the duplicate button (two green plus signs) to create a duplicate of the Mintage table occurrence and link it to Coins directly:

               Coins::year = Mintage 2::Year AND
               Coins::TypeIDLink = Mintage 2::TypeID

               Then you can add fields from Mintage 2 to your Coins layout or specify auto-enter options on Fields in the Coins table that copy data from fields in Mintage 2.

               Note that Mintage 2 is not a duplicated table, it's a duplicated reference to the same table as the Mintage Tutorial: What are Table Occurrences? in your relationship graph.

          • 2. Re: Lookup data from one table to another that share a common third table.
            miw

                 You are correct in what I am trying to accomplish.  As for method one, I presume it would be a one line portal with one field (mintage) with the portal filter you mention.  For method two, when I add the Mintage2::Mintage field to the coins layout, I guess I need a refresh window when a Coin record loads?  I am not sure what you mean by "specify auto-enter options" to copy data - the mintage data is informational only for reference - do you mean that it would actually copy information from the mintage table to a coin table field (which in this case I do not need that redundancy)?

                 From a performance (speed) perspective, which method do you think would work best?

            • 3. Re: Lookup data from one table to another that share a common third table.
              philmodjunk
                   

                        when I add the Mintage2::Mintage field to the coins layout, I guess I need a refresh window when a Coin record loads?

                   That should not be required.

                   

                        the mintage data is informational only for reference

                   Then you don't need any fields with auto-enter field options. Sometimes developers need to copy data from a related table, such as filling in line items on an invoice and using auto-enter options to copy the current prices from a related table. But that isn't what you need here from what I can tell.