Count number of rows in a separate table that have the same key

Queries and Resources
1

Hi,

I am struggling to apply a filter to a query on a row by row basis... although maybe this is the entirely wrong approach for the task!

I have two tables, one is a list of 'companies', the other is a list of 'contacts'.

Capture1
Capture11549×627 38.7 KB

Each 'contact' is linked to a 'company' using a shared 'accountref'.

I would like the total number of 'contacts' associated with each 'company' to appear under the 'No. Contacts' column of the table on the left.

I have attempted to apply a filter based on the 'currentRow' value but this has not been sucessful.

Any suggestions would be much appreciated.

Thanks!

2

@sewsewsimon Welcome to the community!
Are the two tables populated by separate queries? If so, are the queries hitting the same database? if so you could write one query using JOIN and have one table display all of the data if that is acceptable.

3

Thanks Scott. They aren't currently in the same database but there's no reason why they couldn't be if I change a few other queries.

Will give it a try!

4

Hey @sewsewsimon, welcome to the fam :hugs:

You could (like @ScottR suggested) use a JSON SQL query to join these two datasets together and do your calc there.

Or, create a custom column with something like this:

{{_.filter(contacts_table.displayedData, {account_ref: currentRow.account_ref}).length}}