3 Replies Latest reply on May 16, 2013 8:52 AM by philmodjunk

    Comparing Imported Excel Files with Current Database



      Comparing Imported Excel Files with Current Database


           Hi! I am on the staff of a Collegiate Marching Band and we have recently picked up FileMaker Pro to help with our membership database. Everyday we have to cross-reference our class roster to see if anyone have added OR dropped the course from their schedule. We recieve this information in an excel spreadsheet. Is there any way for us to import this data and see records that are NEW and have been DELETED from the source file. We need this information in order to contact the members as soon as possible. We are hoping that there is a way for FMP to do this for us instead of doing it by hand. We basically just need to do a complete comparison of two lists/databases/tables. Any ideas?

        • 1. Re: Comparing Imported Excel Files with Current Database

               The details would depend on what data exists in that spreadsheet. Does it include some kind of student ID? (Matching by name is not ideal).

               If someone has been added, is the data in the spreadsheet different from the other rows in anyway? That's not necessary, but it might simplify things if there was.

               And students who are dropped are simply no longer present in the spreadsheet or are they listed in the spreadsheet but with a value in a field that identifies them as dropped? (Again, this is not absolutely necessary, but it would simplify things if they are listed as dropped.)

               If new students are not flagged as new and dropped students are simply omitted from the spreadsheet, you can import your spreadsheet data into a table to compare to your class roster on a student by student basis. If there is a student ID column in your spreadsheet, you can define (if you haven't already) a matching student ID field in your class roster and define a relationship that links your two tables by the student ID fields. You can then perform a find to find all records in your class roster that do not have a matching record in the table of imported data. These will be your dropped students.

               You can then do an import records to import the data from either the excel file or this new added table with the matching records option enabled and the student ID field specified as the match field. If you enable the "add new if no matching record" option, newly added students will be added to your Class Roster.

          • 2. Re: Comparing Imported Excel Files with Current Database

                 We can definitely have it use the Student ID number. That is a field. This all sounds GREAT! One more question...

                 Once we have imported the new contacts, is there a way to show ONLY these? Like a show new contacts? (I'm sorry if this stuff is very basic. We are just not getting used to the software and wanting to make sure we learn it correctly the first time.) 

                 Thanks again!

            • 3. Re: Comparing Imported Excel Files with Current Database

                   WIth the two tables, one that is cleared and populated with your imported records from Excel each time and one that is your permanent class roster linked in this relationship:


                   Class_Roster::StudentID = ImportTable::StudentID

                   You can, after importing the records into ImportTable, go to that layout and enter find mode. Enter an asterisk, * , into the the Class_Roster::StudentID field. (You'll need to add that field from Class_Roster to your ImportTable layout.) Then click the Omit button in the status tool bar and perform your find. This will pull up a list of all newly added students in the ImportTable so that you can import them into class_roster.

                   This type of find can also be scripted.