7 Replies Latest reply on Jan 22, 2014 1:30 AM by KrisztianG,

    Tracking field chages on import

    KrisztianG,

      Title

      Tracking field chages on import

      Post

           Hello All!

            

           I have a database where the date gets regulary updated using import of a modified source file. I need to track the changes. Tracking in layout mode works well, because it uses script triggers.

           How may I track the updated fields after an import?

           Many thanks!

        • 1. Re: Tracking field chages on import
          davidanders

               An Auto Entered Last Modified field will show last record modification, are you sure you want to track FIELD modification or Fields In Records???

          • 2. Re: Tracking field chages on import
            philmodjunk
                 

                      Tracking in layout mode works well, because it uses script triggers.

                 I think you mean Browse mode. Triggers don't work in layout mode. It is possible to use triggers to log field changes in a related change log table, but it's a fair amount of work to set up.

            • 3. Re: Tracking field chages on import
              KrisztianG,

                   @David: Yes I need to track somehow if a filed's content gets modified by an import. I use an auto enter calculation to write changes of fields to a tracking field:

                   Evaluate(

                    
                   Quote("- "& Get(CurrentTimeStamp) & " | " & Get(ActiveFieldName) & " | " & Get(ActiveFieldContents) & "¶" & field_mod); 
                    
                   [type; district; rent; price; size; rooms]
                    
                   )
                    

                   But this doesn't work on import.

                    

                   @Phil: Yes I meant Browse mode. Tracking changes is by far not so difficult I am not using a changlog table I just write the changes to a filed using this auto enter calculation. A tracking table is a more elegant way, but to much for what I need.

                   And I really need to see the cahnges made by an import.

                    

                   I would be very happy to get some suggestions how to do it.

                    
                    
                    
                    
              • 4. Re: Tracking field chages on import
                philmodjunk
                     

                          A tracking table is a more elegant way, but to much for what I need.

                     I'm not convinced that is is too much for what you need.

                     Are you sure that you need to see the changes due to an import after they have been made? It's far easier to see what changes are about to be made by an import than detect the changes after the fact. You could, for example, import the data into a separate, but related table. If you can set up a relationship that matches the imported record to the record it is about to change, conditional formatting can be used to highlight the fields where the values are not equal to the corresponding field in the main table. Or a script could compare each field and log the changes first, then it can do a second import to update your data in the main table.

                     Note: and if the data to be imported is already in a FileMaker file, you may be able to use an external data source reference to do the above comparison while the data is still in the source file and that would reduce this to a single import records action.

                • 5. Re: Tracking field chages on import
                  KrisztianG,

                       Thanks for your answer Phil!

                       Yes I need to have some kind of history of some of the fields. Data to be imported is coming from an Excel file, which contains data from a webpage. So After the import I should be able to sort the records which contain modified data. The change modification auto enter field works fine for a filter, but I can't see which fields have been updated.

                       The scripted soulution sounds great. Do you happen to know a kind of template on the Internet, that I can reverse engineer?

                  • 6. Re: Tracking field chages on import
                    philmodjunk

                         I don't know of any such template, but if you are doing an "import matching records in found set" type of import, then you should be able to import the records into a FileMaker Table that is linked to your main table by the same matching fields you would otherwise specify as the matching fields for your import.

                         A script with nested loops could then loop through your fields and records logging any identifiable changes. The outer loop would loop through your imported found set of records, the inner loop would use go to next field to loop through each field in a given record (only put the fields you need to check on this layout). Get ( ActiveFieldName ), Get ( ActiveFieldContents )  and GetFIeld() can be used in this part of the loop to indirectly access the data in the active field and compare it's value to the value of the corresponding field in the main table via this relationship an thus can log any changes about to be made.

                    • 7. Re: Tracking field chages on import
                      KrisztianG,

                           Thank you Phil! I'll give it a try.