Tracking Purchase Ordes

[Mary Ann]

I am trying to track purchases against purchase orders and generate a report to show the balance left on the P.O. I also want to track actual expenses against budgeted expenses by purchase code.

I have a database set up for Purchase Orders, with the assigned PO Number. A second database has the purchase records. I have repeating fields for the Purchase code, Description and Amount, with a subtotal at the bottom.

My P.O. Balance Report is set up in 3 parts, Header, Body and sub-summary sorted by P.O. Number. This works well, except two of the PO's do not calculate the balance correctly. They each add in the second purchase amount field twice. I have been told I should not be using repeating fields. If not, what should I be using and how do I set it up? I am a self taught user, so I may not be aware of some basic things. Thanks for any help.

[bcooney]

Welcome MaryAnn.

Yes, I vote for no repeating fields.

You need a table for POs, POLineItems (related to PO by PO_ID). This related table is shown on the PO form view in a portal.

To show balances, you would need Receipts and Receipt Line Items. Each Receipt would have a PO_ID.

Database is not a FM term. FM has files and each file can have multiple tables. FM Files can also be related to each other if one of their tables is related to another FM file's table (not too common). The entire group is called a "solution."

So, when you say, "in a second database," what do you mean? I would suggest creating a single file solution with multiple related tables.

So, your "second database" of purchase records should really be the PO Line Items table. From the PO, you can create a calculation that sums the PO Line Item extension.

How about we start there. Take apart this demo of a simple solution.

http://fmforums.com/forum/showpost.php?post/309136/

[Mary Ann]

Thanks so much for your help. I had already started studying this demo. What I now have are the following tables: Table 1 for POs which has fields for the PO no, Purchase Code, Vendor, Date Purchased, Qty, Description, Est. Total and Total(subtotal of Est. Total) Table 2 for Purchases (LineItems) with fields for PO number, Purchase Date, Purchase Code, Vendor, Description, purchase amount. Table 3 for Purchase Codes with fields for code number, code description and budgeted amount. Table 1 is related to table 2 by PO number and Tables 1 and 2 are related to Table 3 by purchase code. This seems to be working and I was able to generate the reports I need for PO Open Balance and PO encumbered dollar amounts by purchase code. The results seem to check out with manual tabulations. Does this sound like a workable solution to you? The demo helped a lot and I also studied the inventory template that comes with FM 7. I am still working on getting numbers to flow into the budget format my supervisor gave me, but I think I am almost there.

[bcooney]

Table1 should be called PO. It should have the primary key for the table which I name __kP_PO_ID (that's the nomenclature I use). It should also have the foreign key for the Vendor, _kF_VendorID. You need a Vendor table.

The PO table should NOT have Qty, Desc, Est. Total, Total, as those are LineItem data.

LineItems should not have a PO Date, that is in the parent, PO record.

As for your relationships. PO to PO Line Items by PO ID is perfect. What are Purchase Codes? Are they unique? If they are, like a PartID, then use that in your relationship.

How are you reducing the PO Open Balance? Where are the Receipts?