Jump to content

Find records with multiple fields


Dr.Hamed
 Share

This topic is 5684 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5684 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.