Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Filemaker compare records and document changes

Posted on 2004-11-02
22
Medium Priority
?
905 Views
Last Modified: 2007-12-19
Hello,
I have 2 of the exact databases with the same fields but a different amount of records for customer info.
The main database has all customer records and the second databse has only customer records pertianing to one location.
Both databases are housed in 2 seperate locations.
I know this sounds redundant but we need to keep serperate databases for checks and balances.
What I need to do is every month compare changes and then update.
We need to compare the records that have been updated in database 2 (records specific to that location)with database 1(the main database)
We need to know what changes have happened per record because we have to ensure we have hard copy documentation to support the changes from the users of database 2.

I would loke some indicator that says records compared and match exactly and then compared records and these are the changes.
Can you please help as soon as possible!
Thanks so much
0
Comment
Question by:kcassone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 4
22 Comments
 
LVL 28

Accepted Solution

by:
lesouef earned 1000 total points
ID: 12476346
I would take a copy of database2, take to the main location, link it to the main database with the proper ID field you probably have, then compare every record in database2 versus database1.
To find differences from within database2, I would compare field1&field2&field3...fieldN with database1::field1&field2&field3...fieldN, ie compare a concatenation fo all fields at once.
This can be done either by having a calculated field = all fields concatenated in each base, then compare them, or do a loop in a script which calculates the concatenated values on the fly whichever is the fastest.
When a record is found to be <> , you have then compare in a subloop which fields are different. If you have many fields, it can be tedious to test all of them 1 by 1; instead of naming each field to be compared, I would do a layout with all fields in it, then walk though each with the "next field" command, and compare the current field with database1::[current field name]; if different do what you have to do, ie mark it, update the main base or whatever.
(names of functions might be a bit different, I'am not using an english version)
Let me know if you need more details or if I am on the wrong track.
Of course new records created only in database2 would not be covered by such a sync, but from what you wrote, there are none.  How do you handle new customers? Create them in main database and send them to database2??
0
 

Author Comment

by:kcassone
ID: 12504918
Thanks for your quick respone
Can you give me an example of this script
How will I know if a customer has been deleted in the the database 2.
Currently when we have a new customer we get paper work from the database one people and it is then entered into the main database.
We are trying to avoid data entry twice.
We have just started with this new procedure with 2 databases.

Thanks


0
 
LVL 19

Assisted Solution

by:billmercer
billmercer earned 1000 total points
ID: 12549946
I think Lesouef is on vacation or somthing, he has not posted for a few days.

I don't have a full detailed script that does what he describes, but this illustrates the main loop concept he's describing, and may be enough to get you started. I assume each file has a calculated field called Compare that concatenates the fields you care about, as Lesouef described.

go to record/request {first}
Loop
  if [ "Compare <> OtherFile::Compare" ]
     Show Message["Records don't match!"]
          Perform Script [subscripts, "Do-Detailed-Comparison"] # call a separate script that does something more specific
  end if
  go to record/request [exit after last, next]
end loop

Running this script will go through all the current records, and when the two records don't match it will alert you with a popup, and run a separate script that can then can do a more detailed comparison, print a report, send an email, or whatever you want.

As for deleting, if a record has been deleted from database 2, the record in Database 1 won't have a related record, so it will show up as a mismatch. When the above script runs, the related compare field will effectively be blank.

There's a slight chance that some changes won't be detected by the concatenated field comparison. If two fields are changed such that they equal the same thing after as before, then the comparison won't find this change. This should be pretty rare though.

For adding new customers, you can do this by creating a script that exports only new records and sends it to the other location, where it can be imported.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 28

Expert Comment

by:lesouef
ID: 12550034
Not on vacation unfortunately... just fully busy + I deleted this thread before answering.
Takes more than 2mn to write an exmaple, I'll try to do that next week-end, my mother in law will @ home!
Is it still necessary after bill's anwser?
0
 
LVL 28

Expert Comment

by:lesouef
ID: 12560924
Since I am not using an english version I won't show my script in extenso here. Instead I have posted it on my intranet:
http://extranet.valblor.com/users/lesouef/compb1b2.zip
It will stay there for 2 weeks approx.
It is a basic example with only 4 fields, and the layout used should only be used by my script since when the 2nd base will be missing, 1/2 of the fields will show a nasty missing file. Also the field order is important for the script to work without naming the fields except the last one to exit the loop. No other field should be present in this layout other than the ones to be compared.
As it is now, the script must be used from within b1.

For new records in b1 to be put in b2, I would simply use a field to mark new records, search for them in b1 and call an import script in b2 and then back in b1n remove the new record mark. I guess you can do this without my help, otherwise let me know
0
 
LVL 28

Expert Comment

by:lesouef
ID: 12827610
ok
0
 
LVL 19

Expert Comment

by:billmercer
ID: 12830264
yep...
0
 

Author Comment

by:kcassone
ID: 12837541
I'm sorry I haven't gotten back to you.

i just haven't had a chance to try the suggestions.

thanks
0
 

Author Comment

by:kcassone
ID: 12954518
ok,
 Lesouef thanks so much for the script adn example databases. It helps immensely.
Now I see how you find the differences but how do I make the changes in database 1 when they dont match? Is there a way to track what has changed without documenting each differnce by hand when find a difference?

I also understood billmercer  script but was wondering if you could send me an example of your script showing ( call a separate script that does something more specific) like update file and print changes.
Thanks
Kim
0
 
LVL 28

Expert Comment

by:lesouef
ID: 12954650
gloups, no idea, I forgot what that was about, I'll re-open the example to see what it did and let you know later on.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 12957357
ok, I threw a glance at it, all you need to do is to insert an action in the script where it says "do whatever you need".
This could be simply to copy b2:f1 to f1, etc... or storing in a specific field which field has changed + its contents.
I have done this in my example, redownload it from the same location, the msg is replaced by storing the field name and its contents in a field where I put all changes; you could also add the date of the change, etc... see comments in the script.
And let me know if not clear.
http://extranet.valblor.com/users/lesouef/compb1b2.zip
0
 

Author Comment

by:kcassone
ID: 13084999
Thanks Lesouef your script helped alot.
THe only question i have is that I must compare multiple fields in each record.
How would I do that and documents the changes?.
Thanks in advance
0
 
LVL 28

Expert Comment

by:lesouef
ID: 13085827
Did you reload the example?, it is the same name, but it has been modified for that matter, so it should answer yr pb...
met me know
0
 

Author Comment

by:kcassone
ID: 13089814
You are correct. THanks so much for your help.
I think I am all set now.

0
 

Author Comment

by:kcassone
ID: 13096525
Hey, I'm back.
Can you please show me a script that would actually make the changes when a difference is found after you hit the Ok Button.
thanks
0
 
LVL 19

Expert Comment

by:billmercer
ID: 13098477
You should really post a new question. This one's been closed for a month.
0
 

Author Comment

by:kcassone
ID: 13103558
Ok Thanks.

Didn't realize it was closed.  
Lesouef has been great and keeps helping me with this issue.
I will post a new question if thats what I need to do.
0
 
LVL 19

Expert Comment

by:billmercer
ID: 13103898
Yep, Lesouef is a very helpful guy :)

The reason your question was closed was that you let it sit idle for quite a long time, and a moderator forced it to split the points between me and Lesouef.
Generally when that happens, you're expected to ask a new question. However, if you feel this is all really the same question, you can go to the support forum and request that this question be re-opened. I don't have any objection to that, and I doubt Lesouef would either. That way you could also remove the negative impression of being someone who abandons questions.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 13103902
To change the action, remove the line which defines the fields called "changes" and replace it with:

copy current field
activate previous field
paste
active next field (twice)

See my example updated again, 1st script is untouched, 2nd one is doing what you need, ie silent update.
http://extranet.valblor.com/users/lesouef/compb1b2.zip
0
 

Author Comment

by:kcassone
ID: 13104305
Sorry for the confusion.
I just hadn't had the opportunity try your solutions and thats why I sent the above response on 12/15/04.
Certainly don't want to leave a negative impression.

Thanks again Lesouef!

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question