February 27, 2001

Technology: Here at the college, we have shared Filemaker Pro databases via our Novell servers (instead of running separate Filemaker Pro servers) for some time. Until now, it has never been a problem except the occasional instance when a user with Filemaker installed and configured without a network protocol specified opens a multiuser file. This can cause a multiuser file to reset itself to single user.

However, I recently encountered a corruption problem on our new student information database. Although we were serving the multiuser files from a secure Novell server, we noticed that some workstations could not view all fields in related files. Instead of the field data, they would see <file missing> even though other fields in the same file displayed normally. The files were still showing that they were multiuser and the users had identical access rights to the Novell folder in which the files were stored.

I called Filemaker tech support and learned that Filemaker's network security features are not totally compatible with other network protocols. Although Novell Netware will control security access to a file, security within the file (i.e. prevention of simultaneous record access, etc.) must be managed by Filemaker and Filemaker's networking control is only present if a single machine opens the file on the Novell server and becomes the host then subsequent accesses are made only through selecting File, Open, Host rather than File, Open and then browsing to the appropriate Novell folder using Window's or Apple's browse function. This can be very confusing for Novell (or NT or Appleshare) users who can see the file either way.

I discovered that once a multiuser file is open in a network environment, any Filemaker user in the environment can access the file regardless of their Novell security profile. Therefore, I removed Novell access to my database except for the host user so the folder would no longer appear in the File, Open, Browse window thereby forcing subsequent users to select the Host option. Of course, I also had to password protect all of the files to control access through the Filemaker host since Filemaker passes all access security through to database guests. By setting an identical password on all of the related files I was able to create access by inputing only a single password which was then stored in the user's environment for subsequent related file requests.

Although I was assured reconfiguring access to the files based on Filemaker's recommendations would prevent future corruption it would not correct corruption that had already occurred. It was explained to me that damage and corruption are two distinctly different things. Damage often occurs when a host with open Filemaker files crashes and the files are not closed properly. Attempts to open a damaged file will be met with "This file is damaged" error messages. Damage can sometimes be repaired using the File, Recover command. Corruption, on the other hand, is not detectable by Filemaker. I was told a corrupt file may exhibit odd behavior like the type we experienced (or may not) but open normally until the corruption reaches a point where the file "crashes and burns". Since the database was live and had multiple users, we had no point of reference as to when the corruption occurred and the amount of data loss from a distant backup file restoration would be unacceptable.

Furthermore, there is no repair utility for file corruption. A corrupt file must be recreated - not what I wanted to hear! However, I was assured it really wouldn't take that long if I could still open the corrupt file to print field definitions, check relationships, record value lists, copy and paste layouts, and import scripts. Although I was skeptical, I had no choice but to follow this strategy. I opened each of the twenty four related files and printed lists of the field definitions. I also recorded all of the value lists, the layout names and types, and the established relationships. I then recreated each file defining each field with exactly the same name, data type, and options. Calculated fields dependent on a relationship had to be created after all files were recreated and relationships reestablished. Auto-serial number fields were created but auto numbering was not reenabled yet since my corrupted files were all related on preexisting serial numbers. I also redefined all value lists and reestablished all relationships.

I then created layouts with the exact names as layouts in the corresponding corrupt files. Then I opened the corrupt files, selected Layout mode, then copied all objects using the Edit, Select All function. I closed the corrupt file (because it has the same name as the new file) and opened the new file and pasted the objects into the new layouts. I then checked each field association, since Filemaker didn't always identify them correctly, then reset the field formats to associate the appropriate drop down lists. With all layouts copied into all of the files, I selected the Scriptmaker, Import Scripts option in each file and imported all existing scripts. This is a new function in Filemaker Pro 5. I found a tech note that cautioned about Filemaker's case sensitivity. It said that if all file names, layout names and field names were not identical in every way including case, scripts would not be able to identify appropriate database objects and would revert to file or field <unknown>. Filemaker cautioned that all imported scripts should be individually tested.

I was finally ready to import data. I imported all of the data including the previously assigned serial numbers then sorted the primary files by serial number to identify the highest number in use. Then I reset all of the serial number fields to auto number beginning with a number higher than the highest number already in use. This preserved my existing relationships and reset the fields to continue this numbering sequence.

Although this sounds like a tremendous amount of work, I was able to recreate a database with multiple tiered relationships, hundreds of fields, numerous layouts and thousands of records in just a little over one day. It even surprised me since the database originally required weeks to build from scratch.

I also ran into a Y2K glitch in Filemaker. I had to import records from legacy database systems in which dates had been entered with only a 2-digit year. When I checked the imported data, I discovered Filemaker had defaulted to the century "20" instead of "19". According to a technical note I found, Filemaker uses the century of the importing workstation's operating system to determine the default century for dates that had been entered as two-digit years. Why they came up with this totally useless scheme, I'll never know. To resolve this problem, I used several steps. First I created a calculated field Birth Day (type number) which uses the formula Day(Birthdate) to return the number of the day. Then I created a calculated field Birth Year (type number) which uses the formula Year(Birthdate) to return the number of the year. Then I created a text field Birthdate Text and used the Replace with Calculated Result option and enter DatetoText(Birthdate) to populate the field. Then I found all Birth days = 20. Fortunately, these were few and I manually edited the year fields. Then I found all Birth years>1999 then placed my cursor in the Birthdate Text Field and Replaced the contents with the calculated result Substitute(Birthdate Text,"/20","/19"). When this replacement was completed, I placed the cursor in the Birthdate field and selected Replace with calculated result TexttoDate(Birthdate Text).

There may be a simpler way to do this but I couldn't find any references in the Filemaker tech notes database - just a caution about importing two digit years may produce unexpected results. I explored other functions such as the Replace function but this function requires knowing the start position (from the left) of the string you wish to replace and does not allow you to specify which occurrence. The second "/" begins in a different starting position with different dates . It is in position 6 with a date like 12/10/50 but only position 4 in a date like 1/7/50. To prevent this problem with new records, I set a date field validation to strict data type and selected four-digit year.