Set dynamic Next Serial Value?

[the Otter]

Okay, so here’s the deal: my client wants a system that will allow him to recover all his FileMaker 11 databases within half an hour, in the event of a crash. It’s all working beautifully, all run out of a tiny database that simply imports the most recent data out of the Source File (e.g. the one that just crashed) into the Target File (e.g. last night’s backup). It’s very modular, less than 1 MB, with only about 400 script steps in the whole thing—and about 40% of those are a bunch of If/Else Ifs to import each table appropriately.

 

Obviously, after each import, I want the primary key in that table to be updated appropriately. I can’t perform auto-enter options while importing, because the modification account names, timestamps, etc. are very important to their versioning process. (Okay, I could import each table twice—once with auto-enter options on, once with auto-enter options off—but that will significantly increase the time it takes to run.) So, I decided to use Set Next Serial Value to update each serial number after the import.

 

Here’s the problem: while Set Next Serial Value has the same options as Set Field—specifically, “Specify target field” and “Calculated result:”—they don’t seem to be working the same way. With Set Field, you can leave the first option blank and it will operate on the active field on the current layout. Trying to do the same thing with Set Next Serial Value returns an error #102, “Field is Missing.”

 

Can anyone confirm that this is expected behavior? If so, does anyone know of a workaround besides the obvious, i.e. placing an explicit Set Next Serial Value step after each of the already explicit imports?

 

Thanks!

[bruceR]

Why would any workaround be required?

 

You don't know which field is the primary key?

[Wim Decorte]

+1 on Bruce's question.

 

The name of the primary key should not be a mystery.

[David Jondreau]

What's wrong with the obvious?

 

You have a could other options...you can upgrade and use Get ( UUID ) for keys. You can import a second time, but only have one record in the target and source files.

[the Otter]

Thanks for the responses, everyone.

 

First, a disclaimer: I didn’t design the database, and I’m being very careful with what I change. That’s part of the issue. So, moving on….

 

David, Get ( UUID ) isn’t part of FileMaker 11. There is a UUID custom function in the database, but it‘s not being used for every table. Part of this is because in some of the tables, most of the records are being downloaded from Oracle, which numbers them according to its own parameters. These tables have a serial value specified in the field options so users can create orders manually, but that default is overridden by the ID from Oracle when the primary key is downloaded instead of created. 

 

That being said, in each of the relevant tables, the primary key is whichever field’s name begins with “pk_”. I’m importing 41 tables, which gives me up to 41 different primary keys. I can easily parse the name of the pk_ field from the FieldNames function, but that’s irrelevant because there’s no script step for Set Next Serial Value by Name. So, I’ve given every primary key an object name of “ID” and can therefore simply use Go to Object [Object Name: "ID"]—which would work fine, if Set Next Serial Value worked like Set Field does, but as mentioned, it doesn’t seem to.

 

Make sense?

[bruceR]

No; it does not make sense.

 

You know which table you are modifying. 

 

Therefore you know which is the primary key field for that table; and your script should be specifically written for that table.

[the Otter]

Hey, BruceR.

 

You’re correct that I know which table I’m modifying if I use 41 script steps, but that’s exactly what I’m trying to get away from. I’m calculating each next serial value from a subroutine called by the parent script. It would be a lot easier to just put a Set Next Serial Value [$NextSerial] step into that subroutine instead of creating 41 separate Set Next Serial Value steps in the import script. A single step is a lot easier to tweak, plus it’s extensible so any future tables will automatically be updated along with the existing ones.

 

Thanks again.

[Wim Decorte]

You're overthinking it.  If you have 41 tables you are going to have to 41 scripts, one for each table so that you can do the proper importing with the proper field mapping.  No way around it.  Since you have the script you can easily set the pk to it's next serial value.

 

The only place where abstraction comes into play is if you want to run a generic report that tells you what tables are in the file, what the record count is and what the next serial is. For that you'd need a pk that is named the same in all tables (like "ID") and for the rest you can rely on the FM meta tables to do the work for you.

I use that all the time as a before-and-after quick check.

 

But for setting the next serial - provided you can't/won't use UUID - then you simply do that in the import script, which can not be abstracted anyway.

[the Otter]

Thanks, Wim. That’s basically what I did, although like I said, I’m actually using one If/Else If script for the imports. Each Else If is

Else If [Let ( $Counter = $Counter + 1 ; $Counter = $$Counter )]

…so each time the Import subroutine runs, it fires off a different import. ($$Counter is set inside a loop in the parent script, which also tells it which layout to switch to.) I just added the Set Next Serial Value after each of the appropriate imports. Annoying (as are the many import steps, to begin with), but at least it works.

 

So I guess we’re in agreement that even though Set Next Serial Value allows “Specify target field” to be blank, it’s not really optional?

[bruceR]

No, the set next serial value script step does not allow the field spec to be blank.

 

Test it. Set error capture on. Watch in script debugger; and capture the error.

 

The error is 102; field is missing.

[the Otter]

Thanks for the confirmation.