Searching Multiple Fields - Help Needed

[Species8472]

Hi, I have a problem I can not resolve and would appreciate it if someone could point me in the right direction. and or if possible explain in some details.

My efforts at searching this forum to find the answers have failed despite several hours of reading posts.

My problem is 2 fold.

Part 1

I have a simple table with 10 fields the fields all contain part numbers.

each record relates to a single item so one item can have up to 10 different part numbers.

I need to be able to do a find (search) on all 10 fields for one of those part numbers which could be located in any one of 10 fields. (using a single field as the search parameters.

Part 2

I need use that result it will bring back (A single record most likely) holding anywhere between 1 and 10 fields. I need to use that result to search a single field in a different table.

The reason I say between 1 and 10 is because not all items get issued with 10 part numbers some might only have 1 or 2 part numbers but could have up to 10.

Thanks in advance for any help. Keep in mind I'm a bit of a novice but will spend hours trying things to make them work. and have done same with no success to date.

Regards Keith

[Oldsneekers]

Make a FindAllCalc field. Assuming your part number fields are PartNumA, PartNumB, PartNumC... The cacl field is:

PartNumA & " " & PartNumB & " " & PartNumC... PartNumJ

Thus doing the search in the FindAllCalc will yield the record with one of the matching part numbers.

If instead of " " you use "PP" (PP=paragraph mark) you can use this field as a match field for a portal that will display beside your queery field. (the relationship would be queery field to FindAllCalc. With a relationship you won't have to execute a search, as the portal will show all matching records. On the portal row you could then place a button to use the record information for your other table.

Good luck, "mate"

[Species8472]

Thanks for the info, I had worked out part one my self as no one had been around, but used the " " marks but was still stumped with part 2 will modify my calculation, and see if I can work out your instructions for part 2. Again thanks. Might get back to u if I get into trouble as I had no call to use portals up till now so something new to try.

[Species8472]

Hi with reference to part 1 of my Q.

I must be doing something wrong because the calculation field is only bringing back the first fields data now that I have replaced the space " " with the "¶" paragraph mark.

It seems without that space the calculation does not want to work what am I wrong please ?

I have attached the simple database I will be using as an external database talking to the main database. (As its sometimes hard to describe and a pic or working model is worth a thousand words.)

Of note:

As this database will not be part of the main database, will that affect your solution to part 2 ?.

Database (external)

[sbg2]

expand the FindAll calc field to be 6 or 7 rows and you will see all the values. Since you are using reutrns ("¶") between the values you get a list as such:

AC270

AC270

BCX062

If the field on your layout only shows one row you can't see the other values.

[Tyrant]

easiest way i found to do such a search would be to script it

from Find with the fields in list view. say if you wanted to search the field using temp1 as a global variable

Enter find mode (if not already)

Set Field:A = Temp1

New Record/Request

Set Field:B = Temp1

New Record/Request

Set Field:C = Temp1

New Record/Request

Set Field:D = Temp1

etc etc.....

perform find

whatever you wanted to do with the results i would make another script and attach it to a button next to the fields in list view

[Ender]

Hi Keith,

The situation of having between 1 and 10 items per record, is a good example of a place where a portal is especially suited. You would create a table called Installed_Parts or something, with fields Part# & JobID (or whatever key matches your main table there.) The portal is then based on this Installed_Parts table, and can have up to 10 rows filled in. A search on a field in this portal finds the records where any of the Installed_Parts match. If you needed to do something more based on the results (as in Part 2 of your request,) you may be able to simply jump through a chain of relationships using a Go to Related Records [] script step.

Another possible benefit of a relational structure, is if you have a Parts table with the parts you typically use for a job. The Parts table would be related directly to the Installed_Parts table, making Installed_Parts a join table. It's then very easy to see all the jobs where a part is used (viewing a portal of Installed_Parts from a layout based on Parts.)