6 Replies Latest reply on Jan 24, 2012 8:21 AM by Mike_Mitchell

    Can you subcategorise searches?


      Hey FileMakers,


      I am starting to build a database for musicians on an agencies database. I want to have a searchable field called 'genre' but I for some musicians they play more then one type of genre. How would you put more then one bit of information into one searchable field. I.e under the 'Genre' colum i want to put jazz, classical and soul...so if I type in any of these they come up.


      Thank you!!

        • 1. Re: Can you subcategorise searches?

          Hello, youngguns (I'd address you by name, but I don't know what it is).


          Well, one way to do this is have the user separate their entries with a delimiter, say, a comma. Then have your script parse out the bits between the delimiters and enter them as separate search requests.


          Another way is to treat each word as a separate search request. Basically, this is the same as option 1, except you treat a space as a delimiter.


          Or, you could use a checkbox selection set if you have a set number of possibilities. Then, your user's selections come in as a return-delimited list, and you can use the GetValue function to parse them out.





          • 2. Re: Can you subcategorise searches?

            Are you familiar with the OR request in FileMaker:



            Although, what Mike says is useful as well, and could be easiest to work with once it is set up.


            Are you searching to enter data or is the data already in the field? Just curious


            That would then go into a different direction.

            • 3. Re: Can you subcategorise searches?

              It looks like your database is not normalized. What you need to create is a separate genre table.

              • 4. Re: Can you subcategorise searches?

                Well, I guess we should back up. I had assumed he wanted a single search field to be able to search multiple criteria (such as on a web form), but you may be right.


                youngguns - if you're storing multiple genres in a single field, you should probably separate them out as Martin suggests into a separate table and connect them using a relationship.



                • 5. Re: Can you subcategorise searches?

                  Hi Mike,


                  Thank you - this is correct. I want one field to be able to hold more then one piece of information.


                  So basically, if I wanted Joe Bloggs to be have Jazz and Classical in the 'Genre' collum I would do this:


                  Jazz, Classical


                  Would this mean that when I search genre Joe Bloggs would come up under classical and jazz?


                  Thank you!!

                  • 6. Re: Can you subcategorise searches?

                    youngguns -


                    The answer to the question is yes - if you do a search with multiple finds (such as "classical" and "jazz") and both of those pieces of information are in a single field, you will get that record.


                    However ...


                    As Martin pointed out, this is a violation of basic database normalization. The normalization rule is casually referred to as "one fact, one field". Combining multiple facts in a single field creates problems with proper data isolation.


                    Presumably, you have what's known as a "many to many" relationship here. In other words, each musician can be associated with many genres, and each genre can be associated with many musicians. Hence, in order to normalize this properly, you have a couple of choices:


                    1) You can create a multi-key relationship between them. This has some advantages, but requires a good understanding of how to handle return-delimited lists.

                    2) You can create a join table, where each record represents each combination of musician and genre. For example, you have one record for "Joe Bloggs + Classical", one for "Joe Bloggs + Jazz", and maybe another for "Young Guns + Classical" and "Young Guns + Rock".


                    I suggest downloading the FileMaker Training Series and going through the sections on database normalization so you can get a good grounding in these principles.