Jump to content

Displaying a total from a related Field

Recommended Posts

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)


Link to post
Share on other sites
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

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
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 by comment
Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.