How to filter a lookup column based on a previous column value? Ie previous column selects for active, inactive, or cancelled, then our new column allows to select only for jobs of that previous column status.
This exactly what I need to know. If I use this approach, though awesome for Active and Inactive statuses, I can't how to deploy if I am looking to filter on other data.
If anyone is wonder what happens when you change something from active to inactive in the source list, your lookup column value goes empty. However, SharePoint retains the lookup reference ID (i.e. the lookup isn't broken, the value is just empty), so if you add a second column pointing to one of the other lookup columns (see timestamp 3:36), it will still display this information.
It took me a long time to find your video to solve this problem: How to only show active items in the lookup from the SharePoint list. Wow! You are the best and thank you for sharing.
Hello April, thanks a lot to share your knowledge with us! Is it possible to create a cascade lookup with 3 columns (Countries, States, and Cities) looking in 2 others lists? E.g.: Countries, States, and Cities using a list for Country/States and another one for States/Cities? Thanks again.
Thanks April! this is actually a saver... though i added a calculated column for filtering teh data in the views for active/open positions only but using that for lookup didnt hit me :-)
Hi April, this is good informative, kindly let me know, lookup field not working for date format its visible number format (i.e. 44,745) please guide me how to change as date format in SP List. Thanks
Awesome April, How do I use a lookup value in a calculated column? By default, lookup columns are not visible to choose. Any workaround other than PowerApps?
Sometimes Universe tell you you're on the right way. Since some days I'm coming back to SharePoint, I always find a useful video from you April : starting from Lists to PowerApp through Flow etc. That's incredible on this Friday, last day before my holidays, I found your 1st UA-cam video 🥰 You're just an angel on my way to level up, so thanks a lot for that ❤️👍🏾 Thank you for sharing and keep growing, you are an inspiration 😇 Greetings from France
More than the technical information, I appreciate that you mentioned it was your first video. I find it so inspirational. As I want to share content as well.
This kind of worked and kind of didn't. If you already have data in the column, such as a selection when the item was active, later when you inactivate it, the data is gone.
Hey April I need your help. I have a "Status" column and next to it is a column called "End date". I want that when someone selects (from the choices in Status column) "Closed", then "End Date" column should show "The current date" else it remains blank. Please help April.
Wooho. I see what you did there. Smart. Thanks April for showing this. I was looking at this to solve one of the customer query raised today only.. I would say it was relevant not only back in 2018, it is so even in 2022. Thanks again.
Great and simple video and just what I needed. Anyhow I had to replace comma with semicolon to get it work. Somehow I don't manage to get the result blank as I need to. Instead all empty fields has text "No". What could cause this?
I want to set my Item column to only output selective choices based on my category column for example. If my category is "Documents" you will only see "Financial" or "Contracts" for Item options. Is this possible?
Hi April, This was super helpful. I've tried it out, however I ran into a snag in that the Calculated column isn't updating when the 'Status' column is updated. Will the calculation not auto update? Would I need to update the calculated column using a flow?
Hi April! After you select the available job in field reports, can you set another formula to update the item in the job list to closed or blocked in the job list? For example, if you were only allowed to book 1 active job at a time. Or do I need to use Power Automate for this action?
Hello April, I am trying to figure out how to filter more than 47 items in a column. Apparently, I cannot select more than 47 items without getting an error screen result. I need to filter the Date column per Year, but each date is different so the Filter By feature lists each one, and it only allows me to display 47 items selected.
it is a great video indeed... important thing to learn... can u please let me know how to fetch people from sharepoint column in my combo box? say i have saved "people1" in my sharepoint list people column and now i want to fetch the same in combobox to update the column... i am not using form
@@AprilDunnam thnaks for the reply... yes i have seen this video earlier... but my issue is how can i fetch the people column value from the sharepoint list... i have saved it to my list and now if i want to update it how can i fetch it from the saved record... can u give me an idea / process plz
You can do that with a conditional formula in the form settings. Click the "New" button in the list then "Edit columns". There's a set of dots next to the column and you can put your conditional formula there. For example =If(ThisColumn = "This", true, false)
If i change the status of the person from active to inactive, the field will become empty. As a result I could not able to see the history of that particular person in the list which connected previously. Do you have any other idea to solve this? Thanks.
I just put "Inactive" at the end of the formula instead of "". That way when you mark them inactive, instead of being empty it still has something you can click on and see the details on.
@@mikepeterson1904 Thanks for reply. I don't think it's efficient, I can try to figure out of my situation. I have details some numbers of employees in the list (list A). Each employee working in different construction site. So i need to connect each employee in construction site in another list (list b). I want to see the employee remains in list B eventhough they are inactive (fired/resigned). The problem is starting here, If the HR changes the employee from active to inactive (in the list A). The field of inactive employee become empty in the list B.
Hi April, I came across this video which is kind of what I'm looking for. I need a look up to filter items created by the logged in user. It seems that a calculated column won't work. Do you have a suggestion on how to accomplish this. Thank you for your help!
Hey Olga - Yeah you can't use the CreatedBy field in calculated columns. For that, I think you need to look into customizing the form with Power Apps because you can create a dropdown control that has that filter in there.
I've honestly thought of taking the video down because the quality isn't up to speed with what I'm used to these days but it is still a relevant video. Glad you found it :)
What version of SharePoint are you using? If you're on SPO then it will show a single "Blank" option in the lookup which is normal but you shouldn't see multiple blank options
Great video, very helpful and definitely the best out here on this challenge. You presented this issue (at least for me was an issue) in such a simple way that I would like to see more videos like this. Super helpful. Keep it going!!! I only wish that I knew this earlier - before I linked multiple SharePoint tables without this function to PowerBI reprot as now I have to re-do the whole PowerBI report. Cheers,
Hey there - this blog post by Matthew Devany should help: matthewdevaney.com/power-apps-patch-function-examples-for-every-sharepoint-column-type/patch-a-sharepoint-hyperlink-column-in-power-apps/
Thank you for this video! One question. When using the field, it shows a lot of blank choices before the real options. I mitigated to show a "Z" so the choices showed first, but now there are several Zs at the end of the list. Any secret on just making those options null versus blank?
I noticed that April did not use "Enforce Relationship Behaviour", on the last step, when creating the lookup column. Maybe this is the cause? Just a guess!
What about if i want to add one more status to the column - completed. The connection in field reports will be lost ... how to prevent that ? I want to select only active items but dont want to lose connection to selected ones.
ok one more problem - when opening the element the column is not there. I managed to display it in powerapps form but still cannot click it to get a hyperlink.
it's been a long time since I broke my head to get around this need ... Thank you very much. I have just taken a giant step forward ...
So happy that you found this and it was helpful for you Philip!
How to filter a lookup column based on a previous column value? Ie previous column selects for active, inactive, or cancelled, then our new column allows to select only for jobs of that previous column status.
This exactly what I need to know. If I use this approach, though awesome for Active and Inactive statuses, I can't how to deploy if I am looking to filter on other data.
If anyone is wonder what happens when you change something from active to inactive in the source list, your lookup column value goes empty. However, SharePoint retains the lookup reference ID (i.e. the lookup isn't broken, the value is just empty), so if you add a second column pointing to one of the other lookup columns (see timestamp 3:36), it will still display this information.
It took me a long time to find your video to solve this problem: How to only show active items in the lookup from the SharePoint list. Wow! You are the best and thank you for sharing.
Thanks April. Simple and well explained. And yes, even in 2021 you can still find people who will need a great tip like this :)
Thank you! Glad this video has stood the test of time!
Hello April, thanks a lot to share your knowledge with us! Is it possible to create a cascade lookup with 3 columns (Countries, States, and Cities) looking in 2 others lists? E.g.: Countries, States, and Cities using a list for Country/States and another one for States/Cities? Thanks again.
Thank you! very simple and helpful :))
Thanks Rayen, glad it was helpful for you!
Thanks April! this is actually a saver... though i added a calculated column for filtering teh data in the views for active/open positions only but using that for lookup didnt hit me :-)
You're very welcome, glad it was helpful!
The formula doesnt seem to work for me, i double checked a bunch of times and im using the same syntax as shown in the video
Thank you, this is smart and simple. Please, do you know how to open a dropdown menu (vlookup) and avoid repeats values?
Thanks Pedro. Unfortunately I don't know of a way to accomplish this same thing with a Dropdown control in SharePoint
Hi April, this is good informative, kindly let me know, lookup field not working for date format its visible number format (i.e. 44,745) please guide me how to change as date format in SP List. Thanks
Awesome April, How do I use a lookup value in a calculated column? By default, lookup columns are not visible to choose. Any workaround other than PowerApps?
Sometimes Universe tell you you're on the right way. Since some days I'm coming back to SharePoint, I always find a useful video from you April : starting from Lists to PowerApp through Flow etc.
That's incredible on this Friday, last day before my holidays, I found your 1st UA-cam video 🥰
You're just an angel on my way to level up, so thanks a lot for that ❤️👍🏾
Thank you for sharing and keep growing, you are an inspiration 😇
Greetings from France
Thanks so much ❤ I'm glad you stumbled across my first video and it was helpful 😊
More than the technical information, I appreciate that you mentioned it was your first video. I find it so inspirational. As I want to share content as well.
Hello April, I am trying to get to Auto-populate several lookup columns once a value in one of them has been selected. Is this possible?
This was very helpful, one question, what happens to the records on which I selected an active value that is now no longer active?
This kind of worked and kind of didn't. If you already have data in the column, such as a selection when the item was active, later when you inactivate it, the data is gone.
Hmmm that's a bummer, haven't noticed that behavior before with this.
Great Video. Couldn't find this anywhere!
Thank you Gerard!
Im getting a syntax error on that one (SP2016 on premise)
How to filter out lookup column based on people column in SharePoint
Can I connect lists from 2 different SharePoint sites ?
Hey April I need your help. I have a "Status" column and next to it is a column called "End date". I want that when someone selects (from the choices in Status column) "Closed", then "End Date" column should show "The current date" else it remains blank. Please help April.
That's a use for the new form formatting conditional logic that I did a video about recently: ua-cam.com/video/ECK5ObT8Oc4/v-deo.html
is there any possibility of fixing the lookup in the new item from the URL? in your example, set the Jobs column by URL in FieldReports? thanks!!
Wooho. I see what you did there. Smart. Thanks April for showing this. I was looking at this to solve one of the customer query raised today only.. I would say it was relevant not only back in 2018, it is so even in 2022. Thanks again.
It's 3 years back, but still useful, thank April
Glad it's still useful ☺
Great and simple video and just what I needed. Anyhow I had to replace comma with semicolon to get it work. Somehow I don't manage to get the result blank as I need to. Instead all empty fields has text "No". What could cause this?
I want to set my Item column to only output selective choices based on my category column for example. If my category is "Documents" you will only see "Financial" or "Contracts" for Item options. Is this possible?
Hi April,
This was super helpful. I've tried it out, however I ran into a snag in that the Calculated column isn't updating when the 'Status' column is updated. Will the calculation not auto update? Would I need to update the calculated column using a flow?
Hi April! After you select the available job in field reports, can you set another formula to update the item in the job list to closed or blocked in the job list? For example, if you were only allowed to book 1 active job at a time. Or do I need to use Power Automate for this action?
Thanks for the video! A valuable demonstration of how recombination will create new effetcs in SharePoint. Great hack!
Thank you Daniel, glad you found it useful!
Hello April,
I am trying to figure out how to filter more than 47 items in a column. Apparently, I cannot select more than 47 items without getting an error screen result. I need to filter the Date column per Year, but each date is different so the Filter By feature lists each one, and it only allows me to display 47 items selected.
it is a great video indeed... important thing to learn... can u please let me know how to fetch people from sharepoint column in my combo box? say i have saved "people1" in my sharepoint list people column and now i want to fetch the same in combobox to update the column... i am not using form
Shane Young has a great video on combo boxes that I think might help: ua-cam.com/video/pjs0ZsnJZXo/v-deo.html
@@AprilDunnam thnaks for the reply... yes i have seen this video earlier... but my issue is how can i fetch the people column value from the sharepoint list... i have saved it to my list and now if i want to update it how can i fetch it from the saved record... can u give me an idea / process plz
Excelent explanation. Thanks!
Any time Victor. Thanks for watching!
I want to hide column based on value of other column
You can do that with a conditional formula in the form settings. Click the "New" button in the list then "Edit columns". There's a set of dots next to the column and you can put your conditional formula there. For example =If(ThisColumn = "This", true, false)
If i change the status of the person from active to inactive, the field will become empty. As a result I could not able to see the history of that particular person in the list which connected previously. Do you have any other idea to solve this? Thanks.
I just put "Inactive" at the end of the formula instead of "". That way when you mark them inactive, instead of being empty it still has something you can click on and see the details on.
@@mikepeterson1904 Thanks for reply. I don't think it's efficient, I can try to figure out of my situation. I have details some numbers of employees in the list (list A). Each employee working in different construction site. So i need to connect each employee in construction site in another list (list b). I want to see the employee remains in list B eventhough they are inactive (fired/resigned). The problem is starting here, If the HR changes the employee from active to inactive (in the list A). The field of inactive employee become empty in the list B.
Thank you. That was very helpful.
David Pleasant - Thanks! Glad you found this video helpful!
Hi April, I came across this video which is kind of what I'm looking for. I need a look up to filter items created by the logged in user. It seems that a calculated column won't work. Do you have a suggestion on how to accomplish this. Thank you for your help!
Hey Olga -
Yeah you can't use the CreatedBy field in calculated columns. For that, I think you need to look into customizing the form with Power Apps because you can create a dropdown control that has that filter in there.
Great topic and superb explanation. Thank you very much for your help in doing this tutorial 👏👏
Glad it was helpful!
Genius solution. Exactly what I needed. Thanks so much!
You're welcome!
Hi , I am starting to learn this powerautomate...
What type of code we are using in that formula (expression)
Hey there -
It isn't really code. The closest thing to compare it to would be an Excel expression
Oh wow! I just found this, your first YT video, had no idea. Really good video.
I've honestly thought of taking the video down because the quality isn't up to speed with what I'm used to these days but it is still a relevant video. Glad you found it :)
It's very helpful for me but it shows blank value in lookup field. can you please help me with that?
What version of SharePoint are you using? If you're on SPO then it will show a single "Blank" option in the lookup which is normal but you shouldn't see multiple blank options
Super helpful for the project I am about to start! Thank you, April!
Thanks so much Ashley, glad it was helpful! Best of luck on your project!
Exactly what I was looking for! Thank you!
Glad I could help!
Great video, very helpful and definitely the best out here on this challenge. You presented this issue (at least for me was an issue) in such a simple way that I would like to see more videos like this. Super helpful. Keep it going!!!
I only wish that I knew this earlier - before I linked multiple SharePoint tables without this function to PowerBI reprot as now I have to re-do the whole PowerBI report.
Cheers,
Thanks Marcin! I'm glad this was helpful - sorry you're having to re-do stuff though 😬
This is exactly what I needed! Thank you! :)
You're so welcome!
you made my day
That makes me so happy!
Excellent.
Many thanks!
Super bueno.. un saludo y gracias!
Thanks for watching!
April please I need your help
To patch SharePoint Item from mail Hyper link
Hey there - this blog post by Matthew Devany should help: matthewdevaney.com/power-apps-patch-function-examples-for-every-sharepoint-column-type/patch-a-sharepoint-hyperlink-column-in-power-apps/
@@AprilDunnam thanks 😊
This is a damn good first video and a super useful solution. Good job!
Thanks so much 😊
Thank you for this video, it was sooooo helpful
Glad it was helpful Vincent! Thanks for watching!
Thank you for this video! One question. When using the field, it shows a lot of blank choices before the real options. I mitigated to show a "Z" so the choices showed first, but now there are several Zs at the end of the list. Any secret on just making those options null versus blank?
Hmm this calculated formula should be excluding any blank values. I wonder if it's something wrong with the calculated formula
same issue form me showing blank choices, the formula returns blank string, so it shows .... mabe a configuration somwhere in the lookup field?
I noticed that April did not use "Enforce Relationship Behaviour", on the last step, when creating the lookup column. Maybe this is the cause? Just a guess!
What about if i want to add one more status to the column - completed. The connection in field reports will be lost ... how to prevent that ? I want to select only active items but dont want to lose connection to selected ones.
i figured it out. In article you wrote, you selected title www.linkedin.com/pulse/20140717203332-36740435-sharepoint-filtered-lookup-column
ok one more problem - when opening the element the column is not there. I managed to display it in powerapps form but still cannot click it to get a hyperlink.
Glad you got it working!
Nice one. Thanks.
Thanks!
you saved my day :-)
Awesome! Glad it was helpful!