FileMaker--how to match and flag IDs in two tables
FileMaker--how to match and flag IDs in two tables
- Subject: FileMaker--how to match and flag IDs in two tables
- From: "B2 Fass-Holmes" <email@hidden>
- Date: 20 Feb 2007 04:25:14 -0800
Hello,
Tiger.8
Filemaker Pro 8.5 Advanced
I have two related tables in an FMP file.
Table A has ~300 records, B ~2,000.
Each record within A is unique for sure; I am almost certain that each one within B is unique.
The goal is to determine whether any of the records in A match one in B based upon student ID.
These IDs are like social security numbers; i.e., they are preassigned, unique 9-character values rather than ones that I'm assigning within FMP.
Like SSNs, these IDs are unique. So within Table A, each ID is unique. Likewise, within B the IDs are unique.
However, some IDs in A might also be in B. I need to find out programmatically which ones, if any, are in both tables.
If a match exists, I need to flag a field in each table.
I found and modified an AppleScript that someone else wrote.
The original is as follows.
tell application "FileMaker Pro"
activate
show every record of database "Original Database"
show every record of database "Old Database"
set field "Match" of document "Original Database" to ""
set orgList to field "Full Name" of document "Original Database"
set oldList to field "Full Name" of document "Old Database"
set matchList to field "Match" of document "Original Database"
repeat with i from 1 to (count of orgList)
if oldList contains item i of orgList then
set item i of matchList to "Match"
end if
end repeat
set field "Match" of document "Original Database" to matchList
show (every record of database "Original Database" whose cell "Match" = "Match")
sort layout 2 of document "Original Database" by field "Last Name"
show layout 2 of document "Old Database"
end tell
My revision is as follows.
tell application "FileMaker Pro Advanced"
activate
show every record of table "A" of document "test"
show every record of table "B" of document "test"
set field "flag" of table "A" of document "test" to ""
set field "flag" of table "B" of document "test" to ""
set AList to field "ID" of table "A" of document "test"
set BList to field "ID" of table "B" of document "test"
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"
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
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 sort of understand what each of the above commands does, but would appreciate answers to the following questions.
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 IDs in both A and B do get flagged?
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?
3. How do the commands "set Alist" and "set Blist" function?
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?
Many thanks in advance!
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/mailman//archives/applescript-users
This email sent to email@hidden