This post is based around some recent work that I’ve been doing, which includes canvas apps. For those of you who aren’t familiar with canvas apps, imagine if PowerPoint & Excel had a baby! Though I’m expecting most people who are reading this to already know all about them 🙂

So enough with the waffle, let’s get on with things…let me paint the scenario for you.

The app is aimed to be used by a contact centre. Part of their function is to capture address information. So far this has been done absolutely manually. The issue with this is that data can be typed incorrectly, or in the wrong fields. We’re also needing to enhance the data with geographic-specific information (for reporting purposes). This information isn’t known by either the callers, or by the contact centre agents (for those who are curious, it’s the unique property reference number, which is unique to every address in the UK).

Thankfully, we’ve been given a source from the client which we can look this up against. In essence, we pass a postcode to it, and values are returned (in a JSON format). This includes the data that we’re looking for. Brilliant, so far.

When we got to thinking about things, there are several ways in which we could implement this:

• Capture the data as we are already doing, & use Power Automate to get the relevant additional information

or

• Automate this within the canvas app itself, and even give the customer service agents a bespoke address picker!

Deciding to go with the second option (it was a no-brainer, really), we moved ahead with this. We had the details that we needed in order to hit the address lookup API. One of the developers on the team created the Custom Connector, and got it working. We tested it out, and amazingly we got information back!

The next step was to see how we could do this within the canvas app itself. Now I’m going to admit here that although I’ve HEARD great things about Collections, I had never used them myself. In fact not only had I NOT used them before, I had NO idea how they worked! That was to change VERY quickly though…

Within a few hours, I had learned enough about collections to get how they worked, and pull data into them. It was actually really simple – I used the ClearCollect command to create a collection that was fed by the API query, which then created the data into a collection table for me to use. I was very impressed!

OK – so I had my data in the collection now:

What were my next steps? Well, I was wanting to achieve the following:

• Give the customer service agents an ‘address picker’ to use. They’d enter the customer postcode, & then be presented with a list of addresses that they could pick the correct one from
• Automatically populate the customer address fields on the form from the selected address

Well, the first item (the ‘address picker’) was simple enough. Using a dropdown field, I pointed it at the collection data. This worked great, but the dropdown was only allowing me to select a single column from the collection to display. This meant that I could only select ONE column of data to return:

1 column from the collection. OK, I thought – should be simple enough to handle. Let’s go and concatenate column values in the dropdown, to present the interface I’m looking for:

Now that’s more like it! Much easier for the customer service agents to use. OK – onto the next stage. Let’s go & set the fields to point to the collection, match to the value that’s selected in the dropdown, and populate. Should be simple to do, right?

Well…um, no, it’s not simple to do. In fact, it’s actually impossible to do. I was expecting to point to the dropdown selected value, & have the columns returned (from the collection). I could then select which column to use for a specific field. This, however, was not the case:

You have to love the ‘.’ (or ‘dot’) notation used in canvas app code. It shows you what values are available, and saves having to do lots of type. In this case, however, it also showed me that there was only ONE column of data to select from to display in the field. This was the ‘Result’ column.

This got me very confused. I tried going back to basics, and stripping out the concatenation in the dropdown. Wonderfully I was then presented with all of the different collection columns to use:

So let’s sum up things so far:

• If I want to present the best option to the customer service agents (using concatenation), I can’t select different parts of the data for auto-population into fields
• If I want to be able to auto-populate field values from the collection, I can’t use concatenation (& therefore can’t present user-friendly data to the customer service agents).

Note: Leaving aside wanting to show the house number & street, one of the main reason for wanting to concatenate was to handle buildings that had flats (aka apartments) in them. This is stored in a different column in the collection. It would therefore be difficult to show these both to the customer service agents

In essence, the behaviour of the dropdown field seemed to be that I couldn’t just change the displayed values without it ‘losing’ connection to the rest of the data. There was no ID that I could use to match on, or display what I wanted to.

This seemed to be a massive Catch-22. I tried various things, but couldn’t see a way out of this. I started to try to create a second collection, & concatenate fields from the first collection. This seemed like a good idea, though (with being totally new to it), I got lost. I tried various things; I even ended up managing to collect the entire data from the collection into a new column for EACH ROW!!

Thankfully, the community helped me out, in the forms of Peter Bryant & Clarissa Gillingham (I had posted about my issues on Twitter – the hashtag #poweraddicts is really great!).

With the help provided, I managed to work out the CORRECT syntax to use for the ‘AddColumns’ command. This now being in hand, I was successfully able to create a second collection & add concatenated field values to it:

Now for the moments of truth. Would the dropdown show this new column, & could I point the form fields to auto-populate specific columns?

The answer….was YES! It was working! I felt SO relieved. Let’s take a peek:

This was brilliant! We’re also populating other data in the background, but that doesn’t need to be visible to the customer service agents.

So in summary, I learned about collections, & how to use them. I also learned about the limitations of dropdown controls (when referencing them from other places), but came up with a way around it. Finally I achieved the result that I was aiming for. Very pleasing all round!

Have you come across something like this in an implementation? How did you manage to handle it (if you did)? Drop a comment below – I’d love to hear all about it!