6 Replies Latest reply on Oct 11, 2014 5:31 PM by WillEvans

    Intro and Function Question

    WillEvans

      Title

      Intro and Function Question

      Post

      Hi Everyone,

      First off I just wanted to introduce myself. I am a FM beginner and have recently completed the beginner course on Lynda. It taught me a fair amount, but I feel like the only way I'm really going to learn is to get my hands dirty actually trying to create something. The main reason I'm learning FM is to help a colleague out on our company's FM database. He has built it, it's working well, but I'm hoping to add some features and reduce his FM workload. 

      My first question is about a calculation field. In our database, we have a script that takes the user to particular records in a layout, based on a find function that looks for text in a field where the user's name is written. To perform this script, the user must click on a button. I would like to have a calculation field below this button that gives a number of how many times the user's name is written elsewhere in the database. Basically a number for how many tasks the user has outstanding. 

      What function can I use to display this number? I was thinking a COUNT function but how do I write it so that it looks for particular text within a related field? 

      Any help much appreciated.

        • 1. Re: Intro and Function Question
          philmodjunk

          And what do you do when you get two users with the same name? Even if you get this to work, that will be a problem. Also, people change their names and so what matches by name one month may need to match be a different name the next. You really need a table of users where each user is assigned a UserID value that is not their name and that never ever changes.

          You can then link tasks to the user record by UserID in a relationship and Count (as well as other methods) can then count the number of Task records. But note that my last sentence assumed a great number of details that may not reflect the current design of your tables.

          • 2. Re: Intro and Function Question
            WillEvans

            We only have 6 users and it will never be more than 10. No one changes their name, it's not actually the FM username we use, it's just what they're known as to us. 

            I've experimented with the Count function but how do I get it to look for particular text? It's not the fields I want it to count, it's the instances of a particular name within those fields of text. 

            • 3. Re: Intro and Function Question
              philmodjunk

              You may still find IDs a better option than names. "No one changes their name" seems unlikely in the long term. There can also be issues with mis-entering a name and later needing to correct it--only to find that this creates a mis match and you don't connect to the records you need to connect to.

              I've experimented with the Count function but how do I get it to look for particular text?

              You don't. Either you set up a relationship that matches only to the records you want to count or you use other methods to count the records you want to count.

              Example:

              Users::UserID = Tasks::UserID

              From the User record for "Fred", Count ( Tasks::UserID ) will count all Tasks records linked to Fred's users record. But it won't discriminate between different Tasks linked to Fred--that would require either a different relationship or a different approach.

              And there are quite a few different ways that you can count specific groups of records in FileMaker.

              • 4. Re: Intro and Function Question
                WillEvans

                So what you're saying is that I need to have a separate field that only contains the UserID/name (and no other text) within my Tasks Table, if I want to count the number of records in which the user's UserID/Name is appearing? 

                • 5. Re: Intro and Function Question
                  philmodjunk

                  That would be one approach.

                  A summary report based on the Tasks table could also be used to count records linked to each user.

                  • 6. Re: Intro and Function Question
                    WillEvans

                    Thanks PMJ, I think I'm going to come back to solving the task when I have a better understanding of FM.