# Shadow Tables -- Thinking outside of the box **URL:** \ **Category:** Questions\ **Tags:** relationships, filemaker\ **Created:** [April 1, 2024, 10:08pm UTC](https://the.fmsoup.org/t/shadow-tables-thinking-outside-of-the-box/4068 "2024-04-01T22:08:39Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![TKnTexas](https://avatars.discourse-cdn.com/v4/letter/t/ad7895/32.png) [@TKnTexas](https://the.fmsoup.org/u/TKnTexas)\ **Post date:** [April 1, 2024, 10:08pm UTC](https://the.fmsoup.org/t/shadow-tables-thinking-outside-of-the-box/4068/1 "2024-04-01T22:08:39Z")
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. ---
**Author:** ![Malcolm](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/malcolm/32/196_2.png) [@Malcolm](https://the.fmsoup.org/u/Malcolm)\ **Post date:** [April 2, 2024, 6:30pm UTC](https://the.fmsoup.org/t/shadow-tables-thinking-outside-of-the-box/4068/2 "2024-04-02T18:30:12Z")
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. ---
**Author:** ![TKnTexas](https://avatars.discourse-cdn.com/v4/letter/t/ad7895/32.png) [@TKnTexas](https://the.fmsoup.org/u/TKnTexas)\ **Post date:** [April 2, 2024, 7:16pm UTC](https://the.fmsoup.org/t/shadow-tables-thinking-outside-of-the-box/4068/3 "2024-04-02T19:16:49Z")
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'. ---
**Author:** ![TKnTexas](https://avatars.discourse-cdn.com/v4/letter/t/ad7895/32.png) [@TKnTexas](https://the.fmsoup.org/u/TKnTexas)\ **Post date:** [April 2, 2024, 7:28pm UTC](https://the.fmsoup.org/t/shadow-tables-thinking-outside-of-the-box/4068/4 "2024-04-02T19:28:36Z")
These are the two tables for the one purchase order ![Capture](https://canada1.discourse-cdn.com/flex030/uploads/fmsoup/original/2X/a/a621572c2c630ce46b127722a1bf4259b3dc42a2.png) ---
**Author:** ![Malcolm](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/malcolm/32/196_2.png) [@Malcolm](https://the.fmsoup.org/u/Malcolm)\ **Post date:** [April 4, 2024, 6:35am UTC](https://the.fmsoup.org/t/shadow-tables-thinking-outside-of-the-box/4068/5 "2024-04-04T06:35:58Z")
It looks like you have everything in place. You can see how many you've received from the PO Line Item table. ---
**Author:** ![TKnTexas](https://avatars.discourse-cdn.com/v4/letter/t/ad7895/32.png) [@TKnTexas](https://the.fmsoup.org/u/TKnTexas)\ **Post date:** [April 4, 2024, 4:26pm UTC](https://the.fmsoup.org/t/shadow-tables-thinking-outside-of-the-box/4068/6 "2024-04-04T16:26:53Z")
@Malcolm I really appreciate your assistance. This is the relationship I am working with. This should work: ![image](https://canada1.discourse-cdn.com/flex030/uploads/fmsoup/original/2X/3/326a5b105cdee2ed80906f2819bbf42fb243c094.png) ---
**Author:** ![Malcolm](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/malcolm/32/196_2.png) [@Malcolm](https://the.fmsoup.org/u/Malcolm)\ **Post date:** [April 4, 2024, 6:27pm UTC](https://the.fmsoup.org/t/shadow-tables-thinking-outside-of-the-box/4068/7 "2024-04-04T18:27:04Z")
Yes, that relationship looks correct for your purpose. ---
**Author:** ![TKnTexas](https://avatars.discourse-cdn.com/v4/letter/t/ad7895/32.png) [@TKnTexas](https://the.fmsoup.org/u/TKnTexas)\ **Post date:** [April 9, 2024, 3:12pm UTC](https://the.fmsoup.org/t/shadow-tables-thinking-outside-of-the-box/4068/8 "2024-04-09T15:12:57Z")
@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. ![image](https://canada1.discourse-cdn.com/flex030/uploads/fmsoup/original/2X/b/bab6f06252b796cf186e6d34d5ae18f6d48cc8f2.png)