Chain of Command (COC) - New ability to extend data source and data field methods

Microsoft introduced the new feature of extensibility which is known as Chain of Command (COC). This feature allows to use or call protected methods or members without making them hook able. Or We can say that it allows to extend the logic of public or protected methods without the need to event handlers. It is great feature for developers to do work with extension and avoid overlayering. It has made much easier for us to add pre and post functionality than previously used event handlers.

In Platform update 15 the concept of COC was implemented to class, tables and forms but with the release of October 2018 they have extended this capability and introduced COC  across data source fields/methods which was previously missing.

Note: If you want to use COC you need to declare your class as final and your method should always contain next keyword.

The next keyword behaves like super and it defines execution of your extended code. If next is called after your code then it will behave like Pre handler which means your logic will execute first then original method logic. If next is called before your code then it will behave like Post handler which means original method logic will execute first then your custom logic.

Let's take a example of item group setup. Suppose I want to create new a item group and set it name value on default basis at a time of modifying item group id field. But we want to execute first original method logic then our custom logic. So here we will follow post event handler concept using chain of command.

Create a new class which will be final and add below logic in it. Remember you need to create new class for every object you want to extend.

Delegation in a canvas apps

Today I will be discussing about the delegation in a canvas apps.

Delegation : It refers to the concept where power apps formulas expressiveness meets the need to minimize data moving over the network. In short, we can say that power Apps will delegate the processing of data to data source instead of moving data to app for processing locally.
Note: Not everything that can be expressed in a PowerApps formula can be delegated to every data source. Please refer the list of delegation here

When we work with large data requires data sources and formulas which can be delegated. 

Delegation functions

Filter functions that can be delegated.

• Filter
• Search
• Lookup

Sorting functions : Sort and SortByColumns can be delegated.

Aggregate functions : Sum, Average, Min and max can be delegated.

Example : I have created automatically three screen application based on SQL server table.

For Gateway installation or connection follow here

The gallery item property is already set by sortByColumns and search functions which both can be delegated.

If  I will search for instance black keyword. The search function will looks everywhere in a text columns. Such as below.

However if I want to search particular records for instance I want to search tile name which contain search team at the right side of tile description. Then in this case I will be using following formula.

Here only Black word which is written on right side is searched as a result.

On premises gateway is unreachable on PowerApps

Today I will be discussing about one of the common issue of  on premises gateway on powerapps.
Below is the screen shot of the error. This issue might occur while connecting with sql and data could not be loaded.

Reason : You on premises gateway services is not running .

Solution : Start this service.
Note : You need to open services as administrator.

After stating service your gateway will be live.

Your list of table will be visible here.

Create an ODBC connection in AX

Today, I will be discussing about how to create an odbc connection in AX. As we all know our system might require data from external resources for instance directly connecting with data base.
For this purpose first you need to configure ODBC connection and use odbcconnection class for creating connection with data base using X++.

Below is the code snippet.

  LoginProperty                                  loginProperty;
  OdbcConnection                              odbcConnection;
  Statement                                         statement;
  str                                                     sqlInsert;
  SqlStatementExecutePermission     permInsert;

   // Set the information on the ODBC.
   loginProperty = new LoginProperty();
   loginProperty.setDSN('DSN Name');
   loginProperty.setDatabase('Integration database name');

   odbcConnection = new OdbcConnection(loginProperty);

   if ( odbcConnection)
   {
        statement = odbcConnectionInsertHeader.createStatement();
        sqlInsert = strFmt('INSERT INTO [table_name]([field1], [field2])') + strFmt('VALUES(values1, value2)')
        permInsert  = new SqlStatementExecutePermission(sqlInsert);
        permInsert.assert();
        statement.executeUpdate(sqlInsert);
        CodeAccessPermission::revertAssert();
   }

    statement.close();

Create a canvas app from excel in powerApps

Today, I will be discussing about how to generate a canvas app from excel table in PowerApps.

Lets say we have scenario where we want to show list of volunteers. For this purpose we need to file create a excel file and the data will be format as table in excel. For further details of format a table in excel - Format a table

1) Create a excel file with following columns and format it as table. Store this file on cloud storage in my case i have saved it on google drive.

2) Sign in on PowerApps

3) Click on start from blank

4) Click on new connections and select your cloud storage account. Login on it.

5) Select excel sheet. In my case its with volunteers name and click on connect button.

6) Create view screen. Click on new screen drop down and select list template. It will auto populate all fields.
Following changes it made on view screen.
- Change text of table2 to Volunteers
-  I set fields accurately on browser screen.

7) Go details screen. Changed its label and drag and drop fields up and down.

8) Go to edit screen. Changed its label.

Demo of application click on F5 or preview the app button. Click on Add button (+).
Browser screen

Add information in field and save data.

Delete a record by clicking on delete button.

You can verify your data in excel which is upload on drive. After performing CRUD operations. It will show you updated results.

SSRS reports - Repeat header on new page

Today, I will be discussing about one of the common issue which we might face and that is related to repeating header on new page.

As we all know that in SSRS reports there is already checkbox on tablix  properties which is used for repeating row header. However, it might not work some time.

So here is work around for it.

Step 1) Go to your tablix first and column group. Now right click on its corner. Then select advance mode.

Step 2) Go to row group and select on static above details group.

Step 3) Go to properties of row (static) and this is tablix member. Now set true to repeat on new page property. Keep with group property to after.

Your header will be repeated on each page :D

Configure or Setup ODBC Connection

Today, I will be discussing about how to configure or setup odbc connection. ODBC connection is used for connecting with external database.

Below are the steps required for configuring ODBC connection with Sql server data base.

Step 1) Search odbc setup from start up menu.

Step 2) Go to System DSN tab and click on add button.

Step 3) Select a drive for which you want to setup data source. In my case i have selected SQL Server.

Step 4) Add information for new data source to sql server .

• Name
• Description
• Sql server - With whom you want to connect

Step 5) Add authentication ID details.

Step 6) Select the default data base.

Step 7) Click on next and finish button. You may test the connection by clicking on test data source.

Result

Collections in PowerApps

Today, I will be discussing about the collections and how to use/manage them in powerapps. Let's begin with a scenario where we want to add list of items in a store and can perform addition or deletion on them. For storing data we require a data source/ table but here we will be using collection for this purpose.

Collection : It is used to store data that can be used in your app or we can define it as a set or group of similar items.

-Login to PowerApps.

-Create a canvas app from blank.

- Add a screen and select on Start function which will be used for creating collection.

Syntax : Collect (CollectionName, Items)

This method takes two parameters : Collection name and items which are required to be added in collection table.

Add following formula into it.
Collect(ItemsDemoList, {Items:ItemId.Text, ItemNames:ItemName.Text,ItemQuantity:Quantity.Text, ItemPrice:Price.Text})

It will create a table with columns : ItemId, ItemName, Quantity and Price.

Now for viewing data we will add a gallery control on a screen and will set its datasource to ItemsDemoList. I have mapped Title1 and subTitle fields with ItemId and ItemNames.

Add a collection
Now for adding a collection : I have created a screen which will have following fields
ItemId, ItemName , Quantity and Price.
On its header I have added a icon and set below mentioned expression on that icon.

Collect(ItemsDemoList, {Items:ItemId.Text, ItemsName:ItemName.Text,ItemQuantity:Quantity.Text, ItemPrice:Price.Text})

Remove collection

For removing element from collection. I have added a delete icon on browser screen and added following expression on it which will be used for deleting selected value from collection.

Remove(ItemsDemoList, ThisItem)

This item will be giving  selected item which we are going to delete from the collection.

Clear collection

For instance if we want to delete or clear whole collection then we need to use this expression.

Clear (CollectionName)

Example :Clear(ItemsDemoList)

Final output will be :

Note: At first your app will be showing no data because our collection is empty.

Add items screen

Browse screen

You can also verify your data within collection by going on this navigation : View -> Collections -> ItemDemoList collection

Removed item from collection by clicking on delete icon in browser screen. In my case one item has been deleted now the browse screen will be showing remaining items.

Import and export MS Flow - Part 2

This part is the continuation of MS flow import and export functionality blog. So, today I will be discussing about how to import MS flow on your environment.

Following steps are required for importing MS flow.

Step 1) Login to MS Flow portal.

Step 2)Select your environment by clicking on your account and it will show list of environments.

Step 3)  Click on My Flows menu and select Import option.

Step 4) Upload your file from desired path.

Step 5)  Details of package will be available and you will see resource type gmail will be in red sign. that's why import button is disabled. Click on configure button for adding resource type gmail.

Step 6) Click on create new button and it will navigate you to Connections in new tab.
Note  : We don't have any resource type right now for gmail that's why we are creating new one. If you have any resource type available you may utilize it.

Step 7) Create a new connection and search for gmail. Click on gmail connection and sign in to your gmail account.

Step 8) Newly created connection will be added in your list now. Go back to import setup screen and refresh list. Select your gmail id and save button will be enabled now. Save your settings.

Step 9) Now you will see your Import button is enabled. Click on it and your flow will be imported.

After importing flow successfully, you will be available to check it in your my flow list.

Note : I need to add gmail resource type here but in your case its all depends upon your flow design what type of resource you will need or may be you won't require any resource type.