search using a variable

[andrew29]

Hi,

I have a database with 5 fields that each have a drop down box with 5 different selections. (The idea is that an organisation can apply for more than one grant of the same type)

What I need to be able to do it search for an organisation that has registered for a particular type. (an organisation is able to apply for multiple grants of the same type)

I have constructed a search as follows:

application type = "Minor works" or application type1 = "Minor works" or application type2 = "Minor works" or application type3 = "Minor works" or application type4 = "Minor works"

this works fine, however, using this idea I would need to create a new calculation field for each possible selection (ie minor works, development etc).

How can I create a field where what I type into the field is passed to the query. For example if I type 'development' the field would perform a search on 'development' etc

All help much appreciated

FileMaker Version: 5

Platform: Windows XP

[CyborgSam]

I'm not sure what you're asking or doing, so here's a shot:

I assume you're using FileMaker's Find mode. If so, global fields appear in Find mode but will be inactive (you can't enter data into them for the Find).

To use globals: don't change the layout to Find mode. Instead, enter the data in Browse mode, then use a button to run a script that will put the proper layout in Find mode, and then use Set Field, Insert Text, and Insert Calculated Result to set the fields for the Find, then perform the Find.

If you're not using globals, please provide more info (or upload the file) so we can see what's going on.

[andrew29]

HI Sam,

I'll try and clear things up a bit.

Yep I'm in FM find mode. I've created a calculation field (with the query above in it). It works fine for the query that is hard coded into it (minor works), but if I want to search for 'development' rather than 'minor works' I have to physically change the underlying query.

At the moment to run the query in find mode I type a 1 (1=true I suppose) in the calculation field and the query executes.

In a perfect world I'd like to just select from a drop down menu of all the possibilities (minor works, development etc) and have it execute the query relative to the selection.

Unfortunately I cant attach the file, but if things still arent clear I'll mock something up and post it.

Thanks again for your help.

[andrew29]

Ok I've mocked up a sample database. The design is lacking but its an old system and I have to tinker at the edges.

Let me know if i need to clarify things more.

Cheers

[andrew29]

attachment

test database.zip

[CyborgSam]

Andrew->

See the attachment of the modified version of "test database". It shows how to do the query. Basically, I combined the five fields into one via a calculation and put this on the layout as a one line popup menu. A calculation field with a popup menu? This isn't intuitive, so look at what was done & how to get a complete understanding of the methodology.

Since users shouldn't be able to see/use popup menus with the calculation fields in Browse mode, I show you two neat ways to hide them. The calculated container method is good for hiding non-active objects (text, graphics) but allows active objects (fields, buttons) to become visible when clicked on. The one-line portal method is the best, it hides all objects and deactivates active objects.

FileMaker Version: 6

Platform: Mac OS X Panther

test database v2.fp5.zip

[andrew29]

HI Sam,

EXCELLENT! Thats just what I needed!!!

Gets me another step along the way to fixing up the mess that is called a database...

Thanks again for you help, it really is appreciated