In my database I have, among others, a DataSet (I call them tables) for tblOrders and tblOrderDetails.
tblOrders contains records about Which Customer bought what Product and When
tblOrderDetails contains records such as OrderID, how much they paid (price isn’t the same for each customer), how many they bought and what store did they buy it from.
My problem comes when I’m inputting a new order. I want it to be easy for the User so I have a workflow that creates a new tblOrder record and adds the relevant info from Inputs they’ve filled in. Then I create a new tblOrderDetails with the results of Custom States and other values in inputs BUT this results in one Order number for the OrderID field in tblOrders which is then used to create the OrderID in the tblOrderDetails. This means that if a customer orders several different products in the same order, I end up with a LIST of Products and Quantities and Prices etc in each record single record in the tblOrderDetails whereas what I really want is to create a new record for each Product they’ve ordered and to have the OrderID repeated in multiple tblOrderDetails records so that I can have separate Quantities and Prices etc. But I don’t know how I get this to produce a new record for each Product in an order and then populate the tblOrderDetails with that.
If you’ve read this far and are still with me, I’m incredibly grateful and I’d welcome any suggestions. I’m in the UK and have to get home now but I’ll be back to provide more explanation if needed in the morning…
All the best