New Duplicating Script. Please Help!

[mgxdigital]

Hi.

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 original job and always have the original job number attached with the hyphen 1, 2, 3 (showing number of partials)

Any help would be so appreciated. Please!

Thank you.

Edited October 12, 2009 by Guest

[LaRetta]

This way we could see how many partials were sent off an original job and always have the original job number attached with the hyphen 1, 2, 3 (showing number of partials)

I would suggest that the 'partials' are related records instead. You should keep the Job Number pure (and meaningless) for relationships, as an auto-enter serial as you have it now. So if Job number 4 has 5000 pieces, you would create a new related record in a table called PiecesShipped (for example). That new table would contain the Job Number, date shipped (and maybe tracking number), number of pieces shipped and so on. Your main Jobs table would contain the Customer ID, Ordered Qty and all other details about the job itself.

You would then know how many pieces were shipped by creating a summary field (called sTotalShipped) in PiecesShipped table which would be total of Finished Qty (number field). Then a calculation in your Jobs table would be:

Ordered Qty - PiecesShipped::sTotalShipped

There is no need to append your job number with a 1,2,3 etc because the number of related records in the PiecesShipped table will tell you the number of shipments which have taken place. Also, adding the hyphen and count to a record can break in multi-user mode. And if you duplicate your Jobs records, it will show improper ordered quantities in every duplicated record and you would never be able to count the number of Jobs either.

Edited October 12, 2009 by Guest
Corrected typo

[mgxdigital]

Thank you for your help.

The main reason that i need to keep these records the same as my regular jobs is when i create reports i need those to show all of my partials as well.

For instance,

I run a report for all of my jobs and the ordered qty. Results in:

Job Qty Finished Qty

50..........100.........100

51..........50...........50

52..........200..........50

53..........100.........100

If job 52 had 2 partials to 2 seperate people, i need my reports and summary fields to show these are seperate jobs.

I would like it to show:

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

Thanks again...

[LaRetta]

i need those to show all of my partials as well ... If job 52 had 2 partials to 2 seperate people, i need my reports and summary fields to show these are seperate jobs.

One job's pieces ship to two different people? I encourage you to provide a file showing your structure so you can be properly advised because proper structure should be your main focus. Once the structure is correct, reports can be generated however you wish.

Since you need to view the partials (and also each piece shipped as separate jobs), you still need the second table. Then generate your reports from this second table, listing your Job Number in the leading part and the body will be the individual 'people' or shipments. As you've shown in your second example, you will no longer be able to know the original quantity ordered because 52 will be added to 52-1 and 52-2. The difference between original Job order quantity and sum of amount shipped will tell you whether it is complete.

If you provide a sample file showing your existing structure, we can help you through it. Honestly, you do NOT want to duplicate your Job Numbers (auto-enter serial) in the the Jobs table. Another related table (because it is many shipments to one order) is truly needed here. :smile2:

Edited October 12, 2009 by Guest
Added sentence