6 Replies Latest reply on May 25, 2010 9:33 AM by jared67

    counting records?

    jared67

      Title

      counting records?

      Post

      I'm trying to count portal rows, or related records from an outside table. I haven't had much success.

       

      I have a "tasks" table visible through a portal inside a "project" record/table. I'd like to be able to count the tasks I have associated for further calculations. FM can clearly count the related records, however I have yet to find a solution for sending that number to a variable automatically.

       

      I'm using FM 8.5 Any pointers would be much appreciated.

        • 1. Re: counting records?
          fitch

          When you say "FM can clearly count the related records," I assume you have used the Count() function. I'm not sure what you mean by "sending that number to a variable automatically," though. Set Variable is a script step. You want to use a script trigger, perhaps? It would help to know what your actual goal is. Are the "further calculations" happening in a script?

          • 2. Re: counting records?
            jared67

            I didn't know about the count() function. I may have to read more on the syntax. FM can count the records showing [number] found and total [number] in the main GUI. I was hoping to grab that number as a variable to play with in a script.

             

            My ambition: equalize all the tasks (respective to their project) so they would all "inter-weave" in a global view of the tasks table.

             

            If I had Project1 with 3 tasks, I'd like to find the number of tasks then divide 100 by that number (33 here) and assign each task a priority, 33 steps away from the other (on a scale of 100). Task1,33|Task2,66|Task3,99... It was my hope that Project 2, (which could have 25 tasks) would be equalized against Project1 in a combined view.

             

            I'm trying to re-weigh each tasks' priority, automatically.

            • 3. Re: counting records?
              fitch

              That's fairly straightforward to script, using Set Variable to get the Count of the related records, and then you'd use a Loop to go to each portal row and set the number.

               

              Let me know if you get stuck.

              • 4. Re: counting records?
                jared67

                I'm running into one hitch. I'm trying to count just the "live" tasks. If I have 4 tasks linked to a project, and 3 of them are complete, the count still return 4, (when I feed this script to FM).

                 

                Set Variable [$rowCount; Value: If (Tasks 2::CompletedTask  = 1 ; 0; Count ( Tasks 2::zID ))]

                Show Custom Dialog [$rowcount]

                 

                 

                I tried case as well and couldn't produce the expected result.

                • 5. Re: counting records?
                  fitch

                  I was going to suggest this:

                  ValueCount( FilterValues( List( Tasks 2::CompletedTask ) ; 1 ) )

                  (Credit to comment for reminding me about this technique.)

                   

                  However, I didn't realize you were counting instances of "1."

                  If only completed tasks are set to 1, then I would expect Sum( Tasks 2::CompletedTask ) to do the job.

                  • 6. Re: counting records?
                    jared67

                    Thanks for all your help, Fitch. I really appreciate it.