Adding a record to a columnar list table

[Quartekoen]

I have two related tables. One is a "Purchases" table, which holds all records of items purchased by a certain buyer. The other is an "Order" table, which lists all those records, based on a particular Purchase order (PO). When a record is added to the Purchases table, it is given a PO number to associate it with that particular order.

The Order table only has two fields: the Purchase Order and the Grand Total. If you perform a find for a particular PO number, it will show all the items with that PO number from Purchases, and the Order table will Calculate the grand total. Order is used only for displaying data, not for editing it (since there is a lot more fields available for adding purchases in the Purchase table).

Here is my problem. When I add a purchase to the purchase table, I can give it a PO number. This is fine for numbers that also exist in the Order table, and they can be found easily. But if I add a number that is new, that does not exist on the Order table, I can't perform finds from the Order table for them. Since Order doesn't actually have a record of that PO number, it returns zero results for it. If I try to add a record to the Order table, it instead adds a row to the list of any record I'm currently viewing (and there are always some being viewed, whether it be from a sort or from showing all records). On the other hand, if I have the Order table's PO field reference the PO from the Purchases table, then I can search it, but it doesn't know where to get the grand total from, and that remains blank. If I try to add a grand total field to the Purchases table, I don't believe I can calculate the total of just the items with the PO I want.

Could someone give me some pointers on this problem? Thanks.

[Quartekoen]

I've found a solution to this. Basically, I have the PO number in Order be refer to the PO number in Purchases (purchases::PO) and I changed the grand total field from a calculation of Sum( order::cost_ext) to an actually summary field of all the cost_ext, and it works!

Hopefully this solution can be useful to anyone else with a similar problem.