Solved: Re: Many to many datasource from SharePoint?

RobRoy · ‎03-13-2017

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

RobRoy · ‎03-20-2017

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

v-micsh-msft · ‎03-14-2017

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 · ‎03-20-2017

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.

RobRoy · ‎03-20-2017

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

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

RobRoy · ‎03-27-2017

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:

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 · ‎03-28-2017

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 · ‎03-28-2017

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

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

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

The third gallery causes the crash.

v-micsh-msft · ‎03-28-2017

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.

AndyPennell · ‎03-29-2017

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 · ‎03-29-2017

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.

Many to many datasource from SharePoint?

v-monli-msft

Helpful resources

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

Updates to Transitions in the Power Platform Communities