How to copy records from a portal to an other table
I have an challenge and I hope someone can help me with some suggestions for a script.
(FileMaker Pro 17 Advanced (188.8.131.52) on a MAC)
Table A: "Facturen" (Invoice - general information)
Table B: "Factuurgegevens" (Invoice data)
Table C: "Producten" (Articles - information of a product)
Table D: "Klanten" (Customers)
Table C is related to B and B to A. D is also related to A.
Table E: "Orders" (general order information)
Table F: "Ordergegevens" (Order data)
Table G: "Leveranciers" (Suppliers)
Table F is related to E. G is also related to E.
I have a Layout ("Factuurdetails") with Invoice data (A) and several portal records.
One of the portal records (named "Artikelen") contains rows of invoice data (from Table B). Each invoice data row contains information from an article (from Table C) and the supplier (supplier number) of this article.
I would like to have a script that can extract the suppliers from the portal named "Artikelen" and use this information to add "Orders" for the relevant supplier (Table E) and add the invoice data to order data (Table F) related to the relevant supplier.
I have got an invoice for customer X with 5 items from 5 suppliers:
- Item 1 -> Supplier 100
- Item 2 -> Supplier 200
- Item 3 -> Supplier 100
- Item 4 -> Supplier 300
- Item 5 -> Supplier 200
Based on this information I would like to add 1 order for each supplier (3 suppliers (100, 200, 300) = 3 orders). Order 1 for supplier 100 contains 2 items, Order 2 for supplier 200 contains 2 items and Order 3 for supplier 300 contains 1 item.
So, the challenge is to write a script that first extract the number of unique suppliers out of a list of portal records (named "Artikelen" from Table B) and use this information to add orders (in Table E). Then copy the content of the portal records to Table F. The portal records contains also information form the supplier, so I can relate them to the proper supplier.
I hope my question is clear and someone can give me some directions to write a good script for solving this challenge.