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 |
---|---|
43 | |
27 | |
24 | |
23 | |
13 |