One search box to search multiple fields

[sydneytim]

My movie database has a number of fields including "actors" and "directors".

Currently, if I move into "FIND" mode, I am able to enter a search term in the "actors" field or the "directors" field for the directors.

Is there a way for me to include in my database a box that allows me to search all fields simultaneously?

EXAMPLE: in this "SEARCH ALL FIELDS" box, a single search for 'Mel Gibson' would show films where Gibson was either an actor, a director, or both.

Thanks in advance,

Sydneytim

[LaRetta]

Hi Sydneytim

You can create a concatenated field which will hold the value from each field you wish to search. You can use either a standard text field (with Auto-Enter calculation, Replace Existing Value) or an indexed text calculation.

It would look something like this:

ActorsField & " " & DirectorsField

Then simply place this field on your layout and allow entry only in Find mode (through Field Behavior). Perform your search and you'll be set.

Structurally, I'd probably use a self-join (ActorID to DirectorID) to assist with pulling associated information into the various Director's fields, or even another table but that would depend upon your needs. A self-join (and then inserting the ActorID into the DirectorID field via value list) would allow you to look up a Director's information for reporting purposes, etc. without having to duplicate it. However, I'm getting off point and I apologize.

LaRetta

[sydneytim]

Thanks LaRetta!

This answer was extremely helpful. I followed your directions, and the concatenated field worked first go!

However, I became over-confident, and despite my limited ability, started to follow your 'structural' advice, and quickly found myself 'out-of-my-depth'. The result?

I encourage you to read about it in my most recent post, entitled, "Are these records gone forever?"...

I'm sure I'll get it worked out. Any tips?

Thanks again,

Sydneytim

[sydneytim]

One more thing...

I set up a new "Combined" field based on a calculation, just as suggested.

The only problem I'm having is that the calculation is only being applied to new records, not to the hundreds of existing records.

Is there a way to apply the calculation of the "Combined" field to existing records?

Anyone?

Thanks,

Sydneytim

[LaRetta]

Hi Sydneytim,

I should have suggested this in my first post because I was aware you had existing records. My apologies for the oversight ...

Turn an indexed calculation into an unstored calculation first. When the calc box appears, click Storage Options and click the box 'Do Not Store...' then OK back out. When you leave Define Fields, it will force them to recalculate. Then go back to Define Fields and change it back to indexed.

If it is a standard text field with Auto-Enter (Replace), you will need to use Records > Replace Field Contents and enter that calc (can be copy/pasted) into the 'Replace via Calculation' box. If this is multi-user, it is safer to script loop through the records instead.

And I'm glad to see Ted assisted you on your missing records issue. With vs. 7, it is critcal that a layout always be pointed to a table occurence (just as it is with fields).

LaRetta

[sydneytim]

HOORAY FOR LA RETTA!

That is perfect! It's working great... Everything came together just as you described.

Thanks again for your clear instructions. Very much appreciated!

Sydneytim