10 Replies Latest reply on May 18, 2017 9:04 AM by SamOsman

    Total count of records show on 'dashboard' layout

    SamOsman

      Hi, I've just created some scripts that are ran from our 'dashboard', so they allow all our job sheets to be assigned to a 'queue' from a drop-down list, then I run a script to perform a find which shows all the job sheets that have a been assigned to that particular queue. This works great.

       

      However, I now want to show a total on the dashboard next to each of the buttons used to run the scripts. So an example would be a queue called 'to invoice' would have a number next to it, to show how many records currently have 'to invoice' selected within the 'queue' field on the record.

       

      I've tried to do this with a calculation field and a summary field, however, it's not giving the correct results.

       

       

      Queue = "to invoice" (calculation field in the table of job sheets)

      Total of the above field (as a summary field) - which is then shown on dashboard

       

      So this would allow you to see at a glance how many job sheets are in a queue without the need to click the button and run the script.

        • 1. Re: Total count of records show on 'dashboard' layout
          philmodjunk

          The script that puts the records into the queue could also count the records and assign the count to field or variable.

           

          But calulations or a summary field can also give you these counts if they are set up correctly. A summary field counting your records can work if you use a relationship that matches to the related records or if you put it in a one row filtered portal where the combination of filter and relationship control what records are counted. 

          • 2. Re: Total count of records show on 'dashboard' layout
            SamOsman

            Hi Phil,

             

            thanks for your reply. I've got 1 script, which I'm using a script variable to state which queue to display. And I'd like to be able to show the count on the dashboard without needing to press the button to view the queue. Is this possible? Or could I write a second script to do this?

            • 3. Re: Total count of records show on 'dashboard' layout
              philmodjunk

              You can add code to your existing script to get a count and set a field or variable to it. A calculation field or a summary field could also provide the count. The pro to using a script is that the layout may display quicker each time you switch to it as there are no aggregate values to calculate. The con is that changes made to your data after you ran the script will not update your count.

              • 4. Re: Total count of records show on 'dashboard' layout
                SamOsman

                I think I want to go down the calculated route, as I want this count to always be up to date, even if the script hasn't been run.

                 

                After some more Googling and playing around I've got this: Case ( Queue = "Quotes sent" ; 1 ) - Which I was hoping it would count 1 for every time 'Quotes sent' appeared in the Queue field. Then I've got a summary field to count all the 1's.

                 

                But this still isn't working.

                 

                I need to be able to count the amount of times "Quotes sent" appears in the queue field, and then show this on another layout.

                • 5. Re: Total count of records show on 'dashboard' layout
                  philmodjunk

                  And what relationship did you set up?

                   

                  "It didn't work" by the way, doesn't give me much to work on. Describing HOW it doesn't work might provide a clue.

                   

                  I'm going to name table occurrences after your layouts as those are all that I have to work with.

                   

                  If you define your calculation field to have a result type of number and define the summary field to be a "count of" or "total of" summary field, then this relationship should allow you to get the correct count on your dashboard by putting the summary field directly on your dashboard layout:

                   

                  dashboard::anyfield x job sheets::anyfield

                   

                  make sure that you have at least one record in dashboard.

                  • 6. Re: Total count of records show on 'dashboard' layout
                    SamOsman

                    Hi Phil,

                     

                    Apologies, let me try and explain this a little clearer.

                     

                    I have a table called 'job sheets' which contains all the job sheets, each has a field called 'queue' which is filled out using a drop down variable list, which puts that job sheet into a queue.

                     

                    I then have buttons on the dashboard layout, which is based on the 'customers' table, to run a script which finds all job sheets that have the 'queue' field set to each of the variables. This works great. I now want to show a number against each button that shows have many job sheets have that particular variable selected in the 'queue' field.

                     

                    To achieve this I've created 2 fields in the 'Job sheets' table as follows:

                     

                    queue_quotesent (Calculation)      Case ( Queue = "Quotes sent" ; 1 )

                    co_queue_quotessent (Summary)      = Total of queue_quotesent

                     

                    The relationship between Job sheets and customers is set to = and is linked by Customer ID

                     

                    I hope this explains it a little better.

                    • 7. Re: Total count of records show on 'dashboard' layout
                      philmodjunk

                      It doesn't really change my last post. From what you describe, if you place your summary field from JobSheets on your customers based layout, it should display the count of all records for that customer that have the value of "Quotes Sent" in that field.

                       

                      If you are not getting the correct count when you do this, then either your relationship, your calculation, or your data is not what you think it is. I am assuming, BTW, that your queue_quotesent field is a field of type "calculation" and has a result type of "number". Make sure that's the case.

                       

                      You could use a number field with an auto-enter calculation for this field, but then you may have issues with records that haven't updated as they should to show the right value.

                      • 8. Re: Total count of records show on 'dashboard' layout
                        SamOsman

                        Hi Phil,

                         

                        That's my problem. As you've just said it'll give me a total count of all the records with the value 'Quotes sent' for that customer, and as I cycle through the records this number changes. I need it to be a total of all customers records with queue value of 'quotes sent'. The correct count of 3 is shown on the job sheet layout, just not customer.

                         

                        So at the moment.

                         

                        Customer A  - 2 records with queue set as 'quotes sent' (So 2 is shown in co_queue_quotessent)

                        Customer B  - 1 record with queue set as 'quotes sent' (So 1 is shown in co_queue_quotessent)

                         

                        I need co_queue_quotessent to show the total (3) instead.

                         

                        How can I do this?

                        • 9. Re: Total count of records show on 'dashboard' layout
                          philmodjunk

                          Then you need the relationship that I previously specified that matches to all records.

                          Assuming that you need the customers to Jobsheets relationship to match by customer ID for other uses, you can add a new occurrence of JobSheets to your relationship graph and link it using X instead of =, then add the summary field from that occurrence instead of the original.

                           

                          If you don't need the relationship to match by customer ID for other purposes, double click the relationship line and change the operator to X.

                          1 of 1 people found this helpful
                          • 10. Re: Total count of records show on 'dashboard' layout
                            SamOsman

                            That's got it working! Thanks so much for your help!