Batch assign ?

[Mike-LI-NY]

I'm making a script to import new records to a database and I want to assign an individual serial # to the batch - however, I want it to be incrementally assigned. For example - if I already have batches 2001 through 2004 assigned in the file, the next batch should be 2005. Can anyone help me with a script to do this?

[Kundinger]

Hi Mike,

Quick answer... use the 'serial number' option in the 'Define Fields'.

This will auto-enter serial numbers when records are created & imported. You can setup a number of serial number schemes...

50001, 50002, etc. -or- ABC0001, ABC0002, etc. -or- Name10001, Name10002, etc.

There is even a 're-serializing' function in the 'Replace' command... this can even be 'scripted'.

Hope this helps... Good Luck!

Bob Kundinger

[email protected]

[Mike-LI-NY]

Yeah I noticed that, but it will provide a serial number for each individual record, and not one for the whole batch....

[Lee Smith]

Can't you just not increment the numbers. I haven't tried this, manually but I suspect that if you set the starting number to say 2004, and the second number to 0. Every record would have 2004 for a serial number.

[Mike-LI-NY]

hmmm maybe... but this is something I want to be automatic - I engage the script and it performs it without any need for input because it's running in the background...

[John Caballero]

Prior to importing the next batch, sort (ascending) by batch #, then go to the last record, then set a global field with the current batch #+1, then import the batch, then create a looping script that goes though all the newly imported records and sets their batch # with the value in the global.

[Kundinger]

Hi again Mike,

So... what your saying is... you want only the imported records to have a new serial number that is identical for all imported records in this single import batch?

If this is the case... the solution is very easy...

First, import the records... you will notice that only the imported records are 'found'... this allows you to do things to just these records (don't do 'Show All Records').

Second, click inside the serial number field on the first record and enter "2005", leaving the text cursor inside this field (don't press 'Enter' or 'Return').

Last, use the 'Replace' command (Records menu) to change the contents of the serial number field (in this 'found' set of records) to "2005".

This should work for you... you can even script these steps if you want to automate the process.

Hope this helps... Good Luck!

Bob Kundinger

[email protected]

[Lee Smith]

I would make a couple of date fields (if you don't already have them) - a Creation & a Modification Date, use FileMaker's Built in Auto Enters for date fields.

Having a Creation Date will come in handy if something should break with your import. Having a Modification date will help sort out problems that come up from time to time.

HTH

Lee

[Mike-LI-NY]

Bob - your idea is great, and I'm working on a script, but the problem is that I don't want to have to type in the serial number each time. FOr example, if my records already have serial numbers 1 through 4, I want it to know to serialize this new batch that it is importing as #5.... any ideas anyone?

[trevorg]

You can create a global field to set the batch number PRIOR to importing the records. The batch number can be figured out many ways (counter, relationship, etc..) Scripted of course and simply attach that function to your import script.

Then, set your Batch Number field to Auto-Enter data based on a calcuated field (your previously set global). Then import the records. All imported records will have the value that was set in the global. No looping required.

[Mike-LI-NY]

Trevor - how would you figure out the batch number?

If I have 200 records, 50 are batch001, 50 are batch002, 40 are batch003 and 60 are batch004, how would you write the script to make the next imported batch 005?

[trevorg]

You can use John Caballero's suggestion:

Prior to importing the next batch, sort (ascending) by batch #, then go to the last record, then set a global field with the current batch #+1

However, if your "Batch ID" field has text in it as well, i.e: "Batch 001", then you have to be careful how it will sort, and then you're still left with setting up a method of extracting the number value out of it.

A simpler method would be to have a "behind the scenes" number field with only a numeric value, ie. 1,2,3,4,etc... which would allow precise sorting. Then if you wanted you could concatenate the "Batch" text with the numberic value and place it into the global field prior to import. (use Set Field)

If I think of any other method I'll post it.

[Mike-LI-NY]

Trvor - First, thanks so much for your help - this is my first forray into the world of scripting and am learning on the fly

Now... - as John suggested, how can I make a script to create a global field? I'm having trouble making nested scripts - they all list directly under one another and I can't make them indent (nested?)

[dbruggmann]

Or you calculate the last BatchNumber over a constant relationship (c.nextBatchNumber) and use this calculation field in the script:

SetField (BatchNumber, c.nextBatchNumber)

If your batch# only contains simple numbers, c.nextBatchNumber is:

Max(::batchNumber) + 1 ==> adds 1 to the highest number in the field BatchNumber

For something like "batch001", c.nextBatchNumber could be:

If(

(c.maxBatchNumber + 1) < 10; "batch00" & (c.maxBatchNumber + 1);

(c.maxBatchNumber + 1) < 100; "batch0" & (c.maxBatchNumber + 1);

"batch" & (c.maxBatchNumber + 1))

where c.maxBatchNumber is: Max(:batchNumber)For something like "batch001", c.nextBatchNumber could be:

If(

(c.maxBatchNumber + 1) < 10; "batch00" & (c.maxBatchNumber + 1);

(c.maxBatchNumber + 1) < 100; "batch0" & (c.maxBatchNumber + 1);

"batch" & (c.maxBatchNumber + 1))

where c.maxBatchNumber is: Max(::batchNumber)

[John Caballero]

Hi Mike,

You don't create the global field with a script. You do that under Define Fields.

[Mike-LI-NY]

John.. ok ....

This is what I have - It imports fine and I am using Detlev's idea of using the Replace function to do:

Max(::batchNumber) + 1

but they all come out with the number 1, because I guess it's only looking at the batch numbers n the found ste (which is the records I just imported), but I need it to look at the Max batch number from the existing records - any ideas on how to do this?

Thanks - you guys are all a tremendous help to a novice!

Mike

[John Caballero]

You're correct in why the imported batch # is returning a 1. You need to get the Max batch # (using which ever method works for you) before you import. No matter how you get that value, you'll need to increment it by 1 and store that result in a global field. Use the Replace function (provided the batch # field is on the layout you use when you import) and have it get its "replace with" value from the global field.

Try this:

Create a global number field called gNextBatch

Find some records (or all records) and sort (ascending) by batch #.

Create Script that looks basically like this:

Find All

Sort

Go To Record/Request [last]

Set Field [gNextBatch, BatchID+1] (BatchID is whatever field you use to hold your batch #)

Import Records

Replace[batchID, gNextBatch]

Like I said, that's the basic idea. I'm sure some of the bigger spuds here will have more useful suggestions/corrections.

[-Queue-]

dbruggmann said:

For something like "batch001", c.nextBatchNumber could be:

If(

(c.maxBatchNumber + 1) < 10; "batch00" & (c.maxBatchNumber + 1);

(c.maxBatchNumber + 1) < 100; "batch0" & (c.maxBatchNumber + 1);

"batch" & (c.maxBatchNumber + 1))

Again, Right( "000" & c.maxBatchNumber + 1, 3 ) can be used here.

I would venture to guess that an ascending sort will go much more quickly than a Max( ) function once the record count gets into the ten thousands.

So the best way would probably be the original suggestion:

Find all records and sort ascending by Batch number.

Go to last record.

Store batch number in global.

Import new records.

Replace (or loop with Set Field) batch number field with stored batch number + 1 in global.

Sorry to step on your toes, John. I started this post about an hour ago and got distracted in the meantime.

[John Caballero]

Sorry to step on your toes, John.

Great...now I'm mashed potato.

[dbruggmann]

-Queue- said:

Again, Right( "000" & c.maxBatchNumber + 1, 3 ) can be used here.

Thank you, Queue!

I once again learned: I should read ALL the posts on the forums

[John Caballero]

Also if you created a single-record file that had a indexible field (i.e., not a global), then you wouldn't need to do the find all/go to last record business. You'd manually set the next batch # value one time and just script it to increment each time you imported and do the Replace step, getting the value of the field in the single-record file via a constant-to-constant relationship. This has big advantages if you're in a multi-user environment.

[-Queue-]

Definitely a better idea. Good call, Johnny Spud.

[John Caballero]

That's too funny - my nickname at work is "Johnny Cab"...I didn't get the reference until I saw Total Recall again.