4 Replies Latest reply on Mar 7, 2014 2:13 PM by MarkObbie

    Multiple values in one text field?



      Multiple values in one text field?


           New user. Reading and watching all the tutorials, but I'm in way over my head and struggling to understand just a fraction of what this application can do. 

           Here's my immediate issue:

           I imported an Excel file and the fields and data all appear fine (although with tons of blank cells that I don't know how to clean up). I'm using text fields to list people by name, affiliation, and -- most important -- fields of expertise. That is likely how I'll search, so that all experts on a given topic appear in a list when I run a "find" query in the topics field. Most of the people I list have more than one topic of expertise. I do not understand how to list their multiple topicsl in separate entries so that they appear in a "find" query of the topics field for any one of their topics. 

           I thought about starting over using a contacts template in starter solutions, but that would be a ton of data re-entry and the converted Excel list is almost there, but for this one glitch (my need for multiple values, or entries, under one field called topics). 

           Sorry if that's not clear. If I could explain it more clearly I probably would be able to understand the help files and tutorials!

        • 1. Re: Multiple values in one text field?

               It is easier than you think.  FMP indexes every word in each field, so it you have  "janitor plumber electrician ditch digger tire changer" in your expertise field, do a find for any of those words and FMP will find it.  there is a small down side in that ditch digger and grave digger each will show up if you find on digger.

               Add to that finds aren't case sensitive and you don't have to enter the whole word.  In the case of first and last name fields, if you use the first couple of letters in the last name and first name you will probably really narrow it down for yourself.  If you aren't sure of the spelling just use the first few letters that you are sure of.  Lastly if you have multiple possibilities do a find one term (like plumber), hit Ctrl-N (new find request if you are in find mode) and enter in the same field enter "handyman".  Now you will get those records that either word in the field.


               Hope this helps

          • 2. Re: Multiple values in one text field?

                 Thank you, that is very helpful. It's that small down side I was trying to fix, but maybe it's not that big a drawback. Due to the peculiarities of my list, I had hoped that the many people listed in multiple, overlapping categories could be winnowed by focusing only on one category. But I can live with it as is. It's already a heck of a lot better for searching that trying to run sorts in Excel. Thanks again. 

            • 3. Re: Multiple values in one text field?

                   While it's possible to put all of that in one field. It is often a much better design that is much easier to work with if you set up a related table where you list one such job skill in each related record. The same basic find logic works with the data in a related table also, you can specify the name of a job skill in the field of that related table and you'll get all records in your main table that link to at least one related record matching that criteria.

              • 4. Re: Multiple values in one text field?

                     I think if I read that 100 times I might start to get it. laugh I think I'll set up a copy so I can play with it without messing up the permanent one forever. Thank you