5 Replies Latest reply on Nov 21, 2010 11:02 AM by AntonChuykin

    Manipulating portal rows

    AntonChuykin

      Title

      Manipulating portal rows

      Post

      Dear Colleagues,

      Could you please help me to show how can I manipulate portal rows via scripting.

      Here is my quick story: I'm working on a purchase orders DB. Each my order is, at least, 1 up to 20 trucks of goods. I have a goal to track each shipment and a payment for it. I started this topic already Auto-add record on some event, but I did it in a wrong way. I tried to duplicate my current record into X records, where X is number of trucks. Later, I found this idea stupid (as well as Phil here), because it ruined or, ok, made it very complicated, to run reports then. 

      Now I want to try to make it via portal rows. For instance, I prepare my PO and press button below, that will run a script. This script is to calculate total amount of trucks per order (this one is easy) and, what I don't know how to do, will make in a new layout X rows for each truck. 

      Could you please lead me to a proper way of making this? Which relations should I make, how-to make this rows? Add record in this "tracking" layout? I tried it myself several times and ways and still can't do it in a proper way. Thank you beforehand. 

      My current script looks like:

      Set Next Serial Value [ Tracking.portal::tracking.portalSERIAL; 1 ]

      Set Variable $tracking; Value:Tracking::trackingID ]

      Go to Layout [ “Tracking” (Tracking) ]

      New Record/Request

      Go to Layout [ “Tracking.portal” (Tracking.portal) ]

      Loop New Record/Request Set Field [ Tracking.portal::tracking.portalID; $tracking ]

      Exit Loop If [ Purchase orders::transportCALC = Tracking.portal::tracking.portalSERIAL ] End Loop

      Go to Layout [ “Tracking” (Tracking) ]

      Layout TRACKING is layout that shows portal, TRACKING.PORTAL layout is layout with portal fields

      Purchase orders::transportCALC is calculation that will shows how many trucks contains this order.

      But it doesn't work at all. First, because Purchase orders::transportCALC doesn't show me related to main purchase order calculation, so it doesn't STOP when I need it to exit loop. This must be due to relations problem. I'm working on it. Second, this script doesn't add new records in my portal. 

        • 1. Re: Manipulating portal rows
          philmodjunk

          Is this what you have in mind?

          Purchase Order: 1245            Date: 11/20/2010    (in MMDDYYYY date format)

          Truck 1
             Item xyz, quantity, description etc.
             Item abc ...
             Item rty ...
          Truck 1 Totals here

          Truck 2
              Item rty ...
              Item
              Item
          Truck 2 totals

          and so forth for each truck

          Order totals here

          • 2. Re: Manipulating portal rows
            AntonChuykin

            Dear Phil,

               Order is 

            Purchase Order: 1245            Date: 11/20/2010    (in MMDDYYYY date format)

            Item1 / description / quantity: 30cbm / price

            Item2 / description / quantity: 15cbm / price

            Item3 / description / quantity: 45cbm / price

            TOTAL------------------quantity: 90cbm / AMOUNT

                                                  FINISH BUTTON

            Under finish button script will calculate that 90cbm is 3 trucks and it will add 3 rows in a portal where it will add checkboxes:

            TRUCK No. / PIAD checkbox / SHIPPED checkbox / each truck amount (calculation TOTAL.AMOUNT/3) / truck plates

            that's all

            • 3. Re: Manipulating portal rows
              philmodjunk

              That's even simpler.

              You need a second portal for listing the trucks, related to your purchase order table by purchase order number.

              PurchaseOrder::PONumber = Trucks::PONumber

              A script can work like this: ( I'm assuming 30 cubic meters / truck, you can adjust the calculations if that's an incorrect assumption.)

              Set Variable [$PO ; Value: PurchaseOrder::PONumber]
              Set Variable [$Qty ; Value: PurchaseOrder::TotalQuantity] //or compute Sum ( OrderlineItems::Quantity)
              Set Variable [$Truck ; Value: 1 ]
              Set Variable [$NumbTrucks ; Value:  Ceiling ( $Qty / 30 ) ]
              Freeze Window
              Go To Layout ["Trucks" (Trucks)]
              Loop
                 New Record/Request
                 Set Field [ Trucks::TruckNumb ; $Truck ]
                 Set Field [ Trucks::Qty ; Round ( $Qty / $NumbTrucks ) ]
                 Set Variable [$Truck ; Value: $Truck + 1 ]
                 Exit Loop If [ $Truck > $NumbTrucks ]
              End Loop
              Go To Layout [Original layout]

              Compute $NumbTrucks by dividing by the maxium load. The ceiling function rounds up to the next larger whole number.

              This script assumes that you will run it just once per order. If you find you need to modify the order and then recompute the truck list, you would need to clear the list of existing truck records for this order first, then run the above steps.

              • 4. Re: Manipulating portal rows
                AntonChuykin

                Dear Phil,

                   One thing I want to make clear: we set variable $PO in the beginning, but you never used it before. Where is proper place to put it? Will it be:

                Set Field [ Trucks::PONumber ; $PO ]

                • 5. Re: Manipulating portal rows
                  AntonChuykin

                  Dear Phil,

                  With your help I did it like that:

                  Set Variable [ $PO; Value:Purchase orders::poID ]

                  Set Variable [ $Truck; Value:1 ]

                  Set Variable [ $numberOFtrucks; Value:Purchase orders::transportCALC ]

                  Set Variable [ $poTOTALamount; Value:Purchase orders::totalAMOUNT ]

                  Freeze Window Go to Layout [ “Tracking.portal” (Tracking.portal) ]

                  If[Purchaseorders::poREVISED =1]

                  Perform Find [ Specified Find Requests: Find Records; Criteria: Tracking.portal::trackingNUMBER: “$PO” ]

                  [ Restore ]

                  Delete All Records

                  End If

                  Loop

                  New Record/Request Set Field [ Tracking.portal::trackingNUMBER; $PO ]

                  Set Field [ Tracking.portal::trackingSERIAL; $Truck ]

                  Set Field [ Tracking.portal::trackingAMOUNT; $poTOTALamount / $numberOFtrucks ]

                  Set Variable [ $Truck; Value:$Truck + 1 ]

                  Exit Loop If [ $Truck > $numberOFtrucks ]

                  End Loop

                  Go to Layout [ original layout ]

                  I added modification check and delete "old" rows. All together it work perfectly! Thank you for help again! 

                  Will be back soon ;)))