Ed Agui EC Posted February 10, 2002 Share Posted February 10, 2002 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! Link to comment Share on other sites More sharing options...
Steven H. Blackwell Posted February 10, 2002 Share Posted February 10, 2002 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 Link to comment Share on other sites More sharing options...
Ed Agui EC Posted February 11, 2002 Author Share Posted February 11, 2002 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 Link to comment Share on other sites More sharing options...
Fitch Posted February 11, 2002 Share Posted February 11, 2002 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. Link to comment Share on other sites More sharing options...
Ed Agui EC Posted February 11, 2002 Author Share Posted February 11, 2002 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! Link to comment Share on other sites More sharing options...
Fitch Posted February 12, 2002 Share Posted February 12, 2002 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 8132 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now