6 Replies Latest reply on Sep 17, 2013 2:02 PM by ByteTheBullet

    How to capture the data in the "imported" popup on FMS12


      When you finish importing on the desktop a popup will come up and give you data about files added/updated. Errors etc. I know that an import.log is usually created when running FMP as a client but it doesn't seem to have this detail. I have a script that imports a file on the FMS12 Server every night, which is managed by the schedules in FMS Admin Console. I would love to capture the number of records added/updated and insert it into a log table in the same database. Since this is running on the server you don't get the popup and if you did no one would see it. So is there anyway to grab that data at the end of the import step without having to go find a log file (if one exists) and parse the data (the script steps through 3 files for 3 different tables).

      Dan Doughtie


        • 1. Re: How to capture the data in the "imported" popup on FMS12



          1) Create a log table in your solution

          2) Create a generic script that accepts several parameters. One I have done before accepts a timestamp, operation "type", description, and user/account. This script will be called whenever necessary during the import operation. The script will create a new record in the log table.

          2a) For me, the operation type would be "time", "comment", or "error".

          2b) I use simple indenting of the "description" text to make it easy to see the entries for a particular import run. First and last entries are not indented. All entries between them just have 2 or 3 spaces at the start of the description text.


          3) You can get values such as the number of records imported by simply doing things like Get(FoundCount) after each import. You should also do error trapping and write those to the log table. Maybe also set key values into variables (local or global as needed) as you move through the import script. You can then use these variables to generate an email message to yourself and/or others when done. If you do an email, you would need to use a SMTP server for the Send Mail. You could also parse the relevant entries in the log table for email data, but loading data into variables while you process, seems easier to me.


          3a) In one solution I did, at the end of the server processed script, I go to the log table, find the records for the current run, go to the desired layout, and then generate a snapshot link file to the temporary folder for this. I then send an email from the server (via SMTP of course), and attach the snapshot link file. In the subject of the message, I will indicate if any errors occured or if there was anything else that needed attention. The mail recipient could then just open the snapshot link and it would take them to the log table with the desired found set of records and they could quickly review the data and action as needed.


          A sample of my log table (in list or table view) might look like this:


          9/12/2013 02:10:00 AM     Time          START__Import My Important Data               Server

          9/12/2013 02:10:02 AM     Time                 Start remove old records from Product Table     Server

          9/12/2013 02:10:12 AM     Time                 Finish remove old records from Product Table     Server

          9/12/2013 02:10:15 AM     Time                 Start import new records into Product Table     Server

          9/12/2013 02:12:00 AM     Comment          10998 records imported into Product Table     Server

          9/12/2013 02:12:01 AM     Time                 Finish import new records into Product Table     Server

          9/12/2013 02:12:10 AM     Time                 Start some process in Staff Table               Server

          9/12/2013 02:12:16 AM     Error                 FM Error Code 12345. OK to continue      Server


          9/12/2013 02:29:23 AM     Time          FINISH__Import My Important Data               Server





          • 2. Re: How to capture the data in the "imported" popup on FMS12



            I've pretty much done all of the things you list. I didn't want to list the entire solution I've created because all I need is this one part.


            The problem with the get(FoundCount)  is that some of the imports are update matching records. I do a Show All records so that the records can be matched (of course have "add new records checked"). So when I do a get(foundCount) it shows all the records. I could do a find based on the mod date but that would limit me to checking once a day for an accurate read. I can't do by mod timestamp because over 190k of records the mod dates will all be different.


            I need something that specifically lists the updated/added records  i.e. "of 90,000 total records in table X, 7,000 were updated or added during the last import process". If Filemaker can produce this in a Import Summary popup it would be nice to grab it. (Feature request.... maybe?)



            • 3. Re: How to capture the data in the "imported" popup on FMS12



              Understand, but confused a bit. I do not have access at this time to the solution where I said that I sent the email at the end. I know for sure in that one, I am doing some full imports and some with update matching records. I believe that the FoundCount right after the import matching script step, is the records updated or added. If I am correct, you would capture the found count right after your Show All Records and then right after the Import Matching. If you needed to break down those updated vs. created, could you not do a constrained find within the found set where the created timestamp is greater than the timestamp when you started your processing. I am assuming that you have a standard auto-enter field in your tables that records the creation timestamp.


              Maybe you can test this quicker that I can. Hope I am not wrong and therefore will need to go back to the client about this...



              • 4. Re: How to capture the data in the "imported" popup on FMS12



                That provides some food for thought. I've not found that the Get(FoundCount) gets just the updated/added records on a match but does when you do a straight import. But then again I need to go back and confirm that. If I look for a time range within the create timestamp (since each record will have different timestamp) I could calculate the adds.  Then do the same for mod timestamps, subtract the difference for the updated records. I hadn't planned on comparing adds to updates but if I can do some finds based on timestamps then that might be an added bonus. The customer really was interested the combined figure because that would match up with their legacy systems that's feeding it. Which I guess I could do simply based on Mod times. ooooh. Just realized that there is a field that shows who has modified the record. It's not really used for anything. All I have to do is each day clear that field and the next time it runs I simply look for those records that have the interface's name in the field then capture the found count. Sometimes discussion is good for the soul. Have a great weekend.



                • 5. Re: How to capture the data in the "imported" popup on FMS12



                  Did some testing and boy was I somewhat wrong in what I recall happening. I did a couple of imports with matching records turned on and the option to add new records. I had two FMP12 files that initially contained 500 identical records, including a simple, numeric serial number.


                  1) Altered a field or two in 10 records in FileB. Serial number field left alone.

                  - Import FileB into FileA with matching on the serial number and add new records. Dialog box says "Total records added / updated: 500".

                  - I have a found count of 500 records. (bummer...)

                  - The 10 records I altered in FileB are now updated in FileA.


                  2) Added 6 new records to FileB.

                  - Import FileB into FileA with matching on the serial number and add new records. Dialog box says "Total records added / updated: 506".

                  - I have a found count of 506 records.

                  - The 6 new records I added in FileB are now in FileA.


                  3) Reset FileA to original 500 records. Reset FileB to original 500 records, but then Added 5 new records and removed 10 existing records.

                  - Import FileB into FileA with matching on the serial number and add new records. Dialog box says "Total records added / updated: 495".

                  - I have a found count of 495 records out of 505 total.

                  - The 5 new records I added in FileB are now in FileA.


                  So, the found count after the import does reflect how many records had matches and/or were added. You can tell how many existing records did not have a match (10 in scenario 3 above).


                  I also noticed that an auto-enter modification timestamp field gets updated (I have the option during import to perform auto-enter tasks turned on) during import even though no data in a record might have been changed. This seems odd to me and I did not expect this. I am not sure how to identify records that did have actual changes. Records created would be a matter of looking for any with a creation timestamp after a timestamp recorded prior to the import.


                  Hope this might help you some. Also hope that someone might have an idea how to determine which records ACTUALLY had any changes during an import.



                  • 6. Re: How to capture the data in the "imported" popup on FMS12

                    I have some similar schedules running, and I've found that peforming a Get(FoundCount) immediately after a successful import does indeed give me the correct number of updated and, if applicable, added records. Where you may have issues is if you have records to import where the data has not actually changed. I believe that FileMaker will still update the existing record, potentially giving you an incorrect number of updated records. Or at least incorrect if your definition of updated records is only those records who's data is different post update. If  you need to know how many new records were created, you can do a Get(FoundCount) immediately before the import, and subtract that from the result after the import to get your new record count.