Help with Multiple Match Fields related to same Table
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:
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!