Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

comparing two fields

comparing two fields

comparing two fields


I can anyone help me with a problem I have. I need to use the data entered into one field to search the database for matches.

I have been using the perform find script with little success because the criteria only read as a string("") where as I need to use field(field reference) for the criteria.

Can anyone tell my how to reference the field in the criteria? or know of a better way to perform this type of search?


RE: comparing two fields

Are you looking for duplicate records or just 'similar' values in other records ?

RE: comparing two fields

Well actually Jean, I am looking for both
I will be performing multiple searches that requires different level of accuracy... so any method will be of great used at this time.

RE: comparing two fields

I use a method based upon a self join and a multikey.

Before I go deeper in maybe useless details, which version of FM do you use and are you basically familiar with relationships and multkey values ?

RE: comparing two fields

I am using Filemaker Pro 8 and am pretty familiar with 'relationships and multkey values'

So if you can paste the script or code I can properly figure what ever you don’t explain.


RE: comparing two fields

Here we go.
I don’t know your file nor fields, so I use some dummy fields.

Let’s say you have following fields:
zipCode and

You need a ‘sameRecord_cti’ (_cti = calculated, text, indexed) field, this will be based on several criteria, each separated by a carriage return. This is the MultiKeyField.

The calculation can be :
nameFirst & “ “ & nameLast & “¶” &
nameFirst & “ “ & Left(nameLast; 3) & “¶” &
Left(nameFirst; 1) & “ “ & “ “ & nameLast & “¶” &
nameLast & “ “ & zipCode & “¶” &
Left(nameLast; 3) & “ “ & zipCode & “¶” &
nameLast & “ “ & Right(telephoneNumber; 4)

And you need a self join relationship sameRecord_cti = sameRecord_cti.

By placing the portal based upon this relationship on a layout you can ‘see’ all the duplicates and also the possible similar records. Place some dedicated fields from the record in the portal to recognize the data.
You have to play with the values in the calculationfield to make it to your needs.

By placing a portal on the layout, all similar records may be shown at once. Once listed,you may provide applicable options/solutions to the similar records -- ie delete, edit flag, etc, etc. It is up to you to decide the appropriate method.

If you don’t have ‘room’ on the input layout, you could place one mergefield on the layout, with a calculation (text) along these lines:
Count( sameRecord_cti::RecordID ) = 1; "";
"Similar Records (" & Count( sameRecord_cti:: RecordID ) & ")"
This will generate a text field with a message Similar Records (x), where (x) will be the number of records in the portal. Redirect the user to the layout where the portal is for further action.

Your actual record will also be in the portal, so it’s best to highlight that portalrow, to prevent bad things would happen to it.

If you need more details, feel free...


RE: comparing two fields

Thanks Jean

I was able to adapt your code to the problem but perhaps I was not as specific in my question as I should have been.

I really want to be able to specify the location too so for example the whole table, another table, or multiple tables, or just one column.

so for example
I have a global field "Search"
I want what ever valued in search to be compared to fields in the table for matches or 'similar'

at current it will only search the fields in the current record and then return matches.

RE: comparing two fields

You can put whatever field in the calcualtionfield, as long as there is a valid relationship between your table and the one/several where you want to 'search'.

Every field, or part of a fieldcontent, one relation away or several is valid, as long as you refer to it in the calc field.

relationship01::nameFirst & “ “ & relationship01::nameLast & “¶” &
relationship02::nameField & “ “ & Left(relationship02::nameField ; 3) & “¶” &
etc.. etc..

You can make whatever combination you need.

I use this f.i. for isotopes mix, which is based upon fields 2 and 3 relationships away in the graph.
It takes some time to tweak the calculation....


RE: comparing two fields

You can also make some global fields, if you want it more or less dynamic, and refer to those fields in the calc.

This way you can play with the outcome, if it's depend on what your need.

I use this workaround in combination with radiobuttons, where the user can make choices before a new record is created. And also on a search layout.

With set field and scriptparameter combination, every user can make his/her own match.

RE: comparing two fields

     Col1 Col2 Col3
Row1  a1   a2   a3
Row2  b1   b2   b3
Row3  c1   c2   c3

okay here is my situation
i can search all fields in row1 so if my criteria is 'a' my search result will be a1,a2,a3

BUT if i search 2 my search result will only be a2
i want to be able to search 2 and get the result a2,b2,c2

as you can see the process I have can only search the current row that is being accessed.

with the latest code you given I am able to access a row? in other tables.

RE: comparing two fields

Two things to keep in mind.

1. The calculation, which will give you a result for yes/no similar

2. A portal for showing the results

You can combine several tables to 'find' similar, but will not be able to show those in 1 portal. Only that 'there are' similars in the application.
Using 1 relationship per calc/table, you can make combinations where this is possible with several 1 or 2 row portals.

That's the'playing' with the calculation.
If you need several portals (for each table 1) it will be a calc in each table of course and a relationship back to your initial table.
Sorry, I thought this was clear from the calc/relationship post that if you go over several tables you need a calc in each table to be able to show records.


RE: comparing two fields

okay I have all the calc done and set(col5) and I know it works

but my prob is once the calculatoion is done and l changed the search field in col4(global field)

     Col1 Col2 Col3 Col4 Col5
Row1  a1   a2   a3   2    a2
Row2  b1   b2   b3   2    b2
Row3  c1   c2   c3   2    c2

to say... 3(when viewing row2) my col5 will look like this

If someone can explain to me how to get the value held in col4, row3 (2) and then search col2 (a2,b2,c2) for values that are similar to col4, row3 (2) and the matches should be a2,b2,c2

the formulae for this type of search I believe should be something similar to:

-- code --

for (i=0 to i<last;i++){
if (field referenced (col4,row2) == col2, row[i]) then
flag col5, row[i] as found
end if

-- end code --

but I am unable to convert it to a lunguage that filemaker pro can understand.

so if I was to look at some of the code structure and syntax of filemaker pro (code sample similar to what I have written but in filemaker pro format) I am sure we can figure out what I am looking for.


RE: comparing two fields

Not knowing how your relational structure is and looking at your formul, I would use the Go to Related Record [From table: "<table name>"; Using layout "<layout name>"], with the option :Match all records in the current found set and put that over the relationship.

I think you can do it with a field and a custom function. The field "Search field", unstored text calc:
If( IsEmpty( YourRel::ID ), ID,
Get Search field( Count( YourRel::ID ) ) )

Get Search field is a custom function:

....and I deleted all the rest.
While I was typing I saw that it was not what you need.

I still think the way to go is a multiLinekey field in combination with the Match all records over it.

On the other hand, the very need to search in multiple tables is an indication that the records really belong in a single table, but again, I don't know your design nor the structure...

You can search each table in turn, but you will need multiple separate windows in order to present the search results. Alternatively, you could import the found records from each table into a union table, so that they could be viewed together.


RE: comparing two fields

Hi jean thanks for all your help

given our long discussion I have been able to figure out how filemaker pro works.

your inputs was of great help and I feel that this thread is now a closed topic

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close