Accessing related data from a third file

[Ed Agui EC]

Hello!

In FM 5.03, when accessing and copying data from a third file that has several records with the same matching field, it copies the values from only the first related record, yielding unexpected or inaccurate results. (Users Guide Chapter 8-6: "Important: When the same value exists in the match field in more than one record on the related file, FMP copies the value from only the first related record, ......" Also Chapter 8-14 : "Accessing related data from a third file )

Is there a way to copy the values from the record that is directly related?

Thank you all in advance!

[Steven H. Blackwell]

What do you mena "Directly related"? FMP is doing what is expected here, taking the first related value it finds.

Are you trying to find some other value?

Old Advance Man

[Ed Agui EC]

To make my point clearer, I will put an example. I have the well-known files: Products, Orders, OrderDetails. In the file OrderDetails there are several records with the same item number, each pertaining to a different Order. Unit prices may vary, depending on the quantity ordered.

Besides, I have the files: Invoices and InvoiceDetails, because I want to make Invoices based on the OrderDetails that have been delivered so far. In the Invoice Form Layout there is a field

[Fitch]

My theory is that you are entering data via your portal to InvoiceDetails, and your InvoiceDetails file is doing a lookup to OrderDetails based on the ItemNumber, so naturally it finds the first item in the file.

A common way to work around this is to use a concatenated key, i.e., make a calculated text field that combines the OrderNumber and ItemNumber:

OrderNumber & " | " & ItemNumber

--note: I like to use a separator like the vertical pipe "|" or tilde "~" to help me see what's going on

Do this in both OrderDetails and InvoiceDetails, and have InvoiceDetails base its lookup on this field, rather than on the ItemNumber.

[Ed Agui EC]

Yes, Fitch, that works nicely, except for one thing: I cannot copy the OrderNumber from the selected record in OrderDetails, because that field is part of the matching field for the relationship. I could enter that value directly in the record, but an automatic lookup would be better. Do you have any suggestion? Thanks for your help so far. I will surely rate you!

[Fitch]

The lookup actually happens in the InvoiceDetails file, regardless of what file the portal is in. OrderDetails and InvoiceDetails both need a calculated OrderItemNumber key field, then you relate the two files based on this field.

(You can have more than one Relationship to the same file, based on different key fields.)

I have two alternate suggestions that may work just as well or better:

1. Just import the OrderItems into the Invoice Items; or...

2. Use the same file for OrderItems and InvoiceItems.

Of course, I'm sure you have good reasons for not doing it that way. Sorry, no time right now to elaborate, let me know if you need more info.