Working with JSON data in SQL Server

July 26, 2020 1 comment

In this article, lets see how to work with JSON data in SQL Server using SSMS. Following features will be covered in this article.

  • Format SQL table data in JSON format (Using FOR JSON PATH clause)
  • Store JSON data in SQL table
  • Query JSON data in SQL server (Using JSON_VALUE and JSON_QUERY functions)
  • Update JSON data in SQL table (Using JSON_MODIFY function)
  • Convert JSON data in to SQL table format (Using OPENJSON function)
  • Validate JSON data format (Using ISJSON function)

Format SQL table data in JSON format:

Lets see how to format SQL query tabular results in JSON format using FOR JSON clause.

  • Create a simple ‘Employee’ table in SQL server.
  • When you query ‘Employee’ table you would get the result in tabular format.
  • Lets convert the result set in to JSON format using FOR JSON clause.
-- JSON PATH
select *
from Employee
FOR JSON PATH
  • Use WITHOUT_ARRAY_WRAPPER to get a single JSON object instead of an array. Use this option if the result of query is single object.
-- JSON PATH and WITHOUT_ARRAY_WRAPPER
select *
from Employee
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  • We can also form inline JSON object format. Lets see how to show ‘Address’ as a separate object.
-- JSON PATH, WITHOUT_ARRAY_WRAPPER and 'Address' as seperate object
select
ID,Name,
Street1 as [Address.Street1], Street2 as [Address.Street2],City as [Address.City], ZipCode as [Address.ZipCode]
from Employee
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Store JSON data in SQL table:

  • JSON is a textual format that can be used like any other string type in SQL Database. JSON data can be stored as a standard NVARCHAR.
  • In the example below SQL table has ‘JSONData’ column which is NVARCHAR(MAX).

Query JSON data in SQL server:

  • Lets query the JSON data using JSON_VALUE and JSON_QUERY.
  • JSON_VALUE:
    • JSON_VALUE function extracts a value from JSON text stored in the SQL column.
    • The extracted value can be used in any part of SQL query.
  • JSON_QUERY:
    • JSON_QUERY function extracts complex sub-object such as arrays or objects that are placed in JSON text.
    • Example ‘Tags’ column in above SQL Table.
Query records whose Color is ‘Yellow’

Update JSON data in SQL table:

  • JSON_MODIFY function can be used to update the JSON text without re-parsing the entire structure.
  • Below is the query to update the ‘Price’ of all ‘Yellow’ color products to 1000.
Update Price for records where Color is ‘Yellow’
Update Products
set JSONData = JSON_MODIFY(JSONData,'$.Price',1000)
where
JSON_VALUE(JSONData,'$.Color') = 'Yellow'
  • Post query execution, ‘Price’ updated to 1000 as shown below.

Convert JSON data in to SQL table format

  • Using OPENJSON function JSON data can be transformed in to SQL table format.
  • OPENJSON
    • Its a table-value function that parses JSON text, locates an array of JSON objects, iterates through the elements of the array, and returns one row in the output result for each element of the array.
Convert JSON data to Table format

Validate JSON data format

  • Since JSON is stored in a standard text, there are no guarantees that the values stored in text columns are properly formatted using ISJSON function.
  • ISJSON function returns the value 1 if the data properly formatted in JSON.
ISJSON(SQL_Cell) > 0

🙂

Categories: SQL Tags: ,

Power Apps | CDS | Track deleted records using ‘Change Tracking’

For unversed, Change Tracking in CDS allows data to be synchronized by detecting what data has changed since the data was initially extracted or last synchronized.

Its useful in Integration scenarios, where you have to keep data between CDS and external systems in sync.

Assume that your customer has a LOB application along with CDS and you want sync the LOB application (i.e., When record gets Created/Updated/Deleted in CDS), this can be achieved using ‘Change Tracking’ feature.

In this article, lets understand how ‘Change Tracking’ works in CDS using ‘Organization Service’, to:

  • Get the Created or Updated records from last sync.
  • Get Deleted records from last sync.

Enabling Change Tracking for an Entity:

  • To use ‘Change Tracking’ make sure that the change tracking feature is enabled for that entity.
  • This feature can be enabled by using the customization user interface.
Enable Change Tracking

‘Change Tracking’ using the Organization Service:

  • When change tracking is enabled for an entity, RetrieveEntityChangesRequest message can be used to retrieve the changes for that entity.
  • In my example, I am using OOB ‘Account’ entity which has 10 records.
  • When ‘RetrieveEntityChangesRequest’ triggered for the first time, we would get all 10 records in ‘Changes’ object and a ‘Data Token’.
  • Copy the ‘Data Token’.
  • Now lets delete an Account and add a new Account in CDS.
  • Trigger ‘RetrieveEntityChangesRequest’ again by passing the copied ‘Data Token’.
    • Passing ‘Data Token’ in ‘RetrieveEntityChangesRequest’, fetches only data for those changes that occurred since that version will be returned.
  • This time we would get 2 records in ‘Changes’ object.
    • 1 NewOrUpdatedItem
    • 1 RemovedOrDeletedItem.
  • ‘NewOrUpdatedItem’ is object of type ‘Entity’ with all the properties. ‘RemovedOrDeletedItem’ is object of type ‘EntityReference’ contain only GUID of deleted record.
  • Following is the code snippet to track changes and display in console.
// Created or Updated records will be returned as 'Entity' object.
var createdorUpdateRecords = new List<Entity>();
// Deleted records will be returned as 'Entity Reference' object.
var deletedRecords = new List<EntityReference>();

// Retrieve records by using Change Tracking feature.
var request = new RetrieveEntityChangesRequest
{
    EntityName = "account",
    Columns = new ColumnSet("name"),
    PageInfo = new PagingInfo() { 
       Count = 5000, 
       PageNumber = 1, 
       ReturnTotalRecordCount = false 
    },
    // DataVersion is an optional attribute
    // If not passed system returns all the records. 
    // Dont pass this attribute when calling for the first time.
     DataVersion = "706904!07/24/2020 08:00:32"
};

while (true)
{
  var response = (RetrieveEntityChangesResponse)_serviceProxy.Execute(request);

  if (response != null && 
      response.EntityChanges != null &&   response.EntityChanges.Changes != null)
  {
    foreach (var record in response.EntityChanges.Changes)
    {
      // Read NewOrUpdatedItem in to 'createdorUpdateRecords' list. 
    if (record is NewOrUpdatedItem)
    {
     createdorUpdateRecords.Add((record as NewOrUpdatedItem).NewOrUpdatedEntity);
    }

    // Read RemovedOrDeletedItem in to 'deletedRecords' list.
    if (record is RemovedOrDeletedItem) 
   {        
     deletedRecords.Add((record as RemovedOrDeletedItem).RemovedItem);
       }
      }
   }
    // Display Created or Updated records
    createdorUpdateRecords.ForEach(x => Console.WriteLine("Created or Updated record id:{0}", x.Id));
    // Display Deleted records
    deletedRecords.ForEach(x => Console.WriteLine("Deleted record id:{0}", x.Id));

    // Logic for Pagination
    if (!response.EntityChanges.MoreRecords)
    {
      //Store token for later query
      token = response.EntityChanges.DataToken;
      break;
    }
   // Increment the page number to retrieve the next page.
   request.PageInfo.PageNumber++;
   // Set the paging cookie to the paging cookie returned from current results.
   request.PageInfo.PagingCookie = response.EntityChanges.PagingCookie;
  • Run the code and you would get response as below

🙂

Categories: CRM Tags: , ,

Power apps – Model driven app – Excel import not working

Other day, when we copied one of the Environments to another and tried to import csv file using ‘Excel’ import feature, file stuck at ‘Submitted’ state.

Reason:

  • Post copy, Environment’s ‘Administration mode’ gets enabled by default, which prevents all background (Async) operations.

Fix:

  • Turn off the ‘Administration mode’

🙂

Azure function and Cosmos DB Table API – Method not found exception

While creating ‘Entities’ in Azure cosmos Table API from Azure function we encountered following exception.

Reason:

  • Azure Function application project was configured using ‘Azure Function v1 (.NET Framework)’.
  • “Microsoft.Azure.Cosmos.Table” nuget package is not compatible with ‘Azure Function v1 (.NET Framework)’.

Fix:

  • Change the Azure Function application project type to .NET Core.

Now lets understand the technical Know-how to configure ‘Azure Table API’ and interact from Azure Function application project.

Configure ‘Azure Table API’

  • Login to your Azure Portal.
  • Create a new ‘Azure Cosmos DB Account’ by selecting ‘API’ as ‘Azure Table’.
  • Once deployment is complete, open the ‘Resource’.
  • Lets create a new table by clicking on ‘New Table’ from ‘Data Explorer’. Provide the ‘Table id’ and click ‘OK’.
  • In ‘Azure Table API’ record/row is referred as ‘Entity’ and every ‘Entity’ must have ‘PartitionKey’ and ‘RowKey’ values.
    • You must include the PartitionKey and RowKey properties in every insert, update, and delete operation.
  • To connect to ‘Azure Table API’ from external applications (i.e., Azure Function,etc..) we need the ‘Connection String’.
  • Go to ‘Connection String’ tab and copy the ‘PRIMARY CONNECTION STRING’.

Create ‘Azure Function Application’ project

  • Open Visual Studio and create a new ‘Azure Functions’ project.
  • Select .NET version either v2 or v3 .NET Core.
  • Rename the ‘Function Name’ to a meaningful one. I set my function name as ‘CosmosTableAPI’.
  • Add ‘Microsoft.Azure.Cosmos.Table’ NuGet package.
  • Following is the code snippet to connect to ‘Azure Table API’ and Insert entities.

public static async Task Run(
[HttpTrigger(AuthorizationLevel.Function, “get”, “post”, Route = null)] HttpRequest req,
ILogger log)
{
CreateTableandAddData().Wait();

}

public static async Task CreateTableandAddData()
{
string tableName = “customers”;

// Create or reference an existing table
CloudTable table = await Common.CreateTableAsync(tableName);

IndividualEntity customer = new IndividualEntity(“Harp”, “Walter”)
{
Email = “Walter@contoso.com”,
PhoneNumber = “425-555-0101”
};

Console.WriteLine(“Insert an Entity.”);
customer = await InsertOrMergeEntityAsync(table, customer);
}

public static async Task<IndividualEntity> InsertOrMergeEntityAsync(CloudTable table, IndividualEntity entity)
{
try
{
// Create the InsertOrReplace table operation
TableOperation insertOrMergeOperation = TableOperation.InsertOrMerge(entity);

// Execute the operation.
TableResult result = await table.ExecuteAsync(insertOrMergeOperation);
IndividualEntity insertedCustomer = result.Result as IndividualEntity;

// Get the request units consumed by the current operation. RequestCharge of a TableResult is only applied to Azure Cosmos DB
if (result.RequestCharge.HasValue)
{
Console.WriteLine(“Request Charge of InsertOrMerge Operation: ” + result.RequestCharge);
}

return insertedCustomer;
}
catch (StorageException e)
{
Console.WriteLine(e.Message);
Console.ReadLine();
throw;
}
}

public static async Task<CloudTable> CreateTableAsync(string tableName)
{
string storageConnectionString = “{Connection string copied from Azure Table API in previous section}”;
// Retrieve storage account information from connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(storageConnectionString);

// Create a table client for interacting with the table service
CloudTableClient tableClient = storageAccount.CreateCloudTableClient(new TableClientConfiguration());
tableClient.TableClientConfiguration.UseRestExecutorForCosmosEndpoint = true;

// Create a table client for interacting with the table service
CloudTable table = tableClient.GetTableReference(tableName);
if (await table.CreateIfNotExistsAsync())
{
Console.WriteLine(“Created Table named: {0}”, tableName);
}
else
{
Console.WriteLine(“Table {0} already exists”, tableName);
}

return table;
}

public class IndividualEntity : TableEntity
{
public IndividualEntity()
{
}

public IndividualEntity(string lastName, string firstName)
{
PartitionKey = lastName;
RowKey = firstName;
}

public string Email { get; set; }
public string PhoneNumber { get; set; }
}

  • Compile and run the project.
  • Visual studio opens up a console (Storage Simulator) as below. Copy the URL.
  • Hit the URL either from browser or Postman tool.
  • You would get following output from the console.
  • Go to Azure Portal and you should see a new entity created

🙂

Categories: CRM

Power Apps – Component library

Components

In Power Apps, Components are reusable building blocks for canvas apps so that app makers can create custom controls to use inside an app.

  • Components scope is local, which means you can create a Component in an App and can reuse with in the screens of that particular App.
Adding ‘Component’ from ‘Canvas’ App.

Component Library

Component Libraries (Currently in preview) are the recommended way to reuse components across apps.

Unlike the ‘Component’ whose scope is limited with in the App, ‘Component Libraries’ can be reused across the Apps.

In this article, we are going to learn following topics

  • Create a new ‘Component Library’ with ‘Header’ and ‘Footer’ components.
  • Share the ‘Component Library’
  • Use the ‘Component Library’ in Canvas App
  • Modify the ‘Component Library’
  • Update ‘Component Library’ in Canvas App.

Lets get started with Prerequisites.

Prerequisites:

  • PowerApps account. Refer here to get an account.

Once you fulfill the Prerequisites, lets get started with first step.

Create a new ‘Component Library’ with ‘Header’ and ‘Footer’ components:

  • Connect to Power Apps maker portal
  • From the ‘Apps’ tab, click on ‘New component library’.
  • Provide the name and click on ‘Create’.
  • As we know ‘Component Library’ is collection of reusable ‘Components’, In the next screen, a default ‘Component’ would be presented.
  • Rename the default Component to ‘Header’.
  • Add a Label control ‘lblHeaderText’, as shown in the screen.
  • Add a new ‘Custom property’ of type ‘Text’ and name it as ‘Header Text’.
    • A component can receive input values and emit data if you create one or more custom properties.
  • Set the Label controls ‘Text’ property to the ‘Header Text’ property.
    • ‘Header Text’ property will be handy while referring the components in ‘Canvas’ apps.
  • Also set the ‘Header’ components ‘Width’ property to the ‘App.ActiveScreen.Width’.
    • App.ActiveScreen.Width sets the width of Components as per the Canvas app.
  • Lets copy the ‘Header’ component using the ‘Duplicate component’ option, and create ‘Footer’ component.
  • I’ve added a Label and new ‘Custom property’ of type ‘Text’ and name it as ‘Footer Text’, as shown in screen below.
  • Save the ‘Component Library’.
  • In the maker portal ‘Component Library’ would show up.

Share the ‘Component Library’:

Now that we created the ‘Component Library’, we need to share the App.

  • Post the ‘Save’, click on ‘Share’.
  • Add people you would like to share. I’ve shared the Component Library with ‘Everyone’ in my account.

Use the ‘Component Library’ in Canvas App:

As we created the ‘Component Library’ lets put that in use by creating a Canvas app.

  • Create a new Canvas app.
  • To add the ‘Component Library’, click on ‘+ Insert’ and ‘Get more components’.
  • From the list choose the ‘Components’.
    • You would get the ‘Components’ those were shared to you.
  • Added ‘Components’ would be available under ‘Library Components’.
  • Add both ‘Header’ and ‘Footer’ Components to the Canvas app’s screen.
  • Set the ‘HeaderText’ property of the ‘Header’ component. This would immediately changes your Header label text.
  • Similarly, Set the ‘FooterText’ property of the ‘Footer’ component. This would immediately changes your Footer label text.

Modify the ‘Component Library’:

Lets modify the ‘Component Library’ and see how it reflects in the Canvas App.

  • Change the background color of the ‘Header’ component.
  • Save and Publish.
  • Go to ‘Canvas App’ and you would notice banner as below.
  • Click on ‘Review’ and click on ‘Update’ to get the latest Component updates.
  • Canvas apps Header changes as below.

Notes:

  • Sharing a component library works the same way you share a canvas app.
  • When you share a component library, you allow others to reuse the component library.
  • Once shared, others can edit the component library and import components from this shared component library for creating and editing apps.
  • If shared as a co-owner, a user can use, edit, and share a component library but not delete or change the owner.
  • You can’t add existing component libraries to a solution. However, you can create new component libraries for solutions using add component library flow.
  • You can’t access controls in the component from outside of the component.
  • You can’t refer to anything outside of the component from inside the component. The exception is data sources shared between an app and its components

🙂

Power Apps Portal – Remove an Entity from Global Search

If you are not familiar with Portal ‘Global Search’, please refer my other article to learn the steps to configure global search for an entity.

In this article, how to remove global search for a configured entity.

Disable the ‘Search Index’:

  • Open the entity you would want to disable the ‘Global Search’ and navigate to ‘Views’ and open ‘Portal Search’ view.

GS_7

  • Rename the ‘Portal Search’ view and ensure the new name doesn’t have the Portal Search term.
  • Save & Publish.
  • This step will block the entity from getting indexed.
  • Connect to Portal and rebuild the ‘Search Index’ by navigating to ‘../_Services/about’ page and click on ‘Rebuild search index’.

GS_12

Remove the entity from search filters in ‘Site Settings’:

  • Open the ‘Portal Management’ Model-driven App and go to ‘Site Settings’.
  • Open the ‘search/filters’ site setting and remove the entry for the entity you would want to disable Global Search.
  • As an example, for Account the entry would be ”Accounts:account‘. Remove the entry.

GS_4

  • Save the ‘search/filters’ site setting.

🙂

 

 

[Step by Step] Power Apps Portal – Configure Global Search

In this article, lets learn how to configure global search in ‘Power Apps Portals’.

What is Global Search in Portals:

  • Global search of portals allows you to search for records across multiple entities.
  • Allows you to search across multiple columns and configure what columns of an entity would be searchable.
  • By default, few OOB portal entities are enabled for Global Search. To name a few ‘Knowledge Article, Blog, Blog Post, Forum, etc…’.

So lets see how to configure global search for other entities. I am taking ‘Account’ entity for this article and you can pick the entity of your choice.

Prerequisites:

  • PowerApps account. Refer here to get an account.
  • PowerApps Portal. Refer link for steps to enable portal.

Once you fulfill the Prerequisites, you would get the following two Apps in your PowerApps maker portal.

  • ‘Portal Management’ – Model-driven App which contain Portal related entities.
  • ‘Portal’ – Portal app. I named my Portal App as ‘ExpMay2020’.

GS_1

Following are the high level steps to enable global search for ‘Account’ entity.

  • Add/Update Portal ‘Site Settings’.
  • Create a ‘Portal Search’ view in Account entity
  • Add Portal ‘Web Page’ to display the search result.
  • Add ‘Site Marker’ for the ‘Web Page’
  • Grant permissions to Portal Users
  • Rebuild Search Index

Lets get started.

Add/Update Portal ‘Site Settings’:

  • Open the ‘Portal Management’ Model-driven App.
  • Go to ‘Site Settings’ and Add a new ‘Site Setting’ by Name ‘Search/EnableAdditionalEntities’ and  Value ‘true’.
    • Note: Name should be exact match. If you already have ‘Search/EnableAdditionalEntities’ site setting, make sure the Value is set to ‘true’.

GS_2

  • Also make sure the OOB Site Setting ‘Search/Enabled‘ is set to ‘true’.
  • Add a new site setting ‘search/filters’, if not exists already. Open the record, if already exists.

GS_3

  • Set the Value with following pattern {YourDesiredName:EntityLogicalname}.
  • In my case, its ‘Accounts:account‘.
    • Multiple entities must be separated by Comma(,) (i.e., Accounts:account,Contacts:contact)

GS_4

Create a ‘Portal Search’ view in Account entity:

Open the Solution and go to ‘Account’ entity.

  • Create a new View by name ‘Portal Search‘.

GS_5

  • Add the columns which you want to search to the view.

GS_6

  • Save & Publish.
  • Once saved, view shows as below.

GS_7

Add Portal ‘Web Page’ to display the search result:

To open and display the Account record from ‘Global Search’, we need a new ‘Web Page’ with ‘Account’ Entity Form.

  • Create a new ‘Entity Form’ by selecting ‘Account’ entity and map one of the Account form. I’ve named my ‘Entity Form’ as ‘Account’.

GS_21

  • Create a new blank ‘Web Page’.

GS_8

  • Add a ‘Form’ component to the ‘Web Page’ and map the ‘Account’ entity form created in previous step.

GS_22

Add ‘Site Marker’ for the ‘Web Page’:

  • From the ‘Portal Management’ Model-driven App go to ‘Site Markers’.
  • Create a new ‘Site Marker’
    • Name : {EntityName_SearchResultPage}. In my case, its ‘account_SearchResultPage’.
    • Page: Web Page which we created in previous section.

GS_18

Grant permissions to Portal Users:

Portal User must have a minimum ‘Global’ Read access on the entity you are configuring global search for. In my case it’s ‘Account’ entity.

  • Create a new ‘Entity Permission’ as below.

GS_13

  • Create a new ‘Web Role’ and map the ‘Entity Permission’.

GS_14

  • Grant the ‘Web Role’ to the ‘Portal User’.

GS_16

Rebuild Search Index:

We left with one last step before we test the Global Search. Its ‘Rebuild Search Index’

  • Connect to your portal. Navigate to ‘../_Services/about’ page.
  • Click ‘Rebuild search index’.

GS_12

Test the Global Search:

  • Connect to your Portal.
  • Click on ‘Magnifier’ symbol from the ribbon and type your Account to Search.
    • Note: You can Search only the columns available in your ‘Portal Search’ view added on ‘Account’ entity.

GS_23

  • You would get the matching Accounts as below.

GS_19

  • Click on the one of the Accounts link and you would be redirected to that ‘Account’.

GS_20

Note:

  • Refer this PG’s article for more details.
  • To remove the entity from Global Search refer this article.

🙂

 

 

 

Embed Canvas App Error – Environment doesn’t have any CDS database

If you encountered following exception while embedding Canvas app in Model Driven App, then this article is for you.

Canv_4

Reason:

  • You must have a CDS database in your environment to configure Embed Canvas App, as the Canvas App metadata gets stored in CDS database.

Fix:

  • Connect to PowerApps Admin Center
  • Open the ‘default’ environment.
  • Click on ‘Add database’.

PAPPS_1

  • Give it sometime to complete the database setup.
  • Once done, you would get ‘Database version’ rendered as below:

Canv_5

  • Retry embedding Canvas app.

Refer article for step by step details to embed Canvas app in Model driven app.

🙂

 

[Step by Step] Embed “Azure Blob” connector Canvas App in Model Driven App

In this article, lets learn

  • Building a Canvas App with the functionality to upload and display images to/from Azure Blob.
  • Embed the Canvas App in Model Driven App.

Before we start, make sure you have the following prerequisites met.

Prerequisites:

  • PowerApps account. Refer here to procure an account.
  • Azure Storage Account and Container. Refer here for steps to create Azure Storage account.

Now that we know pre-requisites, lets understand the high level design:

  • Configure an Azure Container
  • Configure a ‘Model Driven’ App with OOB ‘Account’ entity.
  • ‘Embed’ Canvas app to ‘Account’ form.
  • Configure Canvas App with ‘Upload’ and ‘Display’ files to Azure Blob feature.
  • Save & Publish the Canvas App

Configure an Azure Container and capture ‘Access Keys’:

  • Add a new Container in Azure Storage Account.

Canv_8

  • Copy the ‘Storage account name’ and ‘Key1’ under ‘Access Keys’ tab of storage account, which would be needed in next steps.

Canv_9

Configure a ‘Model Driven’ App with ‘Account’ entity:

  • Configure a new ‘Model Driven’ App with OOB ‘Account’ entity.

Canv_22

‘Embed’ Canvas app to ‘Account’ form

When adding an embedded canvas app to a form always use a required field. Refer article for detailed steps.

  • Open the ‘Account’ entity’s Main customization form.
  • Add a new ‘Tab’ to the form and place ‘Account Rating’ field.
  • Go to ‘Field Properties -> Controls’ tab of ‘Account Rating’ field.
  • Click on ‘Add Control’ and choose ‘Canvas app’ and click on ‘Add’

Canv_2

  • Click on ‘Customize’ which opens up a new ‘Canvas App’ with ‘ModelDrivenFormIntegration’ component and few fields.

Canv_3

Configure Canvas App with ‘Upload’ and ‘Display’ files to Azure Blob:

As we are building ‘Upload’ to Blob functionality we would’t need the default fields.

  • Delete the auto populated fields and ‘Form1’.
  • Add a ‘Add picture’ control to the form.

Canv_7

  • Add an ‘Azure Blob Storage’ connector and provide ‘Storage Account Name’ and ‘Account Access Key’ captured in previous steps.

Canv_10

Now that we established connectivity between our Canvas app and Azure Blob container. Lets add the Upload functionality.

  • Add ‘Upload’ button and ‘OnSelect’ add following statement to upload the file to blob
    • AzureBlobStorage.CreateFile(“canvasimages”,AddMediaButton1.FileName,AddMediaButton1.Media);
      • “canvasimages” is the Container Name.

Canv_11

  • Run the ‘Canvas App’ and browse and ‘Upload’ image.

Canv_12

  • Make sure the file gets saved to ‘Blob’ container.

Canv_13

Steps to display images in Gallery:

  • Add a Gallery and set the ‘items’ property as below
    • AzureBlobStorage.ListFolderV2(“JTJmY2FudmFzaW1hZ2Vz”).value
    • “JTJmY2FudmFzaW1hZ2Vz” is the Azure Container ID.

Canv_23

  • Now set the ‘Image’ property of image control as below
    • AzureBlobStorage.GetFileContent(ThisItem.Id)

Canv_16

Save & Publish the Canvas App:

  • Save the Canvas App.

Canv_17

  • Navigate to Model Driven app’s ‘Field Properties’ window.
  • You should see the ‘App Id’ populated.

Canv_18

  • Click ‘Ok’ and publish the customization’s.

Test the Embedded Canvas App:

  • Open the existing Account record.
  • Navigate to ‘Upload’ tab and you would get the Canvas App loaded.

Canv_21

  • Click on ‘Tap or click to add a picture’ to browse the image you would want to upload.
  • Click on ‘Upload’ button to upload image.
  • We can also add ‘Reset’ option using ‘Reset(AddMediaButton1)=true;‘ statement.
    • Also add “ModelDrivenFormIntegration.RefreshForm(“true”);” which refreshes the data on the host model-driven form.

Canv_24

Notes:

  • Refer guidelines while working with embedded canvas apps.

🙂

 

 

DataMigrationUtility – Unable to connect to the Dynamics instance

I was getting following error while connecting to dynamics instance using ‘DataMigrationUtility’ comes with SDK.

You don’t have permission to access any of the organizations in the Microsoft Common Data Service region that you specified. If you’re not sure which region your organization resides in, choose “Don’t know” for the CDS region and try again. Otherwise check with your CDS administrator.

 

MigrationUtility_1

 

Fix:

It seems the issue is with ‘DataMigrationUtility’ and following workaround resolved the issue in my case

  • Get the latest version of the ‘DataMigrationUtility’ tool.
  • In the Login screen,
    • Select ‘Online Region’
    • Leave ‘User Name’ and ‘Password’ fields blank

MigrationUtility_2

  • Click ‘Login’
  • Tool will prompt you the credentials in a new popup.
  • Enter your credentials and ‘Connect’

Notes:

  • I tried by unchecking ‘Show Advanced’ option but had no luck.
  • Also, I could not connect when I chose “Don’t Know” option in ‘Online Region’ field and left the ‘User Name’ and ‘Password’ fields blank.
  • The exact combination specified in ‘Fix’ worked for me.
  • After the workaround, I could connect to the Dynamics instance from ‘DataMigrationUtility’ normally from next time.

🙂