1 2 Previous Next 15 Replies Latest reply on Dec 14, 2010 12:48 PM by philmodjunk

    Relationship table -Do I need another join table?

    miw

      Title

      Relationship table -Do I need another join table?

      Post

      Using FM 11Pro, I have a database with seven tables (see attached .png file).  I have three tables, "Country", "Origin" and "PlaceNow" that I want to feed information up to my "Library" table and through the Library table to a table called "LibraryItems" by which many LibraryItems are linked individual Library records. 

      I have created a join table, "JoinCountryOrigin", that is linked to my Library table.  The purpose of this table is that each record in the CoinLibrary table must be linked to a unique combination of a Country and Origin source.  I am able to successfully build a layout to make the appropriate link to the JoinCountryOrigin table to work on both the Library and LibraryItems tables.

      My challenge is that I want to do the same for the records in the PlaceNow table.  My simple solution which worked (but did not make my layout idiot-proof), was to simply make a link from the PlaceNow table to a linked PlaceNowIDLink field in the LibraryItems table; that worked fine except that when making the link on the layout, the drop-down list consisted of all PlaceNow table records.  However, I want to limit the selection of which records can be linked to a LibraryItems record based upon what Country may be linked to a PlaceNow record (that Country, of course, being the Country noted from the JoinCountryOrigin table).  My first thought was to link the PlaceNow table to the Country table via a"JoinCountryPlaceNow" table - many PlaceNow table records can be linked to many Country table records (in the attached diagram, I have since broken the relationship links as I struggle to find the right approach).  Note that linking the PlaceNow table to the existing JoinCountryOrigin table is not useful as that join table is too limiting to the Country/Origin combination).  My next challenge is that the records in the PlaceNow table should be linked at the LibraryItems table (not the Library table, as multiple PlaceNow records can be linked to any one Library record made up of multiple LibraryItems records). 

      Bottom line:  what I am trying to do is to get a link to the PlaceNow table for a LibraryItems record by which a selection is made whereby the selection for such link is based on the Country that is linked to Library table.  Any ideas would be helpful!

       

      RelatinshipTable.PNG

        • 1. Re: Relationship table -Do I need another join table?
          philmodjunk

          I'm trying to understand the real world issues involved here. What would you be doing with your library items that requires creating or updating a record in the PlaceNow table?

          • 2. Re: Relationship table -Do I need another join table?
            miw

            Rather than have a static list of PlaceNow names, I use the linked tables to develop a dynamic listing.  So if someone created a PlaceNow record (say it has a PlaceNowID [serial #] of 4, whose name is Constantina, and they decide the name is mispelled or the PlaceNow has changed all together, they can go to the PlaceNow layout and make the edit or change which will then update all the linked Library and LibraryItems records.

            • 3. Re: Relationship table -Do I need another join table?
              philmodjunk

              Sorry, but that doesn't really answer my question. Your coin library tasks are very familiar to you, but not to me. Wink

              What I'm trying to understand is what does a "place now" record represent to you when you manage your library?

              What kind of event are you documenting when you create a record in that table?

              • 4. Re: Relationship table -Do I need another join table?
                miw

                PlaceNow represents represents a location of a LibraryItem (of which there could be many related to one Library record).  So "Roma" could be be a PlaceNow record.  It could be linked to 10 different LibraryItem records, which in turn may be linked only to two Library records of the correct JoinCountryOrigin combinations.  If for some reason, the user wanted to substitute "Rome" for "Roma", they could then just change the name in the PlaceNow layout, or if for some reason all LibraryItems are moved enmasse from "Roma" to "Athens", that change can be made in the PlaceNow layout, rather than changing each individual LibraryItem record.  Does that help?

                • 5. Re: Relationship table -Do I need another join table?
                  philmodjunk

                  I think you mean that PlaceNow identifies the current location of one or more Library Items. Correct?

                  • 6. Re: Relationship table -Do I need another join table?
                    miw

                    That is correct.

                    • 7. Re: Relationship table -Do I need another join table?
                      philmodjunk

                      Since a library Item cannot be in two places at once, I don't see the need for a join table. You should be able to simply link Library items to PlaceNow by the PlaceNow ID.

                      The except to that, is if PlaceNow records past and future locations as well as the current location--in which case a join table that includes a date field or two would make sense.

                      • 8. Re: Relationship table -Do I need another join table?
                        miw

                        The simple link from the PlaceNow (which records only the current place and not past places) table to the LibraryItems table is what I originally had.  But my challenge was making the lookup on my LibraryItems table only find eligible PlaceNow records for places located eligible to the Country for the LibraryItem that has been defined by the linakge to the JoinCountryOrigin link to the Library file.  Origin and PlaceNow records are each specific to a Country.  The original production of a LibraryItem is specific to a Country and place of Origin and linked to the Library through their join table.  A LibraryItem can be in multiple PlaceNow locations contractually defined by where that specific Country is allowed to export to. 

                        I would like to use a drop-down table that lists only those eligible PlaceNow locations for that Country. I was at one point able to accomplish such a drop down list but I found I was not truely linked to the PlaceNow table: although my record in the LibraryItem table would appropriately record the correct PlaceNow:ID in its record from the dropdown list, when I put the PlaceNow:Name on my layout (to see the actual name on my LibraryItem latout as well as the PlaceName:ID) it would only show the first PlaceNow:Name record name and not that tied to the specifc PlaceNow:ID.  So when I would view a list of LibraryItems contained in a Library record, I could see that each differently assigned PlaceNow:IDs in those records but the PlaceNow:Name was the same for each record.  Because of that, I deduced that the relationship link did not work.

                        • 9. Re: Relationship table -Do I need another join table?
                          philmodjunk

                          You have two different issues, setting up a conditional value list that filters place now options by country and whether you need a join table.

                          The conditional value list does not require a join table, but this statement indicates that you do:

                          "A LibraryItem can be in multiple PlaceNow locations contractually defined by where that specific Country is allowed to export to. "

                          Thus, you'll have this relationship:
                          LibraryItems-----<JoinCountryPlaceNow>----PlaceNow

                          LibraryItems::pk_LibraryItemID = JoinCountryPlaceNow::fk_LIbraryItemID
                          PlaceNow::PlaceNowID = JoinCountryPlaceNow::IDLink

                          Now you can link in a second table occurrence of PlaceNow ( PlaceNowbyCountry ) as:

                          JoinCountryPlaceNow::CountryIDLink = PlaceNowbyCountry::CountryIDLink

                          Your conditional value list can be set up to list values from PlaceNowbyCountry, show only related values starting from JoinCountryPlaceNow.

                          A portal on LibraryItems to JoinCountryPlaceNow can have two drop down lists. The first formats JoinCountryPlaceNow::CountryIDLink with a list of values drawn from Country and the second formats JoinCountryPlaceNow::IDLink with the conditional value list.

                          • 10. Re: Relationship table -Do I need another join table?
                            miw

                            PhilMod:  I see were you are going with this but I think I still have a disconnect - but I think I am almost there with a little more assistance.  I easily created the JoinCountryPlaceNow join table and can successfully use my Country layout to populate that table via a portal.  When I linked the LibraryItems table to the JoinCountryPlaceNow table, I had to create a second table occurence of LibraryItems (I believe since LibraryItems is already linked to a Country since LibraryItems is linked to Library which is linked to JoinCountryOrigin and thus ultimately linked to Country).  I do not think this second table occurence for LibraryItems impacts my later issues.  I see were you are going with the second occurence of the PlaceNow table and the conditional value list but from then on I get stuck.  When I try to use that value list to select a proper PlaceNow for the Country as already selected via the Library (for which LibraryItems is a subset), I get no values in my list.   I think the remaining problem rests with the end of your recommendation in that it will take two drop lists - the first to select a Country and a second to for the PlaceNowID.  I shouldn't need the first dropdown list since in Library I have already established a CountryID link and is that CountryID for which I wish to be able to select the eligible PlaceNow data. 

                            • 11. Re: Relationship table -Do I need another join table?
                              philmodjunk

                              Something doesn't add up here. You should not need a second table occurrence of Library Items. The new relationships I described do not include a new link to the country TO so either you have something different from what I described or I'm missing a detail somewheres.

                              I shouldn't need the first dropdown list since in Library I have already established a CountryID link and is that CountryID for which I wish to be able to select the eligible PlaceNow data. 

                              I can only base my recommendations on the structure of your relationships in the screen shot that you posted. Going by it, a given Library Item record may link to multiple JoinCountryOrigin records--each of which may link to a different country record in the Country table. Thus, you need to specify the country before you can get a conditional value list of PlaceNow records that are located in the selected country.

                              If a given LibraryItem record can be assigned to one and only one country, then you need to restructure your relationships so that you can link a country record directly to a LibraryItem record.

                              • 12. Re: Relationship table -Do I need another join table?
                                miw

                                Actual, a given LibraryItem may link only to one JoinCountryOrigin record; the design is intended to provide multiple LibraryITems to a Library record (each Library record being linked to only one JoinCountryOrigin record).  So items in a given Library record can have only one Country associated with it and thus each sub-LibraryItem record only one Country associated with it.  Any LibraryItem associated with its Library can have multiple PlaceNow locations based on that Country's eligible delivery locations.

                                • 13. Re: Relationship table -Do I need another join table?
                                  philmodjunk

                                  Actual, a given LibraryItem may link only to one JoinCountryOrigin record; the design is intended to provide multiple LibraryITems to a Library record (each Library record being linked to only one JoinCountryOrigin record).  So items in a given Library record can have only one Country associated with it and thus each sub-LibraryItem record only one Country associated with it.

                                  But that's not how you've structured your relationships. As I stated in my last post, if a given LibraryItem record can only be linked to a specific country record, then there should be a CountryID field in LibraryItems so that you can link that record directly to a given country record without using the join table. You'd still keep your current join table so that you can show all the countries for a given library record, but not to show the specific country for a specific LineItem record--that requires a relationship you do not have in the image at the top of this thread.

                                  • 14. Re: Relationship table -Do I need another join table?
                                    miw

                                    PhilMod:  Now that I am back, I am revisiting my post and request for assistance on this post and want to get some clarification.  I believe I see where you are coming from on your latest comments.  But I do want to get some clarification on the relationship setup between the LibraryItems table the the join table known as JoinCountryPlaceNow.  In your November 23 response, you noted to make the relationship link as follows:

                                    LibraryItems::pk_LibraryItemID = JoinCountryPlaceNow::fk_LibraryItemID

                                    Am I correct that the LibraryItems::pk_LibraryItemID is simply a unique defined serial number for the LibraryItem record and that the JoinCountryPlaceNow::fk_LibraryItemID field is merely a number field that is dependent on LibraryItems::pk_LibraryItemID?  Or is LibraryItems::pk_LibraryItemID some other form of calculated field?

                                    Thanks

                                    1 2 Previous Next