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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,362)