Fetching data from a different table

expressions
(Malaka Jayawardene) #1

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.

(Aleksi Alkio) #2

You could try something like LOOKUP([_THISROW].[Surgery Title],Surgery Logbook,Surgery Title,Complications)

(Malaka Jayawardene) #3

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.

(Steve Coile) #4

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")
  )
 )
)
  1. FILTER("Surgery Logbook", ...) selects all rows in Surgery Logbook that match the given criteria.

  2. AND(..., ...) requires selected rows match all of the given criteria.

  3. ([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.

  4. ([Complications] = "Uneventful") matches rows in Surgery Logbook with a Complications column value of Uneventful.

  5. COUNT(...) counts the rows selected by FILTER(...).

See also: AND(), COUNT(), FILTER()

(Aleksi Alkio) #5

@djmalaka It seems that I didn’t understand your request. I didn’t understand that you were asking sum, not a number :slight_smile:

(Malaka Jayawardene) #6

Steve Coile, Thank you so much! It worked perfectly.

1 Like
(Malaka Jayawardene) #7

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.

1 Like