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.
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?
If you click on Use advanced editor in top right hand corner you can attach your file to your reply.
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)
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?
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.
"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.
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.
Best of luck.
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.
Example.xlsx 382.3 K
I forgot to indicate that the Fields to compare in table 1 is C_F and in table 2 is C_C
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.
What do you mean by C_F... do you mean each column individually? Or do you mean CDEF concatenated together?
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.