Today we're going to discuss about how to capture data using something other than Excel, and that's called lists. Let's get started. When it comes to list, it's a part of Office 365. Most of the session today is going to be a demo. And as you know, this is a live event. Which means you can't really unmute yourself and ask questions. So how do we ask questions? We have a. Button there when you are. Attending the session you will see a button called add questions and that is where you ask questions. Remember to put your name when you are asking questions. Why is that important becauses? Today we're going to give prices for the best question. One best question. Get surprised and. We will also do a lucky draw based on the feedback forms you submit, which will be online. And for that also we will do a lucky draw and two more prizes. What is the price ₹1000 Amazon voucher? OK, so let's get started. No. What is list and where is it available? Let's start with that. As I said, I'm going to do this's entire life demo. Any point of time you have a question, please post it there. I have my colleague station who is also online. She is monitoring the question Anne. If she thinks that the question which you have posted should be answered immediately, then she will ask me that question while I'm discussing. Otherwise we will handle all the questions towards the end. OK, now how do we manage data capture on a regular basis? Suppose you have some data requirement, but you can't do it yourself. Generally we go to Excel. Blank Excel file. Put some columns there. Send that empty file to a number of people. Hopefully they will fill it up properly and then they will send it back to you. In effect, what do you have? Then you have multiple files. Then you have to copy paste those files. Not only that, the validations you can put while capturing the data are not very powerful in Excel. Just to recap, what are the validations available in Excel? We go to data and validations, no validations are there. I'm not saying no, but these have not improved in last. I don't know at least 20 years the same features. So what kind of validations can we do? Some kind of number and then we have all the parameters. If you know the range then we have decimal which is similar list which can be from a drop down list and you specify a range and similar datetime with between start date in Dec. Similar for time and if you are just accepting text, for example date or comment then you can limit the length of the text. And then custom, which is basically a formula which returns a logical value if the logical value returned is true. It will be accepted, otherwise it will not be accepted. While we're doing that, you can also give any message which can be useful. To say, suppose I have put minimum and maximum as one and 10. But when the person was adding the data is looking at that cell. There is no indication normally that this is the expected range, so someone puts 11. What are they going to get? They're going to get an error message like this. And then what happens? This error message does not tell me what is valid. It tells me what I have put is invalid. It does not tell me what is valid, so it's a bad idea. So how do you manage that? By the way, in Excel you go here. Now notice earlier had selected many cells and I've applied this validation of 1 to 10. But right now I've selected only one cell. So I want to change some settings not just for this cell, but the entire range. But I have not selected the range. That is a very common problem when you're customizing validations, so that's why this checkbox is there, so it knows where validation is applied, so it will select the cells across the sheet, not necessarily a contiguous range. Now mine, so in order to make it easier for people to understand, you should put the description. Which will help people understand what is expected. So now it's like a tooltip. There's also a good way to just put a tooltip. Don't put a validation, just put a message that becomes a tooltip. Having said that, suppose I want to make a particular column mandatory. For example, this was a column called item, and this was date and this was quantity. Now I want to make sure all these are mandatory. There's no way of doing that in Excel, so although there are some validations. It's a compromise whichever way you look at it, and even if it works the way you want, you're going to get multiple files. Now, yes, you can put this file on a shared drive or one drive or something like that and then give multiple people access fair enough, in which case people can type here itself. Same file, no copy paste. So the copy paste part gets solved. But the problem is if multiple people haven't given access to the same area, then what happens? Anybody can overwrite anybody's data, so that's a problem. Not only can people edit anybody's data, they can see each others data, and that's usually. A problem all of us have. Why? Because when you're capturing data from multiple people, you should be seeing all the data, but they should not be seeing each others data. Maybe it's different sales people, different regions, different product managers, different countries, different group companies, whatever, they should not see each others data. That's another reason why you are forced to send separate files. If you want to go one step further, some people again try to do some weird compromised by saying this is Region 1 sheet to his region to and then create passwords by sheet and give passwords to each other. Again, that's Shorty way of doing it. It will work, but soon you will realize that managing those password it's a nightmare. In short, for years or decades, for actually not years, whatever we have been struggling with, data capture in Excel has no easy solution. And that's why list is the correct way to do it. Now again, some of you may be technical people, so for those who are technical. This is SharePoint list. The entire concept of SharePoint is based on a fundamental principle of list list means something which has columns with specified type of data and lots of rows. Entire SharePoint works on lists. A document library in SharePoint is also a list. A SharePoint site is a collection of lists and so on and so forth. But that list feature never got the credit it deserves from the point of view of simple user level data capture. And that is why Microsoft announced it a little branded it better. And now when you have Office 365 you go to Office 365. Office.com login using Office 365. This on the top left Corner App Launcher. These kind of nine dots you will see in many mobile applications nowadays the technical name for that kind of menu is called a waffle menu. If you just say three horizontal lines, it's a burger menu. If you see three dots just three dots, it's called... Never mind. So this is App Launcher. Now if you go here. It will show you lots of tools and then all apps. And when you say all apps, you actually see what you have available and one of them is list. So that is what we're going to focus on today. So when you go to list it'll be blank probably. You will have to create a list and then share that list with people. Then people will enter data and we need to make sure the people can't see each others data or edit or delete each others data. All that is taken care of, but where is the data getting captured now in some browser page? Where do you eventually want the data in Excel? So don't worry for data capture, we're not going to use Excel for analysis. We're absolutely going to use Excel. Don't worry about that. So what I'm going to do now is a walkthrough of how this whole thing works. Now. If you are thinking of column level. Security. What I just talked about, his role level security if region 1 adds 5 rows in region 2, adds 20 rows, they should not see or edit each others data. That's a row level security. Some people also want column level security. That's a bit tricky. It is possible, but it is tricky. So if column level security is your primary goal, then this may not be the ideal thing. In one sentence, the solution is to create a custom view and do audience targeting in SharePoint. If you don't understand what I just said, forget it, we will see it later. Now let's create a list. What is the list list is a list, so we have a blank list from Excel existing list. Why would you want to use an existing list? Because if you already created a list and you want another thing with the same columns, then you really use it from Excel. We will see later. So why is it still important? Because you may already be using Excel like this. We already created something in Excel and I want to use that for future. For example, I already have this. In fact it already has data. Also an I want this to be the precursor of the new list I'm creating formally. That is possible, but right now let's see a blank list. Now when you go to blank list before you go to blank list you will see some templates there just for demo. Opens but if you click quickly want to see something which has data in it and. You want to see how it looks then. It's a good idea to go to this dialogue. Click on it and see what it offers. So notice this is recruitment tracker so there is a candidate. There's some position. And then there is a progress column it's mentioned who is the person managing this candidate and various phases. Who has taken the interview? What are the notes? And so on and so forth. You can also have a column which can accept files. You can have photos as well. So this you may not want to use as it is you. My requirements may be different, but what I'm asking you to do is go to this, click on each one of them, just crawl and see what it offers, not from the point of view of the exact columns, But what kind of columns are supported. And for example this is color coded, blocked is red. Oh that's interesting. Duplicate is yellow, so conditional formatting is available, so just by scrolling through this list. You will get an understanding of what is possible. We will see how to actually get it done, but just to get an idea. So first step go through these and you will be able to see for example, here we have photos which can be rendered as a part of the list. That is not possible in Native Excel. Now it is possible with custom data types, but that requires a specific new version of Excel. Everyone may not have that feature. Fine, so I'm not going to use any of the existing ones. I'm going to create a blank list, so. It's a live event demo that is the list, but what is the kind of list we're going to create? It's up to you. Let's create the simplest possible list. OK, simplest possible list, so I'm going to create a list with one column and few items in it. That's it and we will use that list for something more complex later. So let's say we're going to eventually create a list which contains some product details. But one of the columns in the product details is package. But the packaging has to be a dropdown and I want what kind of packaging is possible to be another list. So let's create a simple list. Packaging. Description you should give in real life, but right now I will avoid that. You can have a color and you can have some random icons. Not too much of choices or don't bother. I'll just choose something. And then where to save it to notice this is very important. This is my list which is the default place where you created. But if you look at this, these are all the teams I have so on. The time of creation itself. It integrates with team, so this will directly go to that link particular team. Right now I'm creating it centrally. Now when I say create, it doesn't ask you how many columns you want, it just creates it. It doesn't add a column by default. There is always a column called title. Now what happens? I'm not going to use any other column, I am just going to use this column called Title 5. Now in this column I don't want to call it. Title itself, I want packaging type so I can just rename the column here. That so our list is ready. How do you add an item in it? There are two methods, one by one you can add or you can add like Excel in gridview. So one by one is boring. Right now there is only one item here, so there is only one textbox. If there are multiple columns, each column here will become a separate textbox like a form. So right now I'm just going to add, say, box is one kind of packaging. Now I want to add another one, another one. I don't want to waste time clicking on new again, typing, clicking on Save, so go to gridview. OK, this is better so now like Excel I can type here. So whatever. Now, what else? This is that. So this is a list as simple as that. Now this list can be used in another list, which is what we will see next. But while we're here, let's just get the hang of a list anatomy. So this you already saw. I'm in Great view now so. I finished editing. I can say exit greatview. Oh, then I said, oh, there is a spelling mistake. What do I do now? What do I do now? I can always go and edit it. In the same gridview or one by one or whatever it is, so I'll intentionally. Make a mistake. And then I'll say exit greatview. Then I'll go again. Notice when I click directly without saying editing gridview, what happened? It's not in edit mode any longer. Of course I can edit from here also what am I editing now this part so? Done. Now remember this is SharePoint, so many features which you expect in SharePoint or all available here. So you see here or do we have? If you go to a document library, you can see open the document version history. All of that is available here, so this is just a one column list, but there is a version history even for that. So notice whatever I just did, there is a proper nice audit trail. Imagine doing that in Excel nowadays in Excel browser version this is available, but this is much more precise with an more convenient. So that's done. Now let's go to list again. Let's create another list. So let's say demo products. OK. So now what do we have another list? Let's make it. Red in color create. Now, this time we're going to create a more. Detailed list. First of all, this title column is always there. You can't really delete it, so it's a good idea to just rename it to one of the text columns you will need anyway as a part of your list. OK, so title I'm going to call it product. Now what? Now I need more columns, so remember in Excel, what kind of data we can capture. We just saw that report from a validation point of view. We have numbers, we have text and we have date three types. Basically actually technically speaking, date is also a number but never mind. Three types, but now look at this. This is very significant. What are the different types? So single line of text that's easy, simple textbox. Sometimes we want longer text. Description Summary of your CV comment something like that. Or summary of an interview of taken negotiation details, whatever. Multiple lines of text. Then of course we have very nice location that can capture location from the device. This works on mobile also. By the way, it's a browser based application. Number of course yes, no, which is specifically like a checkbox person. This is very important because you may want to say this product is going to be packaged by this person or ship to that person. Something like that. These person things come from your Active Directory, so this is Office 365 so all your staff will have a user ID so you can choose one more person from your company. Date. Time is obvious, but it gives you a nice drop down which Excel doesn't give. Choice, yes, that is what we will use for packaging. And then hyperlink, which is another nice touch currency. If you specifically want something image. Very useful and more so let's go to more. So this is a more comprehensive dialogue. Most of this we have already covered that more has one more option called look up. Oh, so that is what we wanted. So look up is. Look at the information which is already there and we will call it package. That OK now where is this going to come from? Get information from an. We have packaging which we just did an in packaging. What other columns technically there could be multiple columns but we have just created one column but remember behind the scenes it has other columns. Practically speaking we just had the default title column. We renamed it and that's it. An never mind. So these are behind the scenes. You can of course access them, so I'll just use this club. Do you want to allow multiple values? No. Add a column to show each of these additional fields if you want. If there were other columns and just for reference, you wanted to add some other column so that people know they are selected the right one. You could have added, but right now we don't need that then column formatting. We can do very sophisticated column formatting, but for that you will need to look at and learn little bit of Jason. But let's not go there right now. So that's all. Notice there is one very, very important thing here. Do you want this column to have unique values, which is very difficult or impossible to do in Excel? And most important with this column is mandatory. So in our case, yes, this column is mandatory done. So now we have added a column. Package type Now let's add another column for quantity. So this is quantity simple number. What kind of number? If you want it can get currencies, review how many decimal places 0 default value? No. If you want you can as a calculated value. That is generally based on some other columns we have. We have not yet got any numeric columns, so no point. Basically, you can put a calculation there, more options. What is the most important option you have to think of for every column at least? Is it mandatory or not? And then forget the content types part right now. And here we have some kind of column validation and you can put a normal formula. Yeah, the syntax is similar to Excel. It's a good idea to go and look at it once, but right now we're not going to have. So this is similar to Excel validation, but more sophisticated. OK done so. We have one more column at it. Now we can go on and on, but. I am not going to go there, but let's for the time being. Add something which is commonly not used in Excel. Some context so images one useful thing. Others are fairly self explanatory. So image. K. What is the time? Image. More options. Column required yes. And so and just to show you we already have chosen a choice type of column. But how do you add a? Column directly, which is of type choice. This is a very interesting one visually also appealing so. Status now this is a choice. Choice means there are choices. People cannot cannot add other choices. Default value more options now because it's choice. Let's first put some choices here. You can just go here and rename it three or default. You can add more. So let's say status. When is cancelled when is shipped? Something like that. Now as soon as someone exit, we want not started to be the default. OK, done. Now cancel should not be blue in color. I want to change the color, that's why there's a color palette here. So I'm going to choose this. This one has not started so will not give it red color. It just indicates something bad so not started is grey. Fine users can't add values manually. Now this is important. Is it a drop down or radio button when you're putting a grid type of entry dropdown is better. And if you wanted multiple choices, you could have given, this is again impossible to do properly in Excel. But we will not do that right now and we will just use this. And plus unique values, no column validation right now, no done. So that is how the whole thing is as of now. So let's say we are happy with this. And now we want to do something. Let's add, some entry will see both types of it. One is simple form, like entry one at a time. This should be used when the data entry volume is less. Where do you want to see it? Like a phone? So let's say product is, let's say, what masks. Package take notice. It gives you all this comes in a box quantity 1244 if I want I can upload an image Now let's see what kind of images are supported. So. I'm just going to put some logo. I'm trying to put Outlook logo. Let's see if it understands SVG. All this done if I wanted to, I could have added an attachment as well one or more, but right now I'm not. Done not as it is trying to show me the logo. There is something spoiled in the logo. Let me edit it. How do we edit it multiple ways? Just click on it. It goes into view mode. From here I can save it or I can. Click here 3 dots and say Edit or I can say it in gridview or I can select it and said it from here. Many ways of doing the same thing. So now this one I want to delete and add another image. So let's do that. Let's use list PNG. Done so. One entry. Now all this is easy. Let's try gridview. Now Greatview, what happens if there's some validation country when you present it is going to look at the validation. For example product and now going to say glows. Or let's say Senator. This comes as what notice earlier I was in normal edit view. Now I'm in great views. Of course there is a drop down an it shows all this. So I say box Metal no two alright then quantity. And then product photo now see here I can't upload because it's great view and now I'm going to be stuck. Why? Big cause. Because if I go to the next item, it says sorry this field is mandatory. Now what do I do? I'm stuck. So what do I do now? I can't do anything, so I had to discard this item, go and do it again. So these are practical things you have to take into account. Now my well, the time being what I'll try to do and salvage the situation. Is make this column non mandatory and let's see if it understands it live it does, But anyway the data is called. So. Now notice package table that we already got. Some number. The cell is read only, but now it's not mandatory, so it's OK. This order is also let's say in progress just to do something different. So when you press tab, it's going to. Run the validation and that is the time the row is going to get saved, so this is easy, no brainer. Now when I say exit, great if you notice what happens it is showing me this at this stage. Now I can go edit this and upload the picture which I could not do otherwise. So let me do that. I'm just going to put one drive logo. Done now, while all this is happening, this is the standard view. What is a view view is a way of looking at your list. The default view is called all items. I don't want to select anything, so all items. This is the views. This is a standard list. This is a compact list, is the same thing right now there was not much difference because there is nothing which is a lengthy column which is going across multiple lines, but it shrinks it a little and Gallery is like this where the picture gets priority and it looks nice. But if you want you can. You can. You can do what you can, create a custom view exactly the way you want. Fair enough. Now let's go back to all items. Will this is the list view. I can create other things also. In fact I want to add one more column just for one other purpose called data in time an. Deadline for shipment, is it date or time? Date and time. Do you want to include time? No friendly format, yes. Default value. Today's date. No calculated value and just let's leave it at that. So now if I want to add deadline, I want to do that for both of them, so I'm going to go here in the deadline one, it nicely comes. As a proper calendar item. And we're done. Exit Edit view. So far so good. Now I want multiple people to add data here. So like everything else in Office 365, whether it's a document, OneNote notebook. Or a team you can share stuff. So she had from here itself. There's a simpler version of the share dialog if you remember in office tools when there is a shared dialogue, it is a more complex thing. Typically it asks you various types of questions. When you share a document. Which is stored on OneDrive or SharePoint or anywhere in Office 365. This is the shadow dialogue because it's a document and there is more complexity or more. Flexibility in world. This is just a simple list. Here we get multiple options. Typically we use specific people, choose whether to allow editing or not, choose whether to allow me download or not. Things like that. But list is a comparatively simpler animal. So what is it asking who should be able to? Do this so I'm going to add sheesham. Who is my colleague who is on the call and I will also add one more demo user called Assistant. And then what? Yeah, we can choose what kind of control they get. So maybe my boss also should have access to it, but boss just wants to see. Now either I can choose and it or I can save you. So if you want to give you permissions to some people and edit permission to some people, you have to come here twice. So right now let's finish the edit part. So third permission and it means that it. Obviously if humans can't edit full control, means what? You can control everything which I just said, so you're like the owner of the list. Right now I created I'm the owner, but I can make other people the owner so they can change the security. They can customize many other things. Right now I'll just give them edit message and notify people. Yes, they will get a Mail. That's all that is straight. No file was sent. A simple link was sent to people. Now obviously you're going to use this. This is very useful. You will have many lists and very soon you'll forget whom have I shared this list with? Obviously so before we go and see how sharing happens, let us see how to remind yourself and control that sharing in the long run. How do we do that? Again? Go to share. This time I have not come here to share. This time I'll come here to remind myself as to whom have I shared it with so three dots remember means more options, manage access, and here it actually shows you who has this access. My name comes twice because I'm also the global admin, but never mind that. So now here this means honor. And owner. And I have full control. This one is editing. This is can be done now. Of course I could have seen all this from where else I could have gone here. This is called open the details pane. So this actually shows you the access and there is more details which shows you some audit trail kind of thing also right here. Very good, so from here also I can go to manage access and eventually land there only if you have created a sharing link. It'll appear here if you want to get rid of the link so that it no longer works, we can just remove it from here, but this is a direct access argument, never mind. So now let's see what happens on the other side. So Jason is already probably doing the data entry, but I have this guy assistant here, so this is the mailbox of assistant. You go there and look at what happened. Automail looks at this. This should have been a list logo. This is SharePoint now mine. Work in progress at Microsoft level open it. Obviously it's going to open it on browser and there is no application associated with it. Now we have a problem. What is the problem? I don't want this guys just tend to see other peoples data. That's a really bad idea. So how do I manage that? Angry part is exactly the same as what I've told you, but I don't want this at all. So what do I do now? So let's come back to the list 1st and solve that problem because if there was no security, people are not going to use this product. But Fortunately there is security. Riehlman settings. List settings, Fortunately there's only one option. Typically in SharePoint, they'll be seven more options. They're simplified it. Now this is a very scary looking dialogue. Don't worry, you don't have to go to all of them, you just have to go to one item which is called advanced settings. At what settings? Generally people get put off by the word at once at once. Means more useful features, that's all, so don't worry about that part. He had one sitting again. There is a long list of things. Don't bother all that we are interested in is this read access. Who can read? Items. Everyone can read all items, sorry. I just want to make people read items which they created and same thing for creating edit. Done, that's also two clicks and you're good to go. Make sure to click OK here, otherwise it does not get saved. So what did I do? I implemented security and how do I come back? This is settings. This is previous level which is product. So click on this then you're back. Nothing has changed here but now when I go to assistant. That person has already got the link, by the way. I'm open that meal was already open so clicked on that now notice what happened. There are two records we can see that, but this guy can't see it. So now as assistant I'm editing to save time, I'm just going to put it in Grid View and let's say. Glows. Package type. I pressed the wrong key. Edit In great view. Package type against a force. OK, let's go to another list and try it out. So I already have a similar list somewhere. We will use that. I have not encountered this error earlier, no time, no point in. Testing it out right now, so here is another list which is already shared with those people similar. One product quantity, packaging status, date all that. What is the sharing here? Let's try that again. I've list settings. It was, you know, the story now. Right rear access latex is done. Now. Coming back. Data entry who my wife shared it with. How do I know that? Click here. These other people, Nathan Boss session assistant, already shared data entry. So now let's go as assistant and look at that data entry list. Now when I go to list as another user, or do I see I see the list which are shared with me. So this is another one which was shared. So notice although as Nathan, when I went there are many records. What is happening here? As assistant, I'm only able to see the records I have added and the same thing will happen for session, so that is called role level security. You can choose the person, they can see their data job done, so that takes care of the security part of the list. Now as you know this is done and all good. Now what is the next step people are going to continue to add data now depending on the volume of data entry. Maybe people want to go to Gridview and they already have an Excel file. And they don't look the comma of the. Columns are matching. You can actually copy paste from Excel also in the gridview. To simplify this process. Now, while you are doing editing, if you do an mistake in violating before you say exit, there is 100 levels of undo as well available here. So if you make a mistake and realize it in the same edit session later on, you can potentially undo. That's a new feature which was recently added. Now my now let's come back and assume data is getting captured nicely, so that part is now beautifully done without the limitations and problems associated with Excel file security passwords. And finally, too much of manual copy paste. So now when people are adding data, what happens? So let's try to add some entry from assistant. Anyway add new item. So let's say. Let's call it live and just do. 111 what kind of item I want to add? Spray OK, What is the status? Cancelled order the date. Order what is the date? Something like this tab. Make sure it is saved now. This live event was added by assistant. Now when I come to Nathan. Let them see whatever happens is like I didn't even say refresher, so this is life. All good. No notice what happened here. There is some icon here. What does that mean? There is a share button here and there is an icon so you can actually add comments also to individual items and this can be very useful. When multiple people are working on the same list and so on. So that's one feature. Second is share. What is this the same share button? Like available everywhere. So manage access, same story, same thing. Now here there is a link giving access I can share from here control access. We have seen that but now having done all this I want to know who edited this because I don't seem to be getting the name of the person here. So what do I do? I want to see who has edited this, so I just want to have an extra column here, but I don't want to add a column Y. If I go to more, what is it asking me to do is asking me to add a new column. Do I really want to add a new column? No. Already there is a column because obviously it is controlling security, so it knows whether it I want that existing column to be visible here. How do we do that here? Again, go to settings. Settings list settings. We have gone there before and we have gone to advance settings, but if you just crawl down it actually shows what other columns currently visible. So title is visible, whatever columns we have specified are visible, but there is a created and modified by which is not visible, but there doesn't seem to be a checkbox here so that I can do something. But remember how did we come here? We were in a view called all items, So what are we actually doing? Data entry settings, all items view. So this is just a list of what is visible in the all items view. Don't worry, Scroll down. What is this? Now this is the all item view. This is the default view. It will be visible on mobile and this is also the default mobile view. So if you go on a browser, this is the default view. Technically you can have a different kind of view for mobile because mobile is less space. That's a very nice touch, so you create a new view and set it that one as the default mobile view. But right now we're not creating a new view, I just want to customize this view so I click on all items. That's hyper link. It should have been blue in color, but never mind. No, actually we get to see many more columns. So behind the scenes when you created just quantity packaging than four items forever, five one was actually the title which we renamed as product. Now remember in brackets that is called link to edit item, so this you can't really hide because that is the guy on which when you click you will get the edit option, so you repurpose it title, rename it and repurpose it with some text item. Having done that now, so many things are available here. Now, I'm not going to go into all of them, but just remember why these things are there because this is corporate data, so a lot of things which otherwise may not be aware of from a security and compliance point of view are actually behind the scenes being tracked and taken care of. So without going into too much of detail, if it's record then you will want to retain it for X number of years. Or there may be label label are called sensitivity labels confidential Top Secret. Those kind of things which will actually encrypt the data control control who can access it and so on and so forth. Very nice, but right now we'll just use modified by. That's it, we could have used more, but right now one is enough. So we say modified by. Now what do we do again? You have to go ahead and choose save will skip the other options and then we have done our job. So notice modified by was added. Another very nice touch here is very often we add columns and then realize oh that column should have been before this column. Absolutely painful to do in Excel here at any point of time you can drag columns. So this is another very good touch. Now, who am I, the owner of the list? This is assistant assistant. Also can change the columns. OK, now if assistant shares or refreshes this notice, what happened? What takes precedence? The base one? So if as assistant I change the input status first? Then quantity then packaging. Now I see it as nothing and I refresh what is going to happen. Nothing. So the control that is the difference between an editor and owner. If I was the owner then it will affect everyone, so that's how it is, so it's packaging, quantities, status, date. So if now this guy's done it for the time being. Next time this person refreshers. It's back to square one. Now, technically you can create a new view as a user also, so I can create a new view for myself an if I want to. I can make it a public view, but let's not go there right now. So come back now what has happened. I know who has done what and here station has added some assistant is added some and so. So now notice one more nice thing here. What happened here is originally when I created this list, I did not make the date mandatory. Later on I got a brain wave and said no, no date should be mandatory, but this entry was already done. So now it can't go and delete that entry. But it needs to attract my attention saying something is wrong there. A mandatory column has not been filled so this is a very nice touch. Now this may be a long list and you may have some items which have a problem. How do you identify those problems? You'll see this icon. This icon as I can order this, but they may be scattered across thousands of rows. So when you go to all items, notice items that need attention is a special filter, so this will only highlight items which requires some repair or some default values missing or some validation. Failing something like that. So that is item needing attention and there is a red dot there. Now if I go to a standard list also that red dot if I go to standard list and remove this is a toggle. This will show me regular list, right? All items. Even if I go to all items, it is going to show me that Red dot till I manage the show. So never mind. No. Let's manage that anyway. All items list. Turn on lights. Let's see. So even if I have not notice that there is something requiring that red dot means you need to do something. So anyway, I have identified that item. I will edit it quickly. I could have edited in many different ways. What did I do? I clicked here. So what where did it go? It did not go to edit, it went to what it went to. Details, no, that's not what I want. So first identify it and then say edit. Excel even though the my icon is Excel. When you say export to Excel what you get as download is not an Excel file and that's very important to understand. Many people get confused so I wanted an Excel file think. Microsoft knows what you want better than you know, because if this was an Excel file, it would be a snapshot of the current state of data. Sooner or later something is going to change in the data. Are you going to keep downloading again and again? That's back to square one where we're sending attachments. People were sending files back and you're wasting your life. Copying and pasting, we never want to do it again. That's why this is not an Excel file, but it's a file called iqi Internet query file. Don't worry about it. And Excel understands how to open this file. So when you click on it, it is still going to open Excel. Now, because this file is essentially coming from browser or Internet, it will give you this warning, that's OK. And now at last kill very important question, what do you want to do with it? Is telling you I will give you the data? Don't worry, the data will not be a snapshot by the other. Good news is this data is linked to the list, so even if it's in Excel we can refresh it. But how do you want it in Excel? That's the question that is asking. So. Table means it'll just be the raw data as you're seeing it here, it'll be visible in Excel, that's all, but very often. We do that. We get Excel files. We have the data and then we create a pivot table. Why do we do that? Because our method of capturing data itself was Excel. But now our data capture doesn't require Excel at all. So if you choose table notice what is going to happen, I will show you all the options but just to understand which one to use. So I did that or did I get? I got this data. This is of course the table. And the data has come here very nice. All good question is that data is already there. Do you really want to see the raw data or you want to analyze it? That is your decision. That depends on the situation. What are you trying to do with that data in Excel? For whatever reason, you want to see it. By all means use it. Not only maybe you want to see it, but you want to do some calculations on it. That's a very good reason. If you just want to see it is their own browser, was the point in having a copy here doesn't serve much purpose. But suppose I want to do something like there is quantity column and I want to say 10% of quantity. Now notice this is a table. Now I'm adding a column here I'm adding a column called percentage. Now can I put a formula here? Yes, of course I can put a formula. This is a table, so all of us know how to use a table. The column is called quantity, so I could have gone and click there or place left Arrow left Arrow. But you know the story now it automatically gives you this syntax. So if you are in a table press equal to start the Formula Square bracket. It will actually show you all right EMS which are there you have to close the bracket and multiply it by. Whatever 1%, whatever it is. So. Notice what does it say? Quantity is a whole column, it's saying do you want the whole column? No, actually I want the current row, so current role is indicated by the at the rate sign, so that at the rate sign you have to type. Just to clarify that again, when I'm entering the data notice, nor when I'm entering a formula in a table, you can enter the formula anywhere. By the way, equal to. Square bracket notice the first thing there is. This role, which you miss typically, so you said this role will continue to show the columns and now say quantity. So quantity from this row when we want, say, 10% of it. Or do we got that? Now what is the problem here? The problem is, is this column available in our list in the? Browser no. This browser has no idea what happened here. Even if I refresh, Excel is not going to tell this guy anything, so I don't have that column at all. So now let me add a new item and see what happens. I'm going to add a new item in Gridview. It doesn't matter what else is, there will just take something. Let's make it 7 seven. Let's say shift. And today's date. Modified by is read only because that. This guy will automatically manage done now. This new item says hey, whatever, whatever is it going to be visible in Excel, that's number one question. Second, even if that is available in Excel, the calculated column is not available here. So what happens to my calculated column question two? So let's answer both questions. Now this guy has no idea what has happened there. So how do I do when you don't know anything? Right click and hope that there is the relevant feature there and that's all you have to do. So it came here. Of course, beautifully done, so it's a one way sync. What happened to my column? My column is surviving and it also copied the formula, so that's called best of both worlds. This was a discretionary number you wanted to give discount to customers and you don't want the people who are adding the data to see the discount. This is the way to do it. You just want total control over the list locally, but it is still refreshing. Now while we're there. I want this list to refresh. Of course I can right click refresh, but suppose tomorrow I open it I forget to refresh. It's outdated. What do I do now? So what do we do? Get data from sheet. Now where do we go? This is a table right? So go to table end table or do we have there is a refresh button but this is manual refresh. I go to connection properties and here I have refresh data when opening the file. Very nice feature. What does that mean practically now? When you create a list, it's empty. When you create a list and share it with people security, all that you know when are you going to create the connection to Excel. Just after you created the deck list, might as well finish export to Excel. Like you I come to this it'll be empty. Doesn't matter, it'll show you collimating at that point itself. Change this setting to refresh and open. So next time as and when people adding data anytime you open the Excel file you will always see latest situation of the data. That's number one another scenario for refresh is that you are actually adding data. Lots of people adding data, lots of new data is coming and you want to see the live picture of it. They finish data when open will only wear or work when exactly when you're opening the file, you're kept the file open. People are adding data. Life that is not going to happen here. So refresh every 10 minutes is not available here. This can be available for other things, but that option is not available here, so you have to keep on refreshing. Never mind. So this is how you get live data in Excel. So once you create the list and link it to Excel through that IQR file. Then you don't have to go to that list at all unless you want to change something in the list. Fair enough, but now let's close this file. I'm not going to save this file. Why am I not doing that? Because when you're exporting data, let's assume you don't want to manually change anything in the data you want to create a report. Again. We will not do this because in my downloads that earlier Excel file must be already there or never mind will do that Excel. Workbook. I kill I open the file. This will create another new Excel file, by the way. Because earlier file I did not now notice what is happening. Or do you want a pivot table? Because you could have created chart, but generally it's more comfortable to create a report and then a chart anyway. Of course, standard options here new worksheet new workbook, let's say new workbook here itself in properties. Actually while you're doing the whole story, you could have had done refresh data on open. Fine, let's do that anyway. And now we'll say pivot table. Spell create a new file, add a sheet. And. Create a nice little pivot table here. So just for the demo purpose, I'll put quantity here. This will give me total quantity and I also want to see how many items have been added total, so I'll just go ahead and say summarize by count so we have a simple pivot table here which shows total and count. I just for demo purposes, let's create another pivot table here. Which is as of now it is a copy. But let's say different packaging an. What is what easy now notice I copied this pivot table and then. These two pivot tables are put internally sharing the same cache, so when it comes to refresh I can refresh either of the pivot table. The other one will get refreshed. OK, so far so good. Now let's see this in action. Now let's go back to data and add some items. Added some items so this 77 which was here. Let me edit audio edit. Exit great deal. Go to a particular item, either in edit gridview or in edit 1 by 1. Whatever it is doesn't matter. I'm just going to add some bigger numbers so that our data. Our pivot table gets refreshed and let's add one more item. Demo next time you spray. Best identify this item 999. Let's call it cancelled. Done. And this is it only lets tab save it. Now when you come back, obviously Pivot table needs a refresh. There is not a refresh in that sense. Of course pivot table also has options. Pivot Table also has something called data source. So change data source. And here also we have that data connection. This is the connection. It is using an. If you open this connection from here. The same thing now right click and. What in fresh 16 becomes 17 everything changes. Life is good. So this is the entire lifecycle of starting from scratch. Sharing the list with people and then getting a live report, but this is just the beginning. Now, where else can we use this very important question because remember this is a part of what this is a part of Office 365, so wherever it makes sense it will be used. What does that mean? That means the most important place you should be thinking of is teams. Why is that important? Because teams is for teamwork. So if I already have a team. I already have a team so teams as chat for ad hoc short-term work and teams as teams for more structured project management long term work. More in world work. So we have team. We have channels. Different channels how many you create up to you? What do you have in channels you have discussion? We have files and anything else you need to do that team work effectively. We can add by clicking on this tab, so obviously. Needless to say, list is available here, so now if I go to list you know what to do already. Adding list OK. So this is adding an application called list right now, so now it is saying you want to create a list or create an existing list. Oh, so? Or do we have we have some lists which lists if you had a link? If you already created a list and we have the link, we could have done that also. So you can create a list or use existing one. Let's create a list just to show you the process here. Exactly the same interface. So now let's say I'm trying to do it from an existing list. And what is the list or notice? Why am I not getting the list I just created the demo paint Data entry list or whatever we were using, which was the list we were using. The list called data entry. Why am I not seeing it? Because of the simple reason this is teams in the context of another user called assistant. So assistant is not the owner of that data entry list. That's why he or she is not able to seat. Assistant has created some list before, so that is visible here, so I can reuse my list, create a list. This name already exists, never mind. And now what happened? I added the list. I can customize it further. I already have the data, title, agent or whatever. But the important part is. Where is this list in my team? So where is the share button? There is no share button because it implicitly got shared with all the members of this team. So if I go to manage team I know what is. Who are the members, whoever are the members. Automatically and implicitly have edit access to that list, but now if you want that security stuff to be done, then you have a problem. Becaused right now that is not visible. So where is that list which we just created? In that list, if I want something to be done, I forgot where it was. Which channel was it? Now I want to change that security part. Where do I go? I have this here I have export, I have all items. Nothing. So then you have to go to SharePoint and customize it. That is the problem. But the functionality is very much there. So that is about list the other part of list, which also integrates with something else. When someone adds an item there, you may want something to happen automatically that can happen using power automate and you don't have to go to power automate you. Go to your list. So this is our list and let's say as soon as an entry is added, something has to happen. That's the workflow. So how do I do that? You say automated. When you say automate, there are two types of automated by the way. Set a reminder. OK. Create a rule. This is native to list, so let's see what can be done here. Show me how many questions Julia. Well, we have quite a few questions at around 30. OK, so after this I will summarize and finish will need time for. Questions answered. So I don't know why this guy is not working. So what this guy essentially shows is this. I have a screenshot to save time. I will show you the screenshot when you say create a rule, it actually shows you this, notify me. When any of these things happen so very simplistic. Event based notification. OK, easy. But there is more. What else automate manage rules? Create a rule? Now we're actually there. So these are simple and a column value changes when a column changes when an item is at. So let's say when a new item is created, send an email to. And me, that's not very easy, but this is not all, this is just a very primitive notification. The automate part is just a very small part of automation. The real part comes here. This is bar automated and now I say create a floor, see your flows obviously are not created, any flows. So in the context of this. Particular one, so it's just going to show me other flows, so I go here. Power, automate creative flow. So now it's not saying OK create a flow from scratch, it's saying oh there are a lot of flows which are already created. These are templates. So what do you want to do? For example, send a Mail when a new item is added. Now here it says SharePoint List instead of lists because as I told you this is not a new feature, it was called a SharePoint list earlier. So when you go to power automate. Don't look for the list icon, look for the SharePoint icon lists icon is not yet integrated, so there is a SharePoint icon list context. This will work, so these are ready made templates. You can even have approval so this may be requisition of some asset or some approval for person agreeing to interview someone, whatever it is. Approval and serial parallel approvals. Show more. You can even create an outlook task when something is added or tweets is not here. This is now list is becoming a target, so we have to look at what is the trigger, trigger SharePoint. So wherever SharePoint is first. That we have to look at. This is reverse. This is SharePoint for a new SharePoint item. It integrates with all kinds of third party product, so the trigger will be the list action can happen in 304 hundred different products including lots of non Microsoft products. So it integrates with Slack Trail or whatever. Hansel, on and so forth to explore those for the time being, I'm just going to look at. Customize email when a new SharePoint item is added. Now when you go there, it shows you the components of this workflow. SharePoint is the trigger and when something happens here, the action happens in Office 365 outlook. So you just look at it in automatically, pick up your log in. If it says try it out. Some error comes, you click on that. Now it is using this template to create the flow. Flow is a step by step process starting with the trigger. This is a pre created template so lot of work will already have been done for you. So it actually is showing you the floor which is already created, but this is by default you let us specify whose email ID what to put there. So so OK no problem we said it. Now this is the floor. When a new item is created, find out who you are. Because you may want to send a Mail to yourself so it needs your email ID and then send email fair enough so when a new item is created where so here it is showing me all the a list I have created. Remember we created packaging today we created the more products all that. In this case we're going to use the data entry list done now get my profile, it just gets a profile. There are no parameters you know of course you can have advanced options. List columns by view. So remember views you could have chosen different views and it would have shown different columns. Right now we're going to choose all items view. And then this is that collection. Right now it is saying only send a Mail fine. What is the Mail to home? Where is this coming from that I don't want to hardcode that email ID because someone else in my team also may be using this workflow. So ever. Is the currently logged on user that person? But here I don't want to do that, so I want to show you how to do this. So when you say 2 if you want to hard code I can just do that. But I also want to pick up whoever is the current person who created that. Hayden that is where it found my profile, because who is adding the item that was oh so now how do I get that person's Mail here? Get my profile. We did to get the email ID on that person. OK, so now new items are added. Ward. Forward. The list what is the list? I don't want to hardcode the name of the list also, so I get the title of the list also here like that. All kinds of things which are coming from or columns which we have created. OK, so for the timing I just put quantity here. So what happens now? Quantity I could have put any other thing there as well. So done now it has already done the job. New item was added. This is a function. What is this function doing? It's creating created by home, modified at what time, but I want to put some additional stuff. So I say what was that packaging? Which was the specific item. In my newly added item, how do I do that? I will go here and I will see. I don't want to see functions I want to see expressions or dynamic content. Where does that come from? We have two steps above. One step is called get profile. One step is called new items added. Look at this. This is green. This is red, so all the parameters which come from this will be in the red color. All the parameters which come from your will be green color, so these are red no. It also shows you the logo to tell you are these are SharePoint items OK so now what was the thing we were talking about packaging so like that I can actually compose a proper Mail. In addition to that, I want to expand this template and say I want to put a message in some teams also just for demo purpose. So these are different places where you can do all kinds of actions. So after sending a Mail, I also want to put something in teams. OK, no problem. So this is called actions. Look at the amount of items or products which are available where you could have done this action. It's a very, very long list of hundreds of non Microsoft and Microsoft Tools, so for the time being you can just choose teams. But technically you could have treated this automatically if you want it. So what do I want to do? Do I want to add a member? Do I want to get a message to these directions? I can potentially do in that team, so now I just want to post a message. OK, so now it's logging into teams just to get to know which teams I have access to, so post as flowboard, whether that's going to happen automatically, that's fine, or what else? Me who is the user, OK? And then channel I want to put it in channel. Channel is a part of what it's a part of teams which other teams we have. We have lots of teams here, so I'm going to use a team which we just saw called procurement and I'm going to use a channel called RFP for AI systems and here I can just compose any message. So I'm just going to put quantity: and you know how to do that. We know there will be something in the dynamic content called quantity. If you wanted to, you could also put something else. And search for it, scroll whatever. Done. So now our flow is ready. Save it, it's a good idea. As soon as you save it. Save it, but this name is a very long name, so list automation I renamed it. OK, no problem. Whatever it is, save. There is a flow checker which automatically checks if there is any error. If no error then it'll be keeping quiet. If there's an error, there will be a red dot, which will happier there, so it's a good idea to check if there is any problem and now we have to test it out. If I test it manually or automatically add a new item to list, OK test. No, it is waiting. I've added a column. Now I've added a new item no, so let's add a new item. So let's call this floor test. And just for demo purpose will add a number or quantity which is unique, so we understand it 123456789. And what else? Done. So now we'll come back to this guy. So notice this thing called test is running an it's saying yes, the test ran properly, all good, it created its creators create send Israel if you want to troubleshoot it can go here and actually check what Mail was sent. It will show where the Mail was sent. This was hard coded. This is the person I did it so all that has already been done. So if I now go as assistant because I added this person's name hard coded. Is he? New item added notification automatically came. Details have come all that then quantity has come and so on. And just to complete the picture we had also added something here. So when I go to RFP is actually showing me this. Perfectly done so that in a nutshell is less. So. We will take questions now, but before that couple of things. We want your feedback and there is a lucky draw so. System just to do that part. Yes, Michelle shared the link for the feedback in the Q&A panel. Please do click on the link and give us your feedback about this session. And I've also published master class on Office 365. It's for understanding how to use Word, Excel, PowerPoint, Outlook and OneNote effectively. On a site called Social swag. So if you want you can subscribe to it. The link for that also section will post. Now. When you are filling the feedback form, very important there is a name field. Put the field because what I'm going to do after you finish filling the feedback, I'm going to download the data. We will have multiple rows and where I live alone number I'm going to do a lucky draw by generating a random number and who's ever rule comes as lucky to those two people will get the ₹1000 voucher, so make sure you put your name there. Don't put your email ID because the names will be visible publicly. Once they announce the winners, then you can send the email ID to us directly or you can post it privately in the Kearny. For now, while filling the feedback, just mention your name. OK with that. Let's take questions. Yes, yes I'm good. We have 10 minutes with 30 questions. I don't think we'll finish in 10 minutes, but this is getting recorded, so if I exceed I will try to finish all questions. So if you have time, stay on if you have to go at 4:30, no problem. This video will be edited, cleaned and uploaded on YouTube for everyone to see very soon. So if you have to go, you will still not miss anything. Yes, she's good. I mean, start from the beginning. The first question is is list available in all O365 licenses, yes. The next question is if it is from data collection point, how do we differentiate between forms and lists? Farms is similar in that sense, but forms is more for service lists, is more for proper data capture. The way we do in Excel. The simple answer is if you wanted to capture data from people would have sent them forms. No form can only be filled as a form. List can be feel like a grid that's primary the difference number one second when you go to forms, the kind of fields which are available in forms are optimized for service, not for capturing business data. So when I go to a new form, what happens? I do get ability to define various data types, yes, but what do I get? Some of them are common, but I get rating. I get date and these like Kurt. For example, Net Promoter score are very specific to. Sylvie related functionality. So like it means what? I'll get an array like this, so content relevance to my job, quality of delivery and then option one bad, good, ugly, whatever. That's called a Likert. What is Net Promoter score? Net Promoter Score is those irritating questions we typically get informal service. What is the likely over to 1 to 10? Those kind of things? So this is more optimized for service and it has to be filled like a phone. That's the difference. The next question is can we import data in list from Excel? Yes you can. So if you already have something in Excel. So let's try that. Let's say I have some data in Excel. I'll take a small example just to save time. And try to demonstrate. So let's create a file. I'll use a new file. Put some data. History does values with formatting for the time being created table out of it. Siri does something on one drive. I'll call it list input just so that I remember it. Now what happens? I go to list. And English setter does ask me, do you want to create a list which is from Excel? So let's do that part list. New list. From Excel. So now. It says where is that file chooser file from your OneDrive very good? Or does the file we just created? Listing. Done. Obviously it has to be a table, otherwise it gets confused. All the you know why tables are important if you don't know why tables are important, session put the link to the. A blog item. Excel tables knowledge back. If you are using Excel without tables, you're misusing Excel. So anyway, this is showing something. Now also notice it is trying to decipher what kind of data it is. So in case sometimes it gets mistake. For example, some column in Excel there is nothing notice what is happening here. There's actually data and time now it understood because internally Excel stores numbers instead of dates, so that kind of stuff can be done here. Very good, now we say next and then say list. Created from X. That's it, you're good to go. No, there is data also in the list. So it will do the job, get the data and then you can customize the list using list features here and so on. Next OK, the next question is, while importing Excel file, it gives an error because of the title column. How to remove this? There may be some reserved words where those names are clashing with SharePoint requirements. Just change the column name before important. OK, the next question is which back method is better? Enter with Gridview or other metal? If the volume of data you are entering is just single item and there are lots of columns, then you want to see them nicely. Then form, which is basically new, is better if you're entering multiple items and you want the comfort of Excel kind of data entry, then great view is better. What is the limiting characters for single line and multiple line? I think single line is 255 multiple lines. I don't remember let me see. Whatever. So let me get a very large number. That's the best way to find limits for an abnormally large number. So that was single line of text. What does the other one? Multiple lines of text or options. Multiple lines of text. No limit. There must be a limit, but. No my. Start a character next. How many rows and columns can we add? Is there any limitation with respect to permissions or inheritance? No, there is no limit whatever SharePoint limits. If you exactly want to show, learn about how many rows SharePoint lists. Basically it's a SharePoint list. Whatever are the limits of SharePoint list, that number keeps changing. So just go and. Look at the current state. Next the next question is when people are choice. Does that become a part of the pick list value? Also, tick list value. Is there a possibility to have selection of multiple choice? Yes, there is a list of multiple choice. My used one at a time, that's why it gave me one. Technically you can have multiple choices as well. In fact, I think this list was multiple choice. So if I choose such a notice. I have selected three items here, so how is that control column settings edit column? Here. More options. Allow multiple selection. Next meant to use columnist choice and went to put it as a look up column as shown in the package type. And I think it's obvious now, for example packaging. Is this list of various types of packaging only going to be used in my entire career for this list? Then put it here. If there is a chance that that same list of packaging material or options is likely to be used somewhere else, then create it as a separate list. If you are not sure, believe me whatever you think is hard coded eventually will require reuse, so if it's a list. Create it as a separate item and use it as. Look up that makes it more maintainable in future. Can we import list into power be I directly, absolutely yes, because SharePoint list has always been a poor be as data source that also means power query. That also means if you have used the wrong way of editing list which is convenient, that also can be an pivoted and converted to good value. So bad list sorry, let me because someone asked. Let me just explain this part because someone will have a question like that. So the list requires proper columns and good data. Good data means one column should have one kind of data, but very often we have a bad habit. Off creating bad formats of data entry because Excel allows you to do anything you feel like without troubling him. So what is the problem there? The problem is. If you create a list like this and this, we do very often. I am creating this. Let me just rename it to product. And now I want products data across multiple months. So very very often people are going to say number and the name of this column will be Gen. And then. Name of this column will be sorry not single line of text. This will still be a number and this will be fair, but people will happily create well number columns like this. This is bad data column name should not have data name. But now mine. Unfortunately this is very convenient for data entry, so the convenience of data entry I can't deny. So as a compromise for simplifying data entry, let's say we lived with this format and I'll just put one more. Column here just to make it realistic. Now this data is going to be easy to type becauses. Let's say. And because this is like Excel, it can copy paste and all that you know and very easy to manage. But from an analytical point of view, this is a disaster. So ideally when you're analyzing this data, what should you be doing? You should not have this data because then Jan Feb March will become separate columns in a pivot table or whatever metrics you create. So ideally this data when you're importing in Barbie or any power query you should get this data and immediately do one private so that then it comes into proper three columns, product month and value. OK, so next. The next question I asked, why has Microsoft put in all these features in different apps and why not in a single product? How can you put all this in a single product? Why is word different than Excel? It's like asking that question know each product. Has a specific purpose in mind whose purpose are need, so we have to understand. Which tool to use when like someone asked? Technically, if I wanted just number and text, I could have his forms. I could I use Excel? I could have even given a word table. I could have picked it forms. I could have created power, have all kinds of things, but depending on the context depending on the volume, depending on security requirements, what do you want to do as the next step, you choose the right tool in the right place. There will be some overlapping functionality, but if you just go one level up and look at the reason why the product was created, you will know. Overtime which one to use when? Next, the next question is to limit duplicate entries. Is it correct to make the column as a choice with allowing to add choice manually? Yeah, if you're warned, unique. Choices. Typically, choices will repeat so generally unique column is not applicable to drop down because how many items will be put in drop down. So if it's genuinely variable then by all means you can say allow entry where people can hide their own things. But remember, even if you do that, whatever that extra other items someone added that itself make it duplicated because someone else may have that problem. So generally choice field and unique doesn't go hand in hand. Next, the next question is which type of files that's images can be. Upload all kinds of images. I showed you SVG and PNG just now. OK, how to decide which view is better? Is it dependent on the type of nature of data? No, it's dependent on many things. For example, when I'm creating new columns and designing this, it is all items will fair enough. But then when I'm entering data you notice depending on the kind of data in the volume of data, we decided if it's a single record. I'm going to enter once in 10 days then this is good enough, right? But if I'm going to enter 20 records one after another, this is better, so that's one data entry point of view, which field? There is 1/3 option. This can be a very complex thing and this form view itself is also confusing and I want a different layout here. There's so much space gone for status, which is just a small thing I want to put this next to it, that kind of thing cannot happen easily in SharePoint. So if you want a custom form again go to integrate, go to power apps and then you can create a proper app which is a mobile app or browser app but will work both and havocs absolutely. Beautiful UI, absolutely customized, exactly the way you want. Now having the data already. Now, this data of course I'm going to get in Excel and analyze. But suppose I wanted to work on this data here itself. There I may want another view. For example, I want different kind of packaging to be grouped. So what do I do there? I go to view, I go to filter, for example filter. I want to filter something which is status cancelled. How do I do that? Of course I can do it from here. How do I do that? Ascending descending, no filter by now. Filter by. Obviously I can do this. But then I want to filter by this end packaging equal to spray. I will have to go here and do a filter so I can do that, but remember there is one more nice button there. Every button has to be clicked only then you will understand this one is a global filter or does it give it gives you all of them in one goal? Is a beautiful feature so that I can say I won't stray and notice it's alive. And I want whatever so like that you get a much better control over what you're seeing. So that's filter. But now I want this filter whatever multiple whatever I did, I want that to be saved. So what do I do? I have done some customization. And let's see if this has a view so. This place so now this spray is a view like all items. Similarly, I can always put all items now based on status. I want grouping no problem. What do I do? Status group by status Now what happens? It created groups for everything. Groups right? We have rules for all different. Items in the status list now this group is giving giving me this. I want this like this. This itself is a view. This is same view as. By what status? I can create a view only for myself or I can make it public so everyone can use it. So now I can jump between sprays and this very easily, but wait this by status whether it should be open by default where there are 20 different status is I like to close manually. No, that's very irritating. So for the refinement can be done in current view. Remember we had gone to all items we earlier and there were options so I can customize it from here. So one of the things we have to do here is grouping, which we already grouped by right we have group by status, so that's already done. But here you can actually say show it collapse so that people don't have to collapse 20 items manually one by one. So now when I go to this status item, what is going to happen? The moment I go there, it is automatically collapse and while we are there one more very very useful view. Is this create a new view calendar view? If there is a date column, you can actually create a calendar view list. We have seen Gallery typically picks up a picture and shows it like a thumbnail, but this is very useful start date and end date. So in this case we have the same column called date. In some cases it could be ordered a 10 ship date in which you could have chosen 2. And for demo purposes I'm just going to say created date and actual date which we have added here. So two dates basically and then there are more options title. What is the time look that is the word to show and we'll give it a name. Let's call it. Something like that. So now what happens? It actually creates a calendar. This is a view and it's showing you what happened when. So this user manual was created this and delivered on this. Something like that. And notice all this is now just another view. Next the next two questions is list or need a restricted for 365 users within the organization, or can can it be accessed by external users if SharePoint external access is enabled, yes. OK, can we give permission like Edit View at top level instead of individual files? There is no failure, it's a list. So the permissions are given at least level. OK, make it all level security. Does it mean it is advisable to have one document list shared to all instead of creating multiple documents with respective sharing? That's the whole idea. That's why I said you don't learn Excel. That was not possible in Excel. So to maintain confidentiality we send an empty Excel file in the same columns to 20 people. Now I created those 20 columns here, shared it with those twenty people. I went to settings, advanced settings and said people can read only their own so that the whole reason why list is created once and shared with people in a contextual manner. Next the next question is, is there any error message for duplicate entry? We will avoid duplicate and get correct number of rows for creating dashboard. That took different questions. Did duplicate entry error will be caught at the time of data entry of course. And if you have enabled validations and no duplicates will not accept the entire row. So that is the entry part from a energetix part. Yes, of course there is a distinct option in Pivot table, so anything you'll drag drop, it is going to give you unique anyway. So if I added you notice in some records there were multi selected items, but here it is giving me the correct count. So this is already unique. Now, if you wanted to and this was. Coming from there is a distinct count, but that will happen only if you accessed it through power query data model, not if you're directly connected to iqi. Next the next question says if two people are entering same data in the list, means what will happen. Two people can't enter the same row. First of all, if they are entering their entering their own row, so they will just get merged. Without you having to do anything, it's like live consolidation. Now it is possible that we are not restricted security, so anyone can edit anything, in which case if there is exact moment some conflict, it will give you a conflict mirror and then it will say this guy did this. That guy did you decide what to do and that's rare. There is a conflict resolution locking mechanism behind the scenes. Actually it's access. OK, the next question is a created B as owner and gave full control can be create additional owners, yes. Beginning and remove it if you want. Spiderman. Next the next question is for a possible column. Can multiple rows be selected and the person be filled with where the input value is same? No. So what you're saying is, I selected three rows. Now I go to gridview. Perhaps I will still have those rows selected, but individual entry you can't do if you are casking me in Excel context, something like this. Just to clarify. Since this was possible in Excel, for example, I want to change this. Sorry. Girl click control, click Control click Now Save 22 Control Enter no. Artix next. Is it possible to notify via email if someone else to external person after adding a record? Email what? Email sum to an external ID after adding record. Yeah you can. Sharing can be done at any point of time in the life cycle. So remember to fill the feedback form. Oh, I'm going to download the feedback from now because some people may have to go. So I'm going to download the feedback form. At 447 and do the lucky draw and then continue answering questions. So next question. OK, the next question is how many rows and columns are available in list? Let let's I send SharePoint limits that link I will. A post when I've uploaded the video on YouTube. In the description I will post the link detail giving the exact link to go to on Microsoft documentation as to how many columns is the limit, how many rows is the limit? Earlier version it was 30,000 rows on my mom. Sure it has increased now. The next question is, once we select the option during export to Excel can be changed. Change to what? There's nothing specific mentioned. Yeah, so if the person who asked that question is still alive and online, rephrase the question and give a more detailed question next question. Next question is in the date column BC that either or datacine like April seven or four days from now. How to select this display option? Yeah, sure you friendly date option. Let me show you. So when you go to date. In the. Column headed. Friendly format if I remove friendly format. Looks like this. Next the next question is when we edit data after generating Excel query, will the changes get updated in Excel query? When we change the data after. Generating the Excel query so Excel query is just a text file. The Excel query does not contain data. It contains the details of the list. On the browser, Sir, to open that query file, decide whether you want to get data as raw data table or pivot table and then save the Excel file. So once the Excel file is saved, you right click in that raw data table or pivot table and refresh. The query file doesn't have data query filers information about where the data comes from. So if you already have like your file downloaded for a list, you modified the list. You don't have to download another IQR file. Next can it be used for creating for creation of Mark List? What list? Marks Mark list. I don't know what you mean by that. Expand On that. Anyway, I'm going to take a pause now and look at the. Ha forms, I'm going to download it. I hope people have 30 people have given the responses. I'm going to wait for one minute in case someone is not submitted. At 4:45 I'll download the list. OK, let's take one more question till. OK, uh, while sharing link of list can be at times valid ITI to it. Like the link is valid for the next 24 hours. Not really, there is no time limit, but you can just disable the link or remove the link and end of story or go to share and just remove the rights of people to share. Three dots manage access. Now people who can see it. No expiry date at such no, but again, just go to this link now. For example delete the link stops working or I've given it to external people. Whoever has removed their names and it's done. OK, I'm going to download the list of feedback now. Hi and then we'll go look at home. Called. Right so. I'm just going to show you two columns. And Needless to say, we will do the lucky draw using what, of course Excel. So this is the data. This is the order in which people. Added it. But we're not going to talk about the order, we just want people's names. So now what I'm going to do is each person is on a row. So how many people have given feedback? 30 now I need to generate two people who are lucky. How do I do that very simple? It's Excel, right? So I want to generate a random number between zero and 30. This is a demo. What happens right now is not the actual number of person. This is a demo. So how do you do that rank between? This time this is a demo, so I'm just going to say 100. We don't have 100 people. This is a demo, so every time I when I enter it'll generate a random number re calc button in Excel is called F 9. So every time I. Re calc, it generates. The lucky draw. So now I'm going to say 1 to 30 and now when I press enter, whoever comes as the row number is one of the lucky persons. So 26 let's see who is 20. 6. No, she called ice. And if nine for the next one. 24 Ravi Kant mean. So congratulations that we can then. Dice please send your email ID. You can post it as a question itself. It's private, others can't see. It. Will make sure that we send you the voucher. OK, next question. OK, the next question is how many questions left by there? We have another five or questions. OK, sure. Yeah, can we use less forgiving trigger and power apps? Borders does not have a trigger. Persiba Trigger is in power, automate and power automate can trigger a power app if you want Power app is for a UI. Entry looking at something adding data trigger is something which would happen after the row is added. Power App is used to create a custom UI for the list. Once the list item is added, that's the trigger list. Item changing is a trigger, so just to show you what type of triggers are available. How do you find what type of trigger are available for a SharePoint list? That is a good question. So how do you decide or understand? Where can I trigger workflow? That's a very good thought. How do you do that? You go to create a workflow blank workflow. Now there are triggers correct. There are triggers and actions, but this is showing all kinds of things here. What do I want to see SharePoint? Now let's see list. You see, the list part is not available, so it's still SharePoint. Now in SharePoint see this. These are all triggers. All these triggers are applicable now. Some fire, some are for document library and some are for a list. So whether there is an item that means it's a list. So selected item. New item is created, item is created or modified. And so on and so forth. Item is deleted, so these are the triggers now. If you just want to see action, just click here. This will tell you what can you do on a list, so technically speaking all this can be done, so adding an item to a list programmatically using power automate can be an action as well. OK, next. The next question is if list is used with power apps, is it refresh doesn't refresh automatically if data is changed. So power apps is going to link to the structure of the list and then power apps by default creates an and then edit screen for it. Now you created a list, created a power app and then you added three more columns, then power up has to be modified. It's not going to automatically lay it out. But if the structure is same, then perhaps will work be without a problem. OK, is listen advanced version of Ms forms or no, they're two different things. They coexist on the face of it. There are some common things, but the objective of why you're capturing the data and what is the context is different. Bombs is for surveys and quizzes. List is for more business oriented data. Capture the data types available. If you compare in both, you will see why there are two different products. What are the limitations of using Microsoft List? I don't see any limitation. The only limitation I can think of from a practical business data capture point of view. Is one too many is not possible? For example here every row is independent. So now I want to create a list. Which says one item is order and under that order items. That's a one order. Many items, that kind of thing not possible. For that you will have to go to CDs, create a related table and then create a power app for that kind of things. That, I think is the only limitation. Practically speaking, technically there may be more, but practically. Can we restrict deleting of any item and how absolutely so when you save people can allow be allowed to edit and stuff like that. Those people behind the scenes become contributors in SharePoint context. Now a contributor can add, update and delete. Now some people want a weird thing like add OK. But it also OK but delete not OK, no problem. Then you create custom permissions in SharePoint. Sing had it no delete and then map it to the relevant users. So where do you do all that? You have to go to list list settings? Security. My permissions actually. And here you have to understand how to create. Custom permission that is happening at site level, not at least level. So you need to know a little bit of SharePoint security to do that, but you can talk to it. Who knows SharePoint and create a custom permission. Then it's absolutely possible. Next the next question is. Is there any limitation with respect to date storage file size while using with power automated? Our automated self doesn't store any data. The trigger in this case is. The list in some other case, the trigger could be emailed, some other case trigger would be something else, and then there is an action happening there action is happening. Through power automate, but power automate itself is not storing the data, it's sending a Mail. Adding an item in some Excel file, adding a file to OneDrive, or writing a message to teams or creating a task in outlook or in planner, or to do. So if at all you're worried about the size limit of whatever you have to look at the. Place or the tool or the app where the action actually is. Inserting the data. That application will decide the limits. For example, we have a rule or a workflow or a power automate thing which says as soon as I had an item I want to tweet it. Now who is the output channel? In this case Twitter? So whatever is the limitation is going to be controlled by Twitter, not by power to it that way. Next the next question is, is Microsoft List Free app added to home users as well? I don't know. Let me check quickly. Till then you can ask the next question. I don't think is there in home, but let me check. OK, the next question is getting Ms list available offline as in one line as an online, if any. Net issue I won't be able to use. Also, does it autosave enabled? No flying no. So home does not have less. OK, that's all questions that we have for today. Very good. So. Thank you all for joining. How many people are still awake? Oh, 42, that's very nice. Thank you for your patience. If you have any one liner comments. Also you can put it in chat before leaving. I hope this was useful, but don't just clap and go. Very important to actually do something about it, but just to give you live feedback, let me also share the summary of the feedback with you. 37 oh sorry, 39 people give feedback, so those extra 9. Could not be apart of the. Lucky draw, sorry about that, but I'd also promise we will give. Apprised to the best question. So let me see which one is the best question. OK, I think I will give the price to Prashant S. Last two people entering the same data in the list means what happened, so that's conflict resolution. In case, in rare situation when that can happen. Alright, so we're already long long overdue, but thank you for your patience and thank you for. The willingness to learn. The next session we do. Well, we will announce it very soon, but I'm going to do it on a non technical topic. Many people tell me that the way I approach technology is very useful and they understand technology better. And then I realize that many people who are in sales. They're trying to sell technology, but their approach is not very well liked it. Pushy silly, kind of thing. So how to do value based selling? I don't sell, I don't sell any of the products or Microsoft or any other vendor, but people understand it better. And when you understand something better than you will purchase something or invest in it. So whatever I have learned, and I also a colleague of mine called Gurjinder, who has been a veteran in value based selling. So we're trying to do a session next time on value based selling with primary focus on technology. But people were selling other types of products or services I'm sure can also benefit from the generic benefit. So that's the next topic. So with that, let's close for the day. Thank you for your patience and thank you for supporting my venture. Have a nice day. Take care. Vaccinate yourself. Wear double mask. Wear three layered mask. Don't go for cheap mass. And get vaccinated as early as possible. And most importantly, try this out and use the list as an alternative to something which are already doing in Excel. That's the proof of the pudding, and to do that you will have to undergo a lot of teaching to other people who are going to demand the same old Excel file. So teach it to other people in the context of a practical business situation, and that's the best way to learn. So that's it. Thank you. Thank you Jason bye bye.
