cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
la4
Frequent Visitor

Combining multiple data sources in sharepoint to create complex datatable in powerapps

I am trying to build a power app and desperately need some help.

 

Task:

- i currently have three different data sources in sharepoint that i have to use in one dashboard

- one table consists of a list of all users (Table 1), another table consists of a list of all notices (Table 2), and another consists of a list of users and the corresponding notices they have read (Table 3)

 

I essentially, need a way to filter through all these lists, and create a data table in powerapps of a list of users who have NOT read the notices and which notice they have not read

Im thinking along the lines of looking up each user from (Table 1) in (Table 3) and then finding some way and looking up which notices they havent read in (Table 2), but im not sure how to do this.

 

Any suggestions would be much appreciated 

1 ACCEPTED SOLUTION

Accepted Solutions
klaasdejong
Frequent Visitor

Hi @la4 ,

 

There are several approaches but here is one.

 

What you need a condition under which a notice is read. You could have a gallery and do a patch to one of the data source when someone opens the notice. Kind of like how in Outlook a message is considered read once you open it. You also need to record this somewhere.

 

Which data source you patch to is up to you.

  • Patch a notice to the user table in a "notices read" column (by appending/concatenating your messages)
  • Patch the user to the notice in quetion in a "read by" column (by appending/concatenating your users)
  • Create a new list with a log of users who read the notices (recommended by me)

I suggest doing the 3rd option, as it is simpler and more robust.

 

First create a SharePoint list called 'Notices Read Log' with at least 2 columns: "User Email" and "Notice Id". If you are using a SharePoint list as the source for the notices, I strongly suggest storing the SharePoint list ID of the Notice that is read, rather than its message. This allows you to change the message without breaking the link. You can use LookUp to get the actual message from the notices data source if it is necessary.

 

In the example below I'm assuming you "read" the notice by selecting the item in a gallery with the data source 'Notices' (the actual messages):

 

Patch('Notices Read Log',Defaults('Notices Read Log'),{'User Email':User().Email,'Notice':ThisItem.Id})

 

This creates a new entry in the list

 

Now you have a list of messages that were read, but you are actually interested in messages that were not read. For this you have to use an action like a button or you can use the OnVisible of a screen to run the following:

 

Clear(ColUnreadMessages);
ForAll(
    'Notices' As noticeLoopItem,
    ForAll(
        'Users' As userLoopItem,
        If(
            Not(
                noticeLoopItem.Id in Filter(
                    'Notices Read Log',
                    'User Email' = userLoopItem.Email
                ).NoticeId
            ),
            Collect(
                ColUnreadMessages,
                {
                    NoticeId: noticeLoopItem.Id,
                    Notice: noticeLoopItem.Notice,
                    UserEmail: userLoopItem.Email
                }
            )
        )
    )
)

 

This will run through all notices, and for each of the notices runs through all of the users to check whether there are users which have not read them. I use the Not()-function + the in-operator to accomplish this.

 

What will happen in the first loop is: it takes the first notice, then it takes the first user and it checks if the id of the notice is not in the log of read notices. If yes (in which case they haven't read them), then it saves the NoticeId, Notice and UserEmail in a collection (sort of like a local table in Power Apps) which you can use as a datasource in a gallery or table component. The run will then check the same logic against the second user, etc. until there are no more users in the list and then it goes on to the second notice and start with the first user, etc.

 

You will end up with a list of individual messages which are not read. You can message that table into whatever form you need for your app. For instance you could look into the distinct function to get a list of users which have unread messages and use that as a source for a gallery.

 

RenameColumns(Distinct(ColUnreadMessages,UserEmail),"Result","Email")//The default column name for the result is Result, which might be confusing so I renamed it to Email

 

Once you select a user you can have a different gallery with a list of their actual unread messages:

 

Filter(ColUnreadMessages,UserEmail=UnreadMessagesGallery.Selected.Email)

 

A few things to consider:

Note that you might want to filter the 'Notices' so you won't run through old notices indefinitely which will start to hurt performance of the app once you have a lot of historic notices. You can do this by filtering the 'Notices' by Created column at the start of the code. I'm sure the DateAdd-function can help here.

 

ForAll(
    Filter('Notices',<your date filter>) As noticeLoopItem....etc.

 

Another idea is to sort your notices by the Created column and retrieve only the first 2000 items:

 

ForAll(
    FirstN(SortByColumns('Notices',"Created",Descending),2000) As noticeLoopItem,...etc.

 

Make sure you set the Data row limit for this app to 2000 in the settings (this is max) of your app, but I'm not sure how SharePoint manages delegation for these columns...

image.png

 Hope this helps!

View solution in original post

4 REPLIES 4
v-qiaqi-msft
Community Support
Community Support

Hi @la4,

Do you want to filter users those have not read the notices?

Could you please show me the Table3 so that I know if you display multi notices that being read in a single column or multi columns?

 

If each user should read all the notices in the Table2, then you must list the notices that have been read one by one, right?

Please show me you data configuration.

 

Best Regards,
Qi
klaasdejong
Frequent Visitor

Hi @la4 ,

 

There are several approaches but here is one.

 

What you need a condition under which a notice is read. You could have a gallery and do a patch to one of the data source when someone opens the notice. Kind of like how in Outlook a message is considered read once you open it. You also need to record this somewhere.

 

Which data source you patch to is up to you.

  • Patch a notice to the user table in a "notices read" column (by appending/concatenating your messages)
  • Patch the user to the notice in quetion in a "read by" column (by appending/concatenating your users)
  • Create a new list with a log of users who read the notices (recommended by me)

I suggest doing the 3rd option, as it is simpler and more robust.

 

First create a SharePoint list called 'Notices Read Log' with at least 2 columns: "User Email" and "Notice Id". If you are using a SharePoint list as the source for the notices, I strongly suggest storing the SharePoint list ID of the Notice that is read, rather than its message. This allows you to change the message without breaking the link. You can use LookUp to get the actual message from the notices data source if it is necessary.

 

In the example below I'm assuming you "read" the notice by selecting the item in a gallery with the data source 'Notices' (the actual messages):

 

Patch('Notices Read Log',Defaults('Notices Read Log'),{'User Email':User().Email,'Notice':ThisItem.Id})

 

This creates a new entry in the list

 

Now you have a list of messages that were read, but you are actually interested in messages that were not read. For this you have to use an action like a button or you can use the OnVisible of a screen to run the following:

 

Clear(ColUnreadMessages);
ForAll(
    'Notices' As noticeLoopItem,
    ForAll(
        'Users' As userLoopItem,
        If(
            Not(
                noticeLoopItem.Id in Filter(
                    'Notices Read Log',
                    'User Email' = userLoopItem.Email
                ).NoticeId
            ),
            Collect(
                ColUnreadMessages,
                {
                    NoticeId: noticeLoopItem.Id,
                    Notice: noticeLoopItem.Notice,
                    UserEmail: userLoopItem.Email
                }
            )
        )
    )
)

 

This will run through all notices, and for each of the notices runs through all of the users to check whether there are users which have not read them. I use the Not()-function + the in-operator to accomplish this.

 

What will happen in the first loop is: it takes the first notice, then it takes the first user and it checks if the id of the notice is not in the log of read notices. If yes (in which case they haven't read them), then it saves the NoticeId, Notice and UserEmail in a collection (sort of like a local table in Power Apps) which you can use as a datasource in a gallery or table component. The run will then check the same logic against the second user, etc. until there are no more users in the list and then it goes on to the second notice and start with the first user, etc.

 

You will end up with a list of individual messages which are not read. You can message that table into whatever form you need for your app. For instance you could look into the distinct function to get a list of users which have unread messages and use that as a source for a gallery.

 

RenameColumns(Distinct(ColUnreadMessages,UserEmail),"Result","Email")//The default column name for the result is Result, which might be confusing so I renamed it to Email

 

Once you select a user you can have a different gallery with a list of their actual unread messages:

 

Filter(ColUnreadMessages,UserEmail=UnreadMessagesGallery.Selected.Email)

 

A few things to consider:

Note that you might want to filter the 'Notices' so you won't run through old notices indefinitely which will start to hurt performance of the app once you have a lot of historic notices. You can do this by filtering the 'Notices' by Created column at the start of the code. I'm sure the DateAdd-function can help here.

 

ForAll(
    Filter('Notices',<your date filter>) As noticeLoopItem....etc.

 

Another idea is to sort your notices by the Created column and retrieve only the first 2000 items:

 

ForAll(
    FirstN(SortByColumns('Notices',"Created",Descending),2000) As noticeLoopItem,...etc.

 

Make sure you set the Data row limit for this app to 2000 in the settings (this is max) of your app, but I'm not sure how SharePoint manages delegation for these columns...

image.png

 Hope this helps!

la4
Frequent Visitor

Hi.

Thanks so much for responding! 

 

I have an additional question, would this also be possible if instead of having a sharepoint list of all users i actually have an outlook.com distribution list of all users?

 

Thanks

klaasdejong
Frequent Visitor

Good question. You can try to see if you can find distribution groups with the Office 365 Groups or Outlook connectors and then try something like this:

ClearCollect(colUsers,Office365Groups.ListGroupMembers("group Id"))

I haven't tried but it's worth giving a shot.

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (3,848)