8 Replies Latest reply on Mar 25, 2013 10:55 AM by GuilhermePrudente

    Help with Multiple Match Fields related to same Table



      Help with Multiple Match Fields related to same Table


           Hello guys,

           this is my first post here at the FM forum. Just a little info: I only have 1 month of experience with FM, but I feel I am learning quite fast. I'm a programmer/comp-sci new-grad. I have been reading around a lot and I got stuck in a fairly simple problem(I imagine it's simple) but I just can't get my head around it.

           The situation is:

           I have a table called Itinerary, which has two fields to represent addresses(Address from,Address to). I also have a table called Address, and the fields from Itinerary relate to Address by relationship(fk_AddressFromID and fk_AddressToID).

           Each Address has a fk_ServiceCodeID, and there's a table ServiceCode(fields: ServiceCodeID,Location,Surcharge) and another one called ServiceCode_Lines, which relates ServiceCodes(fk_ServiceCodeFromID,fk_serviceCodeToID,price)





           ID:1 Name: ABC bla bla bla bla fk_serviceCodeID: 1

           ID:2 Name: ABC bla bla bla bla fk_serviceCodeID: 2




           ID:1 Location: Iowa Surcharge:$0

           ID:2 Location: Nebraska Surcharge:$0




           serviceCodeFromID:1 serviceCodeToID:2 Price:$90 


           So, on the layout representing Itinerary, for the fields AddressFrom and AddressTo,I need to select from a Value List, related to the Address table(showing all addresses), the Addresses From and To. That will set the IDs... Then I need on the same layout to have the serviceCodes from each of them shown, and then the price that is established from this relation.

           What I used to have was:

           (Table Occurrences)

           Itinerary - Address From - ServiceCode From - ServiceCode_Lines From

           Itinerary - Address To - ServiceCode To - ServiceCode_Lines To

           Note: Can't connect Itinerary - Address To - ServiceCode_Lines because it forms a cycle

           The ideal, in my head, would be that Address related to ServiceCode and ServiceCode_Lines only once, but the fields in Itinerary can relate to the Address table without having to have separate Table Occurrences.


           If anyone can shine a light or give me a hint on what I'm missing, I would be much appreciated. This is a re-ocurring problem in a few of my layouts, and I need to figure out how to have that kind of structure!

           Sorry for long post, and thanks!

        • 1. Re: Help with Multiple Match Fields related to same Table

               Not sure that I have figured out you exact data model here. Check this one and see if it is what you see in Manage | Database | Relationships:


               ServiceCodeFrom::pkServiceCodeID = ServiceCodeLinesFrom::fkServiceCodeID
               ServiceCodeFrom::pkServiceCodeID = AddressesFrom::fkServiceCodeID
               AddressesFrom::pkAddressID = Itinerary::fkAddressFromID
               AddressesTo::pkAddressID = Itinerary::fkAddressToID
               ServiceCodeTo::pkServiceCodeID = fkAddressesTo::fkServiceCodeID
               ServiceCodeTo::pkServiceCodeID = fkServiceCodeLinesTo::fkServiceCodeID

               Names of the same color are Tutorial: What are Table Occurrences? with the same data source table.

               If that's what you have, it won't work to match a specific Itinerary record to a specific ServiceCodeLines record.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Help with Multiple Match Fields related to same Table

                 To continue from my previous post....

                 The key relationship would be appear to be the many to many self join relationship between serviceCodes and ServiceCodeLines:


                 ServiceCodesFrom::pkServiceCodeID = ServiceCodeLines::fkServiceCodeFromID AND
                 ServiceCodesTo::pkServiceCodeID = ServiceCodeLines::fkServiceCodeToID

                 Where the "from" and "to" table occurrences here refer to the same data source table.

                 If you then define this relationship:

                 Itinerary::fkServiceCodeFromID = ServiceCodeLines::fkServiceCodeFromID AND
                 Itinerary::fkServiceCodeToID = ServiceCodeLines::fkServiceCodeToID

                 You have a match to the correct record in ServiceCodeLines to show the service charge for that pair of "From" and "To" service code records. Since you will likely want to link itinerary directly to the ServiceCodes table twice, once for "from" and once for "to", link in two more occurrences of ServiceCodes that are different from the two shown at the beginning of this post. You can, in fact use a different group of occurrences for the many to many self join relationship where you set up your charges for each pairing of From and TO than for the group where you link in your Itinerary table.

            • 3. Re: Help with Multiple Match Fields related to same Table

                   Hi PhilModJunk,

                   thanks for replying and giving your input. 

                   I tried setting this up already:


                   ServiceCodesFrom::pkServiceCodeID = ServiceCodeLines::fkServiceCodeFromID AND
                   ServiceCodesTo::pkServiceCodeID = ServiceCodeLines::fkServiceCodeToID

                   but the main problem is

                   Itinerary::fkServiceCodeFromID = ServiceCodeLines::fkServiceCodeFromID AND
                   Itinerary::fkServiceCodeToID = ServiceCodeLines::fkServiceCodeToID

                   will form a cycle, calling for another instance of ServiceCodeTo(or From, depending in the order I link them).

                   I'm really stuck on this specific scenario, because I need two records from Address in Itinerary, but looked up values aren't doing so good for me. I tried having Itinerary::fkAddressFromID = AddressFrom::pkAddressID AND Itinerary::fkAddressToID = AddressTo::pkAddressID, then using a look-up to populate fk_ServiceCodeFrom and fk_ServiceCodeTo, along with the link


                   Itinerary::fkServiceCodeFromID = ServiceCodeLines::fkServiceCodeFromID AND
                   Itinerary::fkServiceCodeToID = ServiceCodeLines::fkServiceCodeToID

                   but that yielded no results. 

                   I feel I really need to understand what can I do first about the TWO MATCH ADDRESSES from Itinerary to the ADDRESS table, and if it will be MANDATORY having 2 TO's for the two fields.

                   Let me know if you can shine another light! Thanks again! 

              • 4. Re: Help with Multiple Match Fields related to same Table

                     Please read my last post again. There is no "cycle" in what I am suggesting. "another instance of ServiceCodeTo and ServiceCodeFrom" is exactly what I am suggesting that you use.

                • 5. Re: Help with Multiple Match Fields related to same Table

                       Neither of these look like what I have suggested.

                       See this screen shot. Table Occurrences with the same data source table are the same color.

                       Technically, the upper group of three occurrences isn't strictly necessary, but may be useful if you set this up following the guidelines for an Anchor Buoy format for your Table Occurrences.

                  • 6. Re: Help with Multiple Match Fields related to same Table

                         Ok, this apparently has solved my problem... There's still a few clouds and questions, but I need to formulate them better to not waste your time. Thank you for your help!

                         On the Itinerary side, I also have AddressFromID and AddressToID. Will I need 4 Address TO's for this to keep working? Because if I make the relationship AddressFrom::addressID = Itinerary::fk_AddressFromID AND AddressFrom::fk_serviceCodeFromID = Itinerary::fk_ServiceCodeFromID.

                         It is confusing at times for me as how to the foreign key relation works in FileMaker - I am using a Value List with ID(first) and Name(second) to populate the drop-down list for Addresses. The Idea was to select an Address "FROM", have the name/service code(lookup field from address,right?) filled out in relation to that Address. Same for Address "TO". Then when both of them are filled(aka, a match is found through the relationship between the service codes), price would be filled. Am I missing something or confusing you? :P

                    • 7. Re: Help with Multiple Match Fields related to same Table

                           Linking them in by AddressID would be a better approach and I don't see a need for also linking by serviceCodeID to other occurrences of the same table as that may not link you to the correct address. But keep in mind that I only have a very limited view of your database...

                      • 8. Re: Help with Multiple Match Fields related to same Table

                             Yes, you are right. Only the AddressID is needed, in fact. I have realized that I only needed the Address ID through two TO's from Address, and the service code has to be a looked-up value in a mandatory way(I tried "replacing" this step by connecting Address:service code to ServiceCode::pkID). I'm glad I learned a little more today :) Thank you for all your help, and I guess expect seeing some posts from me soon! Haha :)