Write Summary Record Database Script in Filemaker

To a filemaker expert this is easily less than an hour project. Request & Facts: Each Day I upload a file that adds records to the Sales Database. It contains a record of every sales order based on a unique sales order id. It also has purchase date and payment date. It does not have any duplicate Sales Order ID’s. Each Day i upload a file that adds records to the SalesTranx Database. It contains detailed records of many types that are related to a specific sales order. It has record types such as order, refund, shipping services, and other types. Each record in this table has the sales order id along with a number of other important fields. Each record also has a type such as order, refund, shipping service as well as other types. These types represent the various transactions related to an order. The file also contains records that do not have a sales order id associated with them. These records need to be skipped. This database is the detail of each order. NOTE: The file information cannot be gathered in any other format. This is not my preference and is beyond my control. Do not ask about getting the data in any other format or structure because it is not available. The solution has to be structured off the data we get. What Do I want: Because the sales information that i have is detailed information, I am looking for a very simple Solution to provide Summary Data for each Sales Order ID and SKU. While the detailed information is very useful, Most of what I want to do requires the data to be summarized. Unless you have a very easy and simple solution that is easier than creating a summary database, I am asking for a simple script that creates a summary database that contains 1 summary record for each unique Sales Order Id and SKU. What does this mean. Sales Orders may have multiple SKU’s on each order. I would want a database that has summarized records based on the Sales Order ID and SKU. So the database could and will have multiple records with the Same Sales Order ID but not with the same Sales Order ID and SKU combination. The purpose of this script is to create a database and update it daily that contains a summary record for each unique Sales Order ID & SKU combination. The records will primarily contain summary Price, Sales Tax, Shipping Fees Paid, Shipping cost, Product Cost and Profit for each record. This will allow me to sort and analyze data in the manner that I require. You must comment the script sharing the detail of how the script works so that in the future i can understand it and possibly make minor changes. I will provide existing two database structures and files already created. I have attached the relationship diagram. I also already have a layout that works correctly that has all the summary data on it if that is helpful. I have attached a image of it as well. Shipping Record: Data does not have a SKU associated with it Data sometimes has multiple records for the same order-id Data sometimes has single record for the same order-id Logic in my mind: Simply look at total shipping cost for the Order-id and count the number of SKU’s and apply an equal amount of shipping cost to each SKU. Looking to apply shipping charge per order-id and SKU. Refund Record Data does have a SKU associated with it. This means it can easily be tied to sales Order ID and SKU. It just needs to have a field in the new database with the appropriate amount. Order Record It has Data per SKU and is contains critical information. Data is a single Record when a Single SKU in multiple quantities when that is on the order. Data is multiple records when a multiple SKU in any quantity is on the order. Service Fee, Transfer or Adjustment - Ignore New Table Required Records: Sales Order ID (Should be unique in Sales Database) Sales Purchase Date (Must be pulled from Sales Database) Sales Payment Date (Must be pulled from Sales Database) All Records in SalesTranx All fields on layout 25