Connecting two sharepoint lists

Veronika_Kys123 · ‎07-11-2022

Hello everyone,

Wondering if anyone might help me - or if I am even thinking to solve my problem the right way.

I have an approval app (5 stages of approval)- it will be solved by "visibility" of buttons (based on emails) for approvals users (not visible for submitting users).

My plan is to have "Approval screen" visible to approval user where all the records, waiting to be approved by them, will be displayed in a gallery.

So my idea was to somehow connect that > IF the record is waiting for Approval 1, this specific user will see it on his approval screen. Then the record is waiting for Approval 2, so another specific user ( that is assigned to be approval 2) will see the record on the "Approval screen".

PS: I have also multiple countries, so not only 5 approval users, but many more - based on a country.

I was thinking to create another sharepoint list ( not just the one where I store my submitted records waiting to be approved) and link that ex. : IF the record is coming from Spain and waiting for Approval 1 = this user (his email) is responsible for approval.

So my question - is there a way to connect two sharepoint lists - one with my submitted records and one where I will link approval users?

Thank you!!!!

RusselThomas · ‎07-11-2022

Hi @Veronika_Kys123 ,

Understanding when and how to split data sets into separate but linked tables is a fundamental of relational data modelling. I responded to a question similar to this before, so instead of rewriting everything, the principles should still be applicable - you can read it here.

In summary however, there is a principle of a primary key and foreign key,

Every SPO list has a primary key column - which is a column of data that uniquely identifies each row (in other words, this data cannot be duplicated anywhere in the data). In SPO, this is the ID column, which is there by default, auto-populated and managed by SPO, but you can use it to reference a unique row of data with PowerApps.
Any tables that you want to relate to the first table then require a foreign key column. This is a column where you place the primary key of the row of data that the second table row relates to.

This is the simplest relationship, but there are others.

In your case, you might have an Approval Request table, and an Approvers table.

The Approval Request table contains the default ID column all SPO lists have to uniquely identify each row. This is a plain Number column which you can show in your list, but you cannot edit.
- The Approval Request table also has columns containing details of the approval request, including a status like "ApprovalStage" which I would recommend be a Number column - the value of this column for any particular row could be anywhere from 1-x where x is the number of stages you want your approval to have. It would NOT contain any approver details however.
Your Approver table would then have an approver name/email address, and next to it a column called "ApprovalNumber", and then a column called "ApproverStage".
- The ApprovalNumber column would contain the value of the Approval Request ID column field to link the approver to that request. You could have the same approver be linked to the same approval request for different stages.

This layout would link a specific approver to a specific approval and approval stage. You could also create a better defined 'friendly' stage table to give your stages names, or set up your approvers by region or other categories, and add 'admin' screens to your app to allow admin users to create and define stages, as well as configure approvers per stage for all approvals in a region, or per stage per approval like we've done here - but the above should be enough to get you started. First get the linking right and make sure understand how it works, then expand into more complex scenarios. If you need to, plan it on paper first.

Kind regards,

RT