I have a database with multiple fields. I need a script that finds records containing the entries similar to entires of the current record.

[color:red]Ex: Two fields are:

First_name and Last_name.

I create a new record and enter for example JOHN (first_name)and Doe (Last_name).

I need a script that finds the previous records containing John doe in either fields

Thanx

Can you give a more detailed explanation of what you are trying to accomplish? Are you looking to see if the person/record already exists or do you want to see how many times this person has ordered from you, etc.? Please explain.

It sounds like you need new requests. Here is a script that finds all the records matching the current for either last or first name:

Set Error Capture [On]

Set Variable [$First; Value: MYTABLE::first_name]

Set Variable [$Last; Value: MYTABLE::last_name]

Enter Find Mode []

Set Field [MYTABLE::first_name; $First]

New Record/Request

Set Field [MYTABLE::last_name; $Last]

Perform Find []

If [Get(LastError) = 401]

Show Custom Dialog ["Error"; "No people match"]

Show All Records

End If

If [Get(LastError) = 401]

It doesn't seem very likely to happen in this case....

:qwery:

True. Just got carried away. Thanks for pointing it out.

I need a script that finds the previous records containing John doe in either fields

I would say that JMO's script miss an omitted request, that exclude the record from which the search was issued. In my humble opinion is it best done by having ID'ed each record.

--sd

Thanks for the script, it works great; however, the script finds: John Doe, Jane Doe, John Walsh

How to modify the script to find records only containing: John Doe & Doe John.

Thanks

You need to DeMorgan to make AND'ed searches:

Set Error Capture [ On ] 

Set Variable [ $first; Value:namesearch::name ] 

Set Variable [ $second; Value:namesearch::Family ] 

Enter Find Mode [  ] 

Set Field [ namesearch::name; $first ] 

Set Field [ namesearch::Family; $second ] 

Omit Record 

New Record/Request 

Set Field [ namesearch::Family; $first ] 

Set Field [ namesearch::name; $second ] 

Omit Record 

Perform Find [  ] 

Show Omitted Only 

If [ Get ( FoundCount ) = 1 ] 

    Show Custom Dialog [ Title: "Hello!"; Message: "The person is unique"; Buttons: “OK” ] 

Else 

    Pause/Resume Script [ Indefinitely ] 

End If 

Show All Records 

--sd

What is the purpose of building the omitted set first, then showing it - instead of going straightforward:

...

Enter Find Mode []

Set Field [MYTABLE::first_name; $First]

Set Field [MYTABLE::last_name; $Last]

New Record/Request

Set Field [MYTABLE::first_name; $Last]

Set Field [MYTABLE::last_name; $First]

Perform Find []

...

??

Anyway, if I'm guessing the purpose correctly, a portal might be more useful here.

Indeed I made an error, what I meant was this:

Set Error Capture [ On ]

Set Variable [ $first; Value:namesearch::name ]

Set Variable [ $second; Value:namesearch::Family ]

Set Variable [ $theID; Value:namesearch::NameID ]

Enter Find Mode [ ]

Set Field [ namesearch::name; $first ]

Set Field [ namesearch::Family; $second ]

New Record/Request

Set Field [ namesearch::name; $second ]

Set Field [ namesearch::Family; $first ]

New Record/Request

Set Field [ namesearch::NameID; $theID ]

Omit Record

Perform Find [ ]

Since the question was:

script that finds the previous records containing John doe in either fields

Which means that it's going to be done when the record is completed. I have however a suspicion that it isn't a portal nor a script, but instead a field level validation that ensures uniqueness...

--sd

I hate to disagree again, but all field level validation can do is ensure uniqueness. Here, a person should be allowed to enter another John Doe - provided he/she has checked it is indeed ANOTHER John Doe. For this, a 'list of suspects' must be produced.

For the same reason, I am not convinced omitting the current record is a good idea. If anything, it should be shown separately from the previous ones - hence a portal seems like the most suited method.

DS,

I do not get the purpose of adding NameID

Set Variable [ $theID; Value:namesearch::NameID ]

Please explain.

Originaly did you ask for the records holing these names excluding the one you just entered data in. A search without this last omitment will include the newest record as well.

--sd