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
Resident Rockstar
Resident Rockstar

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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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