Forum Discussion
Help Linking SharePoint Lists
Hello SharePoint Community,
I've recently been working more with SharePoint lists, but I've hit a roadblock and could use some guidance.
What I'm Trying to Achieve
I need to create a structure where:
- Users can create new projects for the company.
- Each project can have multiple initiatives associated with it.
The Challenge
Since users manually input data, I can't rely solely on field values to establish links between projects and initiatives. Instead, I need an automatically generated unique ID in SharePoint to connect the lists. (I think this already happens, but I don't know how to use it properly)
The Ideal Setup
- When a user creates a new project, SharePoint should generate a unique ID.
- There should be a button to add initiatives to the project, ensuring they are correctly linked.
Possible Solutions?
- Is there a way to achieve this natively in SharePoint, perhaps using lookup columns, calculated fields, or even Power Automate?
- Would Power Apps be a better approach to provide a user-friendly interface for registering projects and their related initiatives?
- What are the best practices to ensure data consistency and prevent duplication?
I’d really appreciate insights from anyone who has tackled a similar scenario. Thanks in advance for your help!
I use the 2-list option all the time with the default SharePoint form and it works great. But the most important questions isn't how to enter the data, it's how to RETRIEVE the data. Once people enter all these projects and initiatives, the next question will be "Show me all the initiatives for a project and the health of the project." Now what? How are you going to do this in separate lists? Sure Power BI is an option - and that's how I usually do this - but now people are going back and forth between PBI & SP.
Or...Option #2 A nicer but more complicated option would be to have a single list and a choice field for Project vs Initiative. It's the same basic premise as the 2 list option, but the lookup will go back to the same list. You will need a customized list form to make it work. (You could build a full canvas app, but that's alot of work when the list view already exists.)
- The Title field must contain whatever identifier people use when referring to their projects, usually either Project Name or Project Number. Rename the Title field to Project Name/Number and make another column for the other value. I'm going to assume we're using Project Name/Title.
- Make sure that the Project Name & Project Number fields both REQUIRE UNIQUE VALUES. This is critical for people to be able to choose a single project. This Name/Number field will work for BOTH projects and initiatives.
- Create a choice field for Project vs Initiative
- Create a lookup field that references the Title/Project Name field in the same list. This will be used to tag initiatives to a project.
In your custom list form, when people choose Project, all the initiative fields should be hidden and a rule or workflow should add the Title/Project Name to the Parent Project Name field.
When people choose Initiative, they should see the Parent Title/Project Name as a dropdown lookup to choose the Project. This is the part that requires a Power App custom form as you will need to filter that field to show ONLY items tagged as "Project".
NOW you will have a SINGLE list where you can search or filter on the Parent Project Name field and see everything - the main project AND all the initiatives.My approach is slightly to that of ArefHalmstrand; I would have 1 SharePoint list for the master list of projects and a separate list list for the initiatives/tasks. This second list would have a column for the ID column value from the master list. The front end would be a Power Apps app which makes it much easier to link the 2 lists and gives the user a better experience. The ID column of the master list of projects would be used to bind the tasks to it from the list. I'll post up some screenshots as this was exactly what I was asked to demonstrate for a lunch & learn session at my company a while back.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)Rob_Elliott, in what way was that different? :)
You suggested creating a list for each project, I just have 1 list with details of all the projects and 1 list with details of all the task with a column that has the ID number from the 1st list. Then filtering in Power Apps. No need for Power Automate at all.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
Hello vfaria ,
Create a SharePoint list for the projects. The projects should be added through a Power App, that is controlled by Power Automate, with approval flow.
The projects then get added to a controlled list that only the "Power Automate" account has access to add information to. Each project will have a unique ID (default list column). The unique ID will later be used.
Further develop the Power App experience to showcase a dropdown or searchable column for all current projects. The user has to select a project to be able to create an initiative (to connect them).
When the user has selected a project and wants to create the initiative. Trigger a Power Automate to find the list item with the unique value "Title" and fetch the project ID. Use that ID value in another list where you store the initiatives and, in that way, they will be connected to the project.
In the initiatives list, you will need to create a custom column for the "Project ID". Because you cannot override the ID on the initiatives.
Yours sincerely,
Aref HalmstrandIf this was helpful, please mark as resolved 🙂
Feel free to give this message a like! 🤩
