I'm having a terrible time trying to explain this, so here goes.
I am pulling data from Qbks with QODBC.
I have 2 tables, Salesorderline.tbl this has Sales order data, Date, Customer, Item number and quantity.
2nd table is iteminventoryassemblyline.tbl this is the Bill of Materials for each "Item number" in the Sales Order.
I have created an operation connecting to Qbks to bring data into these tables using "Append to existing table".
What I want is to know how many Parts in the Bill of Materials I need to fulfill the Open Sales Orders. I know how to bring in the open Sales orders, which I already have in place and use for Back orders.
I guess I need to create a 3rd table that would have a calculated field that would take the the quantity of each line in the sales order in the 1st table and take it times the quantity of each of the BOM items and then summarize the BOM items to tell me what I need in parts to Assemble the open Sales Orders.
With these 2 tables I have created a 1:M set. I have added a field to the iteminventoryasseblyline.tbl and in Field rules I tried to do a calculated field that takes the quantity in the Sales Order times the Quantity in the child table but get an error that says field does not exist. So I guess the problem is the 1;M won't work that way and I understand that. So how should I try to proceed?
So I guess I need to create another table and do what?
I have tried to attached a sample db with these 2 tables I have created but it will never upload. I can't get screenshots to upload either.
Thanks for any advise on this.
I am pulling data from Qbks with QODBC.
I have 2 tables, Salesorderline.tbl this has Sales order data, Date, Customer, Item number and quantity.
2nd table is iteminventoryassemblyline.tbl this is the Bill of Materials for each "Item number" in the Sales Order.
I have created an operation connecting to Qbks to bring data into these tables using "Append to existing table".
What I want is to know how many Parts in the Bill of Materials I need to fulfill the Open Sales Orders. I know how to bring in the open Sales orders, which I already have in place and use for Back orders.
I guess I need to create a 3rd table that would have a calculated field that would take the the quantity of each line in the sales order in the 1st table and take it times the quantity of each of the BOM items and then summarize the BOM items to tell me what I need in parts to Assemble the open Sales Orders.
With these 2 tables I have created a 1:M set. I have added a field to the iteminventoryasseblyline.tbl and in Field rules I tried to do a calculated field that takes the quantity in the Sales Order times the Quantity in the child table but get an error that says field does not exist. So I guess the problem is the 1;M won't work that way and I understand that. So how should I try to proceed?
So I guess I need to create another table and do what?
I have tried to attached a sample db with these 2 tables I have created but it will never upload. I can't get screenshots to upload either.
Thanks for any advise on this.
Comment