Script to create N number of records

[RDWirr]

I have been trying without much success to write a script to create a number of records in a portal based on a quantity in a related record. Basically it goes like this: I have a portal in which I have a list of “Item” records. Each Item has a quantity of one or more. The portal rows have buttons to expand the quantity of items into individual records for each of the qty of items. So if item A in the original portal has a qty of 5. clicking the button will go to a layout based on a related table with a portal (line items table) and create 5 duplicate records of Item A each with an incremented serial number.

I have worked out the steps of navigating to the related layout and setting a global to the qty of new records to create but then I have a couple of issues:

1.I have been trying to get a looping script to cooperate with making the copies of records and setting incremented serial numbers but my knowledge fails me at this point. I know how to have a loop count through a set of records and perform some function but haven’t found the way to get the loop to perform some function N number of times.

2. I can't find a simple way to copy the contents of a record in the original table (at least some of it) to the related table and I really want to copy the contents so we can modify them independently of the original table. Is the best way to do this to just use lookups for all the fields?

3. I am also stuck on incrementing the serial number for each record although I know I can just have an automatic serial number entered upon record creation but I need to have more control over it than just adding the next number in the counter because I need to have continuous serial numbers. Gaps in the sequence cause confusion for our auditing. If we create new records and then delete some, the auto serial number entry will just keep going from the last serial number entered but I need to capture the last serial number in the remaining records and start again from there. I think I can 'get' the last serial number in the remaining records and just start incrementing from within the loop with a calculation but I am having trouble with how to capture the last serial number in the remaining records, how to determine the last record?

I know this is three questions in one posting but they are somewhat related. If anyone can point me to an example script or otherwise in the right direction on how to accomplish these things, I will be much obliged.

Thanks in advance,

RDW

[comment]

On points 1 & 2 (roughly)

Set Variable [ $parentID ; Parent::ParentID ]

Set Variable [ $n ; Parent::Quantity ]

#

Go to Layout [ Child ]

Loop

Exit Loop If [ $n ≤ 0 ]

New Record

Set Field [ Child::ParentID ; $parentID ]

Set Variable [ $n ; $n - 1 ]

End Loop

#

Go to Layout [ original layout ]

Didn't quite get your point 3: you want to start incrementing from where? What if some child records already exist?

I think you should elaborate on your purpose here. Incrementing serial numbers manually is always iffy (suppose two users are doing this at the same time).

[RDWirr]

Hi Comment,

I have implemented your script and it brings me part of the way. Thanks for that.

The thing is, this script goes to the related table/layout and creates the specified qty of new records but I actually need it to go to the layout, create a new record (Subparent?) and then fill a portal (from another related “line items” table) on that layout with the specified number of duplicate records…each with serial numbers. I am thinking this shouldn’t be too complicated if I just have the script create the new record on the layout that contains the portal and then go to the portal and start the loop sequence, filling the duplicate records. But here is where things are getting a little fuzzy for me; how to jump into the portal to start creating the dup records? Or instead of trying to create records in the portal, I could go to a layout with the line items of the destination portal and create the new records. But then I think I have to set the related key fields for each new record to link up with the parent layout in which the portal resides. I keep thinking there is a cleaner way to do this.

The other thing is, in your script, I couldn’t understand how it solves my point number 2. copying the contents of the fields of the original record into the new records…? Could you give me a hint on that one?

To explain my issue on point 3: The serial numbers are physically used as a tag to gather production data for the unit the serial number is assigned to. We print out tags to go on production items we make and workers use that serial number to report the time, materials and QC checks they expend on a specific unit. But this is also used on the production floor as a simple tally check to keep inventory in process. A missing sequence number usually triggers a physical search for the missing unit. I know we can make a report with the expected serial numbers with any gaps that may occur and avoid this problem but the unbroken sequence is more intuitive and our people preferred to keep this system if it can be preserved without problems. I understand your point about manipulation of serial numbers being risky. I suppose there must be a way to capture the entry error and disallow a user’s transaction if it fails to complete correctly. I am definitely open to suggestions if you have a better idea.

Thanks again,

RDW

[comment]

I am afraid you have lost me somewhere along the way. I suggest you name the tables involved in the process (put layouts and portals aside for now), so that the process becomes clearer.

In your first post, it seems like there are two tables: a parent table (the one with the Quantity field) and a child table (Units??) where the required number of records should be created. Now there seems to be a third table involved?

Or instead of trying to create records in the portal, I could go to a layout with the line items of the destination portal and create the new records.

Most definitely. Portals are for users, not for you. The script should work directly on the data level - and keep on working even if the portal is removed from the layout.

I couldn’t understand how it solves my point number 2. copying the contents of the fields of the original record into the new records…? Could you give me a hint on that one?

Take more variables with you (in the same way ParentID is loaded into $parentID in the example) and set more fields after the record is created.

[RDWirr]

Hi Comment,

Yes, I can see my narrative might be a little hard to follow. Since you gave me these previous good suggestions, I have proceeded and gotten the thing to work…mostly….except the serial numbers. I’ll break down what I have now in a more schematic form:

I have the following tables:

Parent

ParentLineItems

Child

ChildLineItems

1. The ParentLineItems table contains records of items each with a quantity from 1 to N.

2. From a record in ParentLineItems I start a script that sets $PLI to the ParentLineItemsID, and $N to the qty of Items,

3. Then we go to the Child table and create a new record and set the Child : ParentLineItemsID to $PLI.

4. Then from the Child table we set $C to the ChildID and go to the ChildLineItems table and create $N number of records and set the ChildLineItems :: ChildID to $C.

5. Carried along from ParentLineItems, we set some variables to the data from the fields in ParentLineItems and now paste them into ChildLineItems records as appropriate.

All the above is working now. The stumbling block is setting the serial number for each record in ChildLineItems.

As mentioned, we’d like to have continuous serial number sequences in the interest of following the KISS principal on the factory floor. But if this compromises the integrity of the data, then we’ll have to find another solution. It’s just that in our process, once items are started into production, the serial numbers are fixed but until they are released to production there can be some adding and subtracting from the list of ChildLineItems.

I am thinking that setting up some transactional integrity script might be the answer, checking for errors if two people are trying to assign numbers at the same time and reverting the one that fails perhaps? Can you think of any other methods or pitfalls?

In the actual system, things are a little more involved. When creating the Child line items, I have to go to yet another related table of Products and grab the product data from there. In fact the bones of a product structure. The ParentLineItems are just a pointer to the actual product data. I think I will need to find a set of the product data and copy that found set to the ChildLineItems table. The next puzzle...

Thanks again for your help.

RDW

[comment]

I was hoping you'd use more meaningful names, to help visualizing the structure.... anyway, IIUC the structure is:

Parent -< ParentLineItems -< Child -< ChildLineItems

and actually the Parent table is not playing in this game (since the script starts in the ParentLineItemsID where the Quantity is), so we can look only at:

ParentLineItems -< Child -< ChildLineItems

Now, IIUC (again!) the purpose of the script is to create a new series of $N (= ParentLineItems::Quantity) records in ChildLineItems (along with a new record in the Child table that will be the "parent" record for this series).

If (!) the above is correct:

1. Should each new series be numbered from 1 to $N? Or is there some connection to previous series?

2. (and this is the most critical point in the entire plan):

the serial numbers are fixed but until they are released to production there can be some adding and subtracting from the list of ChildLineItems.

Subtracting shouldn't be a problem - you just delete a record or (preferably) mark it as deleted. It's the adding where a duplicate can occur when two users try to add a number more or less at the same time. But I will defer this point until the rest is sorted out.

[RDWirr]

Hi Comment,

OK try these table names. Hopefully I’m not confusing the issue further:

Parent = JobOrders = JOs > this generally is a client order of a range of different products

ParentLineItems = JobOrderLineItems = JOLIs > this is the list of those products ordered each line with a qty of 1 to N.

Child = ShopOrders = SOs >> this is the order given to a particular department in the factory to make those items. Each Shop order is a qty of 1 to N of an individual Product.

ChildLineItems = ShopOrderLineItems = SOLIs > this is the individual top level products and subassemblies broken down into a list of the individual Products, one record for each single finished product and each batch of subassemblies, each with a unique serial number.

There is another part of the DB that gathers production information against these individual ShopOrderLineItems such as time spent, production process data and QC data.

Regarding your point 1, The Serial numbers are unique system wide. They are not only unique within 1 to N for a ShopOrder. We produce the products in batches (JobOrders/ShopOrders) but sometimes after the individual products are finished, they can be mixed with other JobOrders or kept in stock. Sometimes we make a few extra pieces if we have lower than expected scrap rates. In which case we may end up with a non-sequential serial number. In any case, the serial numbers have to be unique because they are also used as tracking numbers out the supply chain to the client in case there are ever service issues and also in our case we have to track chain of custody on the materials. So the Serial numbers are important and have some persistence. Five years at least. Past the end of our production line, unbroken serial sequence doesn’t matter. It is really only a plus for being intuitive in the production sequence and to some smaller extent in stock keeping.

Point 2, Yes, I agree, from both a work flow and database perspective deleting records doesn’t seem to be a problem, unless they are already in production, then they must be protected but before production receives the Shop Order, we can have some adding and subtracting during the production planning process. And I don’t see any database reason that they can’t be deleted. The only problem is when they are deleted, it doesn’t reset the Auto serial number starting number to the last existing record. I have been puzzling over the whole thing and I realize there is a further hole in the process because, who says the deleted SOLIs will always be the ones at the end of the queue? Even if we solve the issue of resetting the serial number to the next available, there could be other holes in the sequence. As happens manually now (with a spreadsheet), the Shop Orders are planned and then our planner just autofills a column of serial numbers before they are issued to production. This is probably the way I have to approach it, assign the serial numbers at the time the ShopOrder tracking cards are generated and printed.

I suppose the way to approach it is add all the items to the SOLI table, do our manipulating, then when the SOLIs are ready to be issued to production, use a script to count through the records without serial numbers and assign sequential numbers starting with the last serial No + 1, doing all SOLIs at the same time rather than each individual SOLI separately. Then lock the records against further change. I see the analogy in check printing in an accounting system. BTW I could use a suggestion of a good technique on the locking method too.

Sorry to be process engineering on the fly here but as usual translating from a manual system that’s already running to a more automated system looks easier than it is.

Thanks,

RDW

[comment]

I'll try to add a few words later. Meanwhile, I'd recommend reading these:

http://fmforums.com/forum/showtopic.php?tid/216331/post/363809/#363809

http://fmforums.com/forum/showpost.php?post/172165/

[RDWirr]

Hi Comment,

Thanks for the links. I can see the many pitfalls of manipulating serial numbers. I think it is clear that I should not try to change the underlying relationship keys to preserve the integrity of the relationship. My serial number is just a generated number according to the needs of the process. My serial number still has to be unique and as much as practical sequential.

Looking at the two solutions LaRetta and Vaughn describe, I think I tend to favor Vaughn's approach so the serial number becomes a permanent and unique identifier. I intend to lock these records once the tags with their numbers are printed anyway. I think I could get it to to work 'well enough' just by assigning the serial numbers after the records have been finalized. And also taking the actions with regard to error capture, record roll back, etc.

I also looked at your commentary back in 2005 about the Numerator table for assigning Student IDs. I didn't really follow the logic though. Could you elaborate a bit more. I think I got lost in your last point:

"This will create a new record in Numerator and assign it a new student number (which you can access thru the relationship).

If the current record has already been assigned a student number in the past, the script will essentially do nothing." I don't get it, if you create a new record with the script along with an auto-entered new StudentNumber, doesn't that just create a new record with potentially a duplicate PersonID? Anyway, this may just be an academic question. But I am curious.

I think I can get what I need just by separating the process to create the new ShopOrderLineItems and then when they are ready to print, assign sequential serial numbers at that time, taking care to check for errors such as locked records and roll back the whole process if that is the case. Unless you see some fundamental flaw in that. It may not be absolutely perfect for preserving the sequence but it should be close enough and safe for not creating duplicates or failing to assign serial numbers.

What I could use help with is the script to find the last assigned serial number and sequentially assign the new serial numbers for the 1 to N SOLIs that are ready for printing. Actually this will most likely be several SOs and SOLIs at the same time but since all the SOLIs are in the same table, I think it's not a problem. I could also use some guidance on how to lock those records once they have been committed for printing.

Thanks for your help,

RDW

[comment]

I think it boils down to two options:

1. Use auto-generated serial numbers and live with the gaps in case records are deleted/removed from production;

2. Delay the assignment of the serial numbers until "records have been finalized", then assign them numbers cast in iron.

If choosing the latter, I would avoid the "find the last assigned serial number" part, and use a separate Numerator table as mentioned in the second thread. The idea is very simple: each unit ready to go "sends" the Numerator table it's original serial ID, and in exchange is issued a unique serial number. There can be no duplicates, because the serial numbers are auto-generated in the Numerator table.

There is a third option that would allow re-using deleted numbers - but it would require pre-generating a pool of serial numbers, and I believe that a sequence like:

1004

1005

987

1006

is equally, if not more jarring than a missing number.

[RDWirr]

Hello Comment,

I agree, the first two look like the best options, option 3 would be confusing I think and after all the point is to reduce confusion. For my purpose, option number 2 is the best. That would take care of almost all cases I can anticipate and should require no extra complications as far as our planning personnel are concerned.

I am still thinking through your numerator table concept. This looks like a good solution for a case where the Serial numbers are assigned after any adding and deleting of ParentIDs has occured and the records are being assigned their permanent serial numbers and locked. As I understand it, this would not work (to keep the serial numbers sequential) if any deletions of ParentID should occur after the Numerator::SerialIDs were created. I had the impression, looking at it in the late hours last evening that there was more to it than that but today it seems, as you say, simple.

For my solution, I am still trying to figure out how to determine which records to create new numbers for. If I allow the records to be created first, then manipulated, then at the last and separate step, create the serial numbers and lock the records, I still need to find which is the last record without a serial number so I don't start a script to create serials that are already assigned. I think this can be the same logic that finds the records to print. What do you think? Is the best to set a flag printed or not printed, I suppose the most direct is testing to see if they have a serial number or not but I don't really know how to do that. I am still trying to find a way to lock these committed records too.

Thanks again,

RDW

Edited October 5, 2010 by Guest

[comment]

I am not sure how ParentID figures in this. Earlier you said that the serial numbers are "unique system wide". I took this to mean that if a batch ended on 1234, then the next batch will start with 1235 - no matter which JobOrder/JobOrderLineItem/ShopOrder is processed. If that's not the case, please clarify.

I am still not entirely clear on this point: when you get to the printing stage, do you assign a serial number to ALL items of the currently processed ShopOrder - or are there some items that are not supposed to get a serial number?

[RDWirr]

Sorry Comment, I relapsed back to an earlier part of the discussion. By ParentID I meant the Parent Key of the SOLI table.

You are correct, the idea is if the last batch ended on 1234 then next SOLI that gets a serial number will get 1235 no matter which JobOrder/JobOrderLineItem/ShopOrder/ShopOrderLineItem is processed. It will be the SOLIs that get the serial number.

When we get to the printing stage, we will assign Serial Numbers to all ShopOrderLineItems that do not yet have Serial Numbers. This could include LineItems from several ShopOrders. I do not foresee excluding any LineItems in this process. We will process all SOLIs system wide that do not have serial numbers at the time of printing.

I suppose it might be convenient to allow people to create ShopOrders for planning purposes without having to delete them before the ones that are ready to go are printed, kind of like having two stages, planning stage which just stays in the queue and then next phase, issued to the shop which would print the tags and commit the records/serial numbers, but at this stage, I will not go into that for the sake of keeping the project from growing past my already stretched skills.

BTW, do you know any good way to avoid having ShopOrders processed twice? It is conceivable that some well meaning person could run the shop order process once and then forget they did it and then run it again. I was thinking of summing all the total required SOLIs that will be required of a JO for each particular product and compare that to the qty of items already processed. I keep thinking there might be some holes in that concept. Sorry I don’t mean to distract from the issue at hand. This a side issue.

Thanks again for your continued help.

RDW

[comment]

Have a look at the attached sketch:

BatchNumerator.zip

[RDWirr]

That is exactly what I needed. Thank you Comment!

You have used a few methods here that I am learning from. Like the method for preventing double entry. Clean and simple. Can you please explain to me how the first step in the Generate Units script works? Is it implied that the Batch::BatchID from the current layout is compared to the Units::BatchID in the script step? I think this is a lesson in context.

To protect the records from change or deletion after they have had the serial numbers assigned, I have added a new privilege set for USER and for the Units table I have set it to disallow editing or deletions of any record that has a serial number. I think this is good security, the idea for which I have to credit Steven H. Blackwell's post on the subject. do you agree or do you have another approach?

Thanks again, This has helped a lot.

RDW

[comment]

The step:

If [units::BatchID]

checks for the existence of related records in the Units table. It's a shortcut for:

If [ not IsEmpty ( Units::BatchID )]

which works because any BatchID is a number > 0 (therefore true).

disallow editing or deletions of any record that has a serial number

Yes. Note, however, that I have purposefully violated normalization rules here. Strictly speaking, the serial number should be stored only in the Numerator table and retrieved through the relationship. OTOH, it would slow down the search for units without a related serial number - and a validation based on the same rule, too.

[RDWirr]

Thanks for explaining that. The logic would have taken me a while to penetrate.

I should think in this case the rules of normalization could be bent without too much risk since we are locking the records upon assignment of the serial number. Of course someone with permission could still get in there and change the serial numbers manually. I suppose I could use a script to update all the numbers in the Batch table from the Numeration table if someone modifies a record. But I think the chances of this are very low.

Thanks again for all your help.

RDW