13 Replies Latest reply on Oct 27, 2016 12:59 PM by philmodjunk

    How do i display the results of an equation

    joshuaw07

      I manage enrollments at a private academy

       

      I'm simply trying to display the results of a simple equation which is the total max capacity of enrollments minus current enrollments.

       

      the result of this equation will give me how many available slots i have to enroll before max capacity.

       

      Any quick solutions to this?

        • 1. Re: How do i display the results of an equation
          keywords

          Are the equation inputs from Summary fields? If so, use the GetSummary function in a Calculation field. The result of the calculation is what will be displayed.

          • 2. Re: How do i display the results of an equation
            joshuaw07

            I have a Text Field set up to input the word "Enrolled" in the student's profile. Once a student graduates the Text Field status is changed to "Graduate."

             

            So of all the students ever served I need to pull the students with a profile status of "Enrolled" which is 23.  From there I would want to subtract the maximum number of possible enrollments which is 36. that should result in 13

            • 3. Re: How do i display the results of an equation
              philmodjunk

              You can set up a relationship that matches only to students with "enrolled" in that text field. Count ( RelatedTable::FIeld ) can return that count.

               

              ExecuteSQL can also be used to return a count.

               

              Is this over all students and you are showing remaining capacity for a school or do you also need to limit this by a specific course or scheduled course?

               

              The principals are the same, the details differ.

              • 4. Re: How do i display the results of an equation
                joshuaw07

                I'm tracking with you on the first part. However, could you help me know how to create

                 

                "the relationship that matches only to students with "enrolled" in that text field"

                • 5. Re: How do i display the results of an equation
                  philmodjunk

                  So far, you've only described a single table. So I will assume the existence of a second table: table 2 for this example.

                   

                  Define a calculation field constEnrolled in table 2. Define it to have this expression:

                  "enrolled"

                  select a text result type.

                  Define this relationship:

                  Table 2::constEnrolled = student::status

                   

                  then a calculation field in table 2 can be defined as:

                   

                  count(students::status)

                   

                  will return the number of enrolled students.

                  • 6. Re: How do i display the results of an equation
                    joshuaw07

                    Yes this is the solution.

                     

                    Thank you!!!

                    • 7. Re: How do i display the results of an equation
                      philmodjunk

                      Just keep in mind that it's not the only solution. I mentioned ExecuteSQL before.

                       

                      Other options:

                      Use a global field in place of the calculation field and then set the field to the needed value. This allows you to count different groups of related values by changing the value of the global field.

                       

                      Perform a find on the table specifying the desired value in the find. A summary field or Get ( FoundCount ) can then return the desired count--which can then be copied to a variable or global field to use in your calculation.

                      • 8. Re: How do i display the results of an equation
                        joshuaw07

                        I'm sorry to bother again, I followed your first solution and it worked. However, I then went to check total number of students served since we opened in 2001, which is 267, and it now displays 23 which is the current number of enrolled students.

                         

                        Originally the total number of students served was displayed by creating a portal, filtering results through primary key for student profile, then adding the {{FoundCount}} this counted all profile entries regardless of "enrollment" status.

                         

                        It seems when I connected the relations (as stated above) the found count in the primary key portal is displaying the result of the calculation field constEnrolled in table 2

                        • 9. Re: How do i display the results of an equation
                          philmodjunk

                          I don't see any way for the relationship to change the found count displayed on your layout. Found Count is the number of "found" records for your Layout's table. The number shown should match the value shown in the status tool bar at top left of your screen to the right of the "book control".

                           

                          In order for it to show the total number of records in your database, you would need a found set of all records in the table which can be accomplished by choosing "show all Records" from the records menu.

                          • 10. Re: How do i display the results of an equation
                            joshuaw07

                            This data is displayed on a dashboard. so the show all feature would not show all records on the dashboard layout.

                             

                            I feel like we are close to solving this. Could there be a problem with the dashboard connection. If you had a blank canvas and you were building this solution how would you do it. The end result needs to be a display that only simply displays

                             

                            Total served since 2001 267

                            Total currently enrolled 23

                            Total available slots for enrollment 13

                            • 11. Re: How do i display the results of an equation
                              joshuaw07

                              Here is a screenshot of the relationship connection

                              Primary Key of Dashboard (table z_resources) is not equal to the foreign key in (table Profile)

                              and the new relationship of total enrolled is equal to profile status

                               

                               

                              Screen Shot 2016-10-27 at 2.52.23 PM.png

                              • 12. Re: How do i display the results of an equation
                                joshuaw07

                                I may have figured it out. I separated the relationships out of the same Table occurrences and created a new relationship with new table occurrences.

                                 

                                It seems to be working once i created the two different relationships using two separate pairs of TOs

                                 

                                 

                                Screen Shot 2016-10-27 at 3.08.47 PM.png

                                • 13. Re: How do i display the results of an equation
                                  philmodjunk

                                  Interesting. You just taught me something new. I've always assumed that this layout object does what it says: Display the found count for the layout on which it is placed. But if you place it inside a portal row, it then produces a count of the total records displayed in the portal.