Is there a script for this?

[FM-student]

How to copy data from a matching source record to a result record and

not overwrite the original result record data when it is different.

An .fp5 file contains records from two similar FileMaker Pro 6

databases, the result file and the source file. Goal is to get all

data from the source file that is missing from the result file into

the result file.

Can a script to do the following?

for two records that match on company name:

evaluate each pair of fields from the two records, eg, phone number

from the result file record vs phone number from the source file

record

if the two fields have equal contents, do nothing

if the source field is blank, do nothing

if the result field is blank, and the source field is not blank, copy

the source field into the result field

if both fields are not blank and they have different contents, copy

the contents of the source field into a different field in the result

record (perhaps a repeating field) to preserve that information into

the result record

continue with the above for each field in the record layout

At the end of the process, all information from the source record

will also be in the matching result record and none of the result

record

[Doug Leib]

Yes this can be done a couple of different ways depending upon the exact particulars. To offer a detailed step-by-step answer one would need all the details.

In general, the two methods that occur to me would be to either use a relationship formed by the match field and script the comparison and set field process or use a find instead of the relationship but the relationship method would be preferable.

Doug

[FM-student]

Dear Doug,

Thank you for your encouraging reply.

May I offer you the details?

We have sixteen .fp5 files, each containing similar information about schools and their programs.

Each file can have its own record layout with different names for the same fields. Some fields are not found in every file.

The file we beleive to be the best one has these 20 fields in its records:

School Name

Address

Listing status

City

State

Zip Code

Country

Person to Contact

Year Founded

Degree Levels

Type of School

Ownership

Phone Number

email

website

Current Listing

Notes

BG13 Text

Pro Listing

Include in Pro

Another similar fp5 file has these 45 fields:

Date

last mod

Listing status

School Name

Address

City

State

Zip Code

Country

Person to Contact

Fields Offered

Year Founded

Degree Levels

Type of School

Accreditation

Ownership

Residency

Phone Number

Fax

Tollfree

email

website

body

Comments

BG13 text

for mailing

Cost

chapter

Reply

Other Books

Record Number

B

[stanley]

Brad:

This is a bit hard to process without knowing what the actual aim is. By which I mean to ask, what are you looking to do here? I see your particulars, but I don't see what result you're after, and something about the way you've designed your solution indicates to me that you're going about this the hard way. In short: what are you trying to accomplish, in general terms, with your data?

-Stanley

[MoonShadow]

It is my impression that Brad is attempting to consolidate. If so, I currently have a similar situation with 26 dbs which are 'similar' and contain much of the same information. It is not a design choice but a design solution ... finally.

Brad, there will be no easy way. You need a db with every possible field to create your 'perfect' db - no creating fields on the fly. Matching fields and records can be tedious. Some of the work can be handled as you are attempting (using built-in logic that you can identify and apply rules to) but be prepared, because someone will have to eyeball much of it and make individual decisions. Establish the precedence for them based upon database and individual field. Use form layouts and portals to display the pre-mapped fields from the other dbs. Instead of writing the mis-matches to another field - which still means someone must look at each one, produce a portal of similarities and provide buttons for this poor person to select which data to use in your final 'perfect' db. Let scripts move the data for you.

Our sales team are currently doing just that ... they have a 'verification' screen which displays a 'matched' field from each of the other dbs. They must select the 'correct' information and scripts move the data. Once complete, that record is flagged as 'proofed' and the others are flagged as disgarded. Of course, 40% of the data was compared and consolidated via FM script first. But, similarity isn't a science, because patterns are difficult to pin down. What matters more is actually the integrity of the field data - where did it come from? Who put it there? And who put data in the db/field it's being compared to? Which would take precedence? Establish your rules and provide scripts to move your data at a User's command.

You may find something like Find Similar Values here useful - I know we did.

And my apologies for this lengthy (and possibly worthless) contribution, if your purpose isn't to considate, Brad.

[FM-student]

Dear Stanley,

Appreciate your fine question.

In a nutshell, there are sixteen fp5 files containing information about schools. We would like to combine all the information into a single fp5 file, eliminating duplicate records.

We wish to retain all the information from each source database in the single resulting database.

The goal: a single database where people can easily look up well-organized information about the

schools.

Am enjoying your idea that there may be an easier way to do this. Please say more, if you like.

Thanks for your posting,

Brad

[FM-student]

Dear MoonShadow,

Yes, as you say, the goal is to consolidate the sixteen fp5 files into a single more useable one.

Appreciate your many useful insights into the process.

My colleague is doing this as a research activity and so wishes to keep the different versions of the same information in the resulting database. Not if they are off by a comma or a misspelling, of course.

But, for example, if different sources show a different head of a department, that could be an interesting change in the school over time.

So, he would wish to show the different versions of the data for that field and which of the source files it came from. Then, it would be possible to see who had provided which information and when.

Can you say if that, possibly not usual, need will make the process easier or harder to automate?

Thank you for your detailed and helpful posting.

Best regards,

Brad

[Doug Leib]

I think we understand your requirements now. There are multiple way to accomplish this, some of which have already been alluded to. Here's you situation in my opinion. While not particularly difficult... what you are trying to do will be tedious. Since each of your databases vary there is no one succicinct answer we can offer you here.

Maybe someone else will see something I'm missing but there is no ONE script that we could relay here that will deal with all of the permutations in your scenario.

Doug

[MoonShadow]

No small feat, Brad. And I agree, Doug!

Too bad an audit log couldn't be used. CobaltSky's new Audit Tracking for 7 doesn't record a field change at all with a scripted Set Field[] and his version for < 7 creates a new log line but doesn't set the log with the new data. It instead returns a [null] when a script is used. Because the concept would be perfect to incorporate in your project. Just too bad an auto-enter or audit-type process (at the field level) couldn't assist you with it - triggering upon modification of the field. Scripts can be a blessing and a curse sometimes because they play outside the 'field rules'.

Okay, here's my hit on it ...

Create a db called TARGET which will contain every possible field - your 'perfect db'. You will need to decide which fields from each database will be compared and logged to each field in Target so map these field matches on paper. Once you have this perfect empty db, clone it and name it SOURCE. Create a relationship from CompanyName in Source to Target::CompanyName.

I would import each db into Source as new records and use a global to store the 'Source File' name and write that name (Looping Set Field) to every record in your newly imported found set. Between each import, change the Source name in the global. I would import into Source db in the order you want their differences to appear in a list result field in Target. In other words, in the resultant history list, the LAST db you import will end up on the bottom of the list.

And your last request, "...if a source record does not match any result record, mark it for retention in the result file."

At the first of your script solve this one by Finding all CompanyNames in Source that are do not have a valid relationship to a CompanyName in Target using an IsValid[] comparison. And script a pull into Target (ie, create an Import Source script in Target which you activate via subscript from within Source). The found set of unmatched CompanyName records will be imported into Target and then you can delete them from Source. Get them out of the way first.

I would write the data to multilines (put each unmatched field entry into the same field as a list separated by carriage returns). It will be easier to work with than repeating fields and will provide the added benefit of being available as a multikey and other manipulations and reports.

Why manual import? Because each db will be different and you'll have to map the fields as you import anyway so map them only once.

I think I would write everything to your Target db as multiline first, then use Ray's Find Similar to delete any lines more than 70% similar or require manual attention - excluding the right-side SourceName of course in your comparisons. Others may suggest incorporating a similarity check within the script. I would not be comfortable advising you on this aspect.

One more thought ... Since you use vs. 6, I think there would be quicker ways - for instance, using Get Field []. Or dropping all your fields onto a layout and then using FieldNames with Get Field[] to compare and write. I have not used Get Field [] enough to advise on scripting its use but it might save writing every single field name in your script.

Simple comparison and example of writing the data to a multiline in Target:

Show All Records

Go to Record/Request/Page [First]

Loop (start on your first field and go through them all.

If [isEmpty(Target::Phone) and not IsEmpty(Phone)]

Set Field [Target::Phone, Phone & " - " & SourceFileName]

If [not IsEmpty(Target::Phone) and Target::Phone <> Phone]

Set Field [Target::Phone, Target::Phone & "

[FM-student]

Dear Doug and MoonShadow,

Hope things are going well for you.

Thank you for your helpful replies and sorry not to respond sooner.

We have been able to revise the project a bit by identifying one of the versions of the main database as truly the best one, simplifiying the file combining issue.

On the other hand, there are several text fields within each record of the main file that need to be compared and have duplicate contents removed, best versions kept, etc.

For that, will gratefully rely on the information in your very useful postings, including a script based on MoonShadow's script to combine elements from the different fields found in the same record.

Your postings have been very helpful to us in allowing us to clarify what approaches would be good to follow.

Thank you for your kind advice and insights.

Best regards,

Brad