Search across several tables

[jhomer]

I have a data base containing several tables. Each table represents hardware on our network, clients, servers, printers etc. Each machine is assigned a unique asset code. I would like to be able to define a search where an admin can come into a layout, enter an asset code and find the related hardware. this requires me to search on a single criteria across multiple tables.

How is the best way to go about this? I was going to create a layout with one search box (for the asset id) and then several portals into each of the tables?

Any ideas as to this working and or any better way to go about this.

as always thanks in advance for any help

[Fenton]

My first reaction is: why separate tables? All you'd need is to add a label for each item and keep them all in the same table. The best "unique asset code" would be an auto-enter FileMaker serial ID, which they could get automatically if they were in the same table.

Are there so many unique fields involved for each different type of equipment? The advantages of one table are many. For example: find all equipment purchased this year, find all equipment > 5 years old, etc..

But, even if they are in separate tables, and the asset code is unique, as you say, then the item could only be matched in 1 table, and only 1 record would be matched.

If you typed an asset code into a global field, with a relationship from it to the asset code in each of the tables, and you had a row of related fields* from each relationship on the layout, the item would immediately show up in one of the rows.

You could "layer" them on top of each, with transparent fill, since only 1 would (should) have data showing.

*Normally one would think "portals," but portals are only needed if you want to see more than 1 row of related fields. If you had just the related fields, without the portal, you'd have the 1st "row" (record); in this case that's fine, if the asset codes really are unique. In this case no portal is needed, nor wanted, as you can't easily "layer" portals (well, you can if you offset them, but it's a pain).

[Ender]

As Fenton suggests, this type of thing is easier when they are combined into one table.

I have an asset database that holds various types of network equipment, computers, and printers. These are all kept in the same table, with an "Equipment Type" field to distingush between the different types.

With this design, the search is a simple Find in the one table, showing the results in a list. If necessary, different detail layouts could be used for different equipment types, with the navigation scripts branching to the correct one.

If you need to group assets together (like the peripherials that are attached to a computer,) this can be easily done with a self-join.