Let’s say the user is in a table called “Analyser”, where he/she selects a Surgery Title from a dropdown Enum column. There is a number type column called [Uneventful] in this “Analyser” table.
Now there is another data table with a lot of data already filled in called the “Surgery Logbook”. In that, there is a column called [Surgery Title] exactly similar to the enum column [Surgery Title] in “Analyser” data table. There is another column called [Complications] in this “Surgery Logbook” table which is populated with different values called ‘Uneventful’, ‘Grade I’, ‘Grade II’, ‘Grade III’, ‘Grade IV’ and ‘Grade V’. What I am trying to achieve is, when the user selects a [Surgery Title] in the “Analyser” table, the [Unevenful] column in that “Analyser” table to be automatically filled up with the number of ‘Uneventful’ occurrences in the [Complications] column of the “Surgery Logbook” table in rows where the [Surgery Title] is as same as the [Surgery Title] in “Analyser” table. These 2 tables are not referenced to each other so I was wondering whether a LOOKUP expression can bring this to work.
Would greatly appreciate if you could help with this! Thanks.
Solved! Go to Solution.
Try this as the app formula for the Uneventful (virtual!) column of the Analyser table:
COUNT( FILTER( "Surgery Logbook", AND( ([Surgery Title] = [_THISROW].[Surgery Title]), ([Complications] = "Uneventful") ) ) )
FILTER("Surgery Logbook", ...)
selects all rows in Surgery Logbook that match the given criteria.
AND(..., ...)
requires selected rows match all of the given criteria.
([Surgery Title] = [_THISROW].[Surgery Title])
matches rows in Surgery Logbook with a Surgery Title column value that matches the Surgery Title column value of the current row in the Analyser table.
([Complications] = "Uneventful")
matches rows in Surgery Logbook with a Complications column value of Uneventful
.
COUNT(...)
counts the rows selected by FILTER(...)
.
You could try something like LOOKUP([_THISROW].[Surgery Title],Surgery Logbook,Surgery Title,Complications)
Thank you Aleksi. Only issue is that it only brings a randomly chosen value from the [Complications] column in the “Surgery Logbook” table. What I’m trying to do is to get the sum of “uneventful” occurrences in this [Complications] column when the [Surgery Type] = [_THISROW].[Surgery Type]. I can’t figure this out somehow, because it brings up an error always.
Try this as the app formula for the Uneventful (virtual!) column of the Analyser table:
COUNT( FILTER( "Surgery Logbook", AND( ([Surgery Title] = [_THISROW].[Surgery Title]), ([Complications] = "Uneventful") ) ) )
FILTER("Surgery Logbook", ...)
selects all rows in Surgery Logbook that match the given criteria.
AND(..., ...)
requires selected rows match all of the given criteria.
([Surgery Title] = [_THISROW].[Surgery Title])
matches rows in Surgery Logbook with a Surgery Title column value that matches the Surgery Title column value of the current row in the Analyser table.
([Complications] = "Uneventful")
matches rows in Surgery Logbook with a Complications column value of Uneventful
.
COUNT(...)
counts the rows selected by FILTER(...)
.
Steve Coile, Thank you so much! It worked perfectly.
@djmalaka It seems that I didn’t understand your request. I didn’t understand that you were asking sum, not a number
Aleksi, it’s okay, I might not have been clear in my request. Thanks a lot. This new community page has been very nicely designed by you all! Awesome work.
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
17 |