Rather than subdivide a single order record into several sub orders, you should consider creating records in a related table that document each truck's portion of the order. That may or may not be a good idea depending on your business model. Frankly, there's not enough detail in your post describing how your database needs to function for you to know.
Does one order consist of multiple items or just one item? (ie. we need 30 tons of coal vs. We need 2,000 widgets, 500 thingamajigs, 40,0000 whatits ....)
Do you need to know exactly what and how much is on each truck?
Your table of related records could just be a "trucking" table where you create a record documenting each truck that will transport all or a part of the order.
My model is quite complicated. Each truck my content from 1 to 10 different positions with different parameters (8 columns with product details). And if it is so, I can hardly predict what will mill produce for next coming truck. In my PO's I use portal rows to fill in spec. So I think it is close to impossible to make it 100% automated. But as I pay most often per each truck I would like to divide them and control each shipment and each payment, so the answer is yes, I would like to know how much is on each truck, but I'm ready to adjust it manually.
I saw it as a script that will make duplicate of my current record basing on order volume as I described it before (actually, most of it is ready now). But when I try to duplicate record it appears to be, that this function duplicates everything, except portal contents. And of course it give my PO new number. I'm not sure how can I "hold" it. BTW, you helped me with that counter and I really love it, thank you.
In that case, it sounds like you want to duplicate your order one or more times, then manually update the related portal of items shipped to record what is actually loaded onto each truck.
To duplicate the portal records, you need a script that finds the portal records and loops through them, duplicating each in turn and updating the foreign key field of each newly duplicated record to match to the new order record. That sound like what you need?
Yes, exactly. I don't see any other way how I can do it. And my issues now are:
1. portal records duplicating
2. Serial numbers
and i don't have an idea where even to start.
Hmm, I wouldn't use a numbering system such as 003-10-01 to link your order record to line items. I'd use an auto-entered serial number for this. You can use the numbering system you've described as a label on your order form--I just wouldn't use it to link to other tables in relationships.
Assuming you have an auto-entered serial number field such as OrderID that links it to your LineItems table, The main portion of your script would work something like this:
Set Variable[$OrderID ; Value: Orders::OrderID]
Go To Record/Request/Page [previous]
IF [Not IsEmpty(LineItems::OrderID)]
Go TO Related Records [Show only related records ; From Table: "LineItems"; Using Layout "LineItems" (LineItems)]
Go to Record/Request/Page [First]
Set Field [LineItems::OrderID ; $OrderID ]
Go to Record/Request/Page [First]
Exit Loop If [Get ( FoundCount ) = 0 ]
Go To Layout [ Original Layout]
Go To Record/Request/Page [Next]
Here's a demo file for this script: http://www.4shared.com/file/otLGFWXb/DupRecordwPortalRecs.html
This doesn't tie up every detail for you but it should get you started. One interesting question is whether it will work best for you to duplicate an order, update the duplicated record to document one truck load, then repeat for each load or to duplicate original order record in a loop that generates all the duplicated records in one pass.
Thank you for a big piece of work. I'll start trying it tomorrow. Will report my results here. Have a nice day!
You script really helps, but I found one thing that made me thinking a lot.
It works in case if I duplicate last record. But when i go back to one of any other prev. records and press duplicate - it will anyway duplicate the last record, because of string #3: Go To Record/Request/Page [previous]
In case of layouts there are an option GO TO LAUOUT ORIGINAL LAYOUT. How should I act in this case? To add variable that will catch current record id and then GO TO this record id? I'll try it.
Here how I solved last probem of duplicating NOT only last, but current record:
Set Variable [ $currentRECORD; Value:Get (RecordNumber) ]
Duplicate Record/Request Set Variable [ $id; Value:Purchase orders::poID ]
Go to Record/Request/Page [ $currentRECORD ][ No dialog ]
If [ not IsEmpty(Product details::productID) ]
Go to Related Record [ From table: “Product details”; Using layout: “Product details” (Product details) ] [ Show only related records ]
Go to Record/Request/Page [ First ]
Set Field [ Product details::productID; $ID ]
Go to Record/Request/Page[ First ]
Exit Loop If [ Get ( FoundCount ) = 0 ] End Loop
Go to Layout [ original layout ]
Go to Record/Request/Page[Last]
All changes I made is bold.
OK. I did what I want, but...... ;) I made fool of myself. I used this index for PO like 003-10-2, where 003 is PO seria, 10 is current year and 2 is truck number of current order. I made it this way for my poNUMBER field:
CounterPO::counterSERIAL & "-" & Right ( CounterPO::counterYEAR ; 2 ) & If ( transportCALC > 1 ; "-" & CounterPO::counterTRUCKS; "" )
You see, it will show last part only if order is more then 1 truck, that is calculated another way, but I don't this is important.
Why I'm fool? Because it is working great before, let's say, "dividing" one big PO into po-by-truck. But when I duplicate PO and adjust it truck-by-truck, of course If parametr hide my truck number.
What is the best way to correct this. To make new field like poNUMBERmultiple where it would be:
CounterPO::counterSERIAL & "-" & Right ( CounterPO::counterYEAR ; 2 ) & "-" & CounterPO::counterTRUCKS
and then make my script to change this field in cases when I duplicate record? Or you can suggest smth else & better?
Hmm, anyway, I tried to do that via "Set Field", but it replies me that this field is not modifiable. I'm stuck.
What field are you trying to modify? PoNumber or TransportCALC?
Your error indicates that you are attempting to change the value of a calculation field. If, instead, you use set field to modify one of the data fields used by the calculation field or change the calculation field to text or number, you will be able to use set field to change what you see in poNumber.
Yes, I want to replace, using script, one field (calculation) with another (calcucaltion as well). I got that I can't modify it with SET FIELD, but there must be some other solution?
Yes, either make the field a data field so that you can modify it. Or use set field to modify one of the data fields that the calculation field uses to compute its value.