13 Replies Latest reply on Jun 28, 2011 1:38 PM by philmodjunk

    Calculating/Counting a found set

    MikeEdwards

      Title

      Calculating/Counting a found set

      Post

      I am looking to use a calculation field to display a count of a found set in a different script driven layout.

      The other layout script includes records that have a Est. status of "Comeplete" and a Job status of "In Process"

      Which calculation formula is best suited for this?

        • 1. Re: Calculating/Counting a found set
          philmodjunk

          Do you want a count of all records in the found set? Get ( FoundCount ) in an unstored calculation filed can do that.

          Do you want a count of all records in the found set with an Est. status of "Complete" and a count of all those that are "In Process"?

          If so, is that status text in the same field? (You refer to Est. status for "complete" and to Job status for "in process")

          If there is a single status field here, are the records in this found set sorted by the status field so that all "complete" records are in one group and all "in process records are in another"?

          There are several ways to use calculation fields, summary fields and/or a related table to compute counts in this latter case, but the best approach depends on the nature of your found set.

          • 2. Re: Calculating/Counting a found set
            MikeEdwards

            This is a count of records found with two seperate field criterea. Two different types of status.

            • 3. Re: Calculating/Counting a found set
              philmodjunk

              Then Get ( FoundCount ) will return the number of records in your found set. Just do not use this in an auto-enter calculation or a calculation that is stored or changes to your found set will not update this count.

              • 4. Re: Calculating/Counting a found set
                MikeEdwards

                I do not see  Get (found count) as an available calcuation in FM 11

                • 6. Re: Calculating/Counting a found set
                  MikeEdwards

                  Wow how might that formula go?

                  • 7. Re: Calculating/Counting a found set
                    philmodjunk

                    Get ( FoundCount ) is the only formula you need if all you want is the count of records in your current found set.

                    • 8. Re: Calculating/Counting a found set
                      MikeEdwards

                      No this is not the current found set. I am lookinf to display a found set from another layout/script.

                      • 9. Re: Calculating/Counting a found set
                        philmodjunk

                        Please describe what you want in more detail.

                        • 10. Re: Calculating/Counting a found set
                          MikeEdwards

                          Rather than looking at this as a different layout. I need to find all the records which meet the criterea of to two seperate status fields. Est. Status = "Complete" and Job Status = "In Process"

                          This is easy when runing a script however it doesn't seem that you can run a script for an individual field.

                          • 11. Re: Calculating/Counting a found set
                            philmodjunk

                            You could certainly run a script that changes layouts, finds the records, uses Get (FoundCount) to capture the number of found records and then returns to report the value--putting the count into a field or variable on your layout if that's what you want.

                            If it's always the same criteria, you could also set up a relationship that only matches to records in this table when Est. Status = "Complete" AND Job Status = "In Process". Then a calculation field that uses Count() can return the number of records.

                            That's why I'm asking for more information here. If the layout where you want to see this count is based on a different table, that has to be taken into account with the script or calculation. If the record count isn't always based on the specified criteria, but can vary---that has to be taken in account.

                            Try backing up and describing what you want to happen in terms of your interface and then be sure to identify the tables and relationships that are involved here. (ie. "I have this layout and when I do this with it, I want to see this appear in this way on my layout...")

                            • 12. Re: Calculating/Counting a found set
                              MikeEdwards

                              I like the second option

                              If it's always the same criteria, you could also set up a relationship that only matches to records in this table when Est. Status = "Complete" AND Job Status = "In Process". Then a calculation field that uses Count() can return the number of records.

                              as the criterea for this field will never change and it works within this table

                              What would the count formula look like if the result must include the critera of both fields to be valid.

                              • 13. Re: Calculating/Counting a found set
                                philmodjunk

                                You haven't indicated the name of the tables on which your two layouts are based and that would help me specify the details here.

                                I'll name your two tables: Layout, Jobs. (This could even be just one table here, used in a self-join)

                                In Layout, define two calculation fields: constComplete, constInProcess. Set them both up to return text as their result type and enter "Complete" in the first and "In Process" in the second.

                                Now define this relationship:

                                Layout::constComplete = Jobs::Est. Status AND
                                Layout::constInProcess = Jobs::Job Status

                                Est. Status and Job Status must be indexed fields for this to work.

                                Define a calculation field, cCount in Layout as: Count ( Jobs::Est. Status )  to return the record count. (Any field in Jobs that is never empty can be used where I used Est. Status in this count function.)