Hi All
I've got a database whereby users can enter parts orders from customers with open payment accounts. We then invoice each customer on a monthly basis for their orders. I want to create a new invoice for each customer and group all of their non-invoiced orders by assigning the unique invoice id (cinv_id) to the corresponding orders.
In order to accomplish this, I'm looking for a way to complete the following 3 actions.
1: Set today's date as the field value of the bill_date field in the data_order table for all orders providing the field is already blank.
2: Create a new record in the data_customer_invoice table for each unique cust_id in the data_order table with the bill_date value of today, and copying the value from each field in the below list from the data_customer table into the new corresponding records in the data_customer_invoice table.
3: Update each cinv_id field in the data_order table with a bill_date of today, with the corresponding cinv_id from the data_customer_invoice.
data_customer data_customer_invoice
Cust_Id Cust_Id
Cust_Fnme Cust_Nme
Cust_Lnme
Cust_Add1 Cust_Add1
Cust_Add2 Cust_Add2
Cust_Unit Cust_Unit
Cust_City Cust_City
Cust_State Cust_State
Cust_Zip Cust_Zip
Cust_Cntry Cust_Cntry
Tel_1 C_Off_Tel
Email C_Email
Attached is a sample section of the database tables.
I've got a database whereby users can enter parts orders from customers with open payment accounts. We then invoice each customer on a monthly basis for their orders. I want to create a new invoice for each customer and group all of their non-invoiced orders by assigning the unique invoice id (cinv_id) to the corresponding orders.
In order to accomplish this, I'm looking for a way to complete the following 3 actions.
1: Set today's date as the field value of the bill_date field in the data_order table for all orders providing the field is already blank.
2: Create a new record in the data_customer_invoice table for each unique cust_id in the data_order table with the bill_date value of today, and copying the value from each field in the below list from the data_customer table into the new corresponding records in the data_customer_invoice table.
3: Update each cinv_id field in the data_order table with a bill_date of today, with the corresponding cinv_id from the data_customer_invoice.
data_customer data_customer_invoice
Cust_Id Cust_Id
Cust_Fnme Cust_Nme
Cust_Lnme
Cust_Add1 Cust_Add1
Cust_Add2 Cust_Add2
Cust_Unit Cust_Unit
Cust_City Cust_City
Cust_State Cust_State
Cust_Zip Cust_Zip
Cust_Cntry Cust_Cntry
Tel_1 C_Off_Tel
Email C_Email
Attached is a sample section of the database tables.
Comment