13 Replies Latest reply on Oct 25, 2010 10:59 AM by philmodjunk

    Auto-add record on some event



      Auto-add record on some event


      Dear Helpers,

         I'm here again with some kind of crazy question. My aim is quite complicated, maybe you will show me where and how it was done before or give me some others advices. 

         I'm working on purchase order DB. When I order smth it is delivered by a full truck, not less then that. But sometimes I make a bigger orders to the mills with 2,3,xxx10 trucks in one, so I don't know. I have order total volume and I know that 1 truck carries about 30cbm. So, using Round operator and dividing one to another - I can get total amount of trucks I will need. 

         So, I would like my DB to make extra records (if needed) according to total amount of trucks to track shipments later and to calculate what is shipped and what is left.

      I see it like a finish button that will run some kind of a script after I prepared a record.

         Also, I have order numeration like 003-10, where 003 - is serial number and 10 is 2 last digits of current year. So, if there is more then 1 truck in order, I want this numeration to have extra index, like 003-10-01, where 02 will be index for each truck.

         I really can't get even close into making this idea real. Where to start from? I have only this draft algorithm. Please help. Thank you!

        • 1. Re: Auto-add record on some event

          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.

          • 2. Re: Auto-add record on some event

            Dear Phil,

            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.

            • 3. Re: Auto-add record on some event

              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?

              • 4. Re: Auto-add record on some event

                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. 

                • 5. Re: Auto-add record on some event

                  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:

                  Duplicate Record
                  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]
                          Duplicate Record
                          Set Field [LineItems::OrderID ; $OrderID ]
                          Omit Record
                          Go to Record/Request/Page [First]
                          Omit Record
                          Exit Loop If [Get ( FoundCount ) = 0 ]
                       End Loop
                       Go To Layout [ Original Layout]
                       Go To Record/Request/Page [Next]
                  End IF

                  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.

                  • 6. Re: Auto-add record on some event

                    Thank you for a big piece of work. I'll start trying it tomorrow. Will report my results here. Have a nice day!

                    • 7. Re: Auto-add record on some event

                      Dear Phil,

                       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. 

                      • 8. Re: Auto-add record on some event

                        Dear Phil,

                        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 ]

                        Duplicate Record/Request

                        Set Field [ Product details::productID; $ID ]
                        Omit Record

                        Go to Record/Request/Page[ First ]

                        Omit Record
                        Exit Loop If [ Get    (    FoundCount    )    =    0 ] End Loop
                        End If
                        Go to Layout [ original layout ]
                        Go to Record/Request/Page[Last]

                        All changes I made is bold.

                        • 9. Re: Auto-add record on some event

                          Dear Phil,

                              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?

                          • 10. Re: Auto-add record on some event

                            Hmm, anyway, I tried to do that via "Set Field", but it replies me that this field is not modifiable. I'm stuck.

                            • 11. Re: Auto-add record on some event

                              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.

                              • 12. Re: Auto-add record on some event

                                Dear Phil,

                                   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?

                                • 13. Re: Auto-add record on some event

                                  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.