Finding duplicate records and comparing the found records to the file as a whole.

[shpc]

I am attempting to use FileMaker Pro 6 to establish an automated method of scheduling rehearsals for a theatrical company. To that end I have created a File that contains Act/Scene/Part/Page/Role and Entrance and Exit Line Fields. In addition I have another File that contains the Actor's names and their contact information along with a listing of their Roles. Using the relationship of the Role field in both Files, I have defined a relationship that allows the Actor Name and contact information to be imported into the body of the first file. Using a Lookup feature, I'm also able to repeat the Actor Name information in the main file as actual fields that are identified in the Layout.

What I am attempting to do is identify those Records that represent actors who are not available for rehearsal, exclude them from consideration and add the exclusion of both text pages that they appear in and all other actors who would be sharing time with them on these excluded pages. In other words, there is little point calling actors to rehearse a scene or part of a scene that features an absent Role. To that end I have started the process by calling upon a Script to automate the first phase of the parring down process. An initial search of data associated with this goal has revealed a defined script for finding duplicate records in the 5-34 section of the FileMaker Pro 4.0 User's Guide. With some minor deviations in the writing of the supplied template, I have attempted to have ScriptMaker produce a Script that will undertake the first phase of the project by identifying which Records will subsequently have to be tested against the File as a whole to produce an accurate representation of all the Records that will need to be omitted to reveal the Records of what scenes and parts of scenes can be rehearsed in the absence of specific actors. This effort has not been successful to date. I am aware of the fact that there are some syntax differences between FileMaker Pro 4.0 and its more modern 6.0 variant. It is entirely possible that these syntax differences are directly responsible for the failure of the Looped examination to complete its task. Indeed, it is my hope that someone may be able to spot the mistake or mistakes and recommend changes that will make this first phase of the project feasible. At present, the Script file I have created looks like this:

 

Enter Find Mode [Restore, Pause]

Sort [Restore, No dialog]

Go to Record/Request/Page [First]

Replace Contents [No dialog, "Mark", """"]

Set field [Global", "First Name 2"]

Loop

Go to Record/Request/Page [Exit after last, Next]

If ["Global=First Name 2"]

Set Field ["Mark", "X"]

Else

Set Field ["Global", "First Name 2"]

End If

End Loop

Perform Find [Restore, Replace Found Set]

 

This Script differs from the 5-34 option described in that it has had the first line of programming, 'Find All', substituted with 'Enter Find Mode [Restore, Pause]'. File Maker Pro 6 lacks any 'Find' or 'Find All' Script Definition and neither 'Perform Find', nor 'Perform Find/Replace' nor 'Select All' seem to make the Script function properly when used to replace the supplied Enter Find Mode script. Further, the line presently in use does allow the database operator the chance to insert Actor Names to be used in an initial Find to isolate the Records that match with the absent actor or actor's prior to the activation of the Looping Script which should then identify the Records affected with an 'X' being placed in the 'Mark' field.

Other differences between the supplied example in 5-34 and the text listed above include the possible syntax deviation in the line, 'Replace Contents[No dialog, "Mark", """"]. In 5-34 the line starts with the word 'Replace', not 'Replace Contents'. Further, the end of the line contains some odd markings that are similar to the "" that appear in the quotation marks but which may in fact be something else entirely. Finally, the last line of the 5-34 Script reads 'Perform Find [Restore]' rather the more involved FileMaker Pro 6 'Perform Find [Restore, Replace Found Set]'. Incidentally, 'Constrain Found Set' also failed to convince the Script to work. Other than these differences, obviously the names of the affected fields was changed to reflect the search that my database needed to be concerned with rather than the example supplied by the User Guide. To date, there appears to be an endless Loop activated when the Script is run. I had hoped that once this initial Loop had completed its tasks of identifying the Records bearing the absent Actor's Name and its attendant Page references, that these Records might then be compared with File as a whole through the use of another set of 'Global2' and 'Mark2' fields. By comparing the Page references associated with the files marked with an 'X' and those remaining after the first find, I had hoped to identify Page duplications that when omitted would also omit all Records and their associated Actor Names along with the names of the absent actor. A second Loop would therefore also need to be set up to follow the first.

I would be very appreciative if someone could offer an opinion as to whether my Scripting plan is sensible or if there is another approach I should be contemplating. Also, any suggestions about how I have gone about setting up this first phase of finding duplicate records would be very welcome. It would be terrific if someone could identify the error or errors that might make this fly. I regret that I am not entirely sure I understand if the program can Find one set of criteria and then look at the rest of the Records using a linkage with the Found Set. I presume the 'Show Omitted' suggests that this is intended but perhaps my assumptions are flawed.

Any and all help would be deeply appreciated as I am presently spinning my wheels trying to do more than a simple Find to limit the totality of the database to a more reasonable subset. Script programming seems so clever but if it's done badly, as in my case, its potential is entirely unrealized. If anyone has a working template of a Script that does effectively find duplicate records in FileMaker Pro 6, I would be most grateful to have a copy of the correct syntax.

All the very best,

Stephen