Use the Find function to search across all fields in all tables

[sfilemaker2023]

Is there a way to "find" across all tables in FM? I have an art database which has a welcome page, leading to 3 tables- painting, sculpture and furniture. 

But some items in these three areas are made by one artist- eg. Frank K may be the artist for a sculpture and a painting.

So on the welcome page, I'd like to have a universal find box and you could click in and type, to search for "Frank K" and find his material in the sculpture table and in the painting table.

Is this possible?

Thanks.

[rwoods]

Morning

This comes back to the original database design I think, although you could do what you are trying to do in a convoluted way.

Perhaps consider having all your art pieces (paintings, sculptures and furniture) in a single table? They probably share many common traits (artist, photo, date manufactured, value etc), and you could have a single field (maybe called 'ArtWorkType') that identifies what type of artwork it is (choosing from painting, sculpture, furniture). This way you could just search a single field (artist), and return all the pieces that match.

By having them in separate tables you are creating extra work, since you will need to create a script that performs three separate searches, and then perhaps collates the key data into a new table of 'results', which you could display and allow the user to select the one they are interested in, which you could then show in a different layout.

Or you could have three portals on a layout showing your three tables, and then use portal filtering, based on your search field, to show only the results that match in three different portals.

I would suggest that standardising your three art tables into a single table would make life a lot easier in the long run. For instance, if you decide to also list 'tapestry' as a new type of artwork that your artists create, you don't want to have to create a fourth table, with all the complication that would bring. It would be easier just to create an extra option in the 'ArtWorkType' field.

I'm sure others will have other ideas, but that is what I would do.

[comment]

31 minutes ago, sfilemaker2023 said:

search for "Frank K" and find his material in the sculpture table and in the painting table.

Is this possible?

It's possible to do a find in all 3 tables - but it is not possible to show the results of more than one table at the same time*.

I believe you should have 2 tables: Artists and Works, related by ArtistID. This way you can search for an artist in the Artists table and see all their works in a portal to the Works table. Or you could search in the Works table (by querying the field/s in the related Artists table) and get the result as a list of all their works.

---
(*) Well, maybe using multiple windows ... But that's not something I would recommend.

 

Edited September 15, 2023 by comment

[sfilemaker2023]

thanks this is very helpful and I've done that now.