8 Replies Latest reply on Dec 5, 2016 8:16 PM by JackRodges

    Character Limits for Import Match Field and Text Field Index

    jdbranum

      I would like to know how many text characters in the match field are used to determine whether a match exists when importing records with the Update Matching option set.  I do not know whether or not the indexing limit on text fields applies to this type of  import operation.

       

      Related question is how many characters are included in the value index of both text and number type fields.  From related posts for databases prior to FileMaker 15, this value was indicated to be 109.

       

      The reason for my question is that I am currently creating a calculated match column when importing from a large Oracle database via ODBC, and need to know the character limit that I must observe to prevent importing duplicates.  The match column calculation includes the contents of the columns that determine uniqueness of the rows in the Oracle source view.  This technique works well and much more quickly than using multiple individual Oracle columns to match corresponding FileMaker fields.  The match field in the FileMaker table is a calculation using the same set of fields corresponding to the Oracle columns.

       

      Thanks in advance,

      James Branum

      Internal Developer at Lawrence Livermore National Laboratory, California

        • 1. Re: Character Limits for Import Match Field and Text Field Index
          philmodjunk

          You aren't limited to a single match field and thus aren't limited to the max characters for that field. You can use multiple pairs of match fields.

          • 2. Re: Character Limits for Import Match Field and Text Field Index
            David Moyer

            Hi,

            you can search for the "Technical specifications ..." document.

            Maximum field length is 100.

            Edit:

            to quote the above-sited document:  "Length of field name: Up to 100 characters."

            • 3. Re: Character Limits for Import Match Field and Text Field Index
              jdbranum

              Thank you for your answers.  I was able to locate the Technical Specifications for several recent versions of FileMaker Pro and verified that the limit on text field indexing is first 100 characters in a word or value, and first 400 digits for number fields.  Unfortunately, noting in the list of specifications addresses the other question, which is how many characters in a text field are used in determining matches when importing matching records.

               

              Re single vs multiple match fields for importing matching records, I did try using multiple fields instead of a single combined field for importing from the Oracle source view, and it was much too slow to be useful in my case.  I have more fields than three to match, but even using only three, I had to give up waiting for the script to run after an hour when trying to selectively import only new records from a set of around 200,000 source view rows.  It only takes a few minutes to import using a combined match field calculated from 9 different columns.  The total length of that field is usually 80 or less, but a few recent entries had a lot of characters and that pushed the length to 120 characters for several records.  The import for those records has continued to work without reporting an error, but probably only because no similar records have come along up compete with the originals.

              • 4. Re: Character Limits for Import Match Field and Text Field Index
                David Moyer

                to be clear, my answer wasn't related to indexing, it was in reference to the maximum number of characters in the field name.

                • 5. Re: Character Limits for Import Match Field and Text Field Index
                  philmodjunk

                  I suspect that the indexing limitation applies here.

                  • 6. Re: Character Limits for Import Match Field and Text Field Index
                    ch0c0halic

                    I suggest an alternate approach. If you can edit the SQL tables!

                     

                    Desired results:

                    Import only new records.

                     

                    Alternate approach.

                    Add a field to the SQL table to flag when the record has been imported.

                     

                    Set the field to a "1" after import.

                     

                    Change Query to exclude records with a 1 in the flag field.

                     

                     

                    Modified Records?

                    If instead you need modified records then you could add a TimeStamp field in the SQL that is set whenever the record is updated. Keep track of last time imported. Any record with a Modification Timestamp later than the last import is possibly modified. Set timestamp field to current Host TimeStamp after import.

                     

                    This also requires the data entry side of the SQL tables to have a stored procedure call (or script step if it's an automated process) to load the modification TimeStamp field.

                    • 7. Re: Character Limits for Import Match Field and Text Field Index
                      jdbranum

                      Thanks for the additional information.  I, too, suspect that the indexing character limit applies, but I am looking for an authoritative answer on this question if possible.  in the meantime, I will do some more testing with a test database to disclose the answer indirectly.

                       

                      In my most-complex solution that uses the combined column matching technique, the Oracle source view is read-only, and is completely redone "from scratch" daily, with no row modification date or timestamp of any kind, and no globally unique ID column. That precludes an interactive semaphore technique, unfortunately.

                      • 8. Re: Character Limits for Import Match Field and Text Field Index
                        JackRodges

                        If this is critically important to you, you could answer your own question by running a test. (Of course the answer might change with a an update).

                         

                        Run a test and then replace the characters in the string beginning at character x.

                         

                        pseudo example

                        aaaaaaaaaaaaaaa

                        aaaaaaaaxaaaaaa

                        aaaaaaaaaxaaaaa

                        aaaaaaaaaaxaaaa

                        aaaaaaaaaaaaxaa