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:
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!
Solved! Go to 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.
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]))))
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.
This works, and it is much more efficient than the monstrosity I constructed. Thank you!
User | Count |
---|---|
40 | |
32 | |
30 | |
16 | |
16 |