What is collection & How to manage a collection in PowerApps ?

In the last article we learned about the different controls which exist in PowerApps although there are some more controls which we will discuss going forward before jumping into remaining controls let’s discuss here the most important part Collection.

What is collection?

A collection is used to store data that can be used in your app or we can say a collection is a group of items that are similar. For example, Student data collection where the collection will store student first name, last name and other information about the student.

To see it practically let’s open PowerApps editor and add a new screen and OnStart function create a collection.

Now to create a collection in PowerApps we have  “Collection” function in PowerApp and syntax is as follows 

Collect(Collectionname, items)

So, Student collection can be defined as follow.

Collect(StudentCollection,      {Name:”Rajat”,LastName:”Jaiswal”},   {Name:”Sandeep”,LastName:”Jain”})

So, we have created our first collection. To view this in action add any control you want here we are using Gallery control.

Once you added this control you will find the data properties and we can see our created collection which is “StudentCollection”

Here we can assign  the title and subtitle  properties of the gallery with the property Name  & LastName of our newly StudentCollection.. Just Save the application at this point and reload the app again.

You will find the following screen.

We can check our newly created collection by file option as shown below

In this way, we can create a collection. Now, the next question is how to ADD/EDIT/Remove in the collection. So, let’s understand this now.

Add Collection

Just drag-drop textboxes, labels, and a button to add the data to the collection as shown below.  On Add, button onSelect write  below function

Now, when you run and click on Add button. You can see the Record appears in your gallery as shown in below figure.

Remove Collection

Now, In the next step, we will delete the record from the collection.

The syntax of this is very simple

Remove (CollectionName,item)

Now, to achieve this add a delete icon in the gallery as shown in below figure.

On select of delete icon, we can write  Remove(StudentCollection, This.Item)

Here This.Item is selected item of the gallery to which we need to delete from the collection.

Now,  What if we want to delete the entire collection? then in such case, we have

Clear Collection

The syntax is very simple Clear(CollectionName)

So in our case, we have to write Clear(StudentCollection)

Now, to Edit a particular item in a collection the first this is select the item in the text box which we want to edit and to achieve this we bind the textbox by Gallery’s selected item as shown in below figure.

Once you have done with this  then on Update button we can write Patch collection function as shown below

So the syntax is very simple

Patch(StudentCollection,Gallery1.SelectedItem, {Name:TextInput4.Text,LastName:TextInput5.Text})

so in this way, we can update the specific item (As shown above we updated only selected item from Gallery).

So, In this session, we understood Collection, add an item in the collection, Remove function to remove a specific item from the collection, Clear Collection to delete entire items from the collection and none other than Patch function to edit the collection.

RJ

Learn How To Start PowerApps Controls

Congratulations, in the last post you have successfully created your first PowerApp.

In this post, we will try to understand different controls available in PowerApps.  Before jumping directly into PowerApps one quick tip wants to share with you that PowerApps is very much inspired by Excel. So, whenever you stuck to any problem which you want to solve than just give a try how you could solve that particular problem in Excel. You will find most of the functions similar to Excel.

Just remember that  screen & its controls have global scope in PowerApps.

PowerApps support various controls which we will discuss shortly but before that would like to share that every control has properties and these properties has 2 types Input properties and output properties. We can update the input properties but not output properties.

with this let’s start controls:

You can use control by clicking  on control’s icon of Insert menu

Label :- Label is simple a ready only control to show text value. We can change the properties like font, color, position, size etc.. from properties window.

You can write a custom function for any input properties or assign direct value.

TextBox : When we need input from the user then in such cases we can use Textbox control. For example, asking the name of the person.

Just to make you aware Text property is  Output property of textbox so you can’t assign value. while Default is in the input property to show any value in the textbox.

Button: Button control is used to perform any action by clicking on it. You can write custom action using the OnSelect trigger.

HtmlText :  When we have to deal with Html Data then in such cases we can use HTML textbox.

Pen Input: This is one of the interesting control. When you need pen input from Apps like a signature or something that then you can use this control

Date Picker : When you want date value then in such case we can use Date Picker control. We have provision to change DateTime format & time Zone from Local to UTC.

DropDown:- Whenever we have multiple options from which we need to select the value then we can use DropDown control. DropDown control has Items properties to bind the dropDown options. If a user selected any Dropdown option then we can get by Selected property of dropdown.

Combobox:- Combobox most likely a DropDown and also provide multi selection option.

Rating :- If we want a rating for a specific reason then we can use Rating control.

Timer:  By the name, it is clear that it is a timer which starts counting. You can invoke an event on OntimerStart, OnTimerEnd etc..

Toggle: Toggle is just True/False option. So, whenever you want true/false option we can use it.

Radio Button:  Radio button is again normal control which you might have used earlier. You have a list of option and you need  to select a specific option then we can use a radio button.

Slider: When we required a predefined range and want a user to select from that particular range then slider is one of the option.

Apart  from this there some other controls like Form Control , Grid, Attachment etc.. which we will describe soon in our coming post as they need individual attention.

Till then enjoy reading.

The A – Z Guide Of Create the first powerApps

In this last post, we discussed the PowerApps templates. Now, in this post finally, we are going to create our first PowerApp isn’t it exciting?

So, as mentioned earlier login with the authorized credentials and after login you will find following dashboard screen. Here, we need to select a Blank Template as shown below. Here, we have a choice to opt mobile or desktop web. We can choose as per your ease or requirement and then press make this App button.

After clicking the make this app button we get following screen which is basically our playground for App creation or actual editor where we can manage our pages, add new pages, add controls  etc..

As this is our first app, we are going to make it very simple and create an addition of 2 numbers screen (Although, it seems simple but bit tricky as well.)

For which we will click 3 label controls ,3 textboxes & 1 button and arrange them properly as shown in below figure. Here we can change the control’s default text. color, etc by property pane.

Once this part is done, in the next step we need to show the sum of First Number, Second number’s value in 3rd text box when user clicks the Sum button.

One more point which we need to take care is format property of the textbox.

Now, to achieve this we are going to write a function on button click as shown in the below snap.

Here, we need to write the code in function’s tab as shown below in screen. if you see here we are using a SUM inbuilt function and using UpdateContext (an internal function) for assigning the addition value of the textboxes to a variable called myAdd.

Now, once we are done with the above function, in the next step we are going to assign the myAdd variable (who holds the addition of text1 & text2) to our result text box. To achieve this select the textbox in which we need to show summation value & click on the Property window’s function tab.

Here, we have to assign the myAdd variable  as a data’s default value (shown in below figure)

Once we done with above steps, next item would be to run this App and do a quick testing. To run this App click on run button (Top right corner of the editor) as shown in below figure.

We will get the below screen to test our sum logic.

That’s great we created and tested our first PowerApps. Now, in next step Save this application. After clicking File menu a new screen (as we are saving this first time)  will appear where we need to provide App name , description of app and icon. After providing this details click on the Save menu .

So, congratulations we have created our first simple PowerApps and saved successfully.

We will see later how to publish it which is also very easy step.

In next article we will see more stuff of PowerApps till then enjoy.

RJ

It’s All About (The) PowerApps Tempates

In the last post, we discussed what is PowerApps and an initial point where we can register our self and start working.

We can use either web studio or desktop studio for creating the PowerApps. I generally, prefer web version.

In this article, we will see different inbuilt templates Apps which give us confidence that what the PowerApps can do.

Click on https://powerapps.microsoft.com/en-us/  and log in with your authorized credentials

 

so, After login, we will find below dashboard screen.

There are several inbuilt PowerApps templates available along with blank Templates. Let’s start few of them to understand the capabilities of PowerApps.

Service Desk:- 

This is simply ticketing system where we can see How many tickets are open? How many are closed? and How many are in progress ? along with ticket details. so, in the organization ticketing system required the can be a good App.

Assets Tracker:

This template app Keep tracks of the tools and equipment your teams need to do their jobs.

Leave Request :

This template will help the organization’s employee to raise a leave request and manager will approve it.

 

There are many more other templates available which you can use as an instant recipe.

As it is Microsoft tool so you can easily access Office 365, planner etc. but on the top of it, you can use Camera & Location feature as well.

Now, in next article, we will see how to start with a new blank App.

step by step powerApps Made Simple – Even Your Kids Can Do It

f you are not family with this awesome thing then surely you might be curious to know what is PowerApps ?, How it works? Where can utilize it? and there are many more questions.

To resolve all these queries we are starting a Zero to Hero series in PowerApps. You will find a series of the blog going forward and we will do deep dive in PowerApps.

What is PowerApps?

You will find a lot of different definition for PowerApps but for me, it is a tool which gives you the power to create your business application which can run on web/mobile/tab with a little writing or without writing code.

PowerApps tool is super easy to use and with the help of this tool, you can create the various business app in which you can use any data source like SQL Server, Excel,  twitter, facebook etc.

Although, PowerApps is basically created for Non-developers but still as a developer you can use it.

In nutshell, we can say it Platform As A Service model. PowerApps has various controls & templates which we can utilize to create our business App.

We can create PowerApps by 2 ways either we can download PowerApps app in Window 10 from Microsoft Store as shown in the figure below.

Or just use following URL http://powerapps.microsoft.com

Once you downloaded or open the PowerApps first or foremost important is to login into the tool using Microsoft /OutLook/office365 account.

Once you  done with sign up process. you have to provide your valid username & Password to access it further.

In next blog, we will see how to create our first simple Power app.

Keep learning

How easy to do a database Copy in SQL Azure?

Sometimes, you might require a copy of your database for some R&D purpose and there are various ways to achieve this.

If you are new to Azure SQL then you might think of those traditional ways which are good.
Let me share a simple command here which do the same task of a database copy.

CREATE DATABASE <New Database Name> AS COPY OF <Source Database>

Suppose, we have a database with the name Northwind and want to create a copy with name MyNorthwind in SQL Azure.
Now, to achieve this we have to write following command.

CREATE DATABASE MyNorthWind AS COPY OF NorthWind

I hope you might like this a quick and easy way and use it.

How easily you can unpivot the pivot data in SQL Server?

I know when we talk about the pivoting & unpivoting the data then most of the time we are making our faces and we feel it would be a tough task. Trust me after reading this post you feel unpivot is super easy.

Before jumping directly into unpivot want to share pivot link to take a glimpse if you are not aware of it.

Pivot in SQL Server pivot 

Now, let us assume that we have following table of employee with id,name,weekid and Dayname columns.

DECLARE @tblEmployeeDayWiseAttendace AS TABLE (Id INT IDENTITY(1,1),
EmployeeName VARCHAR(100),
WeekId SMALLINT,
Monday TINYINT,
Tuesday TINYINT,
Wednesday TINYINT,
Thursday TINYINT,
Friday TINYINT,
Saturday TINYINT,
Sunday TINYINT)

Now let’s insert few rows into it

INSERT INTO @tblEmployeeDayWiseAttendace (EmployeeName,WeekId,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)
VALUES(‘Sandeep’,1,8,8,8,8,8,0,0),
(‘Sunil’,1,8,8,8,8,8,0,0),
(‘Shreya’,1,7,6,8,8,8,0,0),
(‘Shweta’,1,8,8,8,0,5,0,0),
(‘Priya’,1,8,8,8,8,8,8,0),
(‘Rashmi’,1,9,8,9,8,8,4,0),
(‘Bhushan’,1,4,8,5,8,2,0,0)

If you run SELECT * FROM @tblEmployeeDayWiseAttendace then you will get following data as shown in below snap.

Now, the challenge is to Convert Columns Monday,Tuesday,Wednesday and other day columns to row corresponding to employee and show their value.

To make it very easy you have to write below CROSS APPLY query

SELECT tmp.Id, tmp.EmployeeName,tmp.WeekId,tmp2.weekdayname,tmp2.weekValue
FROM @tblEmployeeDayWiseAttendace tmp
CROSS APPLY(values(‘Monday’,tmp.Monday),
(‘Tuesday’,tmp.Tuesday),
(‘Wednesday’,tmp.Wednesday),
(‘Thursday’,tmp.Thursday),
(‘Friday’,tmp.Friday),
(‘Saturday’,tmp.Saturday),
(‘Sunday’,tmp.Sunday))tmp2(WeekDayname,weekValue)

Once you run this query you will get the output which you require.

Now, tell me do you still afraid from unpivot.

Share your thoughts & inputs in comment.

Cheers!

RJ