Monthly Archives: May 2020

Using SQL Management Studio with CDS

Just got to play with the latest cool feature, which is using SQL Management Studio to connect to CDS environments. You’ll find the documentation here:

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query

If you have not seen what it looks like, here is an example:

image

To set it up, you just need SSMS v18.4, and don’t forget to configure your environment to enable TDS endpoint. Which you can do with the help of this solution, for example:

https://github.com/seanmcne/OrgDbOrgSettings/releases

Just need to update the setting below before you try connecting SSMS to the environment;

image

And then it just works. From my very quick testing so far, here are some observations – they are not making this feature less exciting, but this is just something to keep in mind:

  • RetrieveMultiple plugins are not working when you are querying data in SSMS. There are lot of solutions which would be using those plugins for localization/security, and, it seems, SSMS is just bypassing them, so that’s something to keep in mind
  • Looking at the speed of those queries, it seems they are running directly against the database (no fetchxml involved)
  • This is not the same as having access to the on-prem database – there are no views, for example
  • If you have lots of data, I’d suggest using TOP X in your SELECT queries. Or it may take quite a bit of time to get the results
  • All data is read-only (which you can see on the first screenshot above, next to the database name). In either case, UPDATE statements etc are not supported. Which is understandable since they would have to go through the API

Quick Find – when did it get fixed?

I have certainly missed some news. Apparently, quick find in the unified interface now works as it has always been expected to work (but never did):

  • It’s now using selected view columns
  • And it’s now applying selected view filters

If I were reading release wave 1 plan more carefully, I would have noticed the following there:

What does it mean in practical terms? In the classic interface (and that’s how it used to be in the unified interface until recently), quick find would switch view columns/filters, so, if I started with “My Active Contacts”:

image

I would get a different view as a result once I’ve provided a search phrase:

image

This has always been confusing and needed explanations when presenting to the users.

In the unified interface, it works as it should now.

If I start with “My Active Contacts” view:

image

It’s also going to be the view used to display / filter the results once I’ve provided a search phrase:

image

Are there hidden solutions in Power Apps?

I was troubleshooting missing dependencies earlier today, and, after some digging, uncovered a hidden solution in the environment. I could see that solution when looking at the layers:

image

And I certainly could not see it in the list of solutions neither in the old interface, nor in the new one.

Well, there is always a way to bypass smart filtering and just look at the whole list of solutions using web api, so, of course, the solution is there:

image

It’s just marked as “not visible” (“isvisible”: false on the screenshot above)… Huh? Anyway, that’s, likely, the reason why this solution is not showing up in the portal where it should be displayed right after the highlighted solution below:

image

Turned out we can still see those hidden solutions (with the proper application of URL-s), even though this is where power apps portal won’t work since it’ll keep displaying spinning progress indicator:

https://make.powerapps.com/environments/<your env id>/solutions/<solution id you can get from web api>#

image

However, there is still classic solution designer which comes in handy in such cases:

https://instance_url/tools/solution/edit.aspx?id={solution id you can get from web api}&utm_source=MakerPortal#

image

And, yes, that’s where the dependencies were missing somehow. That’s another story, though.

Updated filtering in Model-Driven Apps/Dynamics

Social distancing has definitely took a toll on my blogging – it’s been a while since the last post. Although, I’ve actually learned quite a few things outside of PowerPlatform in the last month, so I could probably start blogging about a few other things now:

  • How to teach math fractions
  • How to build wooden tank models from the stuff we have in the garage
  • How to set up a volcano model in your kitchen safely
  • How to bake bagels…

 

However, Power Platform  has, finally, caught up with me when a co-worker showed me how filtering in model-driven apps works differently now. I think I like it. Although, the reason it came up in the first place is that the users were complaining. Well, at least it’s not on us that this change has happenedSmile But what exactly is different now?

image

Basically, there are 3 changes.

1. Column sorting

There is a drop down there which combines “sort” and “filter” options. It’s not enough to click on the column header now to get the grid sorted – we need to open the drop down and choose a sorting option.

Note: you can still hold “Shift” button to sort on more than one column

2. Filter by

When using the Filter by, you can choose a filter:

image

Which, for a lookup field, will give you a lookup popup:

image

And, for an optionset, will allow you to check off multiple options:

image

3. And this all brings us to the “Advanced Filter”

Which sounds strangely familiar, but it’s not the same as “Advanced Find”.

image

The query builder interface there, even though it seems to be very similar to what it looks like in the advanced find, is somewhat different. And, unlike the advanced find, it actually does pick up on all those individual column filters which I have set up in the “current session”

image

Which, in general, seems to be working quite well. Although, would not it be great if I could also save that query as a personal view? It seems I can’t, yet.

This is all part of the release wave 1, and, if we were paying more attention, we could have given the client a bit of an advance warning. It does not seem to be that disruptive, though, except for the sorting which now requires two clicks instead of one.

And the only other thing I have not mentioned yet is that, once using “Advanced Filter”, you’ll see those additional indicators that an advanced filter has been applied:

image

For some reason, those indicators won’t be showing up when using a column filter, so just keep that in mind when troubleshooting with the usersSmile