how to match and replace

[barryfh]

Hello,

I have two data sets, one with ~300 records (Table A) and the other with ~1,400 (Table , both containing unique IDs (i.e., each ID in Table A occurs only once; likewise for the IDs in Table :. Some of the IDs in the 300 records are missing from the 1,400. Both tables have certain fields in common; e.g., gender, country, education level.

The goal is to search Table B for each record's ID in Table A. If a match is found (again, some IDs in Table A are missing from Table :, copy data from fields in B for which the corresponding fields in A are blank.

Could someone please explain exactly how to script and/or calculate this in FMP 8.5 advanced? Or, if this already has been explained elsewhere in the forums (I did search, but came up empty handed), could someone please provide a link?

Many thanks in advance!

Edited February 3, 2007 by Guest

[LaRetta]

Welcome to FM Forums!!

copy data from fields in B for which the corresponding fields in A are blank.

The idea is NOT to have redundant information. If there is no relationship but the tables contain some of the same information then they SHOULD be related. We would be happy to help you with that if you provide more information about your specific table/field names and how they are related even though not related. What is the meaning and purpose of both of these tables?

As you are finding out, having duplicate data is a major PIB to keep in synch. There are many wonderful people here who will help you change it so you can remove this problem from your list forever AND decrease your file size as an added benefit.

LaRetta

[barryfh]

Hi LaRetta,

Thank you for taking time to reply.

Table A will be used to run statistical analysis in SPSS to complete a mandated federal report on our educational program. Table B has information that is missing from Table A.

The goal has little, if anything, to do with decreasing file size or improving database efficiency. I urgently need to programmatically copy the data missing from Table A, but is present in Table B, into Table A.

Hope that answers your question about the meaning of these tables.

Each ID in each of the two, currently unrelated FMP files, is unique within its respective file. There are no duplicate IDs within either of the two files.

As stated previously, the information (fields) that needs to be transferred from one file (table) to the other is: country, ethnicity, level of education, and the like; they all are plain text fields.

Please note that my rating is "novice," meaning that I already know how to relate tables, and understand basic database concepts but lack programming skills. I have about 5 years experience with FileMaker and another 8 years of experience with dBase/FoxPro. However, I am neither a developer nor a database scientist.

What I need to know is how to script FMP (and/or write calculations) to match on ID and replace fields' blanks in Table A with the corresponding fields' information in Table B.

Can you please tell me exactly how I can accomplish my stated goal in my initial posting?

If you need additional details, please specify exactly what you need to know so that I can expedite.

Thanks again and have a wonderful weekend!

Edited February 3, 2007 by Guest

[LaRetta]

If a match is found (again, some IDs in Table A are missing from Table :, copy data from fields in B for which the corresponding fields in A are blank.

Sometimes information must be ‘frozen in time’ because the data will change later and it is necessary to capture the entity’s status at the time (of a mandated government report, for instance). An example is an Invoice where the client Terms may change later but Terms at the moment of invoicing must be applied to only the current Invoice. Thus, Terms must be planted within the Invoice otherwise the Invoice Terms would incorrectly change if the client changes Terms later. Since most mandated federal reports must be captured and stored as audit/history, I will proceed on that assumption here because otherwise, the fields from TableB can simply be displayed in place of fields from TableA (if fields in TableA are blank).

Please see if the attached is what you require. You would of course also want to set a ReportDate within TableA but I didn't want to muddy the sample with unnecessary fields. It might also be wise to add protection to test that 1) if data needs to be pulled from TableB but no matching TableB::ID exists, a final error message (via Custom Dialog) and found set of any problem children is displayed for further research; as well as verifying there are NO remaining blank fields in TableA before report transmittal. But these are other issues and I'm sure you are aware of them. You may also wish to isolate the records in TableA which contain blank fields. It would save the multiple If-tests if all fields are complete. You can do so by performing a Find (with multiple Find Requests; one for each field based upon =) BEFORE the Go To Record/Request/Page [ First ]. There are ways to make the process more dynamic so that the values don't need to be hard-coded within the script. To add a field to the 'process' you could place the field on this special layout. But I'll wait for further input to be sure we are on same track.

There is also possibility of pre-filling the fields using values from TableB (using auto-enter or lookup) and allowing User override if they need to change it in TableA. Again, it is a timing thing and much depends upon why partial data exists in TableA to begin with (it sounds like TableA data would always take precidence if not empty and that it may be different than TableB?). It also depends upon how the data is completed within both Tables and so forth. But I hope this moves you forward. Please let me know how we can adjust the process for you.

LaRetta :wink2:

StatisticalAnalysis.zip

[barryfh]

Hi LaRetta,

Thank you very much for the reply and file.

Yes, you are correct; some of the 300 IDs in Table A might be missing from Table B and your inclusion of an error handler is spot on!

I will test it out and report back as soon as I can (hopefully by tomorrow, Sunday).

Cheers and have a wonderful day,

Barry

[LaRetta]

Hi Barry,

This file is pretty simplistic. I didn't want to delve into unknown waters until all needs were clear and questions answered. Just start a list of adjustments that you will need (whether considerations I suggested or other issues) and we'll nail this puppy down for you.

LaRetta

[barryfh]

Hi LaRetta,

Thank you again for your time and expert help!

I played with the script today and it looks as though it will meet my needs with modifications that I should be able to handle.

Will know more definitively after returning to the office tomorrow and checking the real data.

Again, thank you soooo much!

Cheers,

Barry

[barryfh]

Hello again,

I have two related tables in an FMP (8.5 advanced) file.

Table A has ~300 records, B ~2,000.

Each record in A is unique for sure; I believe each one in B is unique.

The goal is to determine whether any of the records in B match one in A based upon student ID.

These IDs are like social security numbers; i.e., they are unique 9-character values assigned by the university rather than ones that I'm assigning within FMP 8.5 advanced.

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 and happy weekend!

[LaRetta]

A few things ... 1) I don't do Apple Script (yet, I just got my Mac) so can't help you and 2) Why are you approaching the problem this way when (I thought) we had it worked out for you - it would be the same principle. Create a new table occurrence of Table B (maybe called StudentIDs and join to Table A on that Student ID. Then all you need is:

A calculation in Table A (number) with: not IsEmpty ( StudentIDs::ID ) will produce an 'automatic' flag of 1 indicating that it has a match (according to the ID) in StudentIDs (Table :. And similarly, a calculation in Table B (number) with: not IsEmpty ( Table A::ID ) will produce a 1 if there exists a matching record in Table A. You don't need script to actually mark the records at all.

LaRetta :wink2:

[barryfh]

Hi LaRetta,

Happy Friday!

Thank you very much for the prompt reply.

Congrats on obtaining a Mac! What model?

>Why are you approaching the problem this way

1. Because I tried modifying the above solution to meet my new/different needs and it was unclear to me how it incremented the record in both tables to check whether there was a match on ID.

Will you please explain how the above solution goes to record 1 in Table A, stores its ID in memory, then switches to Table B, checks to see whether the IDs match, then if they don't, increment to the next record in B, if they don't then increment to the next record in B, and so on until there is a match, then switch back to A, increment, switch to B, go to the first record, check for a match, if they don't, then increment, and so on?

2. Because this AppleScript that I found somewhere looks to me as though it does what's in my item 1 above.

3. Because my newest posting represents a different project, different task, different goal.

I need to *flag* the matching records in both tables rather than transferring data from one table to the other.

>when (I thought) we had it worked out for you

That was a different task, different goal.

Flagging the records that match on ID in both tables is imperative. I need to be able to spot the matching records readily in both tables.

>A calculation in Table A (number) with: not IsEmpty ( StudentIDs::ID ) will produce an 'automatic' flag

Really? Cool! In which field will it automatically insert the word "flag" or the letter "x" so that I then can search for the records containing "flag" or "x" in that field?

>You don't need script to actually mark the records at all.

I do, please believe it! That is exactly why I posted and asked the four questions in my original post, the most important one being #4.

If #4 is doable using ScriptMaker, great!

In that case, could you please explain the logic of the script to me per my question in item 1 above in this reply (so that I can understand, learn, and fend for myself!)?

Again, many many thanks for your time and help. Happy weekend!

[comment]

Flagging the records that match on ID in both tables is imperative.

In which field will it automatically insert the word "flag" or the letter "x" so that I then can search for the records containing "flag" or "x" in that field?

The point is, you can find matching records WITHOUT flagging them first. So if your ultimate purpose is to find them, flagging them is just wasting time and resources.

To find the matching records in the other table, make your script show all records, then go to related record (show related only, match found set).

---

By 'script' I mean a Filemaker script, not Applescript.

Edited February 16, 2007 by Guest

[barryfh]

Hello Comment,

Happy Friday!

Thank you for your reply.

To reiterate, I do *need* to flag the matching records.

My goal is to take administrative action that is time-sensitive on behalf of the students to whom the records belong. Thus, I need to be able to see the ones that match between the two tables and that's why I want to "flag" them.

Re: "flagging them is just wasting time and resources." We're talking about two tables that between them have fewer than 3K records. [color:red]To have a script flag them will take, what, maybe 3 seconds of CPU time on my PowerBook G4?

If you know how to accomplish this in ScriptMaker, could you kindly please help me in writing such a script that addresses my question #4 above?

Thanks in advance for your help with this time-sensitive task.

Have a wonderful weekend!

[comment]

You CAN flag them, if you want. LaRetta already told you how: define a calculation field (result is Number) =

not IsEmpty ( OtherTable::MatchField )

This will return 1 for records that do have a match in the other table, 0 otherwise. However, this is an unstored calculation, thus less than optimal for searching.

Even simpler way to visually flag the matched records is to put any field from the related record on the layout. Unmatched records - the field is empty. Matched records show something (provided you picked a field that is never empty).

[barryfh]

Hi Comment,

Thank you for taking time to reply again.

And thank you also for pointing out LaRetta's suggestion about using not IsEmpty.

I did see her suggestion previously, and in my reply above to her suggestion, I responded

"Really? Cool! In which field will it automatically insert the word "flag" or the letter "x" so that I then can search for the records containing "flag" or "x" in that field?"

Since she and you evidently have used the not (IsEmpty) command, her suggestion made more sense to you both than to me.

You have partially clarified by saying that it will return 1 for records lacking a match and 0 otherwise.

I am still missing an understanding, though, of how the not (IsEmpty) command differentially flags records with a match on ID vs. ones lacking such a match between the two tables.

Could you please explain that (so that I can learn, understand, and fend for myself in the future); i.e., exactly how does the not (IsEmpty) command flag as you have described for matching vs. nonmatching records?

I understand that your suggestion would be more elegant.

Cheers!

[comment]

LaRetta's sugestion and mine are identical.

If a record has a matching record in the other table, then the matchfield in the other table CANNOT be empty (else there would be no match). If no matching record exist, the field in the matching record MUST be empty (since there is no such record).

[LaRetta]

I am hoping I don't confuse further but I see three issues: 1) You have an existing relationship between the tables based upon a unique FM ID. 2) You now want to find matches based upon the Student ID. 3) You want to isolate record sets and, while viewing any record, know whether it has a match.

I've attached a file. It opens on your large table (Table :. The top yellow is your standard relationship (based upon FM unique ID). The pink is a NEW relationship matching StudentID to StudentID. View each of the three records. As you view these two different relationships, you can then go to your graph and see they match exactly how your relationships are set up.

I have scripts which show isolating the record sets however you wish ... a) if you want a list of B records which have a match in A, if you want a list of B records WITHOUT a match in A, c) if you want a list of B records who have a match on Student ID from A and d) if you want a list of B records WITHOUT a match in A on Student ID. As you will see, the GTRR (Go To Related[]) does the work for you to isolate the sets. And, as Comment pointed out, it is unnecessary to mark the records at all because the fields in red SHOW whether there is a match (if blank, then no matching otherwise it displays Table A's matching ID). Those fields ARE from the other relationship (either your original Table A relationship or the new StudentID relationship). No additional fields required.

In the example file, record 1 matches on FM ID but does NOT match on StudentID. Record 2 matches on both FM ID and Student ID (notice both red fields will also tell you). And record 3 matches on StudentID but NOT on FM ID. Isolating the records sets so you can perform your admin tasks is just a matter of GTRR (as per the scripts).

Again, no record marking required because your relationship already knows (and can produce) whatever list you require.

LaRetta

FindRelated.zip

[barryfh]

Hi LaRetta and Comment,

Thank you both for your replies. I really appreciate your taking time and your helpfulness.

The good news is that yesterday I solved my need on my own. LaRetta's posts in this thread

Thank you!

However, I still feel that I am lacking an understanding of how the AppleScript posted above functions and how to fix it properly (so that I can learn and thus fend for myself in the future). I understand that LaRetta is new to Mac, but hopefully someone else with AppleScript experience can help me out.

Before I began using FMP, I used FoxPro and dBase. Although my programming skills were (and still are) weak, I could at least follow and understand the logic of the .prg files that I used to accomplish the above tasks.

For instance, the following is a program for finding and marking matching IDs between two files in either FoxPro or dBase.

set talk off

select A

use file1.dbf index file1.idx

select B

use file2.dbf index file2.idx

Select A

do while .not. eof()

store id to idw

Select B

Seek idw

if found()

Repl flag with 'm'

Sele A

repl flag with 'm'

else

Select A

endif

skip

enddo

set talk on

select A

count for flag = 'm'

sele B

count for flag='m'

Sele A

I can tell you exactly what each of the above commands does, and how.

FMP's ScriptMaker commands, by contrast, seem more transparent to the user and thus I have difficulty following the logic of exactly how they function.

Enough. Thanks again and have a terrific weekend!