AnsweredAssumed Answered

Help with Multiple Match Fields related to same Table

Question asked by GuilhermePrudente on Mar 25, 2013
Latest reply on Mar 25, 2013 by GuilhermePrudente

Title

Help with Multiple Match Fields related to same Table

Post

     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)

     Example:

      

     Address

     -----------

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

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

      

     ServiceCode

     -----------------

     ID:1 Location: Iowa Surcharge:$0

     ID:2 Location: Nebraska Surcharge:$0

      

     ServiceCode_lines

     --------------------------

     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!

Outcomes