Multiple fields search fails [ Edited ]

[StuartMcMinigal]

Morning all from the UK,

Below is a screenshot from one of my tabs within my sales area. I'll explain a little about the function of it then pose my area of error/help. Within the salon field each contact may have 1 to 6 different pieces of equipment, each each drop down is the same for equipment, treatments and product range's dependant on which they have. So there can be more than one of the same equipment, or treatment or product range.

My question is with this form filled in there is an error when doing a 'look up by example' with several of the fields and it pulls nothing up.

Should I have gone about doing this differently in the designing?

Designed like -

Equipment 1     Year Acquired Equipment 1     Equiment 1 Upgrade (Yes/No)             Salon Treatment 1      Product Range 1

Equipment 2     Year Acquired Equipment 2     Equiment 2 Upgrade (Yes/No)             Salon Treatment 2      Product Range 2

Equipment 3     Year Acquired Equipment 3     Equiment 3 Upgrade (Yes/No)             Salon Treatment 3      Product Range 3

through to 6

[ghollister]

Hello Stuart,
What is the error message? What version of ACT! are you using?

[StuartMcMinigal]

The error is simply that no records were found even though there are around 50 records with a mixture of different inputs so there should be some return in a way.

[ghollister]

What version of ACT! are you using?

[StuartMcMinigal]

2012 premium version

[dixon7]

You might try doing a lookup by example Lookup/Lookup By Example from the menu.  I think custom tables would work much better for you.  There are a couple of good ones out there and an ACT! Certified Consultant can help make it easier for you.  Brenda

[Roy_Laudenslager]

There are two issues that I see here. The lookup by example won't work where you are trying to use it to find information that might be in one of several different fields. You should be able to use the advanced lookup however trying to construct a workable query wouldn't be easy.

Your main problem is that you're trying to solve a one-to-many data structure by adding fields to a flat file table. The best solution would be to use a custom table designed for one-to-many entries. Each set of equipment fields would then be a line in the custom table. The ACT! program has the capability of adding custom tables to a database but you need a plugin to provide the user interface to design and mange the custom table. Because each of your equipment entries in the cusome table would be separate records you would be able to to a lookup across all the equpment entries easily. You should check out the plugins available from Durkin Computing or Topline Designer.