Answered

How Do I create a Gallery from two linked Sharepoint Lists?

(0) Share

Report

Posted on by PaulR1

Hi,

I am new to Power Apps (as will probably become very apparent with further reading of this post) and I am in the process of building my first Canvas App and it is going OK but I'm stuck on a problem that I'm not sure is solvable or not.

The data for the app comes from 2 Sharepoint Lists, the first of which is called tblEmployees and contains 4 columns "Id", "EmployeeName", "JobTitle" and "Archived" , the latter being (true/false) indicating whether the Employee has left the company or not. This list is linked to the main data list tblDataEntry which has several fields, including "EmployeeId" which is the link to tblEmployees. The tblDataEntry has several other fields based upon employee training and competency and many individual record entries for each employee.

I am trying to create a gallery to provide information from tblDataEntry but I want to exclude the records of "Archived" employees. I can hear you saying, "why do you not just delete the records of the employees that have left the company and its solved?" The problem is that, the business that I'm creating the app for does a lot of seasonal working and very often rehires employees that have previously worked with the company and, as the records for each employee can be many, re-entering all the data would be a time consuming task.

Any help would be very much appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Nogueira1306 7,390 Super User 2024 Season 1 on at

Like (0)

Report

You can create a collection where you join both Sharepoint lists and after thath, when you edit/insert new itens, you use Patch funciton and send the data to each form the way you want.

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,

Gonçalo Nogueira

Check my LinkedIn!

Buy me a coffee!

Check my User Group (pt-PT)!

Last Post on Community

My website!

Was this reply helpful? Yes No
PaulR1 20 on at

Like (0)

Report

Hi,
Thanks very much for your response.
Firstly, this may sound very novice-like but I'm not fully sure how to create a collection or and not sure if I've explained my issue correctly.

The app I'm creating is being designed for users to obtain various reports and will be 'read only'. Therefore no updating of data. The data (SharePoint Lists) is kept up date through a MS Access Database which provides a lot more information to the company than the app is being designed to do. So, no updating of data will be done through the app and unless I have not understood your proposed solution, I don't think it will give me the result I'm seeking.

Was this reply helpful? Yes No
Nogueira1306 7,390 Super User 2024 Season 1 on at

Like (0)

Report

So the only thing you need is to show data, right?

If yes, you only need to create a collection.

Now the problem is in How To create the Collection.
You need to create the collection using LookUp functions to create the "connection" between the 2 datasources and make sure that the item association is correct.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-clear-collect-clearcollect

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,

Gonçalo Nogueira

Check my LinkedIn!

Buy me a coffee!

Check my User Group (pt-PT)!

Last Post on Community

My website!

Was this reply helpful? Yes No
PaulR1 20 on at

Like (0)

Report

Thanks Again Gonçalo,
Yes, that is correct! I wish to show data in a Gallery or Table that displays only certain records that I Filter from the records of the list tblDataEntry. But I also want to exclude Employees who have been "Archived" and that information is in the other list tblEmployees. The two lists are connected by tblEmployees "ID" and tblDataEntry "EmployeeID". Sorting out the filter isn't a problem, it's getting the data into one collection so I can perform the filter that is the stumbling block.

As I said in my original post, I am new to PowerApps . You have kindly directed me to the link that covers the Collect functions but I am still struggling on how to successfully create the Collection I need. I am also unclear why and how I am use the LookUp function as you suggested, which as I understand will only produce the first matching record on the list.

I guess I am probably a lot less savvy with all this than you imagined and maybe I need a lot more help than you are able to offer, but thanks for your help so far.

PaulR

Was this reply helpful? Yes No
Nogueira1306 7,390 Super User 2024 Season 1 on at

Like (1)

Report

Okay, show me your tables and the field that "connects" the 2 sharepoint list

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,

Gonçalo Nogueira

Check my LinkedIn!

Buy me a coffee!

Check my User Group (pt-PT)!

Last Post on Community

My website!

Was this reply helpful? Yes No
PaulR1 20 on at

Like (0)

Report

@Nogueira1306
Thanks again, I have to leave my desk now but I will get back to you tomorrow.

Was this reply helpful? Yes No
PaulR1 20 on at

Like (0)

Report

@Nogueira1306
Hi again,
I've prepared and attach some modified extracts from the tables as the originals contain protected data but they represent what I am trying to achieve.

The EmployeeID column in tblDataEntry is populated via LookUp in the tblEmployees where the ID in tblEmployees = EmployeeID in tblDataEntry.

What I'm aiming to achieve is to produce a Gallery or Table which shows information from tblDataEntry but only includes current employees ie. employees who are not archived.
If I was carrying out this exercise in MS Access the SQL statement for the query would read something like this:
SELECT tblEmployees.EmployeeName, tblDataEntry.Comp, tblDataEntry.Cat, tblDataEntry.Level, tblDataEntry.Expiry
FROM tblEmployees INNER JOIN tblDataEntry ON tblEmployees.ID = tblDataEntry.EmployeeID
WHERE (((tblEmployees.Archived)=False));

If you need any other info please let me know and thanks for the help in advance.

ExampleTables.xlsx

Was this reply helpful? Yes No
Verified answer

PaulR1 20 on at

Like (1)

Report

I've managed to solve the issue myself following some basic guidance from @Nogueira1306 .

I initially created 2 collections: ClearCollect(tblDataCollection,tblDataEntry) & ClearCollect(tblEmplCollection,tblEmployees). I set both of these into the OnStart property of the app. On the Home Screen of the app I placed a button which OnSelect property creates the 3rd and connecting Collection and also navigates to the Gallery of the page I was looking to create as follows: ClearCollect(tblDataEmplCollection,AddColumns(tblDataCollection,"Archived",LookUp(tblEmplCollection,ID=tblDataCollection[@EmployeeID].Id,Archived)));Navigate(Expiries).

Whether or not there are easier or better ways of achieving the solution, I'm not sure but, for a Power App newbie, it works perfectly.

Was this reply helpful? Yes No