Search in multiple fields help

[Showdist]

Good morning!

I have a little trick i'm trying to do and i have not being able to achieve it properly.

I have a field that contains the data from all the fields i want to search into. (I'm trying to do a search for keyword "x" in all the fields of that database). When i search for a keyword, it comes out with only the records that have the exact text in the field.

For example, if i search for Sun, it will show me the records that have Sun in it, but not the ones that have Sunny or Bright Sun in the fields. (I have 37 fields on my record that may contain this data, and there's about 400 fields total per record, and about 10000 records total)

Is there a simpler way to do this? All i want to do is search in a bunch fields at the same time?

thanks in Advance for your time

[comment]

Several alternatives come to mind, but it should also work the way you describe it. How is the "field that contains the data from all the fields i want to search" defined? And exactly how do you search for a keyword?

Edited February 6, 2008 by Guest

[Showdist]

I might have done it wrong, but i made that field a calculation field and in the details, i put:

Description 1 & ¶ & Description 2 & ¶ & Description 3 & ¶ & Description 4 & ¶ & Description 5 ... up to 37.

It shows all the data i want in that field, but i cannot get the search function to work with it.

If you have alternatives, feel free to enlighten me!

[comment]

Is this calculation's result set to Text?

[Showdist]

It is set as text..

:B

[comment]

I cannot reproduce your problem. Can you post a simple file showing this?

[Showdist]

It might be because i have complex descriptions. A typical one i have is:

Chain Master VarioLift BGV-D8, 4-104'/min, 63' lift, patented friction clutch, chain bag, eye plate.

What i want to search is something like either "VarioLift", "chain bag" or something similar.

Could that cause the problem? If that's the case, is there another way to do a search in multiple fields? Because i know my description contains "operators" like "/", "-", "+", etc. It might confuse the search feature.

Help?

[comment]

If you can find "VarioLift" in the original field, you should be able to find it in the concatenating field just as well. I don't think we should look for alternatives before figuring out your current problem. Does this file work for you (there should be 4 records found)?

SearchMultipleFields.fp7.zip

[Showdist]

It works with your file,

the search feature is part of a long script in mine, maybe i've done something wrong. Let me investigate.

Thanks for your help, i'll keep you updated.

[Showdist]

I found it, all thanks to your file, i noticed that i needed to put Global for the text field. It wouldn't work without that.

The only issue i have now is that for a search with:

2421

it won't find HBL2421, HBL2421CN, HBL-2421.

For HBL, it will find all references to it. (works fine)

But the number does not work properly.

Thanks again

[Showdist]

Now that i took a deeper look at it, it seems that it won't find letters that are not at the beginning of the word / number. It's not number related as i tought.

Example, if i search for ampli, it will find amplifier. If i search for fier, it won't find amplifier.

Is there something i can do?

[comment]

The default Filemaker search is for words that begin with ... . For other types, you need to include modifying symbols in your search criteria. For example, to find amplifier by the suffix, you'd search for *fier.

For a detailed explanation with examples see Help > Finding, sorting, and replacing data > Finding records > Finding text and characters.

[Showdist]

This works like a charm now.

You were wonderful. (I was novice for not knowing the way search features works, sorry)

I now have a very powerful system that can search!

Seb

[comment]

There is still the question why 37 description fields are necessary. It seems your data is not normalized.

[Showdist]

Well, my system uses many databases (over 30 different ones) to handle quotes, orders, time sheets, inventory, production orders, rentals, fax, messages, product description, suppliers, currency, etc.

The module in question is a quote database, in which each of these 37 fields lookup from the 37 different parts numbers with price, etc. I started this module in FMP6, so there was no multi-table at the time. I needed to do a search in each of these descriptions (who are each for different products).

Thanks,

Seb

Edited February 6, 2008 by Guest

[comment]

I'm afraid you have lost me there. A normalized solution would have separate tables for Quotes, QuoteLineItems and Products. Each quoted item would be related to its parent Quote as well as to the relevant product in Products. The description of a product would be stored in Products and, unless it changes very frequently, nowhere else. This is the standard setup for a relational database, i.e. since FMP version 3 (although each table would have to be a separate file until version 7).

[Showdist]

I might not have explained myself properly.

The product file has 1 description field in it, 1 product number, 1 cost price, 1 us retail price (calculation), 3 rate fields (calculation) and many many more fields.

My quote database has 37 item number fields, who are each related to the product file. each description, price, cost, rates, etc are looked up from the item number in each.

It goes like this:

1- part # / description / quantity / price / cost / additional rebate

2- part # / description / quantity / price / cost / additional rebate

3- part # / description / quantity / price / cost / additional rebate

...

37- part # / description / quantity / price / cost / additional rebate

Does it make sense now?

[comment]

That's how I understood it the first time. Instead of the 6x37 fields (why 37, and not 24 or 52?), there should be a flexible number of related line-item records, each with 6 fields (well, actually 7, since one would be used as a matchfield to the parent quote). Not that I would recommend it, but even 6 repeating fields with 37 repetitions each would be a better arrangement than what you have now (that's how you would do it before version 3).

And I am still not sure why description needs to be looked up at all.

Here is a quote from Help > Working with related tables and files > About relationships:

For example, a typical Sales database may have these tables: an Invoices table, which keeps a record of each invoice; a Products table, which stores the products and their current prices; and a LineItems table, which stores sales data for each line of the invoice, including the item being sold, the quantity, and the price at which it is sold. Because invoices are a mix of dynamic and static data, you use both related fields and lookups to display your data. Records from the LineItems table are displayed dynamically, in a portal on the Invoices layout, but the actual sales price of each line item is entered using a lookup, so the invoice totals remain the same, even if prices change at some future date.

[Showdist]

It seems it could be the case. The problem is that it handles many many functions like profit margins, Euro / Canadian / US funds, inventory checks, per line rebates, and many other functions / information which i don't have the time to rewrite.

I understand that creating a line item file would have been the way to go for this, but when i started this project, i didn't have that knowledge. I've been fine tuning this for 5 years now and it works like a charm.

I might look into converting it to that, but i'd have to rewrite my price script which checks for real time currency from Euro, US and CAD funds, handles shipping costs, spare requirements, inventory and all.

Thanks for your help!