Getting the Binary Value of an ASCII Character in Power Apps

Huh?

This is the 3rd of a series that could easily be called “Doing Stuff Nobody Asked for in Power Apps”. You can find the previous parts here:

Now it’s time to extend our binary conversions a little farther to allow us to get the binary value of an ASCII Character just like you’ve always wanted! Again, allow me to give another unclear reassurance that there are legitimate reasons why you would do these things and I promise the next part in this series will make that clear.

There’s no reason for this dog with a bowtie, but there’s also no reason not to have a dog with a bowtie

Let’s do it!

I recently needed to get the binary value of an ASCII character (as you do). So, I just figured I’d get the ASCII value for a character and then use my handy Integer to Binary conversion method to take care of it. As you have likely guessed (by the fact the I’m writing a whole post about it), this wasn’t nearly as straightforward as I had thought.

Background Stuff

If you know the ASCII code (value) for a character, Power Apps makes it very easy to get that character by using the Char function. You can literally use Char(65) and you’ll get A. Nearly every language that has this function has its reverse usually called something like Asc and you could call that with A and get 65. But if you’ve been using Power Apps for very long you’re likely not surprised to learn that Power Apps has no equivalent function. Blarg!

A quick search came across this excellent article by Tim Leung, Text – How to convert a character to its ASCII numeric value. In this post, Tim lays out a method to generate a collection using the Char function against numbers 0-255 that can then be used to lookup the ASCII value and he does it all in a single line. If that’s all you’re here for, then click on that link and you’re done. In our case, we need to go just a little farther and be able to get the binary equivalent of the ASCII value for a character so we’ll be combining Tim’s technique with our integer to binary conversion logic laid out previously.

Building the Collection

To make this a little more understandable, I’m going to break this down into pieces. The first is the generation of a collection that will contain the values. Later I’ll show you how to use the collection to get individual values.

I’ve setup a beautiful Canvas Power App with this screen:

A DataTable with its Items set to the asc collection, a label that counts the values for debugging and a button to generate the collection

I’ve done this all in a button to make it easier to demonstrate but in reality you’d probably put this code in your App.OnStart if using it frequently. You wouldn’t need any of the other controls above either.

For those that read the last part in this series, we will be reusing / adapting some of that code. You don’t need to read that article to get this to work but it would probably help make sense of this stuff.

Here’s the code from the OnSelect of that button:

If(CountRows(binaryDigits)=0,
ClearCollect(binaryDigits,[]);
ForAll(Sequence(8,7,-1),
Collect(binaryDigits, {
index: Value,
worth: Power(2,Value)
})
)
);
ClearCollect(intToBinary,[]);
ClearCollect(asc,[]);
ForAll(Sequence(255),
With({
firstDigit: CountRows(binaryDigits) – 1, //starting point depending on the number of binary digits
input: Value,
itbOffset: CountRows(intToBinary)
},
ForAll(binaryDigits,
With({
remainder: If(index = firstDigit, input, Last(intToBinary).remaining) //what's left to process (starts with input and decreases)
},
Collect(intToBinary,{
binary: If(remainder >= worth, "1", "0"), //Actual binary digit (left to right)
remaining: If(remainder >= worth, remainder – worth, remainder) //process what's left
})
)
);
Collect(asc,{
Num:Value,
Char:Char(Value),
Binary: With({full: Concat(LastN(intToBinary,CountRows(intToBinary)-itbOffset),binary)},Right(full,Len(full)-Find("1",full)+1))
})
)
);

Let’s look at what’s happening line by line:

  • Lines 1-9 – This is the same code we used previously to generate a collection of binary digits and their positional values (worth). The last article went into more depth about what these lines do. For our purposes, this is a necessary step to be able to get the binary values.
    • Unlike the last article, we set the Sequence to have 8 records starting at 7 and decreasing by 1. This is because we only care about 255 characters (8 bits) but if you were looking to increase that range for your ASCII table you’d need to increase the size here as well.
  • Line 11 – We are using a temporary collection (intToBinary) to store results as we calculate the binary equivalent of the ASCII value. So, we clear it out before starting.
  • Line 12 – We need to initialize/clear our asc collection which is the actual collection we are building.
  • Line 13 – We start a ForAll loop using the Sequence function to generate a temporary collection of 255 numbers starting at 1.
  • Lines 14-18 – Using the With function allows us to setup some local variables that we’ll be using in our other calculations.
    • firstDigit is set to the total number of rows in the binaryDigits collection minus one. This allows us to know what the “size” of the number will be without having to hardcode it.
    • input is set to the number we get from the Sequence function. This is the ASCII value and what we’ll be using in our binary conversion.
    • itbOffset is the current number of rows in the intToBinary collection. We store this because we cannot clear a collection within a ForAll yet we need to use it 255 times. So we store the offset to know which set of records in our collection apply to which value.
  • Lines 19-28 – We start a nested ForAll loop against our binaryDigits collection. It’s this loop that will do the conversion of the ASCII value that we’ll be referencing later. The details of what this code is doing were covered in the last article.
  • Line 29 – This is where we actually build the record for an individual ASCII value and store it in the asc collection
  • Line 30 – We store the number from the Sequence function as Num and this is the ASCII value. If you only need the binary value this step can be eliminated.
  • Line 31 – We use the Char function to get the character value for the ASCII value
  • Line 32 – We pull the binary value we calculated above out of the intToBinary collection using the LastN function to only pull those values after our itbOffset to ensure we only get the binary conversion calculation values for the number we’re on. There is extra code here to trim the leading zeros from the binary values. Again, more details on how this code works can be found in the previous article.

Using the Collection

You can use the collection by pulling values using the LookUp function. For instance, if we wanted to pull the ASCII value for the letter A we could write LookUp(asc,Char="A").Num and the result would be 65. If we wanted the binary value we would modify it to LookUp(asc,Char="A").Binary and the result would be 1000001

That’s it! Now you can get the binary value of any character’s ASCII code – WOWEE!!

Want to learn how to use this collection to do more stuff that probably has no place in Power Apps? Come back for Part 4: Calculating a DJB2 Hash in Power Apps!

Thank you M365 Collaboration Conference!

I had the opportunity to speak at the M365 Collaboration Conference in Las Vegas this past week and it was awesome! I loved seeing all the friends I haven’t been able to see and I always enjoy the energy and excitement of a bunch of people gathered to learn and teach about stuff we all care about.

I helped teach 2 full day workshops and was able to once again give one of my favorite sessions: Advanced List Formatting. I love presenting this session because it’s demo heavy and it’s so fun being creative with lists and watching people’s eyes light up at all the possibilities.

The Grammys were happening at the same time. People were wearing the fanciest, most expensive Halloween costumes you could imagine!

For those that are interested, my Advanced List Formatting slides can be downloaded here:

Feel free to use the slides in your own presentations (internally or externally). If you feel like giving me credit, that’s great! But it’s not required. Sharing is caring afterall! The slides have several extra slides we didn’t go over (I prefer the demos) that will hopefully provide some additional insight. Feel free to reach out with questions.

Here is the list of samples I used in the demos so you can recreate what we went over:

  • Recruitment Tracker – We took the out of the box recruitment tracker list template and customized the existing column formats using the design panel to add icons to our choice fields. Then we went into Advanced Mode and swapped out the icons with ones we picked out from flicon.io

  • Field Notes – We looked at adding conditional rules to format our rows and also demonstrated a deep link into a custom Power App using the Launch Power App Button sample
  • Grouped FAQs – We looked at how we can use groupProps to customize grouped fields to create a miniature application. We also briefly looked at an alternative FAQ Format

  • Flow Status – We looked at how we can conditionally launch flows and display a dynamic flow chart to represent flow progress as well as a link to the exact flow instance
  • CommandBar Hide Automate – We expanded the flow status sample shown above to conditionally hide the Automate button when an item is selected
  • Elf Progress Board – We looked at building multi-layered progress bars, randomization, and inline editing with the elf progress board
  • Row Actions – We looked at the different default row actions and demonstrated live list updates and format rerendering
  • Random Item – We looked at how we can use layering and randomization to pick a random list item (turkey fact) to show on a page
  • Content Navigator – We demonstrated how to setup 2 views on a list and use them in conjunction as webparts on a page to enable simple navigation
  • Video Navigation – We showed how to use the list webpart with a dynamic connection to the embed webpart to create a custom video navigation application

Custom Icon Buttons in Power Apps with Hover Color

Using icons for buttons is a very common scenario in Power Apps and Microsoft has provided the Icon control to accomplish just that. But… there are only 106 across 4 categories which isn’t a whole lot when it comes to icons. So if you are making anything of any complexity you’ve likely already run out. Fortunately, that’s

Easiest Solution

Just use their stupid icons

Custom Icons

But what if you don’t want to use one of the very few icons they’ve provided? What if you found Flicon and would like your app to match the rest of Office 365 and use the Fluent / UI Fabric icons? Or perhaps Font Awesome or the Noun Project or something your kid drew?

Fortunately, there are still options! There is a pretty easy option laid out in the OKish Solution below and then a PITA Solution that works but there are several steps involved. I’ve included both, but I definitely think the PITA Solution is the way to go until Microsoft figures out something better.

OK-ish Solution

Use images but be satisfied with Border or Fill effects

PITA Solution That Works

What if you want more than just a changing background or border? You know, like how the icons themselves work in Power Apps? For this, we’ll need a custom icon as an SVG. These steps could be adapted to work with a PNG image, but SVG is very common among icon sets and provides a better result overall.

1. Get an SVG Icon

First step, get your icon! Font Awesome and the Noun Project both provide all of their icons in SVG format. However, I recommend using the Fluent Icons provided by Microsoft since that’s what’s being used everywhere else. For that, let’s head over to Flicon.io.

If you haven’t used Flicon.io before, just search for an icon you want to use or browse with the categories. Once you’ve got an icon in mind, hover over it and switch to the Export tab. You can mess around with colors if you’d like, but it isn’t really necessary for what we’re doing. Just click Save as SVG:

2. Edit that Icon

We need to edit the SVG file before it’s ready to be used. There are plenty of tools out there to do that, but I use a free, open-source tool called Inkscape. You can download it directly from the site or just install it from the Microsoft Store.

Open your icon in Inkscape. One thing you’ll notice is that most of the icons are square, but the image isn’t always centered. Don’t worry, we’ll account for that to ensure our icon is a centered square.

In Power Apps we can use an SVG as the Image for an Image control. The Image control has HoverFill as we showed above. We’re going to take advantage of that and create an inverse of our icon. This means we’ll be creating an image that is the background and leaves the part of the icon we want displayed transparent. This allows us to set the “color” of the icon using the Fill and HoverFill properties!

3. Create a Box

In Inkscape, draw a box (fill color doesn’t matter, but it shouldn’t have a stroke). The size doesn’t matter just yet. Grab the square tool and draw something. Don’t worry about making it perfectly square.

Click on your icon and look at the Width and Height displayed. Take note of the bigger of the two (mine happen to be the same):

Click on the square you just drew and put the value you just noted for both its width and height. Now you have a box sitting somewhere near your icon that is the same size as your icon’s largest dimension. If you want additional padding for your icon, add that to the box’s dimensions.

Now we’re going to line them up. Open the Align and Distribute panel (Object > Align and Distribute). Select both the box and your icon. Ensure the Relative to dropdown is set to Page then click the Center on Vertical Axis button followed by the Center on Horizontal Axis button. Your box should be totally covering your icon now.

4. Cut a Hole in a Box

With both the box and the icon selected (just select all if you clicked off of them), perform an exclusion (Path > Exclusion). You now have one object and it is the negative space around your icon (remember the color doesn’t matter).

You’ll notice we’ve got an extra space around the icon and the document size (seen as a black square above). This is because our icon wasn’t perfectly centered before. If your icon looks good, skip ahead. To fix it, however, go to Document Properties (File > Document Properties). In the Custom Size options group, expand the Resize page to content section and click Resize page to drawing or selection.

5. Optimize Your SVG

Let’s save our SVG. Although we can use the default format, there’s a lot of extra stuff added that we don’t need. So let’s Save as (File > Save As…) and change the type to Optimized SVG. In the dialog that pops up, here are the options I’ve chosen with the goal of reducing SVG complexity/length:

Let’s open up our SVG file using a text editor like Notepad. You should see some XML with a viewBox attribute and one or more paths. Let’s do a find for double quotes and replace them all with single quotes.

6. Use Your Icon in Power Apps

Although we can add an SVG file as media and use it that way, I like to have a little more control. So, let’s add an Image control to your App. In the Image property we’re going to replace SampleImage with some text. The first bit of text is just a string:

"data:image/svg+xml;utf8, "

This will let us use the SVG text directly. To do that we need to add the EncodeUrl function. So connect the text above with an & and enclose the SVG text (copied from notepad) in double quotes inside of the EncodeUrl function (don’t forget your closing double quote and parenthesis):

The icon is showing, wowee! Now we need to make that fill match the background (the black stuff shown above). So we can edit our SVG string to add fill='white' (or a HEX or RGBA value that matches your background which is white in my case).

Where did that icon go?! It’s still there, it’s just being sneaky.

7. Add Some Color

Now for the magic! Set the Fill property for the Image control (shown as Color in the properties window for some reason) and your icon shows up! WOWEE!

Now set the HoverFill to some other color and hold Alt to see the magic.

Hold down Alt to test it right in the Editor

Limitations

This is not a perfect solution and the fact that this is our best option is pretty frustrating. But… it gets the job done but with a few notable issues when compared to using the native icons:

  • That’s an annoying amount of work for each icon
  • No option to have the pointer cursor even when using it with an OnSelect action
  • Images sometimes get weird borders depending on the scaling
  • You need to keep your image ratio the same to avoid the background leaking out (set the Width to Self.Height and control just the Height)

Convert Modern SharePoint Page Banner Images to Base-64 using PowerShell

I was recently asked to write a PowerShell script that identified a bunch of pages and emailed them. They wanted the emails to include the Banner Image (the page/news thumbnail). No problem, I’ll just grab the handy BannerImageUrl field and stick it in some HTML, right? Nope.

Although you can certainly create an email with images using the URL, unless the user is logged in, those images will cause a bunch of authentication errors. This is especially a problem for people checking their email on their phones. Sadness!

Fortunately, you can grab those images in PowerShell and convert them to base-64 strings. That way the authentication for the images is only needed when running the script and not when the user opens the email.

The Father

Here’s a basic script that covers the concept using PnP PowerShell:

# Connect to your site
# (this example assumes an entry in Windows Credential Manager,
# but you can pass credentials however you need here)
Connect-PnPOnline https://yourtenant.sharepoint.com/sites/yoursite
$connection = Get-PnPConnection
# Setup a Web Client using credentials pulled from the connection
$client = New-Object System.Net.WebClient
$client.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($connection.PSCredential.UserName, $connection.PSCredential.Password)
$client.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
# Get a page
# (You could be doing this in a loop or using Get-PnPClientSidePage)
$page = Get-PnPListItem List "Site Pages" Id 294
# Grab the value of the image and convert it to base-64
# and slap the data information to the front
$image = "data:image/png;base64," + [convert]::ToBase64String($client.DownloadData($page.FieldValues.BannerImageUrl.Url))
# In this sample, just copying the HTML value to the clipboard to prove it works
# Normally, you'd build an HTML string and append it for your email
Set-Clipboard Value "<img src=""$image"">"
# All done!
$client.Dispose()
Disconnect-PnPOnline

In the Gist above, the HTML value is put in your clipboard. This is just to make it easy to prove it works. Run the script, paste the result in codepen in a browser where you are not authenticated to the site and witness the magic!

The key bit is the setup of the web client (lines 8-10) and the call to convert the downloaded data (line 18). You can easily wrap this logic up inside a foreach loop to process all your pages/news and build a nice html based email. Wowee!

Missing SharePoint Online Classic Administration Links

You may have noticed that your classic administration links are now missing from the SharePoint Administration center. Good luck editing your search schema or creating an app catalog now!

I have no idea why this was removed and hope this is corrected soon. Seems like a mistake to me even if the goal is to eventually roll out updated versions. Fortunately, if you happen to have the old links you can still visit them. Surely you bookmarked them all?!

No need to excessively weep! Reduce your wailing to dry sobs because here are the links to the missing admin pages. Copy them and paste them after your sharepoint admin domain (see below for an example).

infopath
/_layouts/15/TenantForms.FormServer.aspx

user profiles
/_layouts/15/tenantprofileadmin/manageuserprofileserviceapplication.aspx

bcs
/_layouts/15/bdc/TA_BCSHome.aspx

term store
/_layouts/15/termstoremanager.aspx

records management
/_layouts/15/TA_OfficialFileAdmin.aspx

search
/_layouts/15/searchadmin/TA_SearchAdministration.aspx

secure store
/_layouts/15/sssvc/TA_ManageSSSvcApplication.aspx

apps
/_layouts/15/online/tenantadminapps.aspx

sharing
/_layouts/15/online/ExternalSharing.aspx

settings
/_layouts/15/online/TenantSettings.aspx

configure hybrid
/_layouts/15/online/SharePointHybridSettings.aspx

access control
/_layouts/15/online/TenantAccessPolicies.aspx

data migration
https://docs.microsoft.com/en-us/sharepointmigration/introducing-the-sharepoint-migration-tool

With the exception of that last one, type in your sharepoint admin address (tenantname-admin.sharepoint.com) and then throw the link above after it.

For instance, here’s my classic search administration link:
https://thechriskent-admin.sharepoint.com/_layouts/15/searchadmin/TA_SearchAdministration.aspx

Excited, Happy, Woman, Fun, Happiness, Excitement

Update:

You can use this link to get to the original “More features” navigation page to make getting these links a little easier:
https://admin.microsoft.com/sharepoint?page=classicFeatures

Additionally, if you add ?showclassicnav=true to the end of any of the above links you’ll get that classic side navigation.

Extending the List of Sites You can Embed From in SharePoint Using PowerShell

The Embed web part for modern pages lets you display content from secure websites right on your page. Want to show a YouTube video? Grab the embed code from youtube.com and slap it in the Embed web part. Wowee!

By default, modern pages support 30+ sites including the most common like YouTube, Vimeo, TED, and internal domains like Stream and OneDrive. But what about when you’ve got content from a site not on this list? You’ll end up with an error similar to this:

Don’t cry! Wipe those tears off that wet face! If you just need to allow the domain for a single site, the instructions are right there (here’s a quick summary):

  • Go to Site Settings
  • Click on HTML Field Security under Site Collection Administration
  • Type the domain from the error message (no https://) into the box and click Add
  • Click OK
  • Give it another try

But wait… Corporate just rolled out a video hosting platform for the enterprise and they want all sites to be able to embed content from this new site. Does the thought of repeating the above steps hundreds or even thousands of times make you weep in despair? Smack those tears off your moistened face!

Here’s a quick snippet of PowerShell which will show you how to add it to multiple sites:

$SiteUrls = @("HR","Accounting","IT")
foreach($SiteUrl in $SiteUrls) {
Write-Host ForegroundColor Cyan "Applying to $SiteUrl"
$FullSiteUrl = "https://superspecial.sharepoint.com/sites/$SiteUrl"
Connect-PnPOnline $FullSiteUrl ErrorAction Stop
$site = Get-PnPSite Includes CustomScriptSafeDomains
$ctx = Get-PnPContext
$ssDomain = [Microsoft.SharePoint.Client.ScriptSafeDomainEntityData]::new()
$ssDomain.DomainName = "special.hosted.panopto.com"
$site.CustomScriptSafeDomains.Create($ssDomain)
$ctx.ExecuteQuery()
Disconnect-PnPOnline
}

In the PowerShell above, I’m using PnP PowerShell. You can technically do this without PnP PowerShell since it’s just CSOM, but… why would you make your life harder?

Here’s what’s happening:

  • The list of sites in line 1 is just an array of the URL portion of the site after /sites/. You could easily alter this to grab all associated sites for a hub or to get all sites within a classification, etc. But I find a simple list of URLs works pretty well.
  • We connect to the site in line 9 and grab the site object in line 11
  • We get the Client Context in line 12
  • We create a new ScriptSafeDomainEntityData object and set the only part we care about, DomainName, to the URL from the error message before
  • Then in line 17 we use the Create method to add it to the list of domains (there’s no problem if the site already has that domain, it won’t be added twice)
  • We execute the query for the client context to save our changes in line 19
  • Finally we disconnect from the site in line 21 and move on to the next site

You can easily adapt the script above as part of your provisioning process to ensure that new site have the correct domains whitelisted as well. So fun!

Now you can take content from all over the web and mash it together to bring all the relevant stuff directly to your users. WOWEE!

Dog, Pug, Bitch, Pet, Animal, Obedient, Funny, Cute

Customizing the Flow Panel with List Formatting actionParams

I’ve covered launching a flow for a list item using List Formatting a number of times, along with conditionally launching a flow, and recently I even showed how to take advantage of background list updates with automatic format updates using Flow.

In this post I’ll demonstrate some new tweaks the team has made that lets you customize the flow panel itself using list formatting!

Specifically, you can now provide custom text for the panel header and/or the run flow button:

This goes a long way to making the flow panel less scary. The words “Run flow” don’t mean a whole lot to users. Even if you’ve named your flow well, it can still be confusing. Now you can make things even easier by providing context and meaning directly in the panel. You could even customize these values based on values of the list item!

Customizing the Flow Panel

Here is a very basic flow button Column Format from the generic-rowactions sample:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "button",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "{\"id\":\"f7ecec0b-15c5-419f-8211-302a5d4e94f1\"}"
  },
  "attributes": {
    "class": "ms-fontColor-themePrimary ms-fontColor-themeDark--hover",
    "title": "Launch Flow"
  },
  "style": {
    "border": "none",
    "background-color": "transparent",
    "cursor": "pointer"
  },
  "children": [
    {
      "elmType": "span",
      "attributes": {
        "iconName": "Flow",
        "class": "ms-font-xxl"
      }
    }
  ]
}

The part we’re interested in is line 6, the actionParams. The actionParams property is currently only used for the executeFlow action. It is an escaped JSON string (the double quotes have a slash in front of them). And thus far, it’s been used to specify the ID of the flow.

Now you can specify the headerText and the runFlowButtonText properties inside of actionParams as well! Here’s what that looks like using the above format:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "button",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "{\"id\":\"f7ecec0b-15c5-419f-8211-302a5d4e94f1\", \"headerText\":\"Do the things and stuff\",\"runFlowButtonText\":\"Lazerify!\"}"
  },
  "attributes": {
    "class": "ms-fontColor-themePrimary ms-fontColor-themeDark--hover",
    "title": "Launch Flow"
  },
  "style": {
    "border": "none",
    "background-color": "transparent",
    "cursor": "pointer"
  },
  "children": [
    {
      "elmType": "span",
      "attributes": {
        "iconName": "Flow",
        "class": "ms-font-xxl"
      }
    }
  ]
}

The ID is always required but you can specify either or both of the headerText and runFlowButtonText to provide that customization:

Escaped PropertyWhat it does
IDThe ID of the flow to run. This is required.
headerTextReplaces the big text at the top of the panel with whatever you specify.
runFlowButtonTextSets the text of the primary button with whatever you specify.

Some things to keep in mind:

  • headerText will wrap if you get especially wordy:
  • But just because you can, doesn’t mean you should. It’s best to keep the header as short and concise as possible. (for anyone who wants to ignore me, I’ve found I had no problem displaying 5000+ characters)
  • runFlowButtonText will also allow you to put a large amount of text in the button, but it looks terrible because the cancel button wraps below it somewhere around 14-16 characters:
  • Unlike the headerText, runFlowButtonText will eventually just run off the screen as it never wraps to a new line.
  • Changing these properties does nothing to obscure connection details or the name of the Flow (yay!)
  • The headerText is shown even the first time a flow is executed (when the user is asked about the connections used), but as you might expect, the runFlowbuttonText is not:

This is a fantastic addition by the team! Making a flow button that simplifies launching a flow for an item is a great way to increase adoption, decrease confusion, and impress your boss! Special thanks to Cyrus Balsara (Microsoft) for letting me know about these awesome changes!

Generate List Formatting Elements in a Loop Using forEach

A few weeks back I demonstrated how to work with multi-select person or choice fields using indexOf to perform startsWith or contains checks to make some pretty cool formats.

While these are still valid techniques, they have some limitations that the new forEach property and the related operator, loopIndex, can solve. Specifically, applying formatted elements for each value of a multi-select field!

The Problem

I previously created a sample, multi-person-currentuser, that allows you to highlight a multi-person field when one of the users is the current user. The results end up looking something like this:

This sample takes advantage of the contains logic previously discussed by looking for the indexOf the @me (current user’s email address) within a flat string generated using the join operator:

multi-person-currentuser

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=join(@currentField.title, ', ')",
  "attributes": {
    "class": "=if(indexOf(join(@currentField.email,';'), @me) != -1, 'ms-fontColor-themePrimary ms-fontWeight-semibold', '')"
  }
}

Works pretty well and if that’s all you need, go grab that sample!

But what if we could take it further than just displaying the fields as a string? What if we could apply elements for each item? Well… good news, that’s exactly what the new forEach property allows us to do!

The forEach Property

The forEach property is not yet part of the schema (so don’t be surprised if it gets highlighted as invalid in something like VS Code). You can use it within column formatting or inside of your rowFormatter for view formatting.

The forEach property allows you to create virtual fields that you can access within an element. The element where you add the property (along with all of it children) are rendered once for each item within the array (array refers to the collection of selected people or choices).

Because the element is rendered multiple times, you must have a containing element. This is why if you attempt to use the forEach property in the root element, you’ll get an error.

The forEach property’s value is a simple sentence in the form virtualFieldName in ArrayField. Let’s look at an example.

For this first example we’ll use a simple multi-select choice field. In this case we’ve just made the choices some letters. Here it is with no formatting applied:

Now let’s apply a format using the forEach property:

multi-choice-foreach:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "forEach": "choiceIterator in @currentField",
      "elmType": "div",
      "txtContent": "[$choiceIterator]",
      "attributes": {
        "class": "ms-bgColor-themePrimary ms-fontColor-white",
        "title": "='I am the letter ' + [$choiceIterator]"
      },
      "style": {
        "width": "16px",
        "height": "16px",
        "text-align": "center",
        "margin": "1px"
      }
    }
  ]
}

Here’s what that looks like:

So what did we do? As mentioned above, you can’t apply forEach to the root element. So we created a div and then gave it a single child. However, by using forEach within the child we’re using this element as a template that will be repeated within the root container once per selected choice.

The forEach value requires you to provide the virtual field name followed by the word in and concluding with the name of the array to loop over. In this case, as seen on line 6, we are using choiceIterator as the virtual field name and our array is the @currentField (this could have just as easily been another array field in your view using the [$FieldName] syntax).

Note that the virtual field name should be unique. It is possible to clobber your other fields if you use the same name as one of the internal names of your fields! This means that if you use Title then you’ll no longer have access to the actual Title field’s value! This will be true even after the loop completes. So choose carefully. I find it best practice to use either the field name or type followed by the word Iterator. This has the added benefit of making it obvious that you are retrieving a loop value within your element – but that’s up to you.

Now that we’ve added the forEach property, we can access the virtual field anywhere within our template object (and its children) just like it was any other field! You can see this in the txtContent property on line 8 and we even use it in an expression within the title property to create a nice tooltip on line 11.

Taking it further with loopIndex

Back to our person example from above, wouldn’t it be great to do more than simply show their names? There’s another sample called person-roundimage-format that applies the standard circle image for users. Using the techniques above we can quickly convert it to support multi-select person fields (add a forEach and change our field accessors):

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "forEach": "personIterator in @currentField",
      "elmType": "div",
      "style": {
        "width": "32px",
        "height": "32px",
        "overflow": "hidden",
        "border-radius": "50%",
        "margin":"2px"
      },
      "children": [
        {
          "elmType": "img",
          "attributes": {
            "src": "='/_layouts/15/userphoto.aspx?size=S&accountname=' + [$personIterator.email]",
            "title": "[$personIterator.title]"
          },
          "style": {
            "position": "relative",
            "top": "50%",
            "left": "50%",
            "width": "100%",
            "height": "auto",
            "margin-left": "-50%",
            "margin-top": "-50%"
          }
        }
      ]
    }
  ]
}

Here’s what that looks like:

Now the images are showing up and we even get multiple when more than one person is selected! But what happens when we have lots of selections? The results aren’t great and give us all a sad:

You can see that up to 3 people looks just fine, but 4+ starts to have some weird squishing (and nobody likes weird squishing). So we need some way of knowing how many people we have for an item and which one we are on in the template.

Fortunately, you can use the length and loopIndex operators to accomplish this!

The length operator will provide the total number of items in an array (it does NOT provide string length). We can use this value to determine when we shouldn’t show an element (to remove face 4, 5, 6, etc.).

The loopIndex operator provides us with the zero-based index of where we are in the forEach loop. To use it, simply provide it the virtual property name you want to get the index of (since you can nest multiple forEach loops) as a string. So, in our case we can use "=loopIndex('choiceIterator')".

We’re going to base our solution on the UI Fabric Facepile with descriptive overflow. In order to do that, we want to accomplish the following:

  • Show 1 to 3 faces without change (that seems to work great)
  • Never show more than 3 circles
  • Replace the third circle with a descriptive overflow circle when there are 4 or more people selected

The first case we’ve got handled. The second can be done by using "display":"none" as mentioned in my last post. The third requires an alternate element that only shows when there are 4 or more people and we are on the 3rd person.

multi-person-facepile:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "forEach": "personIterator in @currentField",
      "elmType": "div",
      "style": {
        "width": "32px",
        "height": "32px",
        "overflow": "hidden",
        "border-radius": "50%",
        "margin": "2px",
        "display": "=if(loopIndex('personIterator') >= 3, 'none', '')"
      },
      "children": [
        {
          "elmType": "img",
          "attributes": {
            "src": "='/_layouts/15/userphoto.aspx?size=S&accountname=' + [$personIterator.email]",
            "title": "[$personIterator.title]"
          },
          "style": {
            "position": "relative",
            "top": "50%",
            "left": "50%",
            "width": "100%",
            "height": "auto",
            "margin-left": "-50%",
            "margin-top": "-50%",
            "display": "=if(length(@currentField) > 3 && loopIndex('personIterator') >= 2, 'none', '')"
          }
        },
        {
          "elmType": "div",
          "attributes": {
            "title": "=join(@currentField.title, ', ')",
            "class": "ms-bgColor-neutralLight ms-fontColor-neutralSecondary"
          },
          "style": {
            "width": "100%",
            "height": "100%",
            "text-align": "center",
            "line-height": "30px",
            "font-size": "14px",
            "display": "=if(length(@currentField) > 3 && loopIndex('personIterator') == 2, '', 'none')"
          },
          "children": [
            {
              "elmType": "span",
              "txtContent": "='+' + toString(length(@currentField) - (2))"
            }
          ]
        }
      ]
    }
  ]
}

Here’s what that looks like:

Here’s what we did:

  • On line 14, we added a display property to our template element to set the value to none if the loopIndex is greater than or equal to 3 (keep in mind that the index starts at zero so we’re basically saying never show items 4 and up)
  • On line 31, we added a similar display property to our img element to set the value to none if the number of items is greater than 3 and the loopIndex >= 2. This allows us to show it as normal if there are 3 or less people selected but when there are 4 or more, we don’t want to show that 3rd person.
  • On lines 34-54 we add our descriptive overflow element. This is a gray circle that says how many more people were selected than are shown.
  • On line 46, we once again take advantage of the display property to ensure that the overflow element is only shown when there are more than 3 people selected and we are on the 3rd element (loopIndex = 2).
  • We use the join operator to create a tooltip with everybody’s name in it on line 37.
  • We determine the number of additional people by simply subtracting 2 (since we know how many we are showing) from the length of the array. Notice that the 2 is wrapped in parenthesis. This is to combat an issue with the subtraction operator.

While that certainly isn’t the simplest sample in the world, it demonstrates common list formatting patterns such as conditional display, element loops, and customization based on loop position.

This opens up even more possibilities in the already awesome List Formatting world! Whoo!!

Conditionally Launch Flows using List Formatting

Creating a button with Column Formatting to launch a flow is a fantastic way to make actions obvious (not hidden in the Flow menu). If you have a list item flow, I strongly recommend doing this (just copy, paste, and tweak the sample). But what if you want to go further? What if you have multiple flows for list items and you want to help guide your users through this more complex (but relatively common) scenario?

Good news! List formatting expressions make it possible to only show the flow launch button(s) that make sense based on the values of the item! Here’s what we’re building:

In the list above, we have 3 separate flows (Develop, Deploy, and Destroy). We want to only prompt the user to launch one of these based on the value of the Status column in the list item. You could argue that it might make more sense to launch a single flow that handles the conditional logic directly. Sure, but customizing the text, icon, and color to make it obvious to the user what action they are taking is still an awesome thing to do.

Conditional Logic Across Properties

In our sample, we want to conditionally change the color, icon, text, visibility, as well as the flow launched. This brings us to a very common scenario in List Formatting: applying the same logic to multiple properties.

It would be awesome to apply your logic to entire elements, but in List Formatting it is only possible to conditionally apply the value of a property. This means you can’t conditionally include/exclude a property or element. These properties/elements have to be included with their individual values conditionally set.

So, if you want to set a style property based on a condition, like the text color, you have to include the property and set its value regardless. Generally, you handle this by setting up an expression like this: “color”:”=if(@currentField>2,’red’,”)”

You cannot, however, apply that same logic to the inclusion of the color property itself.

Conditional Logic for Elements

Although you must include all the elements and properties whose values you want to set (even if only in some conditions), you can use the style display attribute to remove entire elements by simply setting the value to none. This is how we remove the entire button when the status is ‘Destroyed’ in our sample (line 15 below). We don’t want to prompt the user to launch any flow, so we simply remove the button altogether.

You can extend this pattern further by creating a placeholder top element of a div with children. The individual child elements can be turned on or off by conditionally setting the display property. That’s not what we’re doing here, but it’s something to keep in mind.

Conditional Flows

generic-start-flow-conditionally:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "button",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "='{\"id\": \"' + if([$Stage]=='','b60a26d3-fd87-4947-9d1d-344cb31d953a',if([$Stage]=='Development','3a27a39c-0ec9-4342-8fe3-bfb37fefc3da','3091d383-f8ed-48da-9962-bd7c24e70688')) + '\"}'"
  },
  "attributes": {
    "class": "='ms-fontColor-' +if([$Stage]=='','orangeLight',if([$Stage]=='Development','teal','redDark'))"
  },
  "style": {
    "border": "none",
    "background-color": "transparent",
    "cursor": "pointer",
    "display": "=if([$Stage]=='Destroyed','none','inherit')"
  },
  "children": [
    {
      "elmType": "span",
      "attributes": {
        "iconName": "=if([$Stage]=='','Lightbulb',if([$Stage]=='Development','Deploy','HeartBroken'))"
      },
      "style": {
        "padding-right": "6px"
      }
    },
    {
      "elmType": "span",
      "txtContent": "=if([$Stage]=='','Develop!',if([$Stage]=='Development','Deploy!','Destroy!'))"
    }
  ]
}

You can see that we are applying each of our elements conditionally by comparing the value of the Stage column (also present in the view). For the flow, we build the actionParams conditionally by building the escaped JSON value and swapping the ID value in and out based on the stage (line 6).

You can customize this sample by adding additional conditions, changing the comparison column (use the internal name), and the ID(s) of the flows themselves.

Getting a Flow’s ID

To use the code, you must substitute the ID of the Flow(s) you want to run. The IDs are contained within the expression inside the customRowAction attribute inside the button element.

To obtain a Flow’s ID:

  1. Click Flow > See your flows in the SharePoint list where the Flow is configured
  2. Click on the Flow you want to run
  3. Copy the ID from the end of the URL (between flows/ and /details)

Update

See this demoed on the PnP Call (Live from MVP Summit):

Love List Formatting?

Join the Bi-weekly (every other Thursday) SharePoint Patterns and Practices special interest group for general development call where I will be presenting a new List Formatting Quick Tip on each call!

Also, come get the full picture in my sessions about List Formatting at the SharePoint Conference in Las Vegas in May, or the European Collaboration Summit in Germany in May: