Dr.Hamed Posted May 16, 2006 Posted May 16, 2006 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
aldipalo Posted May 16, 2006 Posted May 16, 2006 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.
John Mark Osborne Posted May 16, 2006 Posted May 16, 2006 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
comment Posted May 16, 2006 Posted May 16, 2006 If [Get(LastError) = 401] It doesn't seem very likely to happen in this case.... :qwery:
John Mark Osborne Posted May 16, 2006 Posted May 16, 2006 True. Just got carried away. Thanks for pointing it out.
Søren Dyhr Posted May 17, 2006 Posted May 17, 2006 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
Dr.Hamed Posted May 17, 2006 Author Posted May 17, 2006 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
Søren Dyhr Posted May 17, 2006 Posted May 17, 2006 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
comment Posted May 17, 2006 Posted May 17, 2006 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.
Søren Dyhr Posted May 17, 2006 Posted May 17, 2006 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
comment Posted May 17, 2006 Posted May 17, 2006 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.
Dr.Hamed Posted May 17, 2006 Author Posted May 17, 2006 DS, I do not get the purpose of adding NameID Set Variable [ $theID; Value:namesearch::NameID ] Please explain.
Søren Dyhr Posted May 17, 2006 Posted May 17, 2006 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
Recommended Posts
This topic is 6834 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 accountSign in
Already have an account? Sign in here.
Sign In Now