I am working on developing a order tracking application for a client that will be running it on an iPad. The first phase of the development involves setting up an order entry form for sales personnel. I have created relationships between three tables Order Entry, Order Detail and Parts. Order Entry is the main form with an order detail portal in tab form. I have three CSV fields set up in the portal. The top level is Product Group, then Product Line and finally Part number. Once the part number is selected the Price associated with the part number is displayed in the price field in the portal. The price field in the portal is the related data from the Parts table (Parts::Price). There is a Qty field in Order detail (OrderDetail::Qty) as well as a line subtotal calculation in the order detail table (OrderDetail::LineSubTotal), which is defined as OrderDetail::Qty x OrderDetail::Price (there is also a price field in the Order Detail (OrderDetail::Price) table).
When I set up the portal to include the Parts::Price field, the correct price is displayed for the part number, and it can be changed, but it changes the price in the parts table which I don't want to happen, and as expected, the OrderDetail::Line Subtotal field isn't calculated.
The issue I am having is that i need to be able to get the parts price from the related Parts table matching the part number, into the price field in the Order Detail portal so that the line item subtotal will calculate. Additionally, I would like to be able to overwrite the default price in the order detail price field.
I tried setting the OrderDetail::Price field to use LOOKUP, which worked but it didn't allow any overwriting of the price but the OrderDetailLineSubtotal field calculated. I also tried to use a simple script, Copy [ Select ; Parts::Price ] and then Paste [ Select ; OrderDetail::Price ]. This works for the first line item but not subsequent line items; the correct Price is copied from the Parts Table that corresponds with the part number and it can be changed in the OrderDetail::Price field, but as indicated above it only works for the first line item.
Could you please let me know what I need to change in my script to allow the script to select the next line item part number and associated Price when the Price field is selected on the next line item, or is there a different way to approach the problem?
Thank you for your time.