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.

Step by Step SSIS–Union ALL Tip # 119

 

Dear All,

In the series of  zero to hero SSIS series this is one of the simplest transformation control which known as UNION ALL. 

As you might aware UNION in general term  which means collective. In SSIS UNION all control is doing the same task.

It collect all the inputs may be of same type of sources or different type of sources and  union them all and provides single output for the same.

Lets understand this by below example.

Suppose, We have different text files which contain fruits &  vegetables name. Now our aim is to combine all the fruits and vegetables name which exists in these files.

if you see below image you will find 3 files which FruitA (Contains fruit names start with letter A) , FruitB (Contain fruit names start with  letter B), FruitP (contains fruit name start with letter P)

Now we need to combine this file using UNION ALL transformation. So follow below step by step

1) Step 1:- Step 1 is simplest step and now you all well versed in this. We need to add a new SSIS package file and drag drop  data flow task .

2) Step 2:- Now add different flat file source for all the 3 files and configure all the 3 files  fruitA.txt, fruitB.txt , fruitP.txt .

3) Once we configured all the 3 flat file sources, We have to drag drop UNION ALL transformation control.Once we drag drop UNION ALL we can provide the output of all the 3 files as a input in UNION ALL control as shown in below  figure.

Step 4:- Now, we have to configure the UNION ALL control. To do this right click on UNION ALL control and click on EDIT option. Here we have to configure all the columns which we need to union.

Step 5:- Once the UNION ALL is configured our next step is to get the output of UNION ALL in a resultant file. for this we have to drag drop destination flat file control as shown in below figure.

Step 6:- Now we have to configure the flat file destination and save it to specific location. A part from this we have to provide input to flat file destination which will be output of UNION ALL control. As shown in below figure I am saving the file on same location and giving name finaloutput.txt

Step 7:- Once all the above step is processed by us the final step is to run the package for this just click on RUN icon or hit F5. If everything is working fine the we will get following  result with all the green right check images.

If you see above image we did union of all the 3 files and saved output in a single file.

Now, for learning purpose and to make the post simplest I use only same type source you can use different type of source and destination as per your need.

A part from this UNION ALL doesn’t remove duplicate so if there are duplicates in the files it will not remove.

I hope this article will help you somewhere.  Please provide your inputs.

Enjoy !!!

RJ!!!

Step by Step SSIS – Conditional Split Transformation TIP #117

In the series of Zero to Hero in SSIS this is our next post. In this post we will see Conditional Split transformation.

I am pretty much sure that by the name you got some impression what it would be.

So, a Conditional Split Transformation is a way by which you can conditionally split an input into multiple output.

Suppose, You are a food supplier and you cook both veg & non veg food. Now according to orders by client you need to move those order requests to particular kitchen to prepare those orders.

Let’s understand it by following step by step execution example.

In this example , I am using AdventureWorks database as a source database connection. We are fetching products along with categories. We need Bikes products in different file and rest other product in different file. You can see products with categories shown below in this image Accessories , bikes are the categories.

Step 1:- Create a  new package and drag drop data flow task control and double click it. You will get a new screen which data flow task. Now drag drop Source Assistance. from SSIS controls tool bar.

Now as usual we have to configure the database connection string and set the database to adventureworks.

Step 2:- Once the database is configured right click on control and choose edit property. You will get below screen where you need to specify the query which we shown earlier in figure.

Once you done with above steps you need to configure columns as well so, click on Columns and configure it.

Step 3:- Now drag drop conditional split and connect input arrow to it as shown in figure

Step 4:- Now choose edit option by right clicking the conditional split and configure it.  When you click you will get screen like below. You will find different condition operator. One important point to remember here you need to drag drop column name to condition textbox.  As our aim is to have product with Bikes category in a different file and other categories in different file.So we use following condition as shown in below figure.

Step 5:-Now drag drop a flat file destination and assign the output of the above conditional split to File destination. You will get following screen. As you see we are assigning Bike condition output to this file which means all the products which belongs to bikes categories should be available in this.

Step 6: Now Configure the flat file destination by choosing the edit option.

 

Step 7:- In similar way we have to capture the output which not belongs to Bikes category as shown in below figure

Step 8: In nutshell we will get following screen

We almost done here. Now to check whether this conditional split is working or not. To check this hit F5 or run the package. So, if you see below everything working fine. We got 32 rows in bike category and 73 rows in other categories.

To cross check result we will see the file output as well

So , If you  go through all the above steps we have achieved Conditional split example.

I hope this post might help you to understand conditional split.

In next step we will go for next step in SSIS.

Enjoy !!!

RJ!!!

An interesting way of aliasing TIP#107

I recently gone through something and found a unique way of aliasing. I thought it must be share so other techies also aware of it (or might be you already aware of it).

See below example

SELECT *
FROM (VALUES (‘Rajat’,30),
             (‘Sandeep’,40),
             (‘Sunil’,35),
             (‘Shreya’,50),
             (‘Virendra’,45)) AS T(Name,Runs)

Now when you it  you will get following result

I hope you may like this tips.

Enjoy!!!

RJ!!!

It is OK its NULL only TIP #103

 

We always take NULL very lightly. Like if we are designing database then whether it is necessary or not we allow the data field to accept null.We think it will not affect anything.

Actually , at some point this is not always true.

According to Microsoft NULL is an Unknown value. It is not EMPTY & ZERO. It is just unknown.

Now according to my experience NULL is very sensitive and should be handle with care.

While we are designing the database and in a particular table if a field can not be null according to business rule then please don’t allow NULL in that field for data consistency.

For example if we have a StudentEnrollment table and in the StudentEnrollment table  we have reference of StudentId which is primary key of StudentId and that can not be NULL then please don’t make the field null able.

Let me share some more interesting facts

1) Two null value not equal :-  Let me explain it with an example

Suppose I have tblStudentSource table  which has different columns and a course column which have some null values  as shown below

Now see below snap where we are comparing the null value course column. You will find in below snap that there is no result while we have null value in course

2)  Use IS NULL or IS NOT NULL  to handle null able column:-  If a column is null and you want to show all the columns which are null then instead of comparing NULL value with column as shown in above figure use IS NULL as shown in below figure

In similar way if we want result which not have null course then we can write below query

3) Handle NULL carefully with IN clause

Let me explain with an example so we have 2 tables which are tblStudentSource (as shown in above (image 1) and another table which is tblCourse as shown in below figure

Now if you see we have course column in tblStudentSource table which has values similar to tblCourse’s course column and tblStudentSource’s course column contain some NULL values

Now suppose we wrote following statement and expecting that it will return all the course which are in tblCourse

Oh !! it is not returning any course. The reason behind it is NULL is not handled properly. Now to get desire result we have to write following statement

 

So , be always careful when your column has null values.

I hope the above points might help you somewhere.

Enjoy !!!

Keep learning & Keep Sharing !!

Thanks

RJ

A myth about view TIP #102

 

I don’t know why every interviewer’s favorite question “Can we insert record using View ?” If you say Yes/No the interviewer will roaming around like so Can you update record using View?  or Can you delete record using view ?

I  hope everyone who is reading this article will be aware of what is view and how to create it ?

If not then need not to worry

“View can be see as a virtual table based on SQL SERVER result or in other world it is a layer above actual data tables” Or we can say when you want to hide actual table then you create a view wrapper”

You can easily create a View with following syntax

“CREATE VIEW

AS

QUERY “

Let’s understand this by an example.

Suppose in I have a database with name “IndiandotnetDB” which has a  table “tblStudentSource”

Now I created a  view just for fetching records from tblStudentSource

 

CREATE VIEW StudentSourceView
AS
SELECT StudentId,
       FirstName,
       LastName,
       Course,
       Detail
FROM tblStudentSource
Go

Now you can fetch records directly from  View as shown in below

SELECT * FROM StudentSourceView

You will get all the records from tblStudentSourceView

Now the Question “ Can you Insert record from View ?“

So the answer is specific condition you can.

In our scenario we will write following command and execute so the record will be added

So you are clear that we can Insert records from View.

In similar fashion we can update the records as shown below figure

We will following SQL statement as showing in figure in which we are going to update record no 2004 as shown in above figure

As shown record 2004’s FirstValue is updated to value “Updated”

 

Now in similar way we can delete the record using View.

Although there are certain other factor due to which “Insert/update/delete” is possible.

like we have only simple schema.

I will describe this later with more detail like  scenario where  we can not Insert/Uppdate/delete using view.

Till than Enjoy !!!

 

Thanks

RJ

Very useful SET XACT_ABORT TIP#94

 

SET XACT_ABORT is one of the hidden gem we can say. It is helpful in many ways.

Let me explain by an example suppose you are working on a stored procedure which is doing a complex data manipulation. You though this query will run with in 1 minute when you run it from .NET or other code , but some how it took quite long time and with in one minute .NET or the code from which you run the query raised a command timeout.

Or we can say you have applied transaction in stored procedure and calling from .NET code and you have mention the command timeout in .NET .The ADO.NET program raised a timeout but still your query is running in background.

Now in such situation sometimes it happened the transaction which is running in SQL SERVER will be open transaction. Which might be a cause of locking problem.

In such situation  XACT_ABORT option is helpful.

By default XACT_ABORT option is off you can on it by following statement

   SET XACT_ABORT ON;

Now when this statement is on and if you face same situation as mentioned above it will rollback entire batch.

Let me explain with below example

Suppose I wrote following statement “Notice here XACT ABORT is OFF”

Now , when I run the above statements I will get following result in result window

And in the message window we will get following result

What it means instead of the error at least 3 records are inserted but sometimes you require either all or no record commit in the table then let me enable the option XACT_ABORT ON in the query

Now let me re run the statements again. When I run the above statements I got following result in message tab there is no result tab.what is means ??

It means what ever inserted is roll backed when an error occurred in a statement. Although The above example is not the best example of XACT_Abort but it will help to understand how to use it.

 

I appreciate your inputs on this valuable topic.

Thanks

RJ!!!

Easy way to reseed identity column ? TIP #77

 

Sometimes, we may require to reseed identity column of a table due to various reason.

For example we have deleted a record from a Student which have an identity column StudentId.  Below is schema

Now it has 3 rows as shown below

Now suppose we have deleted record 3 which is studentID 3.

Now when we want to insert a new record StudentId start with 4 and we want it should be 3.

So how to check and reseed it ?

To achieve this task SQL SERVER provided DBCC CHECKIDENT facility.

DBCC CHECKIDENT has 2 commands

1) To Check current Identity value.

DBCC CHECKIDENT (TABLENAME, NORESEED)

2) To reseed an identity column we have following command

DBCC CHECKIDENT ( TABLENAME , RESEED, VALUE) 

Lets check student table identity to check current column value and current Identity value we will write following command

DBCC CHECKIDENT(tblStudentSource,NORESEED)

See below snap when after running this command

Now if you see 3 is current identity and column value 2 it means if I Insert a new row in tblStudentSource then that row will have studentId = 4 as a next identity column

Now I need to reseed it to 3 means if Insert a new record then studentId should be 3 to do this I have to reseed tblStudentSource.

DBCC CHECKIDENT(tblStudentSource,RESEED,2)

Now to cross check whether it is reseed or not I write NORESEED command again as get following result

It means identity value 2 and current column value is 2 means if now I insert a new record we will get studentId = 3 as a identity column this what we want actually.

So. We did NORESSED, RESSED an identity column.

I hope it may help you somewhere in your work.

Thanks

Rj

How to replace a specific string from a large string with Write feature ? TIP #72

 

This is very interesting feature and I recently come to know this awesome feature.

Lets understand this by an example below

Suppose we have a student table with following structure  as shown in below image

 

We have a detail column with VARCHAR(MAX) .

Now as it is VARCHAR(MAX) column it may content a large amount of data currently it has following data as shown in below figure

Suppose we want to replace a specific string from this large column value like we want to replace “interested in” with “Always”  of  studentId = 1 then

we can use write function easily.

With the help of Write we can update a specific text/string  of a large column.

syntax of Write is as shown below

UPDATE TABLENAME

SET COLUMNNAME.WRITE (ReplacedWithString, startPosition, length)

WHERE clause

Now lets understand this by above student example where we want to replace “Interested in” which is doubled by mistake with “always”

So when you run above query you will get following result when you run select command.

great we have replaced the string which we want.

The main benefit of this the entire column value is not logged. suppose you have 2 GB data in your column then instead of logging 2 GB data at the time update only few KB will be logged.

I hope this might helpful to you somewhere.

Thanks !!!

RJ!!!

Oh! I forgot When I took last Database Backup ? How to determine this? TIP # 66

 

In last few tips TIP #64 & TIP #65  we get information  how to take backup & How to ensure it can be restore? Now lets me share some more information related to backup.

Problem:-   Sometimes , we are interested to know when last backup is taken for particular database ?

Solution:  The first thing I want to share here that  you should have proper maintenance plan to take backup of database and by job History you are able to know when last backup is taken but if this is not the case then not to worry SQL Server maintain itself information related to backup. to determine this you need to write a simple query as shown below

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type 
       WHEN ‘D’ THEN ‘Database’ 
       WHEN ‘L’ THEN ‘Log’ 
   END AS backup_type, 
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

ORDER BY 
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

  When I run above command I get information related backup like databasename, backup start date, Backup finish date, expiry date(if any) , backup type like whether it is database backup or Log backup , size of backup , path where database is taken, name of backup set.

see below snap for detail

I hope this tip might help you somewhere.

Thanks !!

Rj!!