Lookup Issues

[caveat1]

I have an invoicing database with the following tables: Invoices, Line Items, Products, Customers.

I have a standard discount field in the Customer table. Once I start an invoice and start entering line items into the portal, I want that standard discount to show up in the Discount field for each line item. We do this on each line, because there might be different discounts for different items on the invoice, and we want to be able to change some discounts manually, if necessary.

I would do this with a lookup, but the customer table is not directly related to line items, except through the invoice id in Invoices. The current discount lookup in line items uses the invoice id to find the first related record in customers. I know this is not right, and it is only working intermittently. How do I structure this to correct the problem?

There are 2 other lookups in the line items table - Customer id and Transaction Type, that lookup this information in Invoices, based on Invoice id. They only work intermittently too - for the most part, they do not lookup. Is there something in the relationship that is preventing this lookup? It may be that I don't even need these 2 lookup fields, but could use the actual fields from the Invoices table. Even so, I am curious as to why they don't work.

The file was originally in FMP 5 and converted to 8. I brought the Invoices and Line items files together as 2 tables in one file. It is possible the lookup stopped working after I merged the 2, although it was working after the conversion and before the merge.

thanks,

KC

[comment]

This is a known issue: a child record will not lookup from/thru a parent, unless the parent record is committed first.

See also here.

[caveat1]

I am not familiar with how the commit function works. I'm not sure if that is new to newer versions of Filemaker. At what point is the record committed? Can I make it automatically commit? In this situation, how would I resolve this?

I have also had the problem of entering a new customer in the customer file. When entering the new customer number into a new invoice, the customer information fields won't populate (unless I've closed out of the customer file or done something els with the customer records). Is this because the customer record is not yet committed?

[comment]

I am afraid I cannot answer in full, because that would take a day or two... Committing records is not a function. Read about it in Help, starting with:

Adding and viewing data > Entering data in records > Committing data in records

For a more in-depth review, see the '“Record Ownership” in Converted Solutions: Opening and Committing Records' chapter in the 'Migration Foundations and Methodologies' white paper on FileMaker's site.

The short answer is: when you create a new invoice, you need to click on an empty space in the layout (or press Enter) AFTER selecting the customer and BEFORE creating line items. If the customer is new, you need to similarly commit the customer record before assigning it to the invoice.

Another approach would be to script the process, and not allow the creation of invoices other than by script. A couple of strategically placed Commit Records/Requests[] steps should solve the issue.

[caveat1]

That was a simple solution to my lookup issue. I will have to learn more about committing records, in general. Thank you so much for your help. >