Merging Records With Different Fields but common address? Can it be done?

[twortle]

Good day! Wow you are all so very intelligent in Filemaker!! It is such a powerful program that I am relatively new to.

 

Here is my dilemma. I would love to "discover" this on my own but I am up against a time constraint:

 

I have 5 separate Excel spreadsheets regarding a property address. The only common fields in each of these spreadsheets is the address.

 

Spreadsheet One has property tax information

Spreadsheet Two has septic system registration information

Spreadsheet Three has 911 information (longitude and latitude GeoCoded)

Spreadsheet Four has voting precinct information

Spreadsheet Five has trash collection information

 

I want to combine all these spreadsheets into one master database with all the information fields from each of these spreadsheets. The "property tax" spreadsheet is the "master list" addresses I need to work with.

 

There is a catch here though...The property tax address may have two or more lines (records) for the same address, as there may be multiple persons/entities paying the tax on that property. So if I try to combine these records in Filemaker, the program may not know which record to merge the other data from the other spreadsheets into. I would like to first merge all persons/entities that pay tax on that property address into a single address record...then merge the other spreadsheets into that.

 

Also...there not be any information for that address in some of the other spreadsheets...and if so...thats fine for my purposes.

 

Any help would be appreciated. I could manually do this all but there are over 5000 records and I think my eyes would bug out doing this! Thank you in advance.

[AHunter3]

Step a) Convert your Excel sheets to temporary FileMaker files by opening them in FileMaker. (will invite you to save/create new db's named ExcelFileName (Converted).fp7)

 

Step b) In Filemaker, if you want one record per address rather than one record per person AT that address, use any db other than the converted Property Tax db as your starting point, let's call that file OtherTable. From Property Tax db, create a relationship based on Address = OtherTable::Address. Allow record creation via this rel on the OtherTable side. In the other table, create new data fields with field names identical to those in Propery Tax that you have any interest in. Now, in Property Tax, create and run a looping script that does this:

 

Show All Records

Go to Record [First]

Loop

... Set Field [OtherTable::Field A, Case (IsEmpty (OtherTable::Field A), Property Tax::Field A, OtherTable::Field A)]

... Set Field [OtherTable::Field B, Case (IsEmpty (OtherTable::Field B), Property Tax::Field B, OtherTable::Field B)]

...

... Go to Record [Next, Exit After Last]

End Loop

 

 

That will fill in the property-tax info into OtherTable but will not create duplicate records of the same address due to multiple people living there. IF & when it comes to a second (third, nineteenth, etc) person at 100 Whatever Road, it will simply add additional data if it was missing from the previous record from person(s) living at that address.

 

Step c) For the other tables, where you already only have one rec per address, just import into OtherTable, creating new fields to accomodate the new data from the other db files.

[fundraiser]

Hi

I followed the above thread and found it very instructive. Thank you. Can you please help me out with an odd problem: I want to update an existing fm9 address-db(called "basisdatei" with new addresses contained in a csv db. I intend 1) importing these csv data into a new fm db calles "importdatei" , defining a relation between db"basisdatei" and "importdatei" using a field containing an unique address-nr and 3) update selected fields in "basisdatei" using a script similar to the one you suggested.

 

I tested the following script ( I use fm in German, so please apologize if translation not perfect)

 

***

Show all datasets

Goto record/query/page(first)

Loop

Goto related record[from table:"basisdatei";with layout:]

Set Field[basisdatei::name;importdatei::name]

Set Field[basisdatei::street;importdatei::street]

Set Field[basisdatei::other field;importdatei::other field]

Set Field[basisdatei::last field;importdatei::last field]

Goto record/Next,Exit after last

End Loop

***

 

Nearly everything works perfectly: in the fields set, old information is replaced by new information, but....the problem is:

The above script unfortunately clears any information in set fields in non-matching records in the old db"basisdatei", i.e. in records which need no update since no new information is available- very nasty.

 

Can you please let me know where the problem lies- I dont understand whats going wrong. Would really apreciate your kind assistance.

Best regards