Solved

Filemaker compare records and document changes

Posted on 2004-11-02
858 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
Question by:kcassone
    20 Comments
     
    LVL 28

    Accepted Solution

    by:
    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
    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
    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
     
    LVL 28

    Expert Comment

    by:lesouef
    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
    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
    ok
    0
     
    LVL 19

    Expert Comment

    by:billmercer
    yep...
    0
     

    Author Comment

    by:kcassone
    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
    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
    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
    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
    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
    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
    You are correct. THanks so much for your help.
    I think I am all set now.

    0
     

    Author Comment

    by:kcassone
    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
    You should really post a new question. This one's been closed for a month.
    0
     

    Author Comment

    by:kcassone
    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
    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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Prepare to Pass the CompTIA A+ 900 Series Exam

    CompTIA aims to adapt its A+ Certification to reflect the most current knowledge and skills needed by today's IT professionals--and this year's 2016 exam is harder than ever. This certification is one of the most highly-respected and sought after in IT.

    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…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    884 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now