I am having trouble in our solution adding a a relationship between a "Products" table and a "Documents" table and i'm hoping someone can point me in the right direction.
Currently the "Products" table is related to a "Manufacturers" table with a Primary Key from the "Manufactures" table to a foreign key in the "Products" table: ID_Manufacturer = id_manufacturer
the "Estimates" table picks up the "Products" table in a Table Occurrence via foreign key fields (id_manufacturer) in "Products" tables: id_manufacturer = id_manufacturer
to generate a new Estimate we utilize a portal from a "Estimates Line Items" table using PopOvervButton with a select a product via a portal and a script that populates the portal rows. The portal with in the popover is setup to show only products via a specific "Manufacturer"
I tried to more or less duplicate the above steps in using a "Document Line Items". but I have run into the following issues, which I believe may require I add another Table that would bring in information from both "Products" and "Documents"
*** The purpose behind my wanting to add a "Documents" table is that we currently store four (4) PDF's through the use of container fields for each product. Many products have the same PDF, and I was hoping that by using a "Document" table I could slim down the size of the "Products" table allowing multiple products to share the same PDF.
I can get the portal in the PopOver portal to only show "Documents" however my script fails; depending on the how I set the "
Set Variable" and "Value" the following occurs; nothing, every product for a specific "Manufacturer" gets the same "Document", adding a second document deletes the previous portal rows.
In the portal "Estimate Line Items" a portal row with information from the "Products" table can be deleted without deleting the product record.
I think I've tried so many variations on this that I can't even think straight.
The attachments show screens shots of the scripts that works for "Add a Product to Estimate" and the non-working "Add a Document to Product"