cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RobRoy
Level: Powered On

Many to many datasource from SharePoint?

I have a many-to-many table that links my Events table with my People table, since one event can have many people, and one person can belong to many events. I tried to add this table (EventPeople) as a datasource to my power app but the EventId and PeopleID columns do not show up as selectable columns. I have tried experimenting with AddColumns but I am struggling with the syntax order when paired with SortbyColumns(Filter...). The intended purpose is to create an additional search gallery that finds event dates by the people who attended them.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
RobRoy
Level: Powered On

Re: Many to many datasource from SharePoint?

I think I got it. This is working for me:

 

SortByColumns(Filter(EventPeople, Dropdown1.Selected.WorkflowPerson in WorkflowPerson), "WorkflowEventDate", If(SortDescending1, Ascending, Descending))

View solution in original post

11 REPLIES 11
Community Support Team
Community Support Team

Re: Many to many datasource from SharePoint?

Hi @RobRoy,

 

When saying table, do you mean SharePoint list ?

Would it be available for you to share some data referene for those two lists, or data sample?

In addition, which control that you used to display the 'table' data?

For better understanding, would it be available for you to explain a bit for "the intended purpose is to create an additional search gallery that finds event dates by the people who attended them."?

For the Addcolumn function, for example:

 

SortByColumns(Filter('Testing list', StartsWith(SText, TextSearchBox1.Text)), "SText", If(SortDescending1, Descending, Ascending))

 

Here the Filter function returns a table, and the addcolumns function should accept the bold part as the table parameter, so if yo uwould like use the addcolumns with the formula above, reference below:

 

SortByColumns( Addcolumns(Filter('Testing list', StartsWith(SText, TextSearchBox1.Text))ColumnName1, Formula1), "SText", If(SortDescending1, Descending, Ascending))

 

Please reply back if you need any further assistance.

Regards

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RobRoy
Level: Powered On

Re: Many to many datasource from SharePoint?

Thank you for your reply. I think I was mistaken in trying to use AddColumns to add a column from my many-to-many SharePoint list. I understand that AddColumns "adds a column to a table, and a formula defines the values in that column."That was not my issue; my issue was that the lookup columns simply never displayed, similar to how they do not show up as selectable columns in SharePoint if creating a calculated column. I was reminded that lookup columns are not available in SharePoint calculated columns, and perhaps that is why they are also not available in PowerApps? Regardless, I created workflows to copy the data from the lookup column to a new column ("EventDate" copied to "WorkflowEventDate") whenever a new item is created or existing item modified.  So I almost have what I need. I can choose the "workflow" columns now, but the problem has changed. My search field is a dropdown control, and my additional search gallery filters on this dropdown. But my syntax for the dropdown is not correct. When I switch this with a text input control it works fine. In other words this works as a filter:

 

SortByColumns(Filter(EventPeople, TextInput2.Text in WorkflowPerson), "WorkflowEventDate", If(SortDescending1, Descending, Ascending))

But this does not:

 

SortByColumns(Filter(EventPeople, Dropdown1.Selected in WorkflowPerson), "WorkflowEventDate", If(SortDescending1, Descending, Ascending))

Selecting a person from Dropdown1 should filter my gallery list of dates (WorkflowEventDate) to that person.

 

Thanks.

Highlighted
RobRoy
Level: Powered On

Re: Many to many datasource from SharePoint?

I think I got it. This is working for me:

 

SortByColumns(Filter(EventPeople, Dropdown1.Selected.WorkflowPerson in WorkflowPerson), "WorkflowEventDate", If(SortDescending1, Ascending, Descending))

View solution in original post

RobRoy
Level: Powered On

Re: Many to many datasource from SharePoint?

Although the filtering "works", I am still encountering another issue. I added navigation to the OnSelect Heading text box for record items in my gallery list:

 

Navigate(DetailScreen1, ScreenTransition.None, {galleryvar1: 3})

This is my third gallery for this app. I previously had to create a context variable (galleryvar1) so that navigating from either first or second gallery produced the correct DetailScreen result. This is not working for the third gallery. Setting a third state for the variable (galleryvar1: 3) does not navigate to the same DetailScreen as the first two galleries. Instead the app crashes out with the following error:

 

WebAuthoring abnormal termination.
Client date/time: 2017-03-27T14:12:03.055Z
Version: 2.0.602
Session ID: ca32c887-8d13-e844-5415-7e7f52df2fd6
description: {"error":{"isTrusted":true,"detail":{"exception":{}},"message":"Uncaught Error: Exception message: 'Unknown error.'\nException call stack:\nError\n at _sourceFromStacktrace (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/Core.js?v=39dde6617bc0:845:19)\n at _except (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/Core.js?v=39dde6617bc0:854:25)\n at Object.checkStringOrNull (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/Core.js?v=39dde6617bc0:795:55)\n at ControlProperty.typeEnforcer [as _typeEnforcerFn] (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1023:51)\n at ControlProperty.checkAndConstrainPropertyValue (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:894:41)\n at ControlProperty.setValue (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:875:46)\n at ControlWidget.setPropertyValueInternal (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1988:116)\n at https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1939:43\n at ReplicatedContextManager.forEachManagedDescendingBindingContext (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1450:65)\n at ControlWidget._forEachBindingContext (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:2265:64)\n at ControlWidget.setPropertyLambdaInternal (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1934:34)","... Error: Exception message: 'Unknown error.'\nException call stack:\nError\n at _sourceFromStacktrace (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/Core.js?v=39dde6617bc0:845:19)\n at _except (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/Core.js?v=39dde6617bc0:854:25)\n at Object.checkStringOrNull (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/Core.js?v=39dde6617bc0:795:55)\n at ControlProperty.typeEnforcer [as _typeEnforcerFn] (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1023:51)\n at ControlProperty.checkAndConstrainPropertyValue (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:894:41)\n at ControlProperty.setValue (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:875:46)\n at ControlWidget.setPropertyValueInternal (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1988:116)\n at https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1939:43\n at ReplicatedContextManager.forEachManagedDescendingBindingContext (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1450:65)\n at ControlWidget._forEachBindingContext (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:2265:64)\n at ControlWidget.setPropertyLambdaInternal (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1934:34)","...":{}}
stack: null
errorNumber: 0
errorMessage: Uncaught Error: Exception message: 'Unknown error.'
Exception call stack:
Error
at _sourceFromStacktrace (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/Core.js?v=39dde6617bc0:845:19)
at _except (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/Core.js?v=39dde6617bc0:854:25)
at Object.checkStringOrNull (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/Core.js?v=39dde6617bc0:795:55)
at ControlProperty.typeEnforcer [as _typeEnforcerFn] (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1023:51)
at ControlProperty.checkAndConstrainPropertyValue (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:894:41)
at ControlProperty.setValue (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:875:46)
at ControlWidget.setPropertyValueInternal (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1988:116)
at https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1939:43
at ReplicatedContextManager.forEachManagedDescendingBindingContext (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1450:65)
at ControlWidget._forEachBindingContext (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:2265:64)
at ControlWidget.setPropertyLambdaInternal (https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/AppMagic.Controls.js?v=39dde6617bc0:1934:34)
 

The difference bewteen this gallery and the other two that work is that the first two galleries use the same data source (Events), whereas this third gallery uses EventPeople, which is my many-to-many data source.

RobRoy
Level: Powered On

Re: Many to many datasource from SharePoint?

I am confused as to whether or not I can perform this function. I read this in the PowerApps training:

 

"Generated apps are always based on a single list or table (you can add more data to the app later)."

 

We can define multiple data sources and use the Lookup function, but is there some limit to working with a many-to-many table the way I am?

RobRoy
Level: Powered On

Re: Many to many datasource from SharePoint?

Here is a better explanation of what I am facing. Here is my primary gallery:

 

GalleryEvents1.jpg

 

Here is my second gallery so I can search by keywords:

 

GalleryEvents2.jpg

 

And here is my third gallery based off my many-to-many SharePoint list:

 

GalleryEventPeople.jpg

 

The third gallery causes the crash.

Community Support Team
Community Support Team

Re: Many to many datasource from SharePoint?

Hi @RobRoy,

 

Thanks for the explanation.

Working with different data sources under PowerApps should work. 

The error message you posted didn't give any detailed description.

Just from the error title "WebAuthoring abnormal termination", it seems that the issue should be related with authentication.

It would be great if Staff @AndyPennell could take a look for the sssion ID.

Regards

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Power Apps Staff AndyPennell
Power Apps Staff

Re: Many to many datasource from SharePoint?

Mona, this appears to be one (or both) of items 837877/936225. These are still under invesitgation, I added a link to this post to both.

 

Also: Unrelated to authentication: "abnormal termination" can mean literally anything, without additional analysis.

Community Support Team
Community Support Team

Re: Many to many datasource from SharePoint?

Hi @AndyPennell,

 

Thanks for looking into this.

This is Michael Shao from the CST.

The below is Mona Li's profile:

v-monli-msft

Regards

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,404)