Tag: fields

Power Automate & Lookup Fields

Recently I’ve been expanding my knowledge of Power Automate, and how it works. It really is a truly amazing tool, though there can be some quirks to things! There are so many connectors to use, though I haven’t really used that many of them to date.

Truthfully, most of my work in Power Automate is around CDS & Office 365. Occasionally I’ll dip into another system, but for the most part that keeps me busy enough. It’s not to say I don’t want to explore further, but finding the time can be quite difficult!

One of the great abilities that Power Automate has is to be able to update a record. With focusing on CDS entities for the moment, we would use the inbuilt action for this:

We’d run a query to get a specific record – this would give us the record ID (or GUID, depending on your preference). With this, we’d use the Update Record action & pass in the record GUID. After all, we need to know which record we’re going to update! So for example:

What we can then do is set values for the record. So we can pass in Dynamics Content, use Expressions, etc. These can be from records that are part of our Power Automate query chain, or from elsewhere.

For example, I can say that when a contact’s postcode changes (or zip code for USA), go away, look up the new city, and update it (Note: I haven’t shown the postcode lookup part below):

So this is all really brilliant. Different fields have different behaviours, of course, and we need to respect that. Otherwise the Power Automate flow won’t run, and will error. This is, of course, the digital equivalent of not trying to force a square brick into a round hole!

What we can also do is clear a field value. If for example we’re wanting to remove a value from a field, we can use the NULL expression on the field. When the Power Automate flow runs, it’ll clear whichever value the field is currently holding:

Now, one of the the field types available within CDS is the lookup field. I’m not going to go into what this is, as we should already know this!. We can, of course, set lookup fields values to populate the field, which works as expected.

However (& thanks for bearing with me so far), what happens if we want to clear a lookup field value?

Say for example that we have a task, that’s assigned out to someone. If they reject the task, we want to be able to remove them from the task record. We wouldn’t delete the task, as we still need it (& now would need to assign it to someone else). We need a way to do this.

I can hear what you’re thinking right now – mentioned above is the use of NULL, so we’d use this! Um…well, you’d think so. You can try that, but we’ve found that doesn’t always work. Additionally, that doesn’t actually seem to remove the underlying relationship that’s been put in place.

Update: Thanks to Lin Zaw Winn, who dropped me a line to let me know further information around this. The standard CDS connector (the first one that was available) allowed this to work, but the updated CDS connector (Current Environment) doesn’t allow it. Unfortunately the different connectors aren’t at parity, which is a pity!

So, there’s another way to clear lookup field values. This involves the Unrelate action that’s also available. The steps for this are as follows:

  1. Get the related record (lookup the record type, pass in the GUID for it)
  2. Use the Unrelate action to remove the connection

This will then remove the relationship, which actually results in clearing the lookup field value. In practise (for our scenario), this would look like:

Let’s take a bit of a further look at the options available here:

  • The Relationship field is the relationship between the two entities (eg here it’s Contact & Task). Thankfully you don’t need to manually type this – it’s easily selected from a dropdown list.
  • The URL field is the linked record itself

Note: It’s VERY important to have the Entity Name & URL values in the right order. I’d suggest looking up the connected record first (ie what the lookup field is pointing to), and using that as the Entity Name value. You’d then select the record where the lookup is saved on as the URL value.

What I’d usually suggest as best practise is to have a condition before this takes place. As mentioned earlier, removing the lookup would happen on a record update. This is because you wouldn’t be removing a field value if you’re creating the record!

But you’re not always going to want it removed. In the scenario that I’ve been dealing with, we’re only wanting to remove the volunteer if they’ve rejected the assigned task. So our Power Automate flow is set out like this:

  • When Task record is updated
    • Filtering on the field for ‘Task Accepted’, as we could have other things being updated on the Task record that we don’t want to trigger this particular process
  • Condition to check the ‘Task Accepted’ field value
    • When it’s something other than ‘Rejected’, cancel the flow
    • When it’s ‘Rejected’, run the Unrelate process set out above, and stop flow

You can obviously build out other functionality within it as you so desire.

So with this in mind, how do you think you could benefit from this? Drop a comment below – I’d love to hear!

DateTime fields, XrmToolBox, & Dynamics 365 behaviour

Recently we’ve been rapid producing & deploying solutions, due to the current pandemic. One of the apps that I’ve been working on required quite a few fields for data capture. Well, truthfully most apps require quite a few fields, but I thought that I’d talk about this one in particular, due to something that I discovered.

Now, we all know how to create fields in the Power Platform maker experience. It’s really quite simple – you select that you want to add a new field, put in the details/type of field, & save. Hey voila – you have yourself a nice new field! You can then go on to add it to forms, views, etc etc. We all know how it’s done:

What I’ve found myself doing recently though is not to create fields through the Maker interface (make.powerapps.com), especially when there are lots of fields to create. Instead, I’ve been using the XrmToolBox to do this. There’s a very helpful tool within it called Attribute Editor, which allows you to use an Excel spreadsheet. It takes this, and creates the relevant fields through the Dynamics 365 API.

One of the reasons for doing things this way was that it allows me to get on with other things whilst the fields are being created. Although it doesn’t happen in the blink of an eye (especially when there are a lot of fields to create), I can leave it whizzing along, and do something else. This, of course, makes me feel VERY productive!

Right – back to what I was saying. So I had a lot of fields to create, and many of them needed to be datetime fields. Actually, all I needed was the time component, but unfortunately Dynamics 365 DOESN’T allow you to just show the time. It’s either Date, or DateTime, but no option for JUST Time. A flaw, in my opinion, for what it’s worth….

So I created the Excel template, started the process, and went on to do something else. I of course made sure to specify that the field type should be ‘DateTime’.

Coming back to it when it had finished, I started to place fields on forms, and noticed something strange. All of the datetime fields that I had created through this were date ONLY. This was…puzzling! Going to check the fields themselves, they were set as Date ONLY, not DateTime!

I went back to check my upload spreadsheet, and it was set correctly there. I even tried uploading another field, but still the same issue was occurring.

Now, with the way that Dynamics 365/Power Platform works, once you’ve created a field & saved it, you can’t change the field type. When it’s created it’s saved down to the underlying database structure as the specified field type, and that’s it. No way to change it…or at least not through the front end!

With this in mind, I fired up another one of the XrmToolBox tools, namely Attribute Manager. What this handy tool does is, behind the scenes, allow you to change the field type. Well, it doesn’t ACTUALLY change it directly – it clones it, deletes the original, then clones it back. There are some caveats to it working properly (ie that the field isn’t used in a view somewhere, for instance), but it’s really helpful.

Note: It only works for custom created fields, not the default OOB fields!

Depending on the field type that you’re wanting to change it to, you can select different options. However for DateTime, there’s only one option. OK – I was going to see what happened.

Well, I ran the update, but nothing changed. It was still ‘Date’ only within the interface, which was really being incredibly annoying. It wasn’t as if I could just delete & recreate it (well, I could, of course). I had dozens & dozens of these to do, and quite frankly didn’t want to spend all of that time in doing this.

Thankfully (with the help of one of my colleagues, who’s an experienced & devoted developer – thanks Sid!), we found the solution.

See, I had been doing everything within the ‘new’ interface. This is the one that Microsoft keeps pushing everyone to, as they don’t want people to really be using the Classic Interface anymore. That’s all very well & good, but the ‘new’ interface isn’t on parity (for some things).

Reverting back to the Classic interface (note that the option below is only available when working within a solution!), we discovered some hidden behaviour

We located the entity that we needed, and the field itself, and opened it in Classic. With the screen that’s presented (I do miss this in some ways – I remember the days where I almost lived permanently in here!) we AMAZINGLY have the following option:

We can CHANGE THE TYPE!! Now, this is just with the field that we’ve selected. To be frank, I have no idea at this point about any other field types, and would need to explore that separately. But for the moment, my problem has been solved! (well, to the point that I have ‘time’ values available – I’d still like to see JUST time values being an option).

So with this in mind, I merrily waded through the dozens of fields in the Classic UI, changing them all as needed. It wasn’t just a few minutes of work, but it was definitely much less time that deleting & manually creating each one!

So, really quite helpful. The only other thoughts that I had around things were that it would be nice if the various tools within the XrmToolBox could do this as well. However, the fact that they don’t seem able to actually seems to be a limitation of the API. Having gone to check the different field types & how they’re set programmatically (https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/types-of-fields), I’ve noticed the following:

There really doesn’t seem to be any way to specify the different sub-type, which is a shame!

Have you ever had a similar situation with fields? Drop a comment below- I’d love to hear about it.

Canvas Apps & renaming field labels

Today I want to share with you something that I’ve realised. Changing field labels can have unintended consequences!

Let’s cast our minds back to the days of ‘traditional’ Microsoft CRM, or as it’s so lovingly referred to nowadays, ‘model-driven’ apps. What you had were a number of entities (eg Accounts, Contacts, etc), all of which contained fields. Fields could be different types (text, integer, boolean etc), and have varying properties on them. You could set them to be required (or not), searchable (or not), and have so much fun.

At the heart of a field is the name that it has. Well, technically there are two names. One is the actual database name. Once a field was created & saved, this was effectively written in stone. The only way to handle a situation where you spelled this incorrectly was to delete it, and then recreate it. Even then, it could still be floating around in the back-end database in its original form.

The second name is the Display name (or Display label). This was the text used on the entity form itself, & could be changed as desired. This was actually really useful – many a time a business unit would say something like ‘we don’t want the field to show as Zip/Postal Code’; we want it to say ‘Postcode’. Well, that was easy enough to address – simply go ahead, load up customisations, & change the display name property for the field. Everyone was impressed & happy, and could get on with their work.

There were of course times that Business Unit A would say ‘I want ‘ABC’ as the display name’, and then Business Unit B would say ‘Ah, but I want ‘XYZ’ as the display name!’. To handle this, it was very possible to customise the label on the form itself, which would then override the display name value. This, of course, would only be valid for that specific form, so it was then imperative to have different forms for the different business units.

Now, in the good old days we use to create SQL queries against the database, SSRS reports, etc. In order to do this, we needed to know the actual underlying (database) field name. We could of course open up customisations, & start trawling through, but there are better methods for doing this. One of these is Level Up by Natraj Yegnaraman. This can be found at https://github.com/rajyraman/Levelup-for-Dynamics-CRM, and is an extension which can be run on Chrome, Edge on Chromium, & FireFox).

Using this amazing tool, it was possible to merely load an entity form up in an existing system, and then TADA! At the click of a button (well, two clicks actually), the underlying database name was revealed. This was an absolute lifesaver, so many times.

So there we’ve been, toddling along for many years like this. It worked, and worked well. All was good.

Then came along canvas apps. Now I’m not a canvas app guru by any means – I’m quite new to them, and still trying to wrap my head around the ‘special’ way in which they operate. Thankfully there are quite a few gurus in the community who have given me help in one way or another to learn how to carry out various functions, and I think that I may JUST be starting to get the hang of it.

With the current COVID-19 situation, I’ve been working on a series of apps for work, to help local authorities. One of these is a canvas app for call centres, to record information easily & quickly. We chose to go down the canvas route due to being able to have a clean layout, as well as being able to display information for the operators to read. This would have been much more difficult in a traditional model-driven app, especially as such things as dialogues have been deprecated.

One of the functions that I’ve had to learn to do this has been to use the ‘Patch’ function (see https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch for more information on this. The following is an example of one of the Patch statements that I was using:

This was working remarkably well – it was creating the task record, and setting all of the different values that I needed. For those who are curious as to why I was using a Patch statement, rather than submitting the form, it was due to needing to set the ‘Regarding’ field, which has some very special behaviour!

Then someone on the team said ‘Hold on – we’re only storing one address. Let’s change the field display names to remove the ‘Address 1′ part, so that we don’t confuse users’. OK – I didn’t INITIALLY see any issue with this. I bet that you can see what’s coming through…

Yes – you’re right. The patch statement isn’t referring to the field database name. It’s referring to the field display name! The reason for this is that this is the syntax that Canvas Apps use – there doesn’t seem to be a way to refer to the actual underlying field database name

Of course, I only actually discovered this when I ran through the canvas app again. And indeed, it was whilst demonstrating it to other people! Oh joys – what a wonderful time for it to happen.

So, I then had to figure out what had happened – thankfully that didn’t take too much time. What DID take time was going through every single place in the canvas app that had code referring to the specific fields, and update them to the new (correct) values. This therefore ended up looking like:

So, the vitally important lesson to learn here is be VERY careful when changing field display names, especially if you have one (or more) canvas apps that are referencing them. The last thing that you want is a major headache in having to go back through every place that refers to them, and changing/updating the values.

The only workaround that I’d suggest, is that if you’re wanting to change how fields display in the canvas app itself, change the ‘Text’ value for the field:

That way, HOPEFULLY, nothing will break moving forward.

I hope that you’ve found this useful. If you have a different way in which you’ve handled this situation, feel free to leave a comment below!

Canvas App record set Regarding field

For the last few days, I’ve been working on an app. Not just any app – it’s a canvas app! (It actually happens to be a COVID-19 related app, for local authorities to use to contact vulnerable people & check they’re OK etc).

Now, my background isn’t canvas apps – it’s the model-driven app approach. I’ve been doing this for years – after all, my experience goes back to Microsoft CRM 3.0! So that’s all really nice & easy for me (even with some of the more modern ‘tweaks’ that have been brought in). Canvas apps, on the other hand, are very different from what I’m used to, and are taking quite a bit of getting used to.

See, the following example is easy in a traditional model-driven app:

Create a contact, save various attributes to the contact record. Then create a task, and set the Task Regarding field to the contact that you’ve just created

Looking at that, my mind says ‘easy-peasy’!. I create the fields required for the contact entity (& task entity as well, if needed). I then add them to the entity form/s (creating or modifying the form view/s as well). Finally, I create a Business Process Flow for users on the contact entity, and append the task creation to it. Simple, and done – not much time needed to be spent.

But when needing to do this as a canvas app, things change around QUITE a bit. I can’t create that business process flow, and I have multiple screens to have all of the information on.

Now, if I could add the ‘Regarding’ field to the edit form grid, and apply formatting to it, I could hopefully then just submit & save the grid. However, that unfortunately doesn’t work. I can add the field, but when I do so, I get the following:

So that doesn’t work. Hmmm – how then should I go around doing it?

I did (obviously!!) take a look online. Here I came across this wonderful article all about polymorphic lookups (https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/working-with-references). Having read, & re-read through it, I’m STILL not understanding what exactly I should be doing by this!

So I was stumped. Thankfully we have an amazing community, and on reaching out to someone within it (thanks Eric!), I was helped out. I therefore thought to write this post up, so that it can help others as well.

There are two parts to this, for my specific scenario:

  • Saving the contact record down. This is a matter of using (in my case) the command ‘SubmitForm.ContactInformation’ on my contact form screen. I can then also set a variable if I want to, to refer to the Contact record GUID (hey – I’m trying to be cool here & show that I can!)
  • Finding a different way to save my task record. I accomplished this using the Patch statement – this thankfully wasn’t too difficult for me to grasp how it worked.

So, how did I go about using the Patch statement? Well, the function is referenced here – https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch. With Eric’s help, I soon started to see how to do it.

What I did was add the following line in my Patch statement when I was wanting to save the task: ‘Regarding:ContactForm.LastSubmit’ (‘ContactForm was the name of the form for the contact information). What this did was write into the record the GUID for the contact record that I last saved.

An alternative to this would be to use a variable instead, and set it there.

Thankfully this all worked. I’m now able to create Task records and set their Regarding field value to the Contact that I set up before them – which is the exact thing that I was trying to do!

I hope that this has been helpful – leave a note in the comments if you’ve found another way of doing this.