cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Viral21
Helper IV
Helper IV

Distinct not fetch all data from SQL table

Hello,

I have one table in SQL which name is "Entity_Deal"

That table contains one column which name is "Market"
I wants Distinct market on my power apps Dropdown.

I have written below code:

Sort(Distinct('[dbo].[ENTITY_DEAL]',MARKET),Result,Ascending) 

 

Issue: I have 16 distinct record in my SQL table but it fetch only 11 records, 5 records missing.

 

So can anyone please let me know what I need to do?

 

Note: I have refresh my Database in Powerapp, but still it not give proper result

9 REPLIES 9
PG_WorXz10
Community Champion
Community Champion

Hi @Viral21 ,

 

Is there any possibility your SQL data source has item count of around 500-2000 or more than 2000 ?

 

 

 

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

Yes it's have 6815 Rows

@Viral21 ,

 

Thats the issue powerapps can fetch only 2000 items in a server call if you have set the max limit in your app setting. 

 

For this scenario. Try below code before that please check if your collection limit is set to 2000.

 

ClearCollect(col1,Filter('[dbo].[ENTITY_DEAL]',YourIDColumn<=2000));ClearCollect(col2,Filter(DEV1_FlowView,YourIDColumn>2000&& YourIDColumn <=4000));ClearCollect(col3,Filter(DEV1_FlowView,YourIDColumn>4000 && YourIDColumn<=6000));ClearCollect(col4,Filter(DEV1_FlowView,YourIDColumn>6000&& YourIDColumn<=8000));ClearCollect(colFinal,col1,col2,col3,col4);

Sort(Distinct(colFinal,MARKET),Result,Ascending) 

  If you can filter the datasource with some  filter and make it below 2000 records in that case above method is not required. If that is not possible try out above code and replace column name as per your requirement. 

 

 

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

Here is my SQL Table, So what I need to use for column ID?

Viral21_0-1634200364372.png

 

@Viral21 ,

 

Any unique Primary key will work. 

 

In your case is Entity_ID unique ? If so please get the startID and EndID of the table and update the collection according to the startID and EndID. 

 

 

 

 

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

It throws below error:

 

Viral21_0-1634212753372.png

May I doing something wrong? And what is "DEV1_FlowView"

@Viral21 ,

 

My bad please use '[dbo].[ENTITY_DEAL]' in place of DEV1_FlowView.

 

 

 

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

Still it says invalid arguments

Viral21_0-1634216726470.png

 

@Viral21 ,

 

Try removing the sort function and add it in the dropdown items formula. If it helps 

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,497)