Tip #1393: If TDS endpoint does not work

Have you tried turning it off and on again?

IT Support

Dataverse connector in Power BI is all the rage: fast, efficient, understands metadata well, supports both Import and DirectQuery, makes sushi upon request, you name it. Except that sometimes it does not work. Try selecting account table, for example:

DataSource.Error: Microsoft SQL: Invalid object name 'dbo.FilteredAccountTDS'.
RequestId: TDS;a2c61dc8-b400-4d34-b66f-3c30157f0a42;2
Time: 2021-01-20T05:24:22.7689085Z
Details:
DataSourceKind=CommonDataService
DataSourcePath=foobar.crm6.dynamics.com
Message=Invalid object name 'dbo.FilteredAccountTDS'.
RequestId: TDS;a2c61dc8-b400-4d34-b66f-3c30157f0a42;2
Time: 2021-01-20T05:24:22.7689085Z
ErrorCode=-2146232060
Number=40000
Class=16

If you try accessing it using SQL Server Management Studio, the result is pretty much the same:

What is this Filtered nonsense you’re talking about? I have no knowledge of this FilteredAccountTDS view, my dude.

SQL Server on behalf of Dataverse

Support request raised, screens shared, emails exchanged. The solution, believe it or not, is in the best traditions of The IT Crowd. Seriously, go to Manage Feature Settings, turn TDS off then turn it back on. Rumor has it that there was an update applied recently and, if you had TDS enabled during that update, few things may have gone south. Flipping the switch fixes that.

Tip #1392: Missing entities in Power Automate

The classic “give me 5 minutes and I’ll whip a flow for you” did not start well. Users entity table was not there.

Options Choices:

  • It’s something trivial and I’m simply no longer worthy.
  • ‘u’ is not an actual ‘u’ but Russian ‘и’ typed accidentally but in a weird font.
  • It’s a UI bug
  • It’s singular not plural
  • I missed an announcement and it’s a new feature where only selected tables appear in the dropdown and I need to enable some obscure setting. “It’s for your own good” kind of flag because it’s a coveted systemusers entity @&^! table.

At this point the only thing I was happy about I was not running a screenshare. I went for a copout. “Something is not right, give me 20 I’ll call you back”.

18 minutes in, table (the physical one) dented. Take this:

Save, close, reopen. @#%*&^%!

I forgot 3 years ago the client insisted on Employees rather than Users despite my plea not to rename. Nostradamus is my middle name except that my gloomy predictions only apply to my own doomed future.

Lesson: if you can’t find a table in the connector dropdown, check if it hasn’t been renamed. As far the blame is concerned, I’ll go with Occam’s razor – sometimes I’m simply not worthy.

Tip #1391: Content management in Power Apps portals

They said Power Apps portals are only good for building functionality. They said Power Apps portals can’t do content management. They said Power Apps portals do not support publishing cycle without taking pages offline.

They. Were. Wrong.

The Enabler

Watch this video to learn how to use language feature in Power Apps portals to create a true publishing cycle with support for staging environment and one button publishing.

WATCH VIDEO

Tip #1390: Bring back the green bar

In Tip 1386 we bemoaned the death of the green bar indicator of the installation status of solutions in Power Apps and Dynamics 365.

Chart Guy to the rescue! Ulrik Carlsson has created a Power BI report leveraging the FetchXML in Tip 1386 to create a report with green bars that you can use to view the installation status of your solutions.

Download the .PBIT here.

Cover photo by Johann Trasch on Unsplash

Tip #1389: Better screenshots through browser sizing

If you are working on documentation and you ever need to take a screenshot of something that appears in a side panel, such as a Power Automate approval or a model-driven app quick create form, and there is a lot of white space between the content and the bottom button, it can look unsightly in your documentation.

Instead of this screenshot, resize your browser so the button appears closer to the content.

this #simplebutuseful tip will make your documentation better.

Cover photo by Khaled Reese from Pexels

Tip #1388: Check if someone is out

I’m creating an approval flow with Power Automate, and if the approval email gets an out-of-office response, I want to route the approval to somebody else

Reader

That’s a really good idea–say the manager is on a two-week vacation at the time the approval comes in, you don’t want to wait until she comes back before the request can be approved.

But relying on getting an email back is not very dependable. I would recommend checking for that condition before you send the approval request.

The Office 365 Outlook connector for Power Automate lets you check for automatic responses via the Get mail tips for a mailbox (V2) action. Using the Get mail tips action you can identify if automatic replies are turned on or if the mailbox is full.

So in the approval flow, before the approval step you could get the mail tips for the approver’s inbox, and then conditionally send the approval request to the manager or her alternate.

Note automatic replies is an array, so you will need to use a formula like the following in your condition.

body('Get_Auto_Response')['value'][0]['automaticReplies']?['message']

Replace “Get_Auto_Response” with whatever the name of your get mail tips step is.

Tip #1387: Power Apps Form submitted but data not saved

The following tip is from Triasha Jalui. (Got a tip? Email it to jar@crmtipoftheday.com)

You create a Power Apps canvas app, add a form, bind it with the Common Data Service entity. On the form you add all the necessary fields. Now you want to save the form/record. You use the SubmitForm function in the OnSelect property of a Button or Icon to save or update the form but some of the fields value is not saved while the record is created or updated.

SubmitForm validates some of the behavior while submitting the record. Sometimes the Update property on the Card for the field is blank. If that happens, the field value is not saved or updated in CDS. Make sure the Update property on the fields Card is set while developing the Canvas App.

Tip #1386: I miss the green bar

If you use the new solution import process at make.powerapps.com, you might miss the old green bar that told you how far the import had proceeded.

The good news is, while the green bar is gone, the underlying data that fed the bar is still there. You can query the importjob entity to see how much of the import is complete.

<fetch top="50" >
  <entity name="importjob" >
    <attribute name="solutionname" />
    <attribute name="progress" />
    <attribute name="startedon" />
    <attribute name="completedon" />
    <attribute name="modifiedon" />
    <attribute name="importcontext" />
    <attribute name="operationcontext" />
    <attribute name="modifiedby" />
    <attribute name="solutionid" />
    <attribute name="importjobid" />
    <attribute name="createdon" />
    <attribute name="organizationid" />
    <attribute name="createdby" />
    <attribute name="data" />
    <attribute name="name" />
    <order attribute="createdon" descending="true" />
  </entity>
</fetch>

Run this query with the XrmToolBox and refresh until the import finishes.

Thanks to Ivan Kurtev for the suggestion.

Cover photo by Anshu A on Unsplash

Tip #1385: Can not see environment

The administrator gave me a role in a Power Platform/Dynamics 365 environment, but I cannot see that environment in the environment selector from make.powerapps.com or the Power Platform Admin Center.

Lots of people lately

So if you cannot reach an environment after being granted access to it, I’ve discovered the following seemingly fool-proof method of making that environment available:

  1. Have the admin go to make.powerapps.com, select the environment, and create and save any canvas app (even a totally blank one).
  2. Have the admin share the app in step 1 with you.
  3. Open the app from the share email you receive.
  4. You should now see the environment from all maker and admin portals.

Tip #1384: Inline Code in Power Automate

I finally found some courage to publish a video tip of my own. Why now? Because the topic is just too good to simply write it down. You have to see it! What is it? It’s JavaScript code inside Power Automate – yes, we can!

Watch this video to learn how to build the simplest possible Azure Function that will bring inline code execution to your flows! The possibilities are endless – regular expressions, recursive functions, math operations, and much more.

WATCH VIDEO