cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aevoth
Frequent Visitor

Count Unique records in a column with Model-driven app

Hey all,

 

I have a model-driven app using Dataverse and am trying to count all the unique records in a column, but don't seem to be able to.

 

I'm looking or something similar to 'select count(Distinct(columnName)) from tableName' like in SQL.

 

Tried creating a Canvas Page with the distinct function, but it definitely does not count all records - probably some delegation issue without actually displaying any error.

 

Thanks

5 REPLIES 5
melamri
Super User
Super User

Aevoth
Frequent Visitor

Thanks @melamri for getting back to me. I tried your suggestions, unfortunately I can't use the 'HTTP with Azure AD connector' due to DLP policies that block it. A custom connector is again out of the question for the exact same reason. Tried the Premium HTTP connector, but then again, can't authenticate...

 

I'm a bit stumped as I really did not expect such a common scenario to take so much effort, and it's not even resolved!

 

Your suggestion even gave me the idea to try and use an OData filter to get distinct values, but again, the 'List Rows' connector had no idea what to make of it (no wonder, $apply is not in the list of supported query options for Dynamics 365: docs.microsoft.com/en-us/dynamics365/fin-ops-core/..)

$apply=groupby((ColumnName))

 

Any other ideas?

v-xiaochen-msft
Community Support
Community Support

Hi @Aevoth ,

 

Maybe power automate can do this.

vxiaochenmsft_0-1654585503672.png

 

vxiaochenmsft_1-1654585510736.png

 

vxiaochenmsft_2-1654585526942.png

 

Best Regards,

Wearsky

Hi @Aevoth 

 

I understand that you can't use the HTTP with Azure AD connector. For this reason, I had shared two approaches.

The second one uses the dataverse connector, but you have to implement a custom api on dataverse to execute your fetchXml. Have you been able to check this option?

I refer to the following blog: https://xrmtricks.com/2022/03/16/power-automate-dataverse-how-to-execute-a-fetchxml-query-that-inclu...

 

 

Please tick Accept as solution if the answer is useful.

https://xrmtricks.com/

 

 

 

Aevoth
Frequent Visitor

Thanks @melamri. I did read your article, which does look elegant, but I'm afraid you lost me at creating a custom api..  I looked at Tutorial: Write and register a plug-in (Microsoft Dataverse) - Power Apps | Microsoft Docs, but unfortunately, I can't download any apps onto my device and can only access the Power Platform from the work device to register it. Is there a way I can build the plugin on my personal device and register the plugin somehow without the registration tool? Or PowerShell, or any admin access to Azure, none of which I have unfortunately... Thanks

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (1,409)