Chrism 0 Posted December 17, 2020 Share Posted December 17, 2020 I have a table with a course total (£600, or £1000) in each record. I am looking at this table via a relationship, I what I need to do is count how many £600's there are and how many £1000's there are (not total, but display number so if there were 6 values of £600 in the related records it would display 6) Thanks! Link to post Share on other sites
Wim Decorte 514 Posted December 17, 2020 Share Posted December 17, 2020 13 minutes ago, Chrism said: I have a table with a course total (£600, or £1000) in each record. I am looking at this table via a relationship If you want to run a query on the whole courses table then your starting context doesn't matter, except that it may complicate things a bit in displaying the result. There are a couple of different ways to do this, one way is to run an executeSQL() query on the courses table that does a COUNT(*) and GROUP BY on the course total field. The result of this will be a return delimited chunk of text. You can show that in a global text field or go the extra mile and use a Virtual Table to display the result. If you are not familiar with SQL then this is a nice exercise. You can also display the results in a web viewer using a nice data visualization library. You can also just spawn a new window and loop through your course records (actually visiting each one or with the GetNthRecor() function) and keep a running tally per course total. Many options available to you. If you have tried one or two but got stuck, let us know what you have done already and we can see if we can get you unstuck. Link to post Share on other sites
Chrism 0 Posted December 17, 2020 Author Share Posted December 17, 2020 Don't worry sorted! Link to post Share on other sites
Wim Decorte 514 Posted December 17, 2020 Share Posted December 17, 2020 In the interest of contributing to the community, maybe post how you ended up doing it so that the next person can learn from it? Link to post Share on other sites
Chrism 0 Posted December 17, 2020 Author Share Posted December 17, 2020 Yep no problem, calculation field as below ValueCount ( FilterValues ( List ( table::field ) ; "600" ) ) Link to post Share on other sites
Chrism 0 Posted December 17, 2020 Author Share Posted December 17, 2020 However I'd also like to count of possible if value is not £600. Can I adjust this calculation to be a 'Not' in someway? Thanks Link to post Share on other sites
Wim Decorte 514 Posted December 17, 2020 Share Posted December 17, 2020 Look for a custom function by the name "antiFilterValues" or roll your own with the While() function to iterate over the list. But in general: using fields for this kind of thing is perhaps not the way to go. You don't want to be adding a field every time you have a course with a different price point. Link to post Share on other sites
comment 1,779 Posted December 17, 2020 Share Posted December 17, 2020 (edited) 1 hour ago, Chrism said: However I'd also like to count of possible if value is not £600. Can I adjust this calculation to be a 'Not' in someway? How about: Count ( table::field ) - ValueCount ( FilterValues ( List ( table::field ) ; "600" ) ) Note that this counts all non-empty values that are not "600". If you want to include empty values in the count, start by counting a field that cannot be empty - e.g. the match field for the relationship. Edited December 17, 2020 by comment Link to post Share on other sites
Chrism 0 Posted December 17, 2020 Author Share Posted December 17, 2020 Perfect thank you Link to post Share on other sites
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now