web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Many to many datasourc...
Power Apps
Answered

Many to many datasource from SharePoint?

(0) ShareShare
ReportReport
Posted on by RobRoy 111

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

Categories:
Building Power Apps
I have the same question (0)
  • v-micsh-msft Profile Picture
    v-micsh-msft Microsoft Employee on at

    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

  • RobRoy Profile Picture
    RobRoy 111 on at

    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.

  • Verified answer
    RobRoy Profile Picture
    RobRoy 111 on at

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

     

    SortByColumns(Filter(EventPeople, Dropdown1.Selected.WorkflowPerson in WorkflowPerson), "WorkflowEventDate", If(SortDescending1, Ascending, Descending))
  • RobRoy Profile Picture
    RobRoy 111 on at

    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)","filename":"https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/Core.js?v=39dde6617bc0","lineno":855,"colno":9,"error":{},"NONE":0,"CAPTURING_PHASE":1,"AT_TARGET":2,"BUBBLING_PHASE":3,"MOUSEDOWN":1,"MOUSEUP":2,"MOUSEOVER":4,"MOUSEOUT":8,"MOUSEMOVE":16,"MOUSEDRAG":32,"CLICK":64,"DBLCLICK":128,"KEYDOWN":256,"KEYUP":512,"KEYPRESS":1024,"DRAGDROP":2048,"FOCUS":4096,"BLUR":8192,"SELECT":16384,"CHANGE":32768,"type":"error","target":"[window]","currentTarget":"[window]","eventPhase":2,"bubbles":false,"cancelable":true,"defaultPrevented":false,"timeStamp":2629466.835,"srcElement":"[window]","returnValue":true,"cancelBubble":false,"path":["[window]"],"stopPropagation":"[function]","stopImmediatePropagation":"[function]","preventDefault":"[function]","initEvent":"[function]","composed":false,"composedPath":"[function]"},"errorLine":855,"errorCharacter":9,"errorUrl":"https://paawuscdn.azureedge.net/v2.0.602.0/studio/js/Core.js?v=39dde6617bc0","errorMessage":"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)","setPromise":"[function]","exception":{}}
    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 Profile Picture
    RobRoy 111 on at

    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 Profile Picture
    RobRoy 111 on at

    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.

  • v-micsh-msft Profile Picture
    v-micsh-msft Microsoft Employee on at

    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

  • AndyPennell Profile Picture
    AndyPennell Microsoft Employee on at

    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.

  • v-micsh-msft Profile Picture
    v-micsh-msft Microsoft Employee on at

    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

  • AndyPennell Profile Picture
    AndyPennell Microsoft Employee on at

    Sorry Michael, I got your ids mixed up.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 345 Most Valuable Professional

#2
11manish Profile Picture

11manish 207 Super User 2026 Season 2

#3
Mohsin Ali Profile Picture

Mohsin Ali 177

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans