1 2 Previous Next 17 Replies Latest reply on Sep 10, 2012 2:19 PM by philmodjunk

    Date Modified Doesn't Update During Import

    Terri

      Title

      Date Modified Doesn't Update During Import

      Post

      Hi,

      I have a very simple import from a CSV to a table in FM Pro 11. I have a Date Modified field on my layout and it is set to automatically update. When I import, the box is checked to perform auto-entry options such as date modified, serial numbers, etc. However, when I do the import I review a record that I know has been changed (because I changed it as a test) and the date modified remains the same.

      Any ideas?

        • 1. Re: Date Modified Doesn't Update During Import
          philmodjunk

          What exact import options did you specify. Is this an "Import Matching" option?

          As a quick test, I created 3 records with an ID key field and a value field along with a date field. After creating the records I went in to field options for the date and enabled the modification date field. I did it this way so that there was no date in the field when I first created the test records. I then exported a csv file of just the value and ID key fields.

          Lastly, I enabled the Modification Date field option for the date field and did an Import Records with the matching field option matching on the ID Key field. I enabled the "auto entry options" check box during the import.

          Even though none of the fields actually changed, today's date appeared in the date field after performing the import.

          Note: When performing and Import Records operation with the "import matching" option, make sure that the records you want to update are all in the current found set before starting the Import process or the record willl not be updated by the import.

          • 2. Re: Date Modified Doesn't Update During Import
            Terri

            I did the exact same test scenario as you did. I then changed the first record of my import file slightly, saved the file and waited five minutes (my date modified is actually a timestamp). I then imported again and chose "Updated existing records in found set." (Note, I'm still showing all records based on the initial import.) I enabled the "auto entry options" check box. The field that updated does change but the timestamp does not.

            • 3. Re: Date Modified Doesn't Update During Import
              philmodjunk

              "update existing" and "update matching" are two different import options and will produce different results.

              Just to be sure, I changed the field to time stamp, exported again. Edited a field and re-imported using the Update Existing option. I did not map the modification timestamp field only importing data into other fields in the table. When I imported the timestamp field updated for all records. This included the edited record, but the other records updated their TimeStamp fields as well even though they had not been modified.

              This is consistent with what I expected to see. Note that if you have "6" in a field and you delete and retype the value as 6, FileMaker will also update the modification timestamp field when you commit this record even though you didn't actually change the value of the field.

              • 4. Re: Date Modified Doesn't Update During Import
                Terri

                I just used update matching records based on two other fields and I see what you mean, all records will update the date modified. So that leads to my next question:

                How can I can easily identify records that have changed on the last import. Either just a notification that they have changed (like a check box or something) or (ideally) what field was updated (conditional formatting)? Perhaps this isn't possible. Eventually I'll be importing just a file that shows updates, which can include changes to the existing records and some field has changed, or brand new records. That is a very brief desription of the problem but I'm hoping you can decipher enough to understand.

                In another program we use I know they indicate updated records by creating new records for the new import and then running a script that shows duplicates and then the individual needs to compare them visually. I guess that would be an option, but least favorable.

                • 5. Re: Date Modified Doesn't Update During Import
                  philmodjunk

                  I'd import the data into a separate table and link the records by an ID field that uniquely identifies each record.

                  If you have modification date or timestamp info in your imported data, you can compare that value in your imported records and use a script that finds only records in the temporary table where the two do not match, then a second import can be scripted to only import the found records.

                  If there is no such data in your imported data, the same method can be used, but now you have to compare values on a field by field basis and this can be scripted. You might, for example define a calcualtion field in both tables that concatenates all the fields into a single text field with some sort of delmitter character separating each field value. If the two calculation fields are not equal, the record has been changed.

                  • 6. Re: Date Modified Doesn't Update During Import
                    Terri

                    Unfortunately this table includes 64 fields and each would have to be compared so the concatenate idea is unrealistic. I asked and I'm unable to add the date modified to the import data.

                    In the end I will need to be able to view the records that have duplicates. Let me explain. What I'm importing are work orders in a sense. Often some elements of that work order may change, from the color ordered, the deadline for delivery, or perhaps the order has been dropped all together (dropped is a field on the import). These changes affect the workflow of this order and need to be reviewed to ensure it is assigned to the correct person, etc. Since FM can't update existing records AND tell me what was modified/changes then the only option is to import the new records into a new table and then run a script that will show me the duplicates, so I can compare them next to each other, and make any changes or copy any instructions to the new record. I hope that makes sense.

                    Also, the unique field (or ID field) would really be the combination of two fields - Style Number + Color Code. I know I can do this with a merge field. This again cannot be on the import data, it is something that would have to be added after the import.

                    With that in mind can you help with the next steps? When to add the merge field, the relationship needed between the two tables and especially the script. Thank you.

                    • 7. Re: Date Modified Doesn't Update During Import
                      philmodjunk

                      Unfortunately this table includes 64 fields and each would have to be compared so the concatenate idea is unrealistic.

                      That doesn't sound unmanageable at all. It may be a bit tedious to create but well within the capabilities of FileMaker and once you have created this calc field in one table, you can copy and paste either the field definition (requires FileMaker Advanced) or the expression (Works with any version) to put the same calculation field in both tables. The exact nature of the delimitter field you should use in this calculation will depend on your data. If there are no returns in any of these fields, you can use the List function like this:

                      List ( Field1 ; field2 ; field3 ; .... Field64 )

                      If you have text fields with returns in them, you might need to use a different character for the delimitter such as:

                      Substitute ( List ( Field1 ; field2 ; field3 ; .... Field64 ) ; ¶ ; "|" )

                      And there's any number of characters you can use if "|" is a character used in one or more of your fields.

                      For the sake of this example, I'll call this field cRecordCompare.

                      Also, the unique field (or ID field) would really be the combination of two fields - Style Number + Color Code. I know I can do this with a merge field. This again cannot be on the import data, it is something that would have to be added after the import.

                      Not at all. You can add a calculation field that combines the values of two fields and this will be computed automatically when you import your data, but you don't need to do even that. You can simply define a relationship that matches on both pairs of fields instead of just one pair.

                      Let's say your current table is called WorkOrders and you put a copy of this table in place (Copy and paste in Advance, import records with new table option in Pro) and call it WorkOrdersTemp.

                      Link them in this relationship:

                      WorkOrders::StyleNumber = WorkOrdersTemp::StyleNumber AND
                      WorkOrders::ColorCode = WorkOrdersTemp::ColorCode

                      Define a calculation field in WOrkOrders called cCompare and give it this expression:

                      cRecordCompare = WorkOrdersTemp::cRecordCompare

                      Select Number as the return type.

                      Next time you need to import this data:

                      Delete all records from WorkOrdersTemp.

                      Import your data into WorkOrdersTemp.

                      Stay on this layout and Enter find mode

                      Put a 1 in the WorkOrders::cCompare field.

                      Perform the Find

                      Select Show Omitted Only

                      You now have a found set of records in the temp table that do not match a record in the work orders table in every field.

                      Go to your WorkOrders layout, Show All records and use the Import Matching option (specify both styleNumber and Color code as match fields) to import the records you just isolated in the temp field.

                      Note if some imported work order records are new and thus there is no matching Work Order record already in your table, you can enable the "add new" option and the above steps will add them as new records to your WorkOrders table.

                      Also note that prior to this import, you can use conditional formatting to highlight any of these 64 fields if their value does not exactly match the value in the matching record in the temp table if you want to inspect your current work orders and spot exactly what fields have changed values.

                      I've described all this in terms of manual steps, but this can all be scripted to take place automatically.

                      • 8. Re: Date Modified Doesn't Update During Import
                        Terri

                         I still have to work through this solution, but your explanation is so clear I'm sure I can do it in no time. I'm also excited about the conditional formatting.

                        I don't suppose there is any way to export to excel and keep the conditional formatting, is there? Wishful thinking.

                        I'll let you know if I have any questions as I work through this.

                        • 9. Re: Date Modified Doesn't Update During Import
                          Terri

                          Phil, can you help with the conditional formatting. For some reason my formula is changing the format of that field in all records. I'm using the field Start Date. I'm not sure how to indicate the value in the matching record.

                          Thanks!

                          • 10. Re: Date Modified Doesn't Update During Import
                            philmodjunk

                            Keep in mind that this will only work before you merge the changed work order data from the temp table into your work order table.

                            Using the relationship I described earlier. (If you named things differently, then you'll need to use the names from your solution rathre than my example)

                            WorkOrders::StyleNumber = WorkOrdersTemp::StyleNumber AND
                            WorkOrders::ColorCode = WorkOrdersTemp::ColorCode

                            Say you have a field named "Fabric" in both tables and on your layout.

                            You could use this conditional format expression:

                            WorkOrders::Fabric ≠ WorkOrdersTemp::Fabric

                            You can then select text style, color and/or field fill colors that differ from those specified for the field on the layout and you should see this color only if the value in the two fields are not equal.

                            Here's a more sophisticated expression.

                            Not Isempty ( WorkOrdersTemp::StyleNumber ) and WorkOrders::Fabric ≠ WorkOrdersTemp::Fabric

                            This will keep the color change from happening after you delete the records from the temp table.

                            • 11. Re: Date Modified Doesn't Update During Import
                              Terri

                                   I decided to do the calculation for the style/color. Should that make a difference because I'm still getting the same result with the conditional formatting.

                                   Also, in your response on 9/5 you said import the data and then stay on the same layout and do a find for the result of 1 in the WorkOrders::cCompare Field. If I import and stay on the same layout that field isn't there (because I'm on the WorkOrdersTemp table). Is there something I'm missing?

                                   I've also tried something else (on a duplicate database) with just a self-join relationship and then the new records are added and a calculation returns a Y if it is duplicate and N if not. But I don't know how I can do conditional formatting with this type of set up.

                                    

                                    

                              • 12. Re: Date Modified Doesn't Update During Import
                                philmodjunk

                                     "the calculation"? What calculation is that?

                                     You can add this field (WorkOrders::cCompare) to your WorkOrdersTemp layout if you are performing a manual find so that you can specify search criteria in it.

                                     If you are using a script to perform the find you don't have to add the field to the layout to specify find criteria in it.

                                     You haven't posted enough info on your second approach for me to know exactly what you have done.

                                • 13. Re: Date Modified Doesn't Update During Import
                                  Terri

                                       Sorry, my calculation is to contatenate the fields I need to create a unique "key". So it is actually Style Number + Color Code + Season + Season Year.  This field is called Style_Color.

                                       Regarding adding the field, I'll do that. I just wanted to make sure it wasn't a typo or something. 

                                       Second approach is this: 

                                       Self Join Relationship - WorkOrders & WorkOrdersTemp; Style_Color = Style_Color

                                       Added a field: Import_FindDups_Counter; type text; auto-enter serial number on creation, increment by 1.

                                       Added a calculation field: Import_FindDups_Duplicate; calculation - If (Import_FindDups_Counter = Import_Find_Dups::Import_FindDups_Counter; "N"; "Y"). This looks at the serial number and then looks for matching records with the Style_Color and if the serial numbers are different but the Style_Color is the same it is a dupicate and gets Y.

                                       This works great in identifying if a record is a duplicate or not. If it is a duplicate it isn't tell me what, if anything, has changed. 

                                       As you can tell, both options work very similar in identifying if a record is a duplicate or not (your 0 and 1 work fine). One is a self-join and one is a new table based on import. I'm just playing with different options to see which one can get the conditional formatting to work first. Don't meant to complicate things.

                                  • 14. Re: Date Modified Doesn't Update During Import
                                    philmodjunk

                                         The calc would be Style Number & Color Code & Season & Season Year. The & concatenates. The + adds numbers. wink

                                         What you have should work the same for highilighting fields that are different as you can go through the self join to highlight fields that are different, but I like the separate table to keep new versions of the records so that you only have one record for each work order in your main table. I also wonder what happens for work orders that have not changed. You'll still get duplicate values in the style and color fields so you calculation just shows that you have a duplicate work order, it doesn't show if it is different in any of the other fields.

                                    1 2 Previous Next