A Complicated Count

[Andrew_Ford2]

Hello Appsheet Community,

I’m trying to do a complicated COUNT() function and would appreciate any help.

Context: My app allows nurses to log visits to sites. Sites are located in various boroughs and have different site types. I’m working on created a summary page for managerial staff.

Three tables involved:

1. nurseLog: This is the form where nurses enter information about their visit.
2. Caseload: This is a list of sites. Rows in this table have a one:many relationship with nurseLog. Nurses log many visits at each individual site. Rows in caseload also have lots of location information for each site.
3. sumByBorough: This is where I’m trying to compile some summary statistics on the visits. I’m using this sheet as the fodder for some charts.

Goal: I am trying to make a table that shows the total number of nurseLog rows that are related to sites in specific boroughs. The borough information is stored in the Caseload table.

To accomplish this I tried making a virtual column in sumByBorough. But I’m having trouble. I think I want to create a virtual column in sumByBorough that is a count of rows in nurseLog that are related to a row in Caseload Builder that has a value in column Borough equal to the value in column borough in sumByBorough.

Then again, maybe there is another solution all together. Any help would be appreciated. Thank you!

[Andrew_Ford2]

Actually I think I just figured it out! Using the below formula:

COUNT(SELECT(nurseLog[logID], IN(nurseLog[siteid],FILTER(“Caseload Builder”,[_THISROW].[BoroughSum]=[Borough]))))

[Marc_Dillon · Accepted solution]

nurseLog[siteid] is a Ref type column, pointing to the Caseload table, correct?

If so, try this:

COUNT( FILTER( nurseLog , [siteid].[BoroughA] = [_THISROW].[BoroughB] ) )

BoroughA is a column in the Caseload table. BoroughB is a column in the sumByBorough table.

[Andrew_Ford2]

Marc_Dillon:

COUNT( FILTER( nurseLog , [siteid].[BoroughA] = [_THISROW].[BoroughB] ) )

This works, and it is much more efficient than the monstrosity I constructed. Thank you!