My development efforts with FileMaker are to develop tools to do "stuff" with the accounting software data for capabilities not in the accounting software.
I have identified the accounting tables. Data is in the various tables, but not connected. With regards to purchase orders and the receiving against them I want to connect.
There is a PO_Detail table (dbo.POP10110 filename). I can query (via FileMaker) and see the line item records to a purchase order.
There is a PO_ReceivingDetail table (dbo.POP30110). I scan the receiving paperwork to a container field. Using the receiving number I can see the data for the PDF as it is in the accounting software. (THIS is where I started).
I have multiple receiving records against the purchase orders. As an example, we order 100 items of ITEMX. It is shipped in batches of 10-15 items. When it is fully shipped, the vendor will invoice.
Example 2, 10 line items, shipping may be 1-3 lines (fully or partially). Again, the vendor will not invoice until completely shipped. Luckilyfew vendors invoice in this manner.
What I want to do it treat the PO_LineItem Table as the Parent table to the Receiving Table. Both tables have the PONumber field and the ItemNumbr field. I am thinking the relationship would be built from these. I want to avoid the manual process I have to do for these.
It sounds like you want to link the PO items to the PO received items so that you can verify which have been received.
When you say scanning, do you mean OCR or barcodes? Barcodes might contain the information you need. I suspect it will still require a lot of hands on activity. Scanning OCR and expecting reliable results will disappoint you.
The scanning is just the PDF of the paperwork of the receiving.. No OCR or anything like that. In the end, when I process the invoice, a copy of the receiving(s) has to be attached. It is part of the ISO requirements we have.
There is nothing in the accounting system does what I want to do, match the receiving lines to the PO lines. 90% of our vendors will invoice for the items shipped. There packing lists will sometimes have the invoice number as reference.
Sometimes, in any of the orders, product is drop shipped by a third party which adds a new wrinkle of 'complexity'.
These are the two tables for the one purchase order
It looks like you have everything in place. You can see how many you've received from the PO Line Item table.
@Malcolm I really appreciate your assistance. This is the relationship I am working with. This should work:
Yes, that relationship looks correct for your purpose.
1 Like
@Malcolm Thank you for the assistance. I ended up deleting all relationships to rebuild in a more orderly fashion. The result was exactly what I needed. I can view the purchase order and see if all line items have been fully received.
The secondary benefit was that the Great Plains Administrator was impressed that FileMaker could help with the missing functionality of the Accounting Software.
1 Like
