Apple macOS and the TDS endpoint

Part 4 about Mac Mini M4 and Power Platform, this time is about SQL and Dataverse.
Dataverse offers a way to access its data using SQL statements (read-only) by enabling the TDS endpoint.
The documentation shows how to use it with SQL Server Management Studio (SSMS) but this application is only for Windows.

The main reason I use the TDS endpoint is to run some queries (like a count or strange joins) and they are usually faster using SQL. For XrmToolBox there is SQL 4 CDS by Mark Carrington but for now I don't want to launch the virtual machine.

Lucky for us Microsoft has a cross-platform product with similar functionalities as SSMS:
Azure Data Studio
Just download the macOS Apple Silicon version and we are ready to go:

When connecting to the TDS endpoint the procedure requires to specify the database name, it's usually the same prefix of the server:

After the connection we can launch SQL queries and eventually save them in a Notebook:

I am sure SSMS offers different functionalities but for what I need Azure Data Studio is enough.

Before finishing, let's back a moment to SQL 4 CDS, did you know it is also available as Azure Data Studio plugin?
You can download the VSIX from the GitHub repository releases and it can be installed inside the Mac version of Azure Data Studio.

It requires .NET SDK 8 (for PCF development we installed .NET 9 SDK) so install it first (link) otherwise the extension will not work.

After the extension is installed we can create a new connection using SQL 4 CDS and run other commands like UPDATE:

It's nice to have this tool available also inside Azure Data Studio and I don't need to launch the virtual machine!

Apple macOS and Console Applications with Dataverse

Third episode with the Mac Mini M4, let's try to connect to Dataverse using a console application.

I like very much Visual Studio for Windows but the Mac version has been discontinued, details here: What happened to Visual Studio for Mac?

The alternative is Visual Studio Code (we used it for PCF development on the previous post) with the extension C# Dev Kit.

When you create a new project using this extension you get two default settings:

• top level statements
• implicit using

What it means? When you open the Program.cs file you see only the line

Console.WriteLine("Hello World!");

instead of the "classical" structure with the using statements and the Main method.

The top level statements can be turned off by showing the template options and there is a setting for it.

The implicit using can be disabled by removing the line
<ImplicitUsings>enable</ImplicitUsings>

inside the csproj file

After these two changes we get a more "familiar" structure, something like this:

using System;

namespace Test;

class Program

{

static void Main(string[] args)

{

}

}

Next step is to reference the Microsoft.PowerPlatform.Dataverse.Client NuGet package and write some code to execute the WhoAmI Message:

using System;

using Microsoft.Crm.Sdk.Messages;

using Microsoft.PowerPlatform.Dataverse.Client;

namespace Test;

class Program

{

static void Main(string[] args)

{

Console.WriteLine("Hello Dataverse!");

string userName = "guido.preite@___.it";

string url = "https://___.crm4.dynamics.com";

string connectionString = @$"AuthType='OAuth'; Username='{userName}'; Url='{url}';

AppId='51f81489-12ee-4a9e-aaae-a2591f45987d'; RedirectUri='http://localhost';

        LoginPrompt='Auto'";

ServiceClient service = new ServiceClient(connectionString);

WhoAmIResponse whoAmIResponse = (WhoAmIResponse)service.Execute(new WhoAmIRequest());

Console.WriteLine($"Connected with UserId: {whoAmIResponse.UserId}");

}

}

A screenshot with the result:

It's useful to know I can write a console application connecting to Dataverse, I may need to test some small logic or running some updates on a set of records, it's very common scenario for me to open the editor and launch this kind of code. Plus I can do this completely inside macOS without opening the Windows virtual machine.

Apple macOS and PCF Development

Second part about my journey with the new Mac Mini M4 (first post here) and surprise surprise is about PCF development!

I usually don't code PCF components because most of the time I can find the one I need inside PCF Gallery, there are more than 550 components listed there, so big kudos to all the authors.

PCF development usually brings people not familiar with Power Apps/Power Platform inside the ecosystem, when a new control is published inside PCF Gallery I don't ask where they developed it but I suppose someone used a Mac.

First piece is to install Visual Studio Code, it's cross platform, perfect.

To develop PCF controls we need two important components:

• NPM
• Microsoft Power Platform CLI (also knows as PAC CLI)

To install NPM there are several ways, I installed first Homebrew (remember to add to the path by using the commands listed after the installation) and after I launched the command brew install npm.

To install the Microsoft Power Platform CLI you can install this Visual Studio Code extension:

Power Platform Tools

BUT before installing this extension you need to have the .NET SDK on your machine, otherwise you get an error.

The latest versions of .NET are cross platform, I installed the SDK Installer, macOS Arm64 version from the official site:

https://dotnet.microsoft.com/en-us/download/dotnet/9.0

After the SDK, proceed to install the Power Platform Tools extension and we can verify the PAC CLI is installed by running inside the Visual Studio Code Terminal the command pac:

With NPM and PAC CLI we can now create a new PCF, just follow the Microsoft documentation for the right syntax:

Create and build a code component

Once you have the structure inside your Visual Studio Code you edit and test the PCF as usual, in my case I recreated my PCF Custom Url Control.

There is also a Microsoft documentation page on how to create a solution and package the PCF inside it:

Package a code component

Note: the path written inside the documentation is Windows style (c:\), as we are on Mac we need to use a different style, example:

pac solution add-reference --path /Users/username/folder

and because we have the .NET SDK installed we can use the command

dotnet build

this will create an unmanaged solution inside a debug folder, if you want a managed solution you can run the command

dotnet build -c release

Once you have the solution you can install it inside your environment and configure the PCF. A screenshot of my component inside a form:

I had no doubts I can create PCF controls with a Mac but to prepare the machine I needed some steps, however I had no problems by following the instructions I found from web searches and the Microsoft documentation.

What I will write inside the next episode? I don't know yet!

Apple macOS and Power Platform - Introduction

After watching numerous YouTube videos about the new Mac Mini M4 I decided to buy it, here a photo beside my mouse:

It's not my first Apple computer, I have a MacBook Air 2012 that I still use these days for browsing and of course I am a bit locked inside the ecosystem with my iPhone.

The majority (if not all) of the videos and articles about the new Mac Mini M4 is about editing videos, games and general productivity. All the reviews are ok but I wanted to know how this machine performs for MY work, considering I spend several hours with Visual Studio 2022.

I bought the basic model (16 GB of RAM and 256 GB of storage) for two reasons:

• It's the entry level (so the cheapest model)
• RAM is not expandable, so I want to know if they are enough or not (because RAM on these machines is expensive)

First of all: all the activities where only a browser is required works perfectly (basic tasks like modifying a table or adding a column) but I am sure everybody knows this.

Which is the first tool I need for my Power Platform activities? Of course XrmToolBox!

Let's go technical, this new Mac Mini M4 is an ARM machine and the current virtualization technology only allows (with decent performance) to run Windows 11 Arm edition, let's try it.

There are several virtualization software: Parallels, VMware and VirtualBox.

I like and use VirtualBox on Windows but I am not convinced of its performances about Windows 11 Arm.

I use Parallels but it's a paid product, I already spent money to buy the machine, let's skip it for now.

VMware offers a free version of VMware Fusion Pro, so I decided to use it.

You can follow this YouTube video: https://www.youtube.com/watch?v=LWXO4DhQRL0

Note: the download page is a bit different now, you need to download the latest version of the software and when you run the setup you get prompted to obtain the free version (some of the comments in the video are about this step).

The video also provides the step to install Windows 11, so it was perfect for me.

After I completed the installation, the question is: will XrmToolBox works?

And the answer is YES!

I quickly downloaded some tools (the one I used the most), in particular:

• FetchXML Builder by Jonas Rapp (the most downloaded tool)
• Plugin Registration Tool (a must for the work I do)
• Custom API Manager by David Rivard (I am an avid user of this tool)
• Ribbon Workbench by Scott Durow (still required sometimes)
• Dataverse REST Builder by me

I don't know if they work 100% of the cases but I didn't experience crashes and also Ribbon Workbench that is still using the old Internet Explorer engine works under Windows 11 Arm.

My tool uses the WebView2 component (based on Edge) so I also wanted to know if it works as well.

The virtual machine to run Windows 11 Arm is configured with only 4 GB of RAM (Windows and XrmToolBox works fine), the used RAM in the moment I am writing is around 12 GB but I have several apps opened (including Teams for Mac occupying 800 MB and Google Chrome taking 1 GB), with only the Virtual Machine opened you use 8/9 GB total of RAM. In my opinion 16 GB of RAM to use XrmToolBox inside a Virtual Machine are enough, my goal is not to use the Virtual Machine for everything (I don't plan to install Visual Studio 2022 inside it) but only for the apps I cannot use directly on macOS.

I will be able to use this new Mac for everything? Let's see in the next episodes!

a Canvas app for bus tickets? Possible!

A month ago I attended Scottish Summit 2024 in Aberdeen, because was a new place for me I decided to visit the city and I used the public transportation.

I downloaded the "Stagecoach" app and I bought a ticket, it appeared like this screenshot:

When I launched the app I was surprised, in addition to the QR Code in the top there is also a centered colored bar, switching between the time and a random word.

The colored bar also tilts based on the accelerometer of the mobile phone.

Why they implemented this colored bar? The reason (in my opinion) is to facilitate the driver to check the ticket validity without scanning the QR Code.

• Color and word change every day, today can be green with the word "house", tomorrow can be red with the word "crisp", probably at the beginning of the shift the driver knows the color and the word of the day.
• The switch between the time and the word and the tilt is to show you are inside an app and is not a screenshot

After I used the app I asked myself: "Can I build a Canvas App with similar concepts?"

I usually don't do Canvas apps but this was interesting to me for two reasons:

1. it's not the usual gallery/edit data app
2. I am implementing a real business scenario

Let's start with the QR Code, there are some PCF controls but I wanted to keep it as simple as possible, so I used a service from this site: https://goqr.me/api/

It returns a QR Code as image with the text I want, for example this one shows my LinkedIn profile:

https://api.qrserver.com/v1/create-qr-code/?size=320x320&data=https://www.linkedin.com/in/guidopreite/

The challenging part is of course the colored bar.

First of all I needed to display the current time, so I added a Timer (with AutoStart equals true and Duration 1000 - 1 second) where I wrote the following formula:

Set(CurrentTime, Now())

In this way I have a variable updating every second with the current time (Now function).

After I added a label to display the time, I just want to show hours and minutes, so the formula for the Text property is:

Text(CurrentTime, "HH:mm")

After I needed to switch between the time and the word, I decided to create a second label with some dummy text and I needed some logic to make visible the first label and hide the second (and vice versa) every X seconds.

Another timer then, this time running every 3 seconds and with AutoStart true. Power Fx has the Mod function, if I do a formula Current Seconds MOD 2, I get 0 or 1. Because the timer runs every 3 seconds, I am sure he seconds will be one time odd and one time even. Let's wrap everything in this formula: the variable is named CurrentMode, it fetches the seconds of the current time, converting it to a number (Value function)

Set(CurrentMode,Mod(Value(Text(Now(), "ss")),2))

Now I can use the variable CurrentMode for the Visible property of these two labels, one is set to CurrentMode and the other to !CurrentMode. In Power Fx 0 and 1 are equivalent to false and true statements.

Next step is the tilt, I didn't find a way to rotate a shape inside a canvas app so I decided to change the vertical position of the labels based on a device property. I used the Compass.Heading but there are others functions you can use.

The formula I applied for the Y property is the following:

350+(Compass.Heading)

So every time I move the mobile phone, the label position gets updated.

Now we need to take care of the color, for simplicity I assume each day has a different color, so the Fill property of the label is a Switch statement based on the Weekday of Today (Weekday returns a value from 1 to 7). Formula:

Switch(Weekday(Today()), 1, Color.LightBlue, 2, Color.Orange, 3, Color.LightGreen, 4, Color.Yellow, 5, Color.Violet, 6, Color.PaleVioletRed, 7, Color.LightGray)

Last part is to have a different word every day, we need 366 words, so I created a Named formula called Words, storing a Table with a property named Word:

Words = Table({Word:"apple"},{Word:"bread"},{Word:"crane"}, ...)

This is a bit tricky, inside a Canvas app we don't have a function as DayOfYear so I needed to use DateDiff function with a combination of Year and Today functions:

Index(Words,DateDiff("1/1/"& Text(Year(Today())),Today())+1).Word

The Year is used to get the first day of the year, DateDiff calculates the difference and we add 1 because Index to access the table starts from 1.

Now the app is ready, here a video in action (I left the timers and the value of the Mod variable visible):

https://www.youtube.com/shorts/jIx48p0k-yM

new project: Hōjin Bangō Custom APIs (Japanese Corporate Number)

I was recently in Japan (not my first time there) and each time I try to learn a bit about the culture.
One of my recent encounters is the Corporate Number called in Japan hōjin bangō (法人番号), in a certain way the equivalent of a VAT Number to identify a company.

My next thought was: I am sure there is a service to check the validity of these corporate numbers.

There is indeed a web site by the National Tax Agency in English and Japanese and looks like there is also a web service to check the validity by using an API, more info (only in Japanese) here.

I am not proficient in Japanese but with the help of Google Translate looks like this endpoint is not public (like the EU one) but requires a registration and can take up to a month.

For me it's not possible to use this API endpoint but their website allows to search without registration a corporate number.

Next step was to search for some Japanese corporate numbers to verify them, quickly I found the number of two companies: Nintendo (1130001011420) and Sony (5010401067252) ;)

This is a website screenshot with a result, if the number is correct, the website returns the company name (also in Furigana) and the address:

My goal was to create a Custom API to verify a corporate number, because I am not able to use their API, I used the old WebClient to call the page and parse the returned content.

I am aware this is not a robust situation but we can apply this technique when an API is not available and the parsing of the page is not complex.

The Tax Agency website accepts a URL in the following format:

https://www.houjin-bangou.nta.go.jp/henkorireki-johoto.html?selHouzinNo=

followed by the corporate number

and if the number is found, the details are contained inside a dl tag (Description List).

The Custom API call the parameterized URL, parse the response and returns 4 properties:

• Success (if the number is found or not)
• CompanyName1
• CompanyName2
• Address

You can find the code and the managed solution inside the repository:

https://github.com/GuidoPreite/HojinBangoCustomAPIs/

Here a screenshot of this Custom API inside a Power Automate flow using the "Perform an unbound action":

hope it helps!

Rich Text to Plain Text conversion inside Power Apps

Sometimes we need to get only the text from an HTML document, like the Rich Text format available for the text type inside Dataverse/Model-driven app.

If you need to perform this action client-side, a simple JavaScript function can be used:

function parseHTML(html) {
	let doc = new DOMParser().parseFromString(html, 'text/html');
	return doc.body.textContent || "";
}

function test_richtext_OnChange(executionContext) {
	var formContext = executionContext.getFormContext();
	let richTextValue = formContext.getAttribute("test_richtext").getValue();
	let cleanValue = parseHTML(richTextValue);
	formContext.getAttribute("test_plaintext").setValue(cleanValue);
}

It uses the DOMParser interface, here a screenshot on how the result looks like inside a model-driven app:

Hope it helps!

Power Fx and what I mean for low-code

Power Fx is one of the programming languages available inside Power Platform, the Microsoft documentation reports:

Power Fx is the low-code language that will be used across Microsoft Power Platform.

It's mostly used inside Canvas Apps but inside a canvas app it's very difficult for me to call it "low-code", if there is a function called "Explain This Formula" inside Copilot (link) it usually means it's hard to read or people tend to write complicated code that is not readable.

Being a developer probably for me it's easier to read some C# code than some Power Fx formula connected to items inside a canvas app where fields are referenced with the display name.

However there is another part where Power Fx is used inside Power Platform: the possibility to create new Formula columns inside Dataverse, in my opinion this is a very powerful tool at our disposal.

Years ago I created this PCF: Custom Url Control
The idea behind the PCF was simple, to create a clickable url with data coming from the record.

The same thing these days can be done with a Formula column writing a simple Power Fx formula.

Let's assume you have two columns inside a table: Code (text containing the tracking code) and Company (choice with the couriers, in my example USPS and UPS)

We want to create a URL column so when the Company is USPS the link is:
https://tools.usps.com/go/TrackConfirmAction?qtc_tLabels1=[Code]
and when the Company is UPS the link is:
https://www.ups.com/track?tracknum=[Code]

This column can be a Power Fx formula using a Switch statement:

Switch(Company, 'Company (Shipments)'.USPS, "https://tools.usps.com/go/TrackConfirmAction?qtc_tLabels1=" & Code, 'Company (Shipments)'.UPS, "https://www.ups.com/track?tracknum=" & Code )

In this case the first parameter of the Switch operator is the column we are checking: our Company column.
The following parameters needs to be considered by two: Match and Result.
Match in our case is the choice value (USPS) and the Result is the URL we want, the first part is fixed (the USPS website) and after is concatenated the Code.
You can also see the second couple (UPS choice and UPS website).

The result in a model-driven app form is the following:

The main advantage? The value of this column is available everywhere: inside a model-driven app, a canvas app, a Power BI report, a Power Automate flow, etc etc.

This is in my opinion a good example of low-code, you wrote something simple, understandable but very powerful, years ago for the same requirement you needed to create a PCF, before that probably a C# plugin, now just a short Power Fx formula.

My idea to give something to the community: the CCA License!

If you follow tech news you may have read something about "xz Utils" (you can find an article about it here).

I don't have a solution to this kind of problems and my support goes to Lasse Collin, the creator and my maintainer of xz Utils.

How an Open Source software is licensed plays an important role and made me think:

what can I add to the MIT License (the license I normally use) something to recognize the community around what I am releasing?

This is the idea behind the CCA License (link: https://github.com/GuidoPreite/CCA-License)

CCA stands for Community Contributor Awareness, practically is a MIT License with the addition of a donation clause.

With the next releases of some of my tools I will start to use the CCA license, with this donation clause:

XrmToolBox or any of the Open Source tools in its library, any time, no minimum amount required or you are a code contributor of an Open Source software related to XrmToolBox.

What does it means?

It means that in order to use my software and be complaint with its license, the end user must have donated to XrmToolBox or any of the Open Source tools in its library, I don't care if you donated in 2018 or in 2024 (for me the important is that you donated), I don't care how much you donated (we live in different parts of the world), also you can use my software if you created an Open Source tool for XrmToolBox.

The GitHub repository of the CCA License has some examples of donation clauses.

XrmToolBox is an important software for anyone working with the Power Platform and it's free. I use it everyday, what I am doing with this new license is to help with this project and the ecosystem around it.

I am well aware this license will not change the world but I hope it can help a little some projects that are essential to this community.

new project: QR Code Custom APIs

As I wrote in my previous post, in the recent months I created several Custom APIs and some of them are available inside my GitHub account.

The latest one is QR Code Custom APIs

Right now there is a single Custom API called GenerateQRCodeUrl

As input accepts a Url (string) and it returns the Base64 representation (string) of a PNG file, this can be used for example inside HTML content (setting the src property like data:image/png;base64, [BASE64 VALUE]) or for example to store the value as a note/attachment.

Here an example of a QR Code generated with this Custom API (it points to www.microsoft.com)

Big thanks to the project QRCoder that I am using inside this Custom API.

Impersonate Dataverse Users connected to Entra ID Groups

When a Security Group is assigned to a Dataverse Environment users need to be part of the selected Microsoft Entra ID Group (directly as members or if they are inside a group that is member of the main group) otherwise they cannot be added.

This facilitates the work of the IT department to manage who can access or not a specific environment, also a specific Team inside Dataverse can be created to map the Microsoft Entra ID Group:

Keep in mind that to a Dataverse Team you can assign Security Roles or Column Security Profiles.

Which is the disadvantage? By default users appear inside the Environment only after their first login, for some apps this is not a big deal, but for other apps users must be present before their first login.

To force the users you can use a PowerShell cmdlet (link) or a Power Automate flow (link1, link2), in this way the users will be added to the environment and they will appear inside the systemuser table.

However this approach solves half of the problem (in my opinion) because the users are added indeed to the Environment, but they are not added to the Dataverse Team connected to the Entra ID Group they are coming from, the association to the Team will happen indeed at their first login.

Let's recap: inside Entra we have the user "John Doe", it has been added inside the Entra ID Group called "Super Users" setup as Security Group for the Environment.

Inside the Environment there is the Team (Entra ID type) connected to Entra "Super Users", we forced the synchronization of the user so there is a systemuser record related to "John Doe" but this record is not associated with the Team "Super Users".

And you can think: why we should care? the problem is that the user is inside the Environment but without security roles, probably the security roles will be inherited from the Teams the user is a member of, but right now is not member of any team, they need to login first.

Again: why we should care? the problem is that if the user has no security roles, the user cannot be the owner of a record inside your Environment.

Think about a migration: you have 500 users, and 10000 account records to migrate from Ennvironment A to Environment B, these 500 users they have the right security role by the Team described above but we cannot assign them an account until they login first.

The solution is to programmatically impersonate these users and perform an action inside the Dataverse, in this way we simulate a login and all the processes associated with it (including the association to the Team)

You can probably do this with a Power Automate flow but I decided to implement this logic in C#, mainly because the WhoAmI request is not available inside the unbound actions but is one of the common requests to be executed using the C# Dataverse SDK:

QueryExpression querySystemUsers = new QueryExpression("systemuser");
querySystemUsers.ColumnSet = new ColumnSet("fullname");
EntityCollection collSystemUsers = service.RetrieveMultiple(querySystemUsers);
// Note: if your users are more than 5000 you need to do pagination!

Dictionary<Guid, string> dictErrors = new Dictionary<Guid, string>();
foreach (Entity user in collSystemUsers.Entities)
{
    try
    {
        service.CallerId = user.Id;
        service.Execute(new WhoAmIRequest());
    }
    catch (Exception ex)
    {
        dictErrors.Add(user.Id, $"{user.GetAttributeValue("fullname")} - {ex.Message}");
    }
}

As you can see inside the code, the property "CallerId" is used to impersonate the user before executing the WhoAmIRequest (probably you can use also the property "CallerAADObjectId" if you have a list of the Entra Object IDs if the users are not synced before).

Why I added a try/catch inside my code? If for example the user has not a license, it will throw an exception, so better to handle this scenario as well.

Hope it helps!

Custom APIs for Power Automate: why they should be developed

Power Automate is an important piece of the Power Platform, no doubts about this.
Can it be improved? Sure.

Low-Code and Pro-Code are two buzz words constantly being around Power Platform, which side you prefer it's not my business, as I often says there are different ways to implement a process, it's also the beauty of programming (traditional or visual).

One thing I do not like is to create convoluted processes to achieve something that is very easy using another tool, we always need to tend to simplification in my opinion, considering the context where the application lives.

How can we improve and simplify flows created in Power Automate? If your flows have Dataverse at your disposal, one way is definitely Custom APIs.

The documentation link is this one https://learn.microsoft.com/en-us/power-apps/developer/data-platform/custom-api and at the beginning is written:

"...you and other developers can call in their code or from Power Automate."

Last Nordic Summit I had the pleasure to meet Amey Holden, she presented a session about Power Automate and one of the examples she demoed was about Choices, she needed to query the Metadata and her flow was working. However I instantly saw the opportunity to create something to help the specific task she faced. The result was a Custom API and she described it in this blog post:

Converting Dataverse Choice(s), (Multi-select) Option Sets, or Picklists in Power Automate

The Custom API I created (download here) simplified the flow, no need to query the Metadata writing the http query, no need to add special conditions, it's just a block doing the task but it's easy to use and does the work.

Why I am talking now about this? In the last months I created several Custom APIs designed to be used inside Power Automate flows, and yesterday I created another one that is public (RegexCustomAPIs) to execute Regex operations. (thanks Mark Christie for giving me the idea).

The Regex operations available are Match and Replace and they may fail sometimes due to the Regex pattern passed as input, but it's a starting point. As I said in the beginning there are different ways to implement something and you can find other ways to execute a Regex inside Power Automate, what I created is just another way and it may be useful to you.

So next time you are creating a flow and you think a Custom API can make the flow easier to interact and less cluttered, worth asking if it's doable.