Filemaker compare records and document changes
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
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
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
yep...
ASKER
I'm sorry I haven't gotten back to you.
i just haven't had a chance to try the suggestions.
thanks
i just haven't had a chance to try the suggestions.
thanks
ASKER
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
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
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.
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
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
ASKER
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
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
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
met me know
ASKER
You are correct. THanks so much for your help.
I think I am all set now.
I think I am all set now.
ASKER
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
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
You should really post a new question. This one's been closed for a month.
ASKER
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.
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.
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.
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.
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
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
ASKER
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!
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!
ASKER
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