Orphaned Childs - How to deal with these?

[John_McCredie]

I have a one to many relationship between two tables. I want to be able to delete a row in the parent table, but not delete the many child rows in the child table. I have my app setup like this, and it leaves lots of orphaned rows in the child (I want to keep them).

What I want to be able to do is have the orphaned rows display something, like “orphaned” in the column, instead of the deleted referenced key ID. That way I can have a view that just shows orphaned rows that can be re-assigned to other parent rows.

Could anyone suggest a way of achieving this? I’ve tried using automation, but not having much success. Any ideas or advice would be welcome. Thanks in advance.

[Suvrutt_Gurjar · Accepted solution]

Please create a VC , called say [Record Status] in the child table with an expression something like

IF(NOT(IN([Ref Column in Child Table], Parent Table[Key Column])),“Orphaned”, “Has Parent”)

Please create a slice on the child table with [Record Status]=“Orphaned”

[John_McCredie]

Thanks. That has helped.

Any reason you used the NOT() expression rather than just:

IF(IN([Ref Column in Child Table], Parent Table[Key Column]),“Has Parent”, “Orphaned”)

[Suvrutt_Gurjar]

Thank you good to know that worked. No specific reason. Either is good, with the one without NOT() will in fact be a bit more efficient.