comparing two fields 1

[thanh1800]

Hi

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?

Thanks
thread295-724255

 

[JeanW]

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

 

[thanh1800]

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.

 

[JeanW]

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 ?

 

[thanh1800]

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.

Thanks

 

[JeanW]

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

Let’s say you have following fields:
nameFirst
nameLast
zipCode and
telephoneNumber

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:
Case(
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...

HTH

 

[thanh1800]

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.

 

[JeanW]

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....

HTH

 

[JeanW]

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.

 

[thanh1800]

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.

 

[JeanW]

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.

HTH

 

[thanh1800]

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
a2
b3
c2

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) then
flag col5, row 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.

thanks

 

[JeanW]

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.

HTH

 

[thanh1800]

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