Recovery of Serial Numbers

[LaRetta]

Hi everyone,

I am scripting a recovery process. I've attached a file I have started (actually two files): One with the data and the other called GrabSerials. What I want to do is this:

When I export recovered data from each base table (layout) in the Data file, capture that 'next serial number' in the GrabSerials file. Then when the .MER files are imported into a new clean clone, set the next serial with that matching serial number.

This process should not relate between the files in any way; or, if it relates, it should only relate simply from one FILE to the other and not from the GrabSerials TABLE to each table in the Data file. Make sense? Is there a way to script and create records in GrabSerials as I export the data from each layout (table) in the data file?

I suppose I could sort each newly imported table by its serial, go to last record and grab the serial etc. But I am asking this question because sorts on unindexed newly imported tables will NOT be fast. I believe that, since I am scripting it anyway, creating a record in another file for each export MIGHT be faster. Also, there may be reasons that simply grabbing the 'last serial + 1' isn't safe enough. I cannot give you an example when it wouldn't be, but if I ALWAYS use the serial from the trashed file (listed in auto-enter serial) then I know I will always be safe. If I'm off base and this idea isn't the way to go, please tell me straight. So far all I can think of is to 1) open database definition and write down each 'next serial' or 2) sort and set next serial.

ps. When I recover files, I do NOT 'perform Auto-Enter' upon import. Doing so destroys all of my Modification timestamps, modified by fields and so forth. So the serials must be set via script or manually. Ideas appreciated. I apologize that the attached folder doesn't have anything really to go on ... I simply wasn't sure how best to proceed. But the structure is in place for your use.

LaRetta

REPLACED FILE (ADDED FieldName)

Grab_Serials.zip

Edited July 22, 2008 by Guest

[Fenton]

Function:

GetNextSerialValue(fileName;fieldName)

Example:

GetNextSerialValue("Customers";"CustID") returns the next serial number for the CustID field.

Get (FileName) can be used for the file name instead of text I believe. But it can be, and usually is text, another file.

The FieldName IS a "field name", ie., text. It is NOT a reference to a field, hence must be in quotes.

This is good, because you can build the function to target a field that is not in the current file, nor related in any way.

But the file specified must be open; notice I say specified, not referenced (which would imply a File Reference).

The script step to use is:

Set Next Serial Value

You can combine these two in one step.

Edited July 22, 2008 by Guest

[comment]

This can be easily accomplished using the GetNextSerialValue() function:

GetNextSerialValue ( gFileName ; TableName & "::" & FieldName )

Note that the recovered file must be open.

However, there is the question whether this is reliable enough, coming from a recovered file.

I presume the recovered file will always have the same structure and the same name - so the other file could be a "control file" that has all the TO's of the recovered file. I wonder if it would take that long to get the Max() of serial ID over a cartesian product relationship (that exists only in the control file) and compare it to the next serial value - just in case.

[LaRetta]

Hi Fenton! Thank you. Yes, I planned on using GetNextSerialValue ( Get ( FileName) ; "InvoiceID" ) to create the record's ID in GrabSerial. I see no way around hardcoding the fieldname within the script however, since FileMaker doesn't recognize (or treat) auto-enter serials in any special way. And that's okay, since I'm hardcoding the export from the Recovered tables anyway so creating the record in GrabSerials shouldn't be a problem.

My main confusion stems around how to grab the corresponding serial from that fle after import to set the next auto-enter serial in the proper table in the new cloned file. But at least you didn't totally poo-poo the idea so that indicates it might be an acceptable approach. :wink2:

[LaRetta]

Hi Michael, just considering out loud here ...

Reliable coming from recovered file? It would be relative term any time recovery takes place; whether the field data of the uniqueID is damaged or whether the auto-enter serial (field definition) is damaged. I have never seen a damaged auto-enter serial (in field definitions) although I've seen plenty of damaged calculations and particularly custom functions. But I HAVE seen damaged data AND, if (in the moment of crash) a script is creating a record, the auto-enter serial FIELD DATA can be blank (although, IIRC, it still completes lookups sometimes before it crashes). I would think we would want a placeholder for that partial record (or would we?).

There is also another consideration in resetting serials: I've already scripted (and most people would remember) to perform a Show All Records before exporting from Recovered files. But if that is missed, and you set the serial to the Max() and activity begins before it is identified, then you have duplicate serials. If any records are trashed (that need to be pulled from backups), one might also cross over the serials. These are the reasons I've always dis-trusted records and relied more on auto-enter definition.

I wonder if it would take that long to get the Max() of serial ID over a cartesian product relationship (that exists only in the control file) and compare it to the next serial value - just in case.

I'm not discounting this possibility entirely; it just makes me very uneasy but might work as a double-check to be sure. The files will be identical except one will be named with Recovered at the end and the layouts are FM created and the layout names match the table occurrence names exactly. Also, the clone files are re-used after each crash, ie, I go back to the orignal good files each time since we have stopped all development on the solution until we resolve the network issues.

Added comments on my perceptions or suggestions on my considerations would be greatly appreciated.

UPDATE: BTW, thank you also for the idea of combining the table and field names when creating the record. If I end up going that route, your suggestions and Fenton's suggestions will come in handy in scripting it.

[comment]

Reliable coming from recovered file? It would be relative term

Yes, that's why I think comparing next to max and taking the larger one in case of discrepancy would be a good idea. BTW, I don't see that asking for Max() in the control file causes indexing in the recovered, so it could be fast.

I can see no harm in hard-coding the file/table/field names here (provided these are periodically synched to on-going development). After all, this is tailor-made for a specific solution, no? You could use the control file to mechanize most of the recovery process, perhaps even all of it.

[LaRetta]

Yes, that's why I think comparing next to max and taking the larger one in case of discrepancy would be a good idea.

Ah. This makes sense after some sleep. Yes, taking the largest of the two (one which grabs the serial from Auot-Enter definition and one which is Max() SerialID). What are you calling the Control File, Michael? Is that the GrabSerial file or are you envisioning an approach like migration ... with target, intermediate and source files? Since we have identical structures, I didn't see the need for intermediate. Are we seeing this concept the same way?

[comment]

It *is* migration, in the sense that you need to move data from recovered to clone and do some cleaning up. I don't see a need for an intermediate either, I just think that the logic of migration (as much as possible) could be in a separate file.

[LaRetta]

I had already put the Recover Export and Recover Import scripts within the data file itself. At this point I guess it doesn't really matter since the final solution will have a different recovery process entirely (FMDataGuard) so these two scripts will eventually be deleted. I suppose I should have used the GrabSerials file (named instead Control) to house the scripts and then create file references to the source (recovered) and target (clean clone). But we already had a layout named each base table occurrence so creating this portion in the control file would have meant adding 25 layouts to match.

So yes, tonight I will add the serial grab portion (and also record count and serial comparison final report) to the process. Wish me luck! And I've no doubt that you are right ... as soon as I get this process in place, they will find the problem and have it fixed. But I will STILL win from having done it. :wink2:

[LaRetta]

I'm stuck right off the bat, Michael. I planned to use a script parameter which used a Let() to specify the variables so I could pass layout name (table name), file name and GetNextSerial(). But my Recover Export script looks like:

Go To Layout [ Users ]

Show All Records

Export [ yada yada ]

...repeat with every table (moving to each layout and repeating)

I can attach a script parameter to the first time I execute the script (if I MUST place it on layout simply to attach a script parameter) but, as I move from layout (table) to layout, how do I 'regrab' the proper script parameter to pass to the Control file (previously named GrabSerials) for the new record creation?

I suppose I want to relate via cartesian anyway, like you suggested, to each table from Control. But even if I do that, I believe I will have the same question. I've used script parameters to pass values and variables to another file before ... but it has always been based upon clicking a button. I assume its something very simple I'm missing? I just don't know how to CHANGE the script parameter value within the same script - there is no Set Script Parameter[].

UPDATE: Maybe I should skip adding the serial process within the export. Maybe that should be handled separately. But I figured that, since I was on the layout, I should do it then.

Edited July 22, 2008 by Guest
Added update

[LaRetta]

Or maybe I could use globals and set the globals and refer to them within the script parameter? That feels like I'm going backwards in concept to pre-variable and pre-script parameter era. :content:

Edited July 22, 2008 by Guest
Added update (what's new) :-)

[comment]

I'm afraid I didn't get what you need to do.