Date: 22 February 2007 5:14:38 AM
Subject: Re: FileMaker--how to match and flag IDs in two tables
1. How come a test record that I created in A and B that
has bogus ID 1234 does not get flagged by the above
revised script, but other records with valid matching
I think that you need to have a loop in the part of your script that clears your "flag field". You may be keeping old data as the first part of your script will only clear the first record.
2. How come all records in A that got flagged by the
revised script do have counterparts in B, but only
some of the ones in B that have a match in A got
flagged? >
Your revised script checks correctly for duplicates in both files, so I suspect that this is a continuation of the first problem.
3. How do the commands "set Alist" and "set Blist"
function? >
This is really obvious if you set your Script editor to "event Log" and run the script. The script gets every item in the relevant field and creates a comma separated list of them as a variable called aList.
4. How do I fix the revised script so that it properly,
correctly matches IDs between A and B, then properly,
correctly flags the records in A and B that have
matching IDs? >
No guarantees, but if the problem is only the first section not clearing the field properly, create a loop to clear every record
tell application "FileMaker Pro Advanced"
activate
show every record of table "A" of document "test"
show every record of table "B" of document "test" -- show all records in both databases
set AList to field "ID" of table "A" of document "test" (*moved from later in the script so that you can use it in the loop. sets the variable AList to every record if ID of table A*)
repeat with i from 1 to (count of AList)
set field "flag" of table "A" of document "test" to ""
set field "flag" of table "B" of document "test" to "" (*clear the flag fields for new data...You probably want to clear all data in all records so there may need to be a loop here to clear any old data in all fields. This would be easier to use the "replace field contents" in Filemaker to clear all previous flags as this will save time in the loop*)
end repeat
set BList to field "ID" of table "B" of document "test" -- sets the variable BList to every record of ID of table B
set flagList to field "flag" of table "A" of document
"test"
repeat with i from 1 to (count of AList)
if BList contains item i of AList then
set item i of flagList to "flag" -- finds the fields to flag
end if
end repeat
set field "flag" of table "A" of document "test" to flagList
set field "flag" of table "B" of document "test" to flagList -- marks the relevant records.
show (every record of database "test" whose cell "flag" = "flag")
sort layout 1 of document "test" by field "ID"
show layout 1 of document "test"
end tell
(*I would have thought that this script can not work without a loop in it to clear the "Flag field" and your current example lacks this. "Set field" will only mark the current field. Without a repeat to clear the flag field your data will probably contain flags from previous searches.*)
(* I would also tend to refer to layout 1,2,3 instead of table A,B,C, however this is just my habit.*)
