Displaying a total from a related Field

[Chrism]

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!

[Wim Decorte]

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.

 

[Chrism]

Don't worry sorted!

[Wim Decorte]

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?

[Chrism]

Yep no problem, calculation field as below

 

ValueCount ( FilterValues ( List ( table::field ) ;  "600"  ) )

[Chrism]

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

[Wim Decorte]

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.

 

[comment]

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

[Chrism]

Perfect thank you