I have a customer purchase system for taking orders. The file contains an order table, line items table and a table that counts the quantities purchased from the line items table for inventory and reporting. There are additional reasons as to why I have this setup but those aren't pertinenet to this question. My question is, I have an Order Status field in the order table. I don't want to count those items in orders that are quoted, cancelled, etc. My problem is this:

If I create a calc field in the line items table to give say a 1 or 0 to a complete order based on an IF statement - I cannot use that field as part of my relationship for the counter as it isn't indexed. I tried using an auto enter field for the line items table (to bypass the problem) but it will not update itself across tables (even if the Don't update values if one exists is unchecked).

What can I do?

I think I would do this by script. A button that the user presses to finish the order. A dialog box that askes for the status. Buttons for Order, Quote, Cancel. Script then goes to the related records ( show only related ) then loops through those records setting your field to 1 or 0.

I was attempting to avoid a script as the only button on the form thus far is the one to print. There is no "Place Order" button although I could certainly make one. Is there a reason why the auto enter works directly on the order form but will not work across a table?

Thanks for the tip though.

Have you tried the Evaluate function? You can make it work on trigger.

Is there a reason why the auto enter works directly on the order form but will not work across a table?

Yes, there is - see here.

See the attached for a possible workaround. Note that this has some serious refresh issues, so when changing the status of an Invoice, a script is required anyway.

validItems.fp7.zip