Answered

how to overcome multiple levels of relationship expansion?

(0) Share

Report

Posted on by Medoomi

523

I have A CDS entity "Member Agency Contact List," which stores the autonumbers of the related entity "Member Contacts," which also contains a lookup field "State" which holds those related values.

I am trying to display the "State" field in a gallery based on "Member Agency Contact List," but when I enter:

ThisItem.'Contact #'.State.Name

I receive the error "Multiple levels of many-to-one relationship expansion aren't supported"

What would be a good way to overcome this limitation?

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

You could do multiple Lookup() functions, where each Lookup is looking into the next chain of relations.
v-yutliu-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Medoomi ,

The reason why you met this problem is that it's not supported to use too many ".fiedlaname.fieldname.fieldname" in powerapps.

Usually at most".fieldname.fieldname".

Now, let us back to the feather that you want.

Do you mean that:
1)you have an entity named "Member Agency Contact List", a lookup field named "State", related to entity "Member Contacts"?

2)you want to display "Member Agency Contact List" in a gallery and display the lookup field?

Could you tell me what data do you want to display in the lookup field? (which field in related entity?)

A lookup field's value actually could represent a record in the related entity.

For example:

1)set the gallery's Items:

Member Agency Contact List

2)set one label's Text inside the gallery:

ThisItem.State.fieldname1 //fieldname1 should be field name in Member Contacts entity, this field should be text type.

If it is complex type, then you need to use other formula.

Best regards,
Medoomi 523 on at

Like
a
(0)

Report
Copy link

Link copied!
thank you so much @v-yutliu-msft
Sorry, I wasn't clear.

The entity I wish to display in a gallery is "Member Agency Contact List" and within that entity, I am working with a lookup field "Contact #" (related to the entity "Member Contacts.")
The entity "Member Contacts" contains a lookup field "State" (related to the entity "State or Province")
The entity "State or Province" contains the data I need to display in the gallery within a field called "Name"

As such, I would have expected the powerapps gallery to take the formula:
ThisItem.'Contact #'.State.Name
However, I understand that I cannot use three levels of relationship expansion.

Is there an alternative formula I can use in a gallery to display the "Name" of the state?
(i.e. I do not have a text field within two levels of relationship expansion from which I can draw).
Verified answer

v-yutliu-msft Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @Medoomi ,

Since you have three level relationship, just using ThisItem is not enough.

In powerapps, you could only at most directly connect two level relationship.

To solve your issue, I think you need to use LookUp function.

Could you tell me the unique identifier field in "Member Contacts" entity?

I assume that it is named MemberID.

Try this:
The gallery's Items:

'Member Agency Contact List'

The label's Text:

LookUp('Member Contacts',MemberID=ThisItem.'Contact #'.MemberID,State).Name //ThisItem.'Contact #'MemberID will get the unique id in 'Member Contacts' entity Then you will get that item's State Status.Name is the Name value in State or Province entity

Please replace MemberID with your identifier field in "Member Contacts" entity.

Best regards,