Can I really have relational SharePoint lists in Power Apps?

[dsgnmind]

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. 

[WarrenBelz]

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]

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.

[dsgnmind]

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. 

[dsgnmind]

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

[WarrenBelz]

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]

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.

[dsgnmind]

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?

[WarrenBelz]

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.

[dsgnmind]

@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.