Ref_Rows gives me inconsistent result

HCF · 12-14-2020 12:31 AM

I have some many-to-many relationships where I use Ref_Rows to find the related records:
This referaence gives me the correct records:

While, this is not giving the correct records:

The two expressions give the same output, but is referring to different values in the relationship table.
The Key is the UniqueID and there is no Label column.
What am I doing wrong?

Rafael_ANEIC-PY

Hello @HCF, The Ref_Rows simply returns the list of rows that have a column with the same value as the key of the row the function is running on, the “Contacts” or “Activities” columns are simply the names of the columns that are marked as references.

If both of your Ref_Row functions are running on a row with the same Key, and they are yielding the same result, then that’s working as intended i believe.

HCF

Hi @Rafael_ANEIC-PY, is there a way to read through a many-to-many relationship if we can not do it with Ref_Rows() ?

Rafael_ANEIC-PY

A Ref_Rows() function can be equivalent to a Select() expression, as stated in the documentation:

REF_ROWS("Order Details", "Order ID") is functionally equivalent to each of the following:

FILTER("Order Details", ([_THISROW] = [Order ID]))

SELECT(Order Details[Order Details ID], ([_THISROW] = [Order ID])) .

So you could use it to read the information that you need between your tables, i could try to help you with the exact expression if you give me more insight on what exactly are you trying to achieve and the structure of your app

HCF

I have a Table Contacts and a Table Activities. Each Contact can have many Activities and each Activity can have many Contacts. In between, I have a help table that looks like this:

What I am trying to do, is to list the Contacts related to a certain Activity and vice versa.

Suvrutt_Gurjar

If I have understood your requirement correctly,

For contacts related to a certain activity, please create a VC in the Activities Table called say
[ThisActivityContacts] with an expression something like

[Related ActivityContacts] [ContactsAC]

Here [ContactsAC] is the ref column you have for [Contacts] in the table “ActivityContacts” I have purposely assigned a suffix AC to identify that this column belongs to ActivityContacts table.

For activities related to a certain contact, please create a VC in the Contacts Table called say
[ThisContactActivities ] with an expression something like

[Related ActivityContacts] [ActivitiesAC]

Here [ActivitiesAC] is the ref column you have for [Activities] in the table “ActivityContacts” I have purposely assigned a suffix AC to identify that this column belongs to ActivityContacts table.

HCF

I get this error:

Suvrutt_Gurjar

Please try with [Related ActivityContacts] as suggested

Steve

There should be no space here:

HCF

Suvrutt_Gurjar

Please mention where you have used the expression. I believe you should have reverse reference columns titles something like [Related ActivityContacts] in both Contacts and Activities table.

The suggested VCs need to be in the contacts and activities table.

Contacts table VC expression :
[Related ActivityContacts] [ActivitiesAC]

Activities table VC expression

[Related ActivityContacts] [ContactsAC]

HCF

I have used the expression in a VC named Related Contacts in the Activities table and Related Activities in the Contacts table

Suvrutt_Gurjar

So is it working now?

Could you please update the name of the system created reverse reference column names related to the ActivityContacts table in Contacts as well as Activities table?

HCF

OK, I renamed the columns back to their original names and inserted the expression, but get this error:

Suvrutt_Gurjar

Please do not change the expression in the system created [Related ActivityContacts] column.

Please create new VCs as suggested, one each in Contacts table and Activities table and use the suggested expressions in their app formula.
I had suggested those to be different additional columns to be created with suggested names as below

HCF

Aaah, thanks for your patience, I finally get some result. However, the results display as a map which was not intended.

HCF

@Suvrutt_Gurjar - also, I see that from one of the lists in any view - it’s possible to use the View and Add commands. Add, adds a new blank record, but there is no way to add an existing record as I can see. Is this possible through Actions?