13 Replies Latest reply on Feb 24, 2017 6:21 AM by morbegoso

    Comparison Process

    morbegoso

      Hello:

      I want to automate the following process:

      I have two databases I want to compare. In each database there is a field and where there are matches I want to save the data in a third database. Those fields are text.

      Each database has other fields that I must filter before to be able to compare.

      Can somebody help me? I'm using FM Pro Adv 15.

        • 1. Re: Comparison Process
          philmodjunk

          It's not clear what you want to do. Can you provide an example?

           

          What exactly are you matching?

           

          What exactly do you want to save?

           

          What do you mean by "database"?--a very "fuzzy" term in FileMaker as it sometimes refers to a) a table, b) a file or c) a group of files.

          • 2. Re: Comparison Process
            morbegoso

            Ok, I will try to explain the case:

             

            I have 3 tables in the same file.

            Table 1 have X fields, with 3,000 records

            Table 2 have Y fields, with 2500 records.

            In both tables there is a field that I want to compare, if match (both are a text field) I want to add a new record in the table 3 by copying some data from table 1 and table 2.

             

            Is that clear?, how can I send you my project?

            • 3. Re: Comparison Process
              keywords

              If you click on Use advanced editor in top right hand corner you can attach your file to your reply.

              • 4. Re: Comparison Process
                Jason Wood

                So for every record in table 1, there could be 1 or more records in table 2 that have a match in this comparison field? And you want to document all of these matches in a 3rd table?

                 

                Is this a one time process? If not, will you be clearing the 3rd table and starting over every time you run this script? Or will it only look at new or changed records since the last check? Will it have to delete records where the match no longer exists?

                 

                Does the comparison field ever have a carriage return? How much text could possibly be in there? (I ask these questions to determine if a relationship can be used)

                • 5. Re: Comparison Process
                  philmodjunk

                  n both tables there is a field that I want to compare, if match (both are a text field) I want to add a new record in the table 3

                   

                  Is the value in that text field unique in both tables or might a record in one table match to more than one record in the other? And how might that affect the data that you need to transfer to the third table?

                   

                  In general terms, a relationship can be made that links the two records by this field. A script can then check for a matching record on the other side of the relationship and if so, set a pair of foreign key fields in a new record in table 3 so that one field matches to the record in Table 1 and the other to the corresponding record in table 2. That can tigger auto-enter settings on fields in table 3 that copy over data or you might find that you don't need to copy data, but just link to it. This will depend on what you want to do with this data once you have created your new record in Table 3.

                   

                  And one sticky issue to look out for: Are there returns in the text of this text field that you want to use for this comparison?

                  • 6. Re: Comparison Process
                    morbegoso

                    Table 1 contains the physical inventory of assets and,

                    Table 2 shows what is recorded in the accounts.

                    In both tables I have created a field that is filled by a process with data of 5 fields of both tables that concatenate in each case (Field1 & "/" & Field2 & "/" & Field3 & "/" & Field4 "/" & Field5 "/") , Serial #, Model, Description, etc.

                    If match, I want to save certain fields of both Tables in Table 3, this Table will contains the records that have reconciled.

                    • 7. Re: Comparison Process
                      philmodjunk

                      To repeat

                      "Is the value in that text field unique in both tables or might a record in one table match to more than one record in the other?"

                       

                      You really should set up a serial number or UUID as the match field.

                      • 8. Re: Comparison Process
                        vince.menanno

                        Hi there morbegoso,

                         

                        Also unclear exactly what you are going to do... but if all you are doing is looking for a match of a field in table A with another field in Table B. Then maybe loop over your records in Table A with a relationship from Table A to Table B.

                         

                        If on the other hand, it's a combination of fields, then you might want to consider using the handy MD5 hash function.

                         

                        GetContainerAttribute (

                          List (

                          FIELD_::ONE;

                          FIELD_::TWO

                          FIELD_::THREE

                          )

                          ; "MD5"

                        )

                         

                        Best of luck.

                         

                        Vince

                        • 9. Re: Comparison Process
                          morbegoso

                          The text should ideally be unique, but this does not happen in all cases, so it is sought that the matches contain the highest number of comparison criteria and the concatenation is executed at the discretion of the user.

                           

                           

                          First, all the criteria for comparison (Brand, Model, Serial #, Description, etc.) are then taken away (Brand, Model, Serial Number), then only Serial Number, and at the end by single Description.

                           

                           

                          Not always the items to compare have these fields with data.

                           

                          I send you an excel with the tables that I want to compare.

                           

                          Best regards

                          • 10. Re: Comparison Process
                            morbegoso

                            I forgot to indicate that the Fields to compare in table 1 is C_F and in table 2 is C_C

                            • 11. Re: Comparison Process
                              fmpdude

                              Some tools do let you compare and sync databases.

                               

                              Navicat, for example, has both options for its various supported databases - MySQL, SQL Server, Oracle, ... (Compare & Sync). Navicat will sync the structure also.  Navicat is nice in that it also supports "Search in (the entire) Database" for any term, even RegEx!

                               

                               

                              Since it sounds like you have additional logic in your requirement, and so you're in control, you may need to write external code to compare the tables directly in the FMP database and then populate third database. (not sure if you mean table or database, but logically, the same ideas apply.)

                               

                              HOPE THIS HELPS.

                              • 12. Re: Comparison Process
                                vince.menanno

                                What do you mean by C_F... do you mean each column individually? Or do you mean CDEF concatenated together?

                                • 13. Re: Comparison Process
                                  morbegoso

                                  C_F and C_C are the name of the columns in  each tables (table 1 and table 2) that I want to compare. The comparsion is row by row.

                                  I use the table 1 as a pivot, Advancing one by one until the last record, searching in table 2 the conicidences, if find a match , the idea is to marc that records in both tables and copy some data into new record on table 3.