cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dsgnmind
Helper III
Helper III

Can I really have relational SharePoint lists in Power Apps?

I'm trying to duplicate what I currently have in Access, a very small relational database to make a report. The advantage of putting this up on a SharePoint site as a Power App to have a more interactive experience is extremely attractive. I've been able to build a basic app with the Parent data driving Gallery1, and a display form (as nothing is editable) showing the Parent details when the Gallery1 Parent is selected. This is pretty straight forward, but I also need two more Child display forms that show the associated child details depending on which Parent is selected. In all my testing I cannot get the result I'm looking for from the associated child data to populate in an additional gallery or display form when the parent is selected. I've gone through countless videos and articles, but no luck.


From what I understand, I should be able to have another gallery or display form which will show the additional child records where: Filter(child_list,Column_name=Gallery1.selected.Column_name) but any variety of this formula/expression doesn't seem to be happy, as if I'm missing something even more basic. 

 

The end design is to have a gallery or dropdown to select the Parent, a details area to show the Parent details, and two sperate child display forms that show the child-specific details. 

 

All the Sharepoint lists will have no more than 250 records. Both child lists use a multiple look-up column to associate to the Parent. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

@dsgnmind ,

You have multiple values in your child_list.Parent_Name

I assume this is a multi-choice Lookup field?

Try this first - there will be a Delegation warning

Filter(
   child_list,
   Gallery1.Selected.Parent_Name.Value In Parent_Name.Value
)

or 

Filter(
   child_list,
   Gallery1.Selected.Parent_Name In Parent_Name.Value
)

parentmulti.jpg

 

 

 

 

 

 

 

 

 

 

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

12 REPLIES 12
WarrenBelz
Super User III
Super User III

Hi @dsgnmind ,

What is the error under the red cross on this?

The syntax is valid as long as the field, list and gallery references are also.

Also did you know Access connects directly and very well to SharePoint as a data source. I also came from Access and almost all of my legacy apps still work fine with VBA and SQL operating almost the same as off an accbd backend.

eka24
Super User III
Super User III

You are close.

That's, the formula should be placed on the Items property instead of the the Datasource

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

The error is shown over to the right "invalid argument type". 

 

Yes, on the access question. This is pretty low maintenance, content is edited very infrequently. I'm not familiar with VBA and SQL other than by name. 

It is on the Items, just jpeg is cut off. Here is a wider view.

Thanks @dsgnmind ,

So the syntax

Filter(
   child_list,
   Parent_Name = Gallery1.Selected.Parent_Name
)

is in the Items property of the second gallery is saying Invalid Argument Type?
There are only three things here to check:-

 

  1. child_list is the valid name of the List you are filtering
  2. Parent_Name is a Text field in child_list (not a Choice or Lookup field)
  3. Gallery1 has a field in the data called Parent_Name which is also a single line of text.

I suspect Parent_name may not be text? Is so, change to

Filter(
   child_list,
   Parent_Name.Value = Gallery1.Selected.Parent_Name.Value
)

Note you will get a Delegation warning on this as Complex field type filters are not Delegable.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

eka24
Super User III
Super User III

Generally the formula is ok. Due to lookup column, change to:

Filter(child_list,Column_name=Gallery1.selected.Column_name.Value)

Or

Filter(child_list,Column_name.Value=Gallery1.selected.Column_name.Value)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

the Parent_Name column in the child_list is a multiple lookup type.

 

The goal is to have two child lists with their own various properties, in which each child_list record looks up multiple Parents it belongs to. My understanding was that Power Apps can do this, but it seems it can't handle this?

 

 

Thanks @dsgnmind ,

Please try the filter I suggested in the post below

Filter(
   child_list,
   Parent_Name.Value = Gallery1.Selected.Parent_Name.Value
)

SharePoint Lookup fields are problematic at the best of times in Power Apps and for this reason, I have not used them for some time.

 

@WarrenBelz , @eka24 

 

I tried all the varieties of adding the .Value but no go. I added images of the two SharePoint lists in case I am missing something basic there. Much appreciated.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (9,995)