Lookup to another list using current item ID

%3CLINGO-SUB%20id%3D%22lingo-sub-1318889%22%20slang%3D%22en-US%22%3ELookup%20to%20another%20list%20using%20current%20item%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318889%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20building%20a%20list%2C%20where%20I%20want%20to%20protect%20certain%20columns%20from%20editing%20by%20the%20users.%20I%20understood%20that%20Sharepoint%20does%20not%20have%20column%20protection%20functionality.%20One%20of%20the%20workarounds%20is%20to%20have%20the%20columns%20that%20I%20want%20to%20protect%20in%20another%20list.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20built%202%20lists%3A%3C%2FP%3E%3CP%3E-%20List%20A%20(actually%20a%20document%20library)%3A%20editable%20by%20users%3C%2FP%3E%3CP%3E-%20List%20B%3A%20not%20editable%20by%20others%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20List%20B%2C%20one%20of%20the%20column%20is%20the%20ID%20in%20List%20A.%3C%2FP%3E%3CP%3EHow%20do%20I%20add%20List%20B's%20columns%20into%20List%20A%3F%20I%20tried%20creating%20a%20lookup%20column%20in%20List%20A.%20But%2C%20I%20have%20to%20manually%20select%20the%20ID.%20There%20is%20a%20chance%20that%20the%20users%20may%20select%20the%20wrong%20ID.%20Is%20there%20any%20way%20I%20can%20lookup%20to%20the%20columns%20in%20List%20B%20using%20the%20ID%20of%20List%20A's%20items%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20question%2C%20if%20in%20List%20B%2C%20I%20have%20multiple%20entries%20for%201%20item%20in%20List%20A%2C%20how%20do%20I%20retrieve%20the%20value%20of%20the%20last%20entry%20that%20matches%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1318889%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1318935%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20to%20another%20list%20using%20current%20item%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318935%22%20slang%3D%22en-US%22%3ESo%20how%20are%20the%20values%20getting%20into%20list%20B%20if%20they%20don%E2%80%99t%20have%20access%20to%20the%20list%3F%20%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20only%20way%20you%E2%80%99ll%20be%20able%20to%20really%20do%20this%20is%20create%20a%20powerapp%20for%20viewing%20the%20files%20and%20metadata%20which%20you%20can%20tie%20to%20the%20list%20and%20preform%20lookups%20your%20the%20other%20list%20based%20on%20the%20selected%20ID%20in%20list%20a.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1321681%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20to%20another%20list%20using%20current%20item%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F627806%22%20target%3D%22_blank%22%3E%40xhn123456%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20this%20all%20the%20time.%20Give%20the%20users%20read%20only%20access%20to%20List%20B.%20Create%20a%20look%20up%20in%20List%20A%20and%20use%20whatever%20field%20you%20need%20for%20the%20users%20to%20choose%20from%20as%20the%20drop%20down.%20Usually%20it's%20the%20Title%20field%20(or%20whatever%20you've%20called%20it)%20but%20it%20could%20be%20any%20form%20of%20text%20field.%20You%20can%20use%20a%20calculated%20field%20if%20the%20output%20is%20text.%20I%20use%20this%20frequently%20to%20concatenate%20multiple%20fields%20into%20one%20drop%20down%2C%20like%20%22Customer%20Name%20-%20Project%20Number%22%20as%20a%20form%20of%20data%20validation%20where%20any%20given%20customer%20might%20have%20multiple%20projects.%20In%20these%20cases%2C%20I%20usually%20bring%20both%20values%20into%20List%20A%20as%20separate%20fields.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20List%20A%2C%20user%20can%20select%26nbsp%3B%22Customer%20Name%20-%20Project%20Number%22%20in%20the%20form%20-%20default%20form%20works%20fine%20-%20but%20in%20the%20list%20columns%2C%20they%20see%20Customer%20Name%20and%20Project%20Number%20as%20separate%20fields.%20Voila!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi there

 

I am building a list, where I want to protect certain columns from editing by the users. I understood that Sharepoint does not have column protection functionality. One of the workarounds is to have the columns that I want to protect in another list. 

 

So, I built 2 lists:

- List A (actually a document library): editable by users

- List B: not editable by others

 

In List B, one of the column is the ID in List A.

How do I add List B's columns into List A? I tried creating a lookup column in List A. But, I have to manually select the ID. There is a chance that the users may select the wrong ID. Is there any way I can lookup to the columns in List B using the ID of List A's items?

 

Another question, if in List B, I have multiple entries for 1 item in List A, how do I retrieve the value of the last entry that matches?

 

Thanks. 

2 Replies
Highlighted
So how are the values getting into list B if they don’t have access to the list?

The only way you’ll be able to really do this is create a powerapp for viewing the files and metadata which you can tie to the list and preform lookups your the other list based on the selected ID in list a.
Highlighted

@xhn123456 

I do this all the time. Give the users read only access to List B. Create a look up in List A and use whatever field you need for the users to choose from as the drop down. Usually it's the Title field (or whatever you've called it) but it could be any form of text field. You can use a calculated field if the output is text. I use this frequently to concatenate multiple fields into one drop down, like "Customer Name - Project Number" as a form of data validation where any given customer might have multiple projects. In these cases, I usually bring both values into List A as separate fields.

 

In List A, user can select "Customer Name - Project Number" in the form - default form works fine - but in the list columns, they see Customer Name and Project Number as separate fields. Voila!