SharePoint List Columns Calculated , Look up and People Picker

okay in earlier session we have explored that means uh what is the list what is the what contents are list then hierarchy here what hierarchy all those things we have explored in today's session we are going to learn what are the different columns and column type in a sharepoint list okay so moving here i will create a list okay so i will give the list name as a consider and yesterday we will explore that what is the how we can create the list from excel how we can create list from existing list or using the template okay so here i'm going to create my own list and i give the name to the list as a student list okay student data i have given the name as a student data and you can enter the details after that i say create so a student data list will get created here in that now now you can see here title as a default column why title as a default column uh there will be a separate section on that white title as a default column i will discuss on that okay so as of now you will not understand the title white title as a default column whenever we are in content type session then you will understand what is the file title as a default column and this question frequently asked in interview white title as a default part while moving here you can see here there are different column types okay you can different column types and what is the column type column type which is used to store the specific structural or format data single line text which will store the single means text character data twice means it will drop down date time multi-line person number so we will go and each column type and i will explore you and i'll tell you what is the purpose of each call okay moving back before that before going ahead i'll go here and click here on a listing so you know that add column is the one way of adding the columns and if we go into this setting this create column but i have recently have created i have not added any column but still you can see here but still you can see here there are the column section in this column section you can see here this title as a default we are not discussing one but why this one two three four okay why this columns are there why this default columns are here okay so i have not added this column okay i have not added this column but all these columns are there if we i go here and click on all atoms view as of now we are not in everyone as of now we are not in view we will discuss here is a separate session we will discuss on that views okay so now if we go here you can see these are different columns those are created automatically okay and we will discuss on that in coming sessions we will discuss on that so as of now i will discuss on the columns okay what i do i will i will discuss all the columns those are modified created and created okay why these columns are here so before moving that i'll just click here and i'll explain you what is the purpose of this additional four columns and those are automatically added so by default view i have where i can please look here okay we will discuss view means this this human name is all atoms all it's just a representation to show the data to show the data in a table format we will deep dive we will discuss in the coming session on a few next session will be on view so today i'll just show you the default view and in that i will add you the columns created created by modified and modify okay for your understanding okay so after that after that here you can see the those four columns are automatically added here i'll say new here and i say text okay i say save if once i save you can see here here you can see created when few seconds back it was created who created this person created and who modified this person was modified that okay so it stored this preference using this column we can track the you know it will just track with what the changes made at what time those changes are made and who made that change i have recently added these two columns i also did this for this i have not added but by default it came here okay so now moving ahead let me go back here okay so this is the section uh through which we have explored the view is the separate session tomorrow next session will be our review as of now we are discussing again i will go back i can directly add the column from here also i will go here list and list settings okay so uh i will take only complicated columns those are uh views or there are many simple columns are there i will not take those columns okay so single line line text which is i'm going to take the choice column okay so choice say i will say student and this standard okay i will say standard standard okay and i will add few more options here require this column contain information you know yes then i will say here first second third four eight six okay consider my school highway uh up to the sixth standard okay so what kind of you want it in drop down you want radio button i will take the radio button default you want any option already check no i don't want any option to be checked okay you like in default view same default default view without all item we'll discuss on if you whenever view is there you don't get planning we'll discuss more on it okay so default few means all items okay i have added that so you can see here that column is added here standard okay now after that uh i'll just show you how it's look now i have said take a radio button okay so i have click here i click here and i say around which standard you want video we have video button and the purpose of the radio button what was the radio button you will provide the only one selection okay you can select only one option you don't have rights to select multi options if the viewpoint is there okay so i have selected a course standard and i have saved it okay so you can see four and i have said required field that's why you can see there is a question mark in there see the meaning of this question mark is there he's saying something missing some data is missing if i fill that data here let's save this you can see here okay then the question mark will be just vanished okay means information has been filled okay moving ahead now i will go in this setting i will take some columns those are means [Music] some difficult columns okay you know the purpose of the single line text multiline text then choice then now number okay i'll go here and take a number and i'll say i will take here mobile number or see if we take a number then it will be comma separated or it will add the comma okay so number uh say number thing means i will take here so this is number if you scroll down here uh the mobile number is uniquely unique means the unique value unique value the same value will not appear in same value will not repeat enough other items means other items means the list column items list row item sorry list row or the list row the same mobile number should not hit the repeat it will say that this mobile number already used then minimum maximum capacity i will say that mobile number should be in between consider uh how many digits are here what was your tangent three six eight nine okay and in between in between okay so it will be in between uh these two so you want decimal points no zero decimal okay any default value no this is a common column formatting we'll discuss afterwards every see we're saying uh indexing because we have selected unique that's why it's same indexing okay so i have added here mobile number you can see the mobile number now again i will add few more fields i will go here then you know the currency currency is for the unsafe fees fees paid then i take the colors here yes required field okay and there is no minimum maximum limit do you want to put decimal yes to decimal okay currency which format i will say indeed [Music] again i will add few more columns and then we will move and add the list item i'm saying list item okay i'll you need to provide descriptions a student i say short form you can uh right here date of birth okay after that require this column containing information yes date only or you want to provide time okay standard or friendly okay i said friendly here no problem any default value you want no no default value i say okay here okay so after that after that i have put here the ob and now so i'll go back again i'll add few more columns here so important thing here you can see the lookup column okay look up one i will come to this point yes no means boolean field okay so now i will take this lookup person and group okay calculated okay three columns those are a bit complicated and so now before that what i do i will show you what contents are there okay what is our list name student data okay so here i mean store data now see here okay run data i will go here okay and say edit form is there okay form is there now this is standard then enter mobile number if i put the smaller mobile number okay then see if he is paying fee speed means something is there it offers okay so this is the number here i put the random sorry so here again i'll select the top now you can if you want to attach something the student regime or anything you can attach it okay and save see what it's saying the value you feel must be because we have provided the range in that between value should be there okay so this mobile number i will put like this okay so now it will get saved now this information will be saved as you can see but you can see here mobile number with the comma value okay but the comma will not require this comma comma you can take a single line text and you put the column validation how we can put the column validation in the column we discuss on that in coming section okay so before moving ahead now now again go back and we'll discuss on column lookup column okay we'll discuss and that column is a very important role okay this column will place very very important one see here now now here you can see there is a lookup column you can see the lookup column here okay so what it's saying information already on this list it's saying information already on this list once i click on lookup columns here there will be the change in the properties of the column types okay so you can say additional column settings you can see here there will be some change based on what column type you select depends on that the below section of the property will get changed okay so here what mean by lookup lookup means look into the order list look into the other list so our list name is student data our list name is student data and i want to fetch i want to fetch some other details from other lists okay so this is the student conservation list and i want i have that list see consider consider you just consider this is one list okay this is one list okay and this list contains some information okay and this list of relate to the different list okay this list means this list data we can use at a different place okay so let me explain you properly consider consider student data and this student data selected the different subject okay this student will select the subject specific okay uh consider this student select physics chemistry math okay and there will be the associated information okay means you select the student and based on that the other information will get automatically populated okay means see there will be the there will be a drop down like this so what i would like to explain you consider i'll go in excel okay to explain you the lookup very well i'll open the excel and in this excel i will discuss on that so consider consider okay so consider here okay consider uh here i'll say subject okay subjective and here sub name contact then duration there is some information okay so these informations are there so this is subject name say say map then teacher then freeze this additional fees for this subject this is the contact number of feature and this is the duration how much duration he said so here again the science then [Music] this is the information about the sub subject okay so so what i want to explain and this is our student repository as earlier we are discussed this is our student details okay this is our student repository so student okay student data is our strong data so now now what happened here we have the name then say standard then contact this mobile number fees these details are there okay this is the input and here i'll say these fields so here i will select specific few more columns subject okay subject subject subject here now what happened i am featuring this subject okay so consider i will say student in this school there is an additional additional subject of column what is the name of column additional subject means additional subject now but in this this information will be fetched here and it will come here okay this you need to select here map see if you select your math okay if you select your map then what happened the associated information with this map these three things okay that will automatically come and attach here that will automatically become an attacher you can see what i am selecting i am selecting the math only but associated with information related to the map so this student selected additional subject as a map and then the sham fees contact and duration will automatically pop up here okay so how it will be possible this will be possible through the lookup column okay look up also lookup column what it mean by the purpose of lookup column it look into the parent list we called it as a consider we called it as a in some cases we called it as a master list in some cases we called it as a parent please try to understand well some cases we call it as a mastermind as you wish you can say master list of current list so in apparent list and this is a chinese okay from china imperatives and i'll take a look up as this subject name as a lookup column okay subject name has a lookup column which i will create separate list with a subject info in that subject name is there in that column related right my rule is whenever i'm working on student data i'll just select this subject okay this will appear as a drop down in this list okay so how it will work how this lookup will work going back into the assignment here we are here so now i will create separate list and the list name is subject info site contents now here [Music] list now i'll say create here and i say subject i will go here listed again i'll go here here the title is the column i just go here and rename that title you can rename the title use the existing column or you can take if to avoid the confusion no that is not a case we can look up any column subject name single line text we can create the columns okay subject name what are the additional column teacher name page so here teacher name feature is the part of the school so i will select people and group here if you want to select single line you can select we are not discuss on people and we discuss [Music] duration okay i'll take only please try to understand student data okay and uh subject input both are different list okay and then this is the master list from this list we are going to get some content into 200 don't be confused i quickly added little things using the video in spite of keeping on the form and okay so i will do here uh i will keep this blank subject name math teacher name here subject name science then teacher name is considered [Music] so here is the question uh is saying experiment because title is the required field that is the required field that's why it's showing here required next week okay next information so i will put some details here so don't ignore this title we are not in the sub i just put for so many geography here all the information is here now we are in the subject info list and now i will go into the strong data now i will go here okay please try to understand both of these are different now here i'm going to take lookup column now i'll go in the list setting from here click on listening now i will select here create column now i will say select additional [Music] select additional additional subject okay now it's time to take a look after selecting lookup see here description here select subject okay now require this column containing information yes you need no maybe many students left same subject that's right so these things are very important from where you want to face the details okay from here means we are in this list now we are in this list but it's asking there are many lists in my site but it asking from which list you want to take this column okay so i select here say here yes with this subject in full stage there you can see so which column you want okay which one so why i want the column as a subject name here the subject name column i will face the subject okay you want although multiple no single selection after after fetching when you fill that information what are the additional information you want so i want the duration so i want uh i want title is also here subject name okay so this name is already there so we face the duration duration in the additional column but you can see if you can check that you can you see that we have added but unfortunately teacher name is not exist here if you go here teacher name is not exist here okay this column is not here why because lookup will not support the column sorry sorry lookup column support only currency number single line text this type of columns okay so we go here and i have selected the duration now i'll say okay now after selecting that you quickly understand that what change happened here i'll go here in the list run data i will read already you can add new entry no problem you you just say edit you can see here additional column select additional subject if i click here matrix if i see map and save this here see the change you can see here in my list here is the map and associated information this is a title column this is a title column and this is a map this is a duration column all will be fetched in this list all the information you can see this information and this i have selected map but the duration will automatically come up with the map okay because i have selected that if i click on the map it will redirect to the source list or destination list or master list or parent list the content from there you can see here math open okay so again again i will go back i'll put some new entry here i'll select here [Music] here fourth standard now here select the subject i said physics you can see the difference here this it will automatically fit some details okay okay so if you can see phoenix is here and the information duration will advertise title is also automatic page okay so like this way through the lookup it will automatically fade it get the information look into the parent this master list and create the contents okay you understand this lookup lookup is somewhat similar to the uh free lookup function that we use in excel input data from external sheet using the reference value you can say that i i don't have that much exposure in excel so you can say that okay so i hope this is the concept of lookup okay you understand that again we'll go here listening next column type so after look up i'll create great column and now uh data so person and group okay so we'll discuss percent so i selected that that student okay and the person and group so student if the student will select the class teacher or he wants uh what we say or student having option to select his own teacher okay or mentor we can say mentor teacher mental teacher means that the student has option to the teacher he like as a mentor okay so i have selected that and i have selected here people and group okay so people or group it will face the information okay from here there will be the uh azure active directory our azure active directory as your active directory there it will face the content to the sharepoint okay this is our sharepoint and it will display the people picker information from here is whoever added in the azure active directory his name will pop up there okay so i want to select the mentor teacher so that in a column continue information yes many people many students select the same teacher as a mentor okay although multiple selection if you want to select multiple teachers you can people only or group people will discuss on this group and people section here i will select only people okay after that what additional details you want to show okay i will say is what kind of information you want to display about the teacher will say name or name with presence is general use this if the teacher is online or offline or his id or his account name okay any kind of information we can use okay and i will say okay if you go here you can see here i say student data here once i click here i select here round first time student mobile number the kube if they want some additional one so they can select the name okay they have what they have they can select the house distribute both the teachers here okay and they can what to do means they are having the option to select the teachers and teachers are the part of the school teacher are the part of our school okay and from where so our school have it as your active directory directory and in that the information will be come to our sharepoint sharepoint service through the and this is our sharepoint in the part of o365 and this then it will appear here how it will flows what is the user profile service this is the picture of user profile service we will discuss it afterwards okay what is the user profession is which will face this content from there so i just save this data so if you can see here if i scroll here you can see foo bear okay and now see that it will likely show me the details all the details of me have all the details this related to they have i can see here the linkedin profile show her profile match show more this entire detail of that mentor in the organization you can check his contact details then here the organization is organization that one is not enabled then if i share his linkedin profile you can fetch all the details of your mentor yes let all the details as he see you can select the details okay see what the files he shared and all this information show more you understand all the details related to the kubernetes here okay so that's why here's mentor for if in organization you want to select manager you want to select the manager senior manager or assign some assignment to someone then people pick a preview of organization and his information is stored in an active directory so we can select that person details personal information and through that people speaker okay you understand the people here now moving ahead then i'll go in now third column i'll select here you can see create column now that column is a calculated column okay that column is calculated now what is the purpose of the calculated column so if you have experience on excel in excel there are many calculation associated with the different columns like some we do the mathematical calculation date calculation text calculation any kind of a calculation with any data type those are performed in this calculated column so if i select here say calculated column i want to calculate the average percent or some percent of the student so what i do first before that i will create one column say math mark okay and i'll select here number i add two columns here map mark and i will say here okay then again i go back i can create one more column physics mark okay i added two columns and selected number okay and i'll see here okay again i go back here create column now i use here sum okay then i want the sum of these two marks then what i do i will select calculate one because i want to do a calculated information from the existing two column math and physics so what i need to do i need to say physics where is the physics mark and here's the math mark okay so here i put the sum sign okay so and what is the number output in which format you want or i want the output number okay i put that number here let's say okay okay so here you can see now now i'll go in student data so here i will add some details put here see that's why i need to fill those details then physics mentor teacher who will see the excellent teacher okay and then here mark so he obtained 45 and 79 where it will extract the sum of this now if you want to do the average marks okay again whatever calculated you can click here also check here calculate the column option is there click on mode after keeping one more then it will come to this okay and i will say average mark then when i select every month then i need to collect here click here average marks mass margin is calculated then average means sum of two mass divided by two so i'll select this math mark then click smart and plug this and divide this okay let me work let me check if formula is wrong then it will give the message that the formula okay so i have put the mass my mass okay so my entire result automatically pop up here you can see here you can see now is this correct no i haven't is a appear properly not there is some mistake because 45 plus 89 divided by 2 there needs to be the value arranging between uh 60 or 60 to 70 okay but here 89.5 sorry again again we need to go list list listening what is the mistake in my calculating column i'll go here again i will go here and select the average mark so what i do it divided this okay actually what happened it divided only this part so i will put in curly braces so it will first sum and then divided by two okay let me check the output let's run data now go here scroll down now it's working fine okay here are no details no match field here that's why there is nothing better now we are discussing about this calculated column these two are calculated one so calculated column is not about the sum and all the calculated column is having huge huge scope okay what is the scope of calculating let's move ahead okay now if you are working in excel same properties functions formulas are here in calculated calculated column okay so calculate the column in shell now moving here calculated column so you calculated two formulas you can see this block here or examples of the common column now you can see here calculated columns are here there are different kind of calculators you calculated okay if you scroll down see there is a first you can see if the column one having the information say column one contain fifteen thousand and column two contain nine thousand so the greater value column one is greater than column two then it will extract yes or no if column one is less than or equal to column two then it will say not okay means what output you want okay so what i do what i do i will see this is the calculation on number bits this is calculation on number okay greater than or equal to if you go down logical value after comparing the column if we say column 1 is 15 column 2 means column one one value is greater than column two yes means this thing is a true and here and operation means this is a true and column one is is less than this column one and column three column three and column this is the bond because it is the small value compared to the 15 means this output of this logical operator is false okay and then what we are doing we are doing the and operation on that then it will return the output false right no or false okay so this is logical operations are there so there are many kind of operations associated with this calculated column it will grow down display zero as a blank card or dash okay if column one minus one then what you want means if the output if the output return dash when the value is zero if the value if the value is a zero then return dash okay so it will put the dash in the value zero hide error value in column date time formulas seal date time if you want to add some dates and three days okay then this means consider we have date picker we have that figure and i selected that duration means if i selected the duration and duration is a five month okay if i say duration in the finals then it will automatically uh pop up with the uh course ended after adding the five ones okay so here you can see there's one column here we do the some uh add dates you add the date here you means here you can extract add the combination of days month years to date year math and days formula here what they are doing add three years and one month and five days to the particular date so how they and this is the formula for that okay so here calculate the difference between two dates so if you want to calculate that what is the age of the candidate in the days you can calculate the intensity each okay so date dated it means two days difference okay calculate difference between two times calculate this this is related to the date okay so again we scroll down mathematical formulas are there then subtract formulas are there multiply number divide number okay so all these formulas are there okay all these formulas are there and having different roundup then then text formulas are also placed very important if there is a if i want this small text i want to display it in upper text then you need to say upper function lower function so combination of first and last name many times this will require we need to display the uh full name last name first name and last name so all these kind of formulas are here so here what you need to do if you compare columns column compare column contents okay exact column one and column two compare the contents of the first two columns if these contents are matched then it will not match then it will say no if a match then it will say yes okay so that for that purpose exactly there compare content of column one and string bd one one two is then what output will be there determine whether column value other part of the specific match of a specific text this if this is the name column one contain this specific text this this name contain v yes it contain me then it will display okay this part of the character will be there so there are text formulas also there are many kind n number of formulas associated with the see sv field calculated if you go here you can see here not this estimate so field class is multiple custom how to customize reading the list so uh text formulas see all math formulas okay so this is the article i will give you the link of this article and if what you need to do you need to i will ask you and again i will give you some assignments on this so in in in this session what you need to do you need to work out on calculated columns and different formulas okay so i will paste this link here okay so maybe you have configured in your mind that that's uh see again i will explain you what you need to do while working on this assignment you need to create three columns column one column two column three and check that what is the output here once entering this details these all are the number columns again scroll down select the date take one date column take one base column check that and take one third column output calculated column see this is the date column and what i'm saying this is the date column and i will add three days in that okay so and check that what is the output in the third column that is our calculated column okay and in that this formula we need to enter again i will just quickly show you in today's date i will add 50 54 on any value okay so what i do what i do i'll create here one more column i'll show you maybe so let me check okay i'll show you one thing here substituting for now or subject data what i do [Music] i will create one column here i will create one column here so oh sorry i move here let me run data go into one data and i will add one column quickly here then list setting and i put here one column create column and i'll say [Music] course let's see here course start date okay and it depends on student when you want to start that date okay and date only and i will say okay then consider here i will add one more column days to complete okay this to complete and i will say number here okay and i'll say okay that student will feel that yes i will complete this course within 10 days 15 days okay and great column this when this course will expire expires okay this it will add that many days in that okay now see that created now now this scores x bar will be the calculated one sorry this is our calculator i don't know the formula i need to take a reference okay i need to take i will go into the date time column for the c here this is the formula column one plus column it's a pretty simple okay so here i need to select the column here first four start plus of course days to complete this how many days it will require to complete and then you know the output will be the date output will be the date it will add sorry it will add this co-star date and days to complete and that will be the my expiry date okay so i say okay and it will be the output is it date only now once i will add i'm going to run data i will put some entries there is a new here let's see test or some student name uh consider affordable regency standard [Music] i need to fill these details okay so consider this guy is going to start the course on 31st december and he will say i will require 80 days to complete and just save this okay then if we go in the last column here the course expirated automatically plus 80 days then date will be 21st 31st december then that will be the after 80 days it will be this okay this is the course and you can see here there will be the some some wrong date will be here because there is no data there is no data here okay that's why so like this way what you need to do you need to explore each column each column fill this and check that out tomorrow what what i will do i will do monday i will check that this many columns there you need to what you need to do while working on a calculated column you need to take one list and keep adding the columns keep adding the columns and check that don't create multiple lists okay don't create multiple lists just change okay date okay this is a number okay then there's output okay go into the next formula what is the formula difference between two data take the two date columns and check the output in the same number column what is the output okay add one more column so like this or complete this assignment so what assignments you are going to do you need to do what got on look up in lookup you need to take any scenario you need to take two scenarios okay and show me the how your lookup will work two scenarios then calculated column and then people i have shared link of a calculated one i want all these formulas implemented in your list okay take this formula take this formula take this column three number columns and check the output subtract multiple divided then if i scroll down here here text column again there are multiple calculated column in your list okay so i will check your outfit on monday so thanks we stop here and we'll discuss all my

Channel: Czarvision Technology Solutions

Views: 2,010

Rating: 4.7333331 out of 5

Keywords: html training, css training, javascript training, czarvision training, Office 365 Training, microsoft office 365 training, azure technologies, sharepoint online training, SharePoint List Columns Calculated, Look up and People Picker

Id: 4Suj7KY01Ec

Length: 54min 45sec (3285 seconds)

Published: Fri Dec 25 2020