Tag: lookup

‘Ghost’ lookup value following deployment

This is something that stumped me fairly recently. It’s also something that I was trying to work out what I should use at the title for this post! Let me share what happened.

I’m working on a project that’s quite critical (COVID-19 related). This is a project that we’ve built something around Dynamics 365 as an additional wrapper, to provide specific functionality for the pandemic. It’s being rolled out (the same solution) to multiple clients, and is only using the functionality from Power Platform. No custom code at all.

Now, before going into the specifics around it, let’s take a moment to revisit what a lookup field is, and what it does. Essentially a lookup field connects two tables together (wow – that felt strange not to use the word ‘entity’!). In the front interface, it’s used for a 1:N relationship.

So for example, we can have a lookup from Account to Contact, to set the primary contact for the account. The user navigates to the field, searches for the record they’re wanting to associate, and saves it.

Underneath, there’s a relationship that’s automatically created between the two tables, showing the way that the relationship will go (ie 1:N or N:1). This is created on both sides (more on that another time around dependencies), and most people will never need to modify it

When I first started with this particular project, I got the solution, and deployed it into the Dev environment (for the project that I was on). On testing it out, I found something very interesting. We’re using the Case (Incident) table, and there are various lookup fields on it. One of these was already populated with a value. Hmm – that’s interesting, I thought. It was a new deployment, and we hadn’t set any static data up yet at all. So how could it already be populated?

How is this being set, when I’ve not entered it into the system as a record…

Furthermore, I was unable to save the Case record. When I tried to, I was getting an interesting error:

On drilling down into the error log (which admittedly is actually getting better in the details shown in it, thankfully!), it turned out to be because I didn’t have access to the referenced record (in the lookup field). It just didn’t exist.

So the lookup field value was coming in with a hard-coded GUID (record identifier). But how was this being done, especially if there weren’t any records (of that type) in the system at all?

From my experience of things, I could think of two ways in which to populate a lookup field with a hard-coded value:

  • Through a ‘real-time’ Power Automate flow, on create of the record. It’s possible to set a GUID value in the flow, and then it would be set
  • Through custom code, running on the form. Again, it’s possible to hard-code a GUID there, and then set the field

However on checking both options, none of them were happening. No Power Automate flows touching the Case record, and no custom code at all on the Case.

It was then, digging through the other parts of the solution, that I saw various Business Rules. For those unfamiliar with these, I’ll quote from the official Microsoft documentation around them:

By combining conditions and actions, you can do any of the following with business rules:

  • Set column values
  • Clear column values
  • Set column requirement levels
  • Show or hide columns
  • Enable or disable columns
  • Validate data and show error messages
  • Create business recommendations based on business intelligence.

I’ve used Business Rules (somewhat extensively) before. However on going into the one for the Case table, I found that something was happening that I wasn’t aware could happen! It’s actually possible to set a lookup field value through it:

I spy a lookup option

Even though we’ve deployed the solution from the original development environment to a different environment, this is still set. But there are no records that are available:

I had never thought that it would be possible – to set a static value (eg a number, or some text), fine. But to set referential data? Wow.

Obviously this can be quite helpful. The bit that it’s NOT helpful though is when deploying the solution to another environment (as this situation was). It doesn’t help if you re-create the record that it’s referring to with using the same record name, as it’s using the underlying GUID (which you can’t re-create). This really does take solution deployment into a whole new perspective, where you need to be careful around these sorts of things as well.

So something new that I’ve learned (I do try to learn something new each day), and specifically around an area I thought I knew quite well. It did take some time, but I’m glad that I (finally) found the root cause of it, and identified what was causing it.

Have you ever had something like this happen, where you’re searching & searching for the cause of it? Drop a line below – I’d love to hear!

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!