Perform find depending on the contents of a field with a button.

[edudna]

I want to make the follow find set:

Let we have a field called find and a button to perform the find script beside it just like the webpages. I want depending the text or number I put in the field and then push the button to find anything in the database matching the text or number that I put. Just like a search machine of a web page that finds anything matches inside the site.

Pascal

[T-Square]

I'll assume that your FIND field is a global field called gSearch. Here's one way:

Set Error Capture[On]

Go To Layout["The Layout To Search On"]

Enter Find Mode[]

Set Field["The Field You Are Searching"; gSearch]

Perform Find[]

If [Get(LastError) = 401]

Show Custom Dialog("No Records Found")

Else If [Get(FoundCount) = 1]

Go To Layout["Full Record Display"]

Else

Go To Layout["List Display"]

End If

Since you're in FM8.5, you could set this up using Script Parameters, but the steps would be essentially the same.

HTH,

David

[LaRetta]

I might add to it ... if you want to search ALL fields (or MOST fields), then you either have to create a concatenated calculation which combines all the fields into one calculation and search IT instead or you need to use a developer layout which contains all the fields to search and perform multiple find requests (one new find request for each field). Concatenating all fields into an indexed calculation will increase your file size as well.

Do you want the results as *global* or only if it starts with the global? Mixing data types can also give you grief, ie, date fields and text fields. You'd need to test the field type to know whether to include the * - you wouldn't want to include it in a date field search.

It isn't necessary to go to a layout which has the concatenated field if you are using Set Field[] and it is using the same table occurrence. But if you have to loop through the fields, ie, you don't hard-code the fields within the calc, then you'll need to be on a layout containing all of the fields you are searching (because Go To Field requires it).

Edited December 2, 2006 by Guest
Added sentence

[Genx]

I just want to know why you would want to do a search like that?

[LaRetta]

I agree that doing a search on EVERY field is too much. But, while viewing a list of records, one single search box with a GO button (like Google provides) makes sense and I use it myself in a few places. It's a lazy way of providing a search so User doesn't have to type into each specific field and/or it allows seaching on fields which aren't displayed to the User. I turn off 'Allow Entry' to most List views as well so there is no error of thinking they are in Find when in Browse.

Usually, I provide dedicated layouts but List view and one search box works nicely together when searching basic fields such as Name, Address or Phone. At least, that's why *I* use them. :wink2:

Edited December 2, 2006 by Guest

[Genx]

Yeah, i do use a one field "global" search aswell, but only provide for name, or phone number -- all names and phone numbers in my DB's are stored in one table each (i.e. 1 name table, 1 ph table) so that's fairly easy.

[Ender]

all names and phone numbers in my DB's are stored in one table each (i.e. 1 name table, 1 ph table) so that's fairly easy.

You Aussies always got to do things goofy.

I can understand separating the Phone Number, as there is often a one-to-many between the entity and the associated Phone Numbers. But does this happen with your names too?

I think of a Name as a single attribute of the entity it is associated with. A Contact has a Name, an Employee has a Name, or a Student has a Name. It seems odd to separate the Name into a separate table. Maybe you can enlighten me on how this is useful?

[Genx]

Well for example.. property:

Everything revolves around houses or groups, not generally individual people. Houses have multiple owners, or tenants, or contacts etc. Buyers will often come in in groups of two or more -- when you address e-mails etc. it's rude to only address one and double entering the exact same property requirements is sort of redundant.

The same applies in finance.. Multiple applications, therefore multiple applicants per application, multiple banks == multiple contacts per bank which in most cases will not justify entire individual records.

Oh and i use one extra field to indicate "primary contacts" where they exist, though more often than not, there may be more than one primary contact or applicant.

Plus: One-to-One relationships that exist i would say roughly 60% in the above circumstances never hurt any one.

and Plus Plus: It makes searching in the previously described manner A LOT easier...

[Genx]

You Aussies always got to do things goofy.

LOL! I think that's the funniest thing i've ever heard (or rather read...) you say :B

[Ender]

Everything revolves around houses or groups, not generally individual people. Houses have multiple owners, or tenants, or contacts etc. Buyers will often come in in groups of two or more -- when you address e-mails etc. it's rude to only address one and double entering the exact same property requirements is sort of redundant.

I guess I could see this. Except that there's usually more than just the Name to remember about a particular person in those situations. Birthdates, Social Security number, plus a number of other things might be useful to know about an Owner (or whatever). So instead of using a Name table, there's usually a Person or Contact (or Owner) table that has all those fields, including the Name.

Plus: One-to-One relationships that exist i would say roughly 60% in the above circumstances never hurt any one.

I would disagree. Usually one-to-one relationship can and should be eliminated by combining the tables. They can be useful segmenting a module or keeping stored images in a separate file, but in general they add complexity to the relationship graph unnecessarily and could complicate Finds.

It sounds like maybe the reason you've designed it that way is to allow for a general Name search that searches the Owners, the Tenants, and the Contacts from one place. I suppose this could be a valid purpose. Personally, I like my searches to be context specific, but I can imagine the business requirements where a search of all three could be desired. Though if that were required, I'd probably design it with a general Contact table instead, where multiple Contact Types (Owner, Tenant, Whatever) could be selected.

LOL! I think that's the funniest thing i've ever heard (or rather read...) you say

Hmm, I'll have to remember to kick you around a bit more. :B

[Genx]

Well, let's just say, i am now sitting on version 2 of a variety of applications that all required major restructuring -- version 1 was trialled with particular clients and more than anything -- the changes requested were the above -- since then i've never had anyone question my logic regarding the above.

As for storing social security numbers... doesn't come up... Birthdays go in to reminder sections, rest goes in notes etc.

As per the "single place" there is, but you can only group so many things together -- most of my searches are context specific, there are clear divisions sometimes. But seeing as what generally happens in an industry such as real estate is someone calls up and says "Hi, my name is Marie, I'm dealing with John"... sure the call goes through with John, but rather than having him be embarassed and not know what she wants i.e. if she's got a house on the market with him, if he's due to have an appraisal, has had an appraisal, she's just a general contact, or a buyer that he's shown specific properties etc. etc. etc.

I'm not saying it's always the best way to structure something -- but in the particular industries i deal with it's quite critical for my clients. I can see other industries where it wouldn't necessarily be appropriate for example mechanics, have particular clients -- generally single names.

Finally, one other advantage, is that there is no need to replicate fields in every single "module" so to speak.

Edited December 3, 2006 by Guest

[edudna]

First I want to thank you all of you, you really realize my mind. Global search in educational databases have an important meaning as I know as expert in education not in databases. Inexperienced Teachers will use the databases that must be strong and easy to use. They want all the utilities before them without to push a button sometimes (case study).Find in separate fields will confuse them a lot.

Pascal

PhD in information technology in Education

Msc in School Education

[edudna]

Set Error Capture[On]

Go To Layout["The Layout To Search On"]

Enter Find Mode[]

Set Field["The Field You Are Searching"; gSearch]

Perform Find[]

If [Get(LastError) = 401]

Show Custom Dialog("No Records Found")

Else If [Get(FoundCount) = 1]

Go To Layout["Full Record Display"]

Else

Go To Layout["List Display"]

End If

I have test this but it is not work very well. I do not understand what do you mean "Full record display" or "list display". Another issue is that this search is going to search all the databases for the text or number that we put in gSearch. Please explain

[LaRetta]

Another issue is that this search is going to search all the databases for the text or number that we put in gSearch.

If you do a search here on Forums for Google Search (wow, did THAT sound funny), there have been various approaches; but it would depend entirely on your structure. The question you must ask your Users, is what RESULT record set do you want to end at? Do you have a Students table and an Addressess table etc? Or do you have each Class or School as separate tables? You need to determine the relevance of the results and what set of data your User will ultimately be viewing.

Envision this:

User: I want to find everything in the universe that I type into search.

You: If you type 123, you want all addresses, phone numbers, social security numbers of anyone in the world who matches?

User: Of course not. Just those from this school.

You: So if I present you a list with 166 matching addresses, 240 phone numbers and 101 social security numbers, what do you want with that information?

User: I don't want THAT information. I want the Students with that information.

Ah ha ... now we're getting somewhere. The result is what is important here. So if you want all STUDENTS matching anything searched, you must be in a Student table. If you want to search other tables, just place the other table's fields on your layout and perform your search - because you only want to find addresses which MIGHT match ANY Student from the student table. Searching is a complex subject - ask Amazon and Google. Even THEY don't open up the world to searching. There must be a logic behind what is entered, what is searched and the resulting set. You can 1) concatenate fields from the same table and search that, 2) place related fields directly on your student layout and search that or 3) go to each different table to search and capture (for example, the StudentID) into a multiline. But the third option disconnects where you will land. If you have two Student tables, and the result 123 exists in BOTH tables and they want a list of Students, what do you do?

So help us pin down the logic; tell us the tables (name and purpose) to be searched and the fields from each table that you want to include and indicate their relationship. And tell us where you want the User to ultimately land with the resulting set. You can script this under a Freeze Window and your Users won't know what is happening. But you'll be doing a lot of work behind the scenes to produce the results they want.

LaRetta

Edited December 3, 2006 by Guest

[Oldfogey]

You Aussies always got to do things goofy.

Watchit, mate!

At least we can write English. Well, most of us, anyway.

Don't forget Genx is young AND lives in Melbourne. You wouldn't pick on a mentally handicapped person, would you? So, leave Genx alone.

[Genx]

Dear lord,

That was a bit harsh don't you think? Insulting my age AND my city? Remind me, what city was voted most liveable in the world? Melbourne ... well untill our mafia started killing each other anyway.

Anyway, calling me mentally handicapped isn't very nice.

As for my english, I'm russian so if you think my grammar is bad, what can i say?

Finally, WHY THE HELL IS EVERYONE YELLING AT ME? I'm not embarrassed about my age - i'm two years younger than most people at uni - and on that note just because I'm young it doesn't mean I'm stupid.

[Oldfogey]

Hey, Genx, nobody's picking on you for being young or mentally handicapped. I'm picking on you because you are young and incredibly clever. That is unforgivable! (I was actually defending you, by the way.)

Being young invites forgiveness for doing stupid things, on the grounds of inexperience. Being old doesn't - until you get to the senile range, anyway.

If you are not a true Melbournian that's OK, too.

Moderator, is this off-topic by any chance?

[Genx]

Lol, technically, i've been here for 6 years, prior to that i had the ah... misfortune of calling myself a new zealander -- no offence to any new zealanders -- for 8 years... technically, i'm only russian cause i was born there : .

In the mean time, off-topic ... I am a moderator and if we're discussing me, it's fine -- Lol! Nah Stephen might kill me for that.

Anywho, I'm glad I'm not being insulted, still a bit confused though

Oh and as a last thought, i never really took offense to Ender's comment, the way it was phrased made me think it meant no real disrespect.

[Lee Smith]

Being young invites forgiveness for doing stupid things, on the grounds of inexperience. Being old doesn't - until you get to the senile range...

Thanks for giving me an excuse that I can use.

Moderator, is this off-topic by any chance?

Yep.

Lee