MS Word Merge, Gap-Free Serial ID

[carmenm]

This is a special situation, I share it partly to get feedback and partly to keep people from banging their head as I had to.

I'm using MSQuery to create a .dqy file that connects Microsoft Word 2003 to FileMaker 8 Server Advanced via ODBC/JDBC and SequeLink. It took quite a bit of doing and some help from MS tech support to figure out that MSQuery was the way I needed to go. It works.

Now, our users need to merge individual record information into complex Word documents, and do a great deal of word processing with this information. The data is updated frequently throughout the day, so exporting to .mer or Excel isn't going to work.

Word gives me two ways to get the data, search by a field or merge a range of record numbers (in my case this will always be 1 record). Search by a field is broken for my purposes. It requires too many steps for the end user. I tried to automate it somewhat with Word scripting and macros, but that breaks the connection. This type of scripting will only work when Word is merging from another Word document or Excel.

To make a long story short, our users need to know *exactly* which record they are merging from FileMaker by the row number it appears in in the MS query results. This information must stay the same for each record throughout its life in the database. There can obviously be no gaps in the sequence, because there are no gaps in the rows of query results given to Office. I plan on displaying this merge number to the user in FM Client, FM IWP, and in the merge documents.

Some things do work in my favor:

I can put strict controls on how records are added and I can keep records from ever being deleted. I will wipe the database clean annually, as it runs by school year. The records must already exist in another FM database before they can be added to the Merge database, so I'm doing a good deal of pre-creation checking anyway. The database will never be over 10,000 records.

I am leery about using FileMaker's built-in RecordID. It seems very sensitive and if it ever does get off for any reason there is no way to fix it. I'm also considering using an indexed calculation field for Get(RecordNumber). I can always make sure all records are showing and the correct sort is in place when a record is created.

Or, I could create a separate table that assigns each record a serial Merge ID according to the timestamp it was entered in the database...

The latter probably gives me the greatest control should something go wrong.

Up to 30 people will be using the database, 10 clients and 20 IWP.

Does anyone have any alternate suggestions, or things I should watch out for? I'd love to hear them.

Thanks!

[Vaughan]

Get(RecordNumber) depends on the current order of the records in the found set, so it is sort sensitive.

You probably mean Get(RecordID) but this too won't meet your needs because the numbers aren't contiguous (at least they weren't in earlier versions).

I don't know why you think FileMaker's auto-entered serial numbers are "sensitive".

[carmenm]

I can make sure to specify the sort order on record creation and for the MSQuery query, so that shouldn't be a problem for Get(RecordNumber). Then, if I index the value it will not change.

My worry with Get(RecordID) is... if something should happen and a user creates a record but, for some reason, record creation fails that number is forever lost and the series is broken, and every merge record beyond that record would be broken. Get(RecordNumber) would not have that problem.

Maybe I'm being paranoid, but I'm trying to look at it from various angles.

Edited August 2, 2007 by Guest

[Vaughan]

"Then, if I index the value it will not change."

Indexing has nothing to do with the ability to change a value or not.

[carmenm]

I should have said, the Get(RecordNumber) value will stay static as it was initially set by FM on record creation and not recalculate on its own, no matter what the sort changes to. At least, that is my understanding of indexing the results of a calculation field. Let me know if I'm incorrect.

[Vaughan]

You are incorrect. I think you are confusing "stored" and "unstored" calculations.

A calculation field that references a Get() function needs to be unstored. Unstored calculations cannot be indexed, but as I have already said, indexing has nothing to do with wthether a field can be changed or not.

You need to auto-enter the Get(RecordNumber) into a normal number field, but then the problem will be that it will result in the record number of the found set of records that is being imported, so it won't be unique. The first record in the found set will be 1, etc.

Just use an auto-entered serial number and stop farting around trying to fix something that isn't broken.