1 2 3 Previous Next 66 Replies Latest reply on Mar 26, 2017 6:50 AM by beverly

    Unexpected Import Behavior


      I am working on a solution with a table that will be regularly updated by importing data from excel spreadsheets. It contains records of every product purchased by members of an organization, with all the relevant specs, including the latest prices. I have script which performs the import and several other tasks that must be done in a particular sequence each month, when they receive updated spreadsheets. The import is set to update records that match the values of two fields (vendor name and item number), then add new records for the incoming records that don't match.


      I imported a test record from a spreadsheet with a single row. Then I set up the import script as described above and imported another test, from a spreadsheet with just a few rows, using my import script. This second spreadsheet had three rows which matched the existing data's match field but had different data in other fields, and one new record which did not match the second key field. The results were as I expected: Row 1 of the new spreadsheet update the first record, Rows 2 and 3 updated it again, and Row 4 was added as a new record. In other words, I was left with two records in the database table, which matched Rows 3 and 4 of the last import and represented the most recent data (exactly what the client wanted).


      Unfortunately, subsequent imports have not gone as well. They seem to be adding new records for every row in the source spreadsheets, regardless of whether there is a match on the key fields.


      Any ideas about what may be going on?  Any suggestions for a work around?



        • 1. Re: Unexpected Import Behavior

          More info:


          Just a reminder: the import is scripted. The options for the import do not change. So it doesn't look like this is a case of user error.


          Also, If I use the same options and import manually (i.e. not using the script), I get the same results as the script.


          As stated above, I am using two match fields (vendor name and item number). Because each spreadsheet comes from a different vendor, the vendor name will be the same for every row in a given sheet. The item numbers may be different or the same as in another row. To me, it seems like the Import command is matching only the vendor name (which is actually the last field/column on the spreadsheet). But that doesn't explain why the same script worked differently the first time, when I showed it to the client with the test record.

          • 2. Re: Unexpected Import Behavior

            Just to be sure, are you performing a Find All or at least a find of relevant records to update within the script?

            • 3. Re: Unexpected Import Behavior

              The suggestion by user27087 is the most likely source of your trouble. That import matching matches against the found set. Keep in mind that on your relationship graph you might have multiple occurrences of your target table and each can have a different found set so it's important to do a "show all" on a layout based on the same table occurrence as the one you specified as your target table.


              Another possible failure point is that you are matching on a name field. Even very trivial changes to the text in that name field, such as including or removing a space will keep the records from matching and you will get a new record. If your item number were always unique (Probably not as you likely have many vendors and no way to keep each to a unique series of item numbers), you would be better to match only on the item number.

              1 of 1 people found this helpful
              • 4. Re: Unexpected Import Behavior

                It doesn't make a difference.

                • 5. Re: Unexpected Import Behavior

                  Showing all records or not makes no difference. Nothing gets updated. All records are added every time.


                  We are not matching on name fields. The vendor names are actually short abbreviations (usually acronyms) -- all text, no spaces or punctuation. The Item numbers are just numerals.

                  • 6. Re: Unexpected Import Behavior


                    Showing all records or not makes no difference.

                    Actually, it makes a very big difference. If the record isn't in the found set, it isn't there to be matched to and you get a new record. That's how this option was designed to work.


                    The fact that it does not even when you don't show all records suggests the possibility that you might be showing all records using a different table occurrence context than the one specified in your Import Records step. But then again, maybe your "or not" option was already with a found set of all records.

                    We are not matching on name fields. The vendor names are actually short abbreviations (usually acronyms) -- all text, no spaces or punctuation


                    It's still a text field and the inclusion of a space won't be visible, but will still prevent a match. You might want to check on that just to be absolutely sure.


                    The next thing to check is to make sure that the field to column mapping options that you specified in your script were correctly retained. You may need to go into the scripts workspace and open some dialogs just to be sure.

                    • 7. Re: Unexpected Import Behavior

                      To be clear:


                      The addition of Show All Record script step makes no difference in the resulting behavior of the import. It imports all records every time. Remember this is the first import, save a dummy record, which will not be used. Hence, there is nothing in the found set that will be updated.


                      There will be many matches among the incoming records, however. And what the client wants is what our demo showed -- namely, that the earliest records from the incoming set would be updated to match any data that had changed for the more recent ones. So, for example, if there was a price increase, there would be only a single product record reflecting the most recent purchase price. They have raw data coming in -- literally every line item from purchases their members have made -- and they want the target table to be a consolidation of the products they have purchased and what was the most recent price paid for each.


                      What we have here now is incoming records failing to update previous records coming from the same data set. I don't deal with this kind of import much, and didn't know (or at any rate remember) whether an import could only update records from a previous import. But the quick test I did showed that it was possible and now I am unable to recreate it -- either with the script or manually doing the import.


                      And, yes, of course, we are dealing with the correct table occurrence.

                      • 8. Re: Unexpected Import Behavior

                        (And the column mappings match. All the data is ending up where it should be. We just get dozens of "duplicate" records, followed by the occasional one where a price or something else has changed -- when we should be left with a single record for each product, reflecting such changes for the product listings that have changed.)

                        • 9. Re: Unexpected Import Behavior
                          The addition of Show All Record script step makes no difference in the resulting behavior of the import. It imports all records every time.

                          Sorry for repeating myself but that isn't really clear. That show all records script step must be performed from the correct layout/table occurrence context or it will not affect your results. It is not clear if you are doing that or not.


                          I've attached a demo file and an Excel file from which to update prices. It does exactly what you want if you run the script that I've labeled as "works". If you use the other script, it does a "show all" on a layout based on a different table occurrence than that specified as the "target table" in the import script step and thus fails to have any effect on the import process.

                          • 10. Re: Unexpected Import Behavior


                            Exactly what philmfdjunk mentioned.


                            And unfortunately sometimes user make unexpected adjustments in the excel file like changing a number field to textfield, add unwanted spaces or enters etc.


                            I would advice to created a temp table to import the excel files.


                            After the import:

                            Show all records in the target table

                            go to the temp table

                            Import and update the existing records en add the new records in the target table.


                            If you want you can also keep the import history in the temp table (add show all records, and omit all records to your import script before importing)


                            Hope this helps.



                            • 11. Re: Unexpected Import Behavior

                              I can't be any clearer than this: I know exactly what you mean and all of what you describe has been done. Correct table occurrence. Show all records. All fields matching up correctly. No spaces, extra characters in match fields.


                              However, subsequent records from the same spreadsheet (the bottom rows) are not updating previous ones (those that were toward the top) when imported into a FileMaker table, as they had been when we tried this with the test records (which were actually copies of real records from the full table that we are now trying to import).


                              It definitely is not the data, nor how it is being imported (because, as I said, I get the same result if I do every step manually without using the script.)

                              • 12. Re: Unexpected Import Behavior

                                Please look at my demo files. This process works for me without issue. Maybe you an spot a difference between my file and yours.


                                And you can be clearer. Not once did you clearly state that you were taking the possible differences in table occurrence context into account in your scripting.


                                Another generally useful tactic when a script does not perform as expected is to run your script in FileMaker Advanced's debugger. You might spot something while stepping through your script that is keeping it from working as expected--such as a script trigger that might be tripped by your script and then it could, in theory, interfere with your import script and keep you from getting the results that you expect.

                                • 13. Re: Unexpected Import Behavior

                                  Your suggestions were all very basic things that I assumed any developer would have ruled out before posting here.

                                  • 14. Re: Unexpected Import Behavior

                                    Try the temp table I mentioned

                                    Works for sure

                                    1 2 3 Previous Next