Valuecount with multiple criteria

Questions
,
1

I've got some calculation fields which use the ValueCount( FilterValues( List( Table::Field1); "ValueToSearch")) calculation to perform an Excel-like CountIf operation, but I'd like to have a calc field which can perform a value count based on the contents of 2 fields to allow some conditionality, ie count where

Table::Field1 = "ValueToSearch" and

Table::Field2 = "AnotherValue"

I have TO linking two two tables based on a different field, and within that relationship, I want to count the records in a child table that meet two criteria.

Suggestions?

2

How about an SQLExecute() with a "count" SQL statement?

1 Like
3

You are getting more and more into scripting territory… but let's stick to calculations.

  1. Create a calculation that performs your condition in the record itself and returns a true or false result. For example: field1 = value1 AND field2 = value2.

  2. Create a count function that counts the results: Count (Table::calculation field).

The disadvantage with calculations is the need for the formulæ to remain static to maintain performance. You want the first to be stored as much as possible. You could use the Evaluate function to make it dynamic… but kiss performance goodbye and be ready for debugging headaches.

1 Like
4

I can be wrong but, if I understand the request correctly, it's a pretty easy feat.
You just have to create a second relationship with the same link as the other, plus all the request you need. Then it's just a matter of using the new TO instead of the old one.

Let's hypnotize to have an Invoices table and a Rows table and their relationship is based on Invoices::id=Rows::id_invoice.
In Rows we have two field: color and quantity. We want, from the Invoices table, to count the rows with quantity=2 and color=red.
We just need two field in the Invoices table to store our filters (they can be constants, user input or scripted input, it really doesn't matter): after having created Invoices::filter_quantity and Invoices::filter_color, we can create a new TO for the Rows table (called FilteredRows) and match it with our Invoices table based on Invoices::id=Rows::id_invoice, Invoices::filter_color=Rows::color and Invoices::filter_quantity=Rows::quantity.
Now we can count the rows (from the Invoices table) using Count ( FilteredRows::id ).

With all that done, we just need to put a value into the filter fields and create a field into the Invoices table to store the result of our Count() function.