6 Replies Latest reply on Jun 24, 2011 8:57 AM by Kays

    Import Only Changed Fields

    AjEGfmTech

      Title

      Import Only Changed Fields

      Post

      Is there a way to match import a CSV of records and ONLY update fields in those records that have different field data from the existing record/field data in the database?

      Example
      Existing Record...
      MemberID = 1234
      Email = john@domain.com
      Email_ModifyTimestamp = 06/23/2011 07:05:15 PM
      Store = Water St
      Store_ModifyTimestamp = 06/23/2011 07:05:15 PM

      Imported CSV Record...
      MemberID (match field) = 1234
      Email = john@domain.com
      Store = London Rd

      Resulting Record after import...
      MemberID = 1234
      Email = john@domain.com
      Email_ModifyTimestamp = 06/23/2011 07:05:15 PM
      Store = London Rd
      Store_ModifyTimestamp = 06/23/2011 07:21:37 PM

      My goal is to have a calculation field like below, for every potentially changing field in the database, that will let me see what field data is being updated after weekly imports.

      Evaluate ( "Get ( CurrentHostTimeStamp )" ; [Email] )
      /* Field calculation results in the current time stamp of the machine  hosting the FM database only IF the field in brackets changes. Note: the  evaluated field(s) MUST be in brackets. */

      Currently every record and field is showing updated timestamps after CSV import. I presume this is because I have a 95,000 record database with sequential ID's and I'm importing a 95,000 record CSV with the same/matching sequential ID's. Therefore it seems that every record is getting overwritten with the imported record/field data, whether the existing record/field data is the same or not.

      I'm hoping someone can shed some light on this. I've been searching this forum for answers and see some possible solutions with validating on import and possibly setting variables in the process. The tricky thing for me is how to evaulate an existing field's data with the matching record importing field data. I would basically like to have a calculation/evaluation that would say "If imported field data equals existing field data, skip field" - I don't know if that is how Filemaker could/should work for this situation?

      Any help/direction would be greatly appreciated.

        • 1. Re: Import Only Changed Fields
          AjEGfmTech

          Hmmm... either this is not possible, or it is not something anyone is interested in doing?

          • 2. Re: Import Only Changed Fields
            philmodjunk

            Modification timestamps, if you choose that auto-enter option updates with each change to the record, not to a specific field within the record. If you want to track the date/time individual fields were modified, you'd need to use a script to first detect the change, then update the timestamp field associated with that field.

            One way to do that is to import your records into a separate table related by memberID, then use a script to loop through the records comparing each field in each pair of records field by field to detect changes and process the updates.

            • 3. Re: Import Only Changed Fields
              Kays

              u could just import the data that u want to change !!

              or the whole file with option "update record" even its the same data nothing will change anyway, what's the problem !

              K|Z

              • 4. Re: Import Only Changed Fields
                AjEGfmTech

                @PhilModJunk: thanks for the feedback. I apologize, I must have explained poorly. The fields I call 'Email_ModifyTimestamp' are actually calculation fields using the calculation below...

                Evaluate ( "Get ( CurrentHostTimeStamp )" ; [Email] )

                Obviously, the field name changes when it's a different field than Email. With this calculation I am able to timestamp changes at a field level within the record when manually making changes, but I cannot find a way to do that when importing.

                In regards to using a script to detect the change it sounds like it would require a separate table to import into first. Is this because FM does not have an ability to compare existing field data with 'about to be imported' field data and then take action based on the comparison?

                @Kays: thanks for the feedback. Unfortunately I do not know what data to change, which is why I'm looking to compare on import. I could update the entire database but I need to know which records and fields have changed so that I can update other related databases that I do not control (non-Filemaker).

                • 5. Re: Import Only Changed Fields
                  philmodjunk

                  Apologies on my part, the expression you used in Evaluate didn't make sense to me and looked to be invalid. A check in FileMaker help reveals a detail about Evaluate that I hadn't known before and so I have learned something new.

                  The problem is that a regular update type import "modifies" all the fields, even though only one field might contain different data.

                  Yes, a separate table would be needed and yes, that would be to keep the import process from "modifying" the fields that do not have different data in them.

                  • 6. Re: Import Only Changed Fields
                    Kays

                    u wellcome :), and now it makes sence,

                    well, you could add another table and import in it, this new table can have an extra field ("update" per example), then throu a script you could transfer data from the new table to the original one and every time u update a record (based on test to indicate if the data is diferent), the extra field in the new table takes "1" or "yes" or .....

                    this is a way to konw what data u updated

                    K|Z