I am trying to create a purchase order system.
One table has the product names, another has the product family (one family can have many products as members).
Since the products that can be ordered may change over time (either their description or not be sold anymore), I have a separate table for the POs called Orders and another called LineItems.
I have a layout that is attached to the Orders table, then inside this layout I have a portal linked up to the LineItems table. The user opens up the New Purchase order layout and enters the items (the portal has drop down boxes so they can select the product), then enters qty in stock and how many to order. Then they click save and they are done.
What I would like to do is have the portal listing all possible items upon load and then the user can enter how many there are in stock and how many to order. This is instead of them having to type or browse to the product in the drop down box. Initially I was going to just use autocomplete but this feature does not work on Filemaker Go.
So, my question is how can I fill in the portal with all of the products from TABLE_A so they appear in the portal that is linked up to TABLE_B. I tried setting up a script to import records to TABLEB but is giving me errors, it says that there are less items on the source and then that imported so many items with 0 errors, but i can't see anything there.
I do not want to use TABLEA for the ordering of items because I need to keep a history of last purchases, if an item is deleted because it is no longer sold at the store then it would dissappear from history, correct? Hence, by having 2 independent (no relationships) tables TABLEB keeps the history of items.