Forum Discussion
Linking Sharepoint lists items across lists and 'Looking Up' columns
Answer to question: You can create a look up column between 2 lists, but only "simple" fields are valid - mainly text based. So you can't lookup to complex fields like People fields, managed metadata or choice fields. It has to do with how data is actually stored (or not) in your SharePoint list. If you can't access a field for a lookup, it means that field type isn't valid. Sometimes you can do a workaround with a calculated column that spits out text values, but now you're doubling all the columns in your list. Not pretty.
Answer to actual problem: I guess I would question why you have 3 separate lists? Why not have all the info for all the packaging requests in a single list? How will you keep data synced between your separate lists? What if a value in one column is changed in one list - on purpose or by accident. Now any connections or filters you've made based on that field value will break for that item.
My suggestion would be to use a single list with ALL the columns. If you need separate forms for requests vs label control and packaging summary, then use content types for each type of form and specify which fields appear on which form. You could still simulate having separate lists by using list views for each of the 3 types.
If you want to get really fancy, you can use workflow to send an item through the form process
- New request gets created, workflow changes the content type to "Label" and sends a notification to someone for a label
- Label person does a thing, changes a status or whatever, workflow changes the content type to "Summary" and sends it to the next person.
I do this all. the. time. and it works like a charm. No more Flow, no more lookups, no more gaps in data. As a bonus, having all the data is in one place makes analytics much easier.
Hi,
Thank you for your response - it's much appreciated. The reason I didn't go for a single list is because I'm a relative novice when it comes to Sharepoint/Powerapps and honestly, I didn't realise you could have different forms derived from the same list - good to know though. I've no idea how to do that but I'll have a google later today.
The lists are really quite large - over 50 columns with entries being submitted by different people with different access restrictions - I need to ensure certain fields can only be updated by certain groups which is why multiple lists suited my needs although, considering what you mentioned below, I'm sure there may be a way around that using a single form. Your sharepoint knowledge vastly exceeds mine so hopefully I can work upto some of the workflow magic you mentioned below.
I've tried the 'unpretty' version you've mentioned below and I might just have to default to that. My only issue was that I couldn't figure out how to have 1 list view the same records across 2x different lists, because the entries are created uniquely (by flow) and aren't linked - but they do share some of the unique columns.
If Sharepoint list 1 has a Column titled - 'Job Number' and item 12345, flow then creates an entry and has that same unique column 'Job Number' and item - 12345, in Sharepoint 2 & 3 - is there some guide for how I can have Sharepoint 3 see the entry for 12345 from each of the 2x sharepoints from it's own item '12345' and pull data from both?
Thanks again for your help! :)
So you have multiple columns that exist as separate data values in multiple lists. Let's say we actually build this connection for Item 12345 that pulls in data from the other 2 lists based on the Flow copying the value "12345" to each of your lists. What happens when Item 12345 is accidentally edited to Item 1345?
Now lets do math. How many fields is Flow copying? Multiply by 3 lists? Multiply again by the number of list items and that is the number of potential data mismatches you have. The multiple list method may protect one or two fields from accidental or malicious changes, but only by putting your entire dataset is at risk.
In my experience, the "requirement" to restrict access to certain fields really masks a concern that some person who doesn't know what they're doing might accidentally change a value that breaks the process, such as assigning the wrong SME to a task. Do the 6-Sigma Why thing. Why do you need this requirement? Why do you have that concern? Why is it a problem if someone accidentally edits the wrong field? By doing that, I can usually get to the root cause/driver, such as "if the wrong SME is assigned, service delivery is delayed while we fix the assignment and X bad thing happens".
Now, that I've identified the actual need, I can usually find ways to address the concerns satisfactorily, such as "let's create a defined list of SMEs for each service so a user CAN'T pick the wrong one". The important thing is to help people understand how separate permissions on separate lists will actually make their problem worse. After all, they wouldn't have this "requirement" if they weren't concerned about dirty data in the first place.
Once we get past the separate lists, then there are things we can do that to guide users in the right direction and minimize their access to fields they shouldn't mess with. We can use views on pages to decide what fields they see. Carefully distributing fields across forms helps determine what fields they can access. Depending on the edits they need to make, quick edit can be a good option.
But the thing that will convince people they're data will be safe even if everyone can access all the fields is line item version control. Seriously, this will make you a ROCK STAR. Process owners will be able to see who made exactly what changes to which fields on which dates. And if a mistake was made, they can restore an earlier version of the item just like with documents. No harm, no foul. :-)
However, if multiple lists is an inflexible requirement and you risk getting fired for challenging it, then you're stuck with multiple lists and I would focus on lookups rather than copying data fields. At least then the data integrity is preserved.
- Those are some very well crafted responses Rachel :). Totally agree.
Some notes. I gave up trying to rig SharePoint to do what I want. It’s so frustrating. I learned powerapps and my god it’s powerful and you can pretty much make any type of business app you want. I would suggest maybe looking more into it. I prefer Sql over SharePoint so I can use the app to handle security over all the data and columns etc. def. some ramp up time but it’s game changing.
