9 Replies Latest reply on Sep 20, 2016 6:26 PM by philmodjunk

    Query fields and show only updated data

    deborabr

      Hi,

       

      I am very new to FMP and I created a query to run and show all students name and enrollment information. Now I need somehow to connect that to the database and check if we have any new enrollment entered in the system and only show me the rows that had any updates. How can I do that? Any help is really appreciated.

       

      Thank you!

        • 1. Re: Query fields and show only updated data
          philmodjunk

          You'll need to describe this in much more detail.

           

          Does this mean that you have two tables, One of "old" data and one of "new" data? And you want to see which records in "new" do not match data in "old"?

          Is there a unique ID in both tables to use to match up records for comparison?

          If so, I've recently seen a really cool way to compare the data in multiple fields to see if any data is different in any one or more of the fields so compared, but I'll stop here until I get some confirmation that I am understanding what you want to do correctly.

          • 2. Re: Query fields and show only updated data
            deborabr

            Sorry I am so new to all that and still trying to figure everything! I am importing data from an ODBC data source. I also added a SQL query to pull the information I need. Now I believe I would need to create a script that would compare what we have in FMP to what is in our database and just return the rows where data has been changed. There is a unique identifier that is the Student ID. These are the fields I have student ID, name, school, grade level, entry date, entry code, entry comment, exit date, exit code, exit comment. I only need to check if any of the enrollment fields has been updated (entry/exit date, entry/exit code and entry/exit comment). Any help is really appreciated! Thank you!!!

            • 3. Re: Query fields and show only updated data
              philmodjunk

              But do you actually have two copies of this data?

               

              Do you really need two copies of this data?

               

              Could you use ESS and link directly to the SQL data source and always have a view of the most recent version of the data rather than trying to set up  what appears to be a "synch" of the data?

              • 4. Re: Query fields and show only updated data
                deborabr

                I don't really have two copies of the data I am just querying the database to get the current information and the idea would be to daily check for any updates done by our secretaries so we can make sure the records are correct.

                 

                I created the ESS connection already and I imported the existing records using my query to pull the information from the database. But now I don't know how to make a script that would run check for any updates and return only rows that has been modified.

                • 5. Re: Query fields and show only updated data
                  philmodjunk

                  With an ESS connection, you haven't imported any data, you are just linking to the live data.

                   

                  It doesn't sound like you need to do anything but display the data as this will display the most recent data from the ESS updated SQL Data source.

                   

                  The exception to this is if your secretaries are creating a new record every time they "update data".

                  • 6. Re: Query fields and show only updated data
                    deborabr

                    Hello...thank you for all the information.

                    Right now I am getting the data from the database according to the SQL query I entered but what I really needed was to show only what is being changed so I can track if they are entering the right information. This could be done to an existing record or they may enter a new one. Do I have to create a script to do that?

                    • 7. Re: Query fields and show only updated data
                      philmodjunk

                      Is there a field that records the date and/or date/time that the record was last modified?

                       

                      If so, you can perform a find for all records where the date or timestamp in that field is on or after a specified date. If you keep track of when you last checked, you can pull up all records created or edited since the last time that you checked.

                       

                      This is a process that can be done manually or it can be scripted.

                       

                      Since this is an ODBC data source, you will need to pay careful attention to how such date/time data is stored in that data source in order to come up with find criteria that works for your find.

                       

                      You might also be able to sort your records so that the most recently created/modified records are listed first and then you scroll down through the set to examine them.

                      • 8. Re: Query fields and show only updated data
                        deborabr

                        I was told the field that records the date of anything modified is not reliable so I was going to just compare if the data has changed or new entries were created and then would return just what was updated. There's any way to do that?

                        • 9. Re: Query fields and show only updated data
                          philmodjunk

                          That would require some kind of audit trail set up or creating a new copy of each record every time it is updated. Otherwise you do not have data to compare to tell what has been changed and when.

                           

                          You might want an explanation as to why the modification date is not reliable v

                           

                          Sent from my iPhone