Fetching data from another table

[djmalaka]

Hi,
I have an app with 3 tables.
Table 1 - “Patient Data”
Table 2 - “Admission for Delivery”
Table 3 - “Delivery Outcome”

Table 1 is the mother table and table 2 and 3 are child tables of Table 1. So Table 2 and 3 both have a [Patient Ref Key], that references each of these tables to Table 1. Now let’s say out of all the columns in Table 2 (“Admission for Delivery” table) it has a date type column called [Date], and a Y/N column called [Maternal Death]. Let’s say the user records details for ‘Patient X’ on the “Patient Data” table and opens up an entry in Table 2 (“Admission for Delivery” table) under the [Date]=18/12/2020, and he records “N” for [Maternal Death] column. Let’s say for the same ‘Patient X’, the user records another row where the [Date] is 19/12/2020, and [Maternal Death]=N on that day too. And on the next day, the user records a third row for the same patient X, and this time the [Date]=20/12/2020, and the [Maternal Death]=Y.

Table 3 (“Delivery Outcome” table) also has a Y/N column called [Maternal Death], and it has another ‘Date’ type column called [Date of Maternal Death]. So what I want is, when the user opens up ‘Table 3’ (“Delivery Outcome” table) to enter a record in it under the same ‘Patient X’, the [Maternal Death] column should already automatically record a “Y” on it as it was recorded as “Y” in [Maternal Death] column in ‘Table 2’. I managed to do this using the following expression.

IF((CONTAINS(SELECT(Admission for Delivery[Maternal death], [_THISROW].[Patient Ref Key] = [Patient Ref Key]),Y),“Y”),“Y”,“N”)

But what I also want is for the [Date of Maternal Death] on ‘Table 3’ to record the same date (20/12/2020) automatically by fetching it from the [Date] column in ‘Table 2’ when the user opens up a row in ‘Delivery Outcome’ table. Every time a [Maternal Death]=Y on any patient in Table 2, I want the Table 3’s [Date of Maternal Death] to fetch that relevant specific [Date] from Table 2. This I failed to achieve. I used the following expression but it brings up a different date from a different patient on the table.

LOOKUP((CONTAINS(SELECT(Admission for Delivery[Maternal Death],[_THISROW].[Patient Ref Key] = [Patient Ref Key]),Y),“Y”),“Admission for Delivery”,“Maternal Death”,“Date”)

I simply can’t get the logic figured on this expression. Can anyone help me with this expression?

Thank you!

[djmalaka · Accepted solution]

Ah, I figured it. I was going about it the wrong way. The following worked.

DATE(SELECT(Admission for Delivery[Date],AND([_THISROW].[Patient Ref Key]=[Patient Ref Key],[Maternal Death]=“Y”)))