5 Replies Latest reply on Dec 4, 2010 4:25 PM by FentonJones

    Searching Based on a Multi-Line Field, and, Saving User Settings



      Searching Based on a Multi-Line Field, and, Saving User Settings


      Hello everyone,

      First, I would like to say that although this is my first post, I've been reading threads here for the last several months. I've learned an enormous amount from reading the advice of posters here, and I am very grateful for this online resource.

      I have two related issues/questions that I've been working for weeks to figure out. I would be very grateful for any advice you would be willing to offer me:

      Issue 1: I have a global, multi-line text field that I would like to use to store a temporary list of names of companies based on user input. When a user clicks on a company name to mark it for later viewing, a script runs that copies the name into the global text field (without deleting any names that have been previously copied there). What I can't figure out is this: I would like to be able to perform a Find for which the results are any records with a Company Name that is listed in the global field. I realize that I could make a script go through each company record one-by-one and Omit it if it matches and then Show Omitted Records Only. I'm hoping, however, that there's some simpler way, using a basic Find or something like it, to accomplish this. The problem is getting FileMaker to recognize that I'm not looking for records that match the /entire/ global text field -- rather, I'm looking for records that match /any one line/ of the text field.

      Issue 2: While I'm interested in Issue 1 above out of curiosity as well as utility at this point, the point of Issue 1 above is to allow users to save settings in a multi-user environment. If I could have a record in a seperate Users table for each user, I could, it seems, allow users, e.g., to flag records for their own later viewing/printing by using text fields (which wouldn't be global, as above, in this case) to store user settings. Is there a better way in FileMaker to accomplish this same end?

      Thank you very much for your help! I'll be very appreciative of your feedback!


      FileMaker Pro 11, Windows XP

        • 1. Re: Searching Based on a Multi-Line Field, and, Saving User Settings

          Use a relationship. A multi-line match to a table automatically matches each line exactly. Use IDs, not names. If people want to see the name, create a portal based on the relationship, and show the names (non-enterable) in the portal. The portal can also be used for removing a company from the list and/or navigation to each company (in the portal) and/or all companies in the portal.

          Yes, a Users table is one way to store the multi-line companies, in a regular text field (or a little join table, which makes it easier to maintain). This will survive multiple sessions (which a global field won't). So it is not temporary.

          Any functionality "per user" implies that you know WHO the person is, which means they must have their own real Account to log in to the database(s). That can also be in their record in the User table. "Who" can be evaluated anyplace/anytime via the Get (AccountName) function (storage "Do not store" if calculation field).

          Another method to do more or less the same thing is to store the "account name" of the logged in user in a multi-line text field in the Company table (or in the previously mentioned join table). These all work from either Company or User, or via Get (AccountName) from anywhere). Much the same result, but from another perspective.

          • 2. Re: Searching Based on a Multi-Line Field, and, Saving User Settings

            Thank you very much! 

            Your answer makes sense to me. As you mentioned, each user will have his/her own account to log in to the database. I hadn't thought of reversing the user storage settings idea to list user account names in the Company records, as you suggest at the end of your reply. I'll do just that, and then will be able both to search through the Company table by account name and to use a portal in the user account layout to display all related companies. That solves my questions nicely. I'm grateful!

            Out of curiosity, I would like to ask a bit more about how I would go about setting up a search if I set things up the other way, with company names being stored in a multi-line field in the Users table. I'm comfortable setting up the relationship between Company ID and the multi-line field in the Users table, but still don't understand how to calculate the search/find itself without using a portal. Is it possible to be in the Companies table layout and run a Find (i.e., without using a portal), using as the search string the multi-line field in the record in the Users table that matches Get(AccountName)?

            Again, thank you!


            • 3. Re: Searching Based on a Multi-Line Field, and, Saving User Settings

              1. Don't use names, use IDs

              2. Don't use Find, use Go To Related Record [ relationship; show only related; layout ]

              You need to learn how to use relationships and Go To Related Record. They are basic to relational operations; which this is.

              Basically, set up a relationship with your multi-line text field (or join table) pointing to the target ID field.

              • 4. Re: Searching Based on a Multi-Line Field, and, Saving User Settings

                Wow, I had never even come across Go To Related Record before. Mr. Jones, thank you very much! It's exciting to have this new tool in my toolbelt. I've got a working demo of what you recommended up and running now.

                Again, thank you!

                My best,


                • 5. Re: Searching Based on a Multi-Line Field, and, Saving User Settings

                  You're welcome. Relationships and Go To Related Records are about half of FileMaker. They work especially well with multi-line anything (which includes checkbox values, which are really just multi-line text fields).