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.
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?
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.
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.
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.
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.
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.
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?
1 of 1 people found this helpful
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.
That's got it working! Thanks so much for your help!