2 Replies Latest reply on Oct 12, 2009 9:06 AM by mgxdigital

    Script Help Needed



      Script Help Needed



      I am looking for help writing a script.


      I have 3 fields in my database: "job number", "ordered qty" and "finished qty"


      Each of my records automatically generates a job number, everything else is filled out.


      I'm looking for a script to do the following:


      If the "finished qty" is less than the "ordered qty" capture the job number from the record, create a new record and insert the job number plus an hyphen 1.

      Then take the finished qty minus the ordered qty from the past record and insert it into the ordered qty field in the new record.

      If the finished qty is equal to or more than the ordered qty then do nothing.


      Ideally, i would like to have a script to where if the script had already been applied (like the job number already had the hyphen 1 after it) then it would move to hyphen 2, hyphen 3 and so on.


      Here is my example.

      Job 5000 has 10,000. pieces on it.

      We want to ship 5000

      So when I click the script button, it will make a new job that has 5000-1 as the job number and 5000 pieces in the ordered qty field.



      The main reason for this script is to keep track of partial jobs. This way we could see how many partials were sent off an origonal job and always have the origonal job number attached with the hyphen 1, 2, 3 (showing number of partials)


      Any help would be so appreciated. Please!

      Thank you.

        • 1. Re: Script Help Needed

          Howdy robin,


          The scripting is pretty straightforward, but I question the approach.


          May I propose a "transactions" table which would be shown through a portal on the job record layout?  A child table, linked by Job# (uniqueID key...don't alter it with hyphens) would have one record per transaction.


          Job 5000 has 10,000. pieces on it.  It is entered in the job record.

          We want to ship 5000, you would fill in the first portal row that you shipped 5000.

          Then in the job record, you would have a "Qty owed" field which would be calculated from:

          QtyOrdered - Sum(ChildTable::Qty shipped)


          The order status would be "Active" if  Sum(ChildTable::Qty shipped) < QtyOrdered

          or "Complete" if Sum(ChildTable::Qty shipped) = QtyOrdered

          or "Overshipped" if Sum(ChildTable::Qty shipped) > QtyOrdered


          Doing it this way will let you see how many partial shipments...the number of portal rows.  It will also let you track how many were shipped on what date very easily.


          This transaction table will also open up the possibility of packing lists, invoicing and inventory control down the road.


          My two cents...hope it is of value.

          • 2. Re: Script Help Needed

            Thank you for help Ninja.


            The reason that I am looking to have the job numbers actally represented instead of a child portal is for reports and calculations.


            Most partials ship to different customers, so i would like them to be shown in my job summary just as the rest of the jobs.


            I would like to see something like to following in my job summary:


            Job        Qty         Finished Qty
            50         100        100
            51         50          50
            52         100         50
            52-1      50          25
            52-2      25          25 (So now the job is closed out)
            53         100        100


            If the partial were hidden in portals then I would not be able to see them on summaries and when billing for the seperate partials i need them to be on seperate jobs.