cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omi18
Post Partisan
Post Partisan

Limiting Combo Box Options

Hello,

 

I have a column called Semester in the SQL server. I'm there are more than 20,000 rows in that column. with historical semesters. I just want to provide a newly added semester as an option in the combo box. 

 

Thanks,

Omi

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @omi18 ,

Do you want to limit the ComboBox to have the newly added semester value? Could you please share more details about it?

Could you please share a bit more about the semester value within your SQL View?

 

If you want to limit the ComboBox to have the newly added semester value (single one option), please try the following workaround:

Set the Items property of the ComboBox to following:

FirstN(
    Distinct(
          Sort(
               Filter('[travel].[CourseDesc]',Label2.Text = email1),
               semesterID,     // use 'semesterID' as Sort column
               SortOrder.Descending   // sort records in Descending
          ),
          Semester
    ),
    1
)

 

If you just want to show the Semester options from latest to the oldest in this ComboBox, please try the following formula within the Items property of ComboBox:

Distinct(
          Sort(
               Filter('[travel].[CourseDesc]',Label2.Text = email1),
               semesterID,     // use 'semesterID' as Sort column
               SortOrder.Descending   // sort records in Descending
          ),
          Semester
    )

 

Please try above solution, check if the issue is solved.

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xida-msft
Community Support
Community Support

Hi @omi18 ,

Could you please share a bit more about your scenario? How many records stored in your SQL Table? More than 20000?

Could you please show more details about the formula you typed within the Items property of the ComboBox?
Further, do you want to add a new option within the ComboBox control?

 

If you just want to add a new options within the ComboBox control, please try the following workaround:

Set the Items property of the ComboBox to following:

Filter(
   Split(
       Concat(
              Distinct('[dbo].[Your SQL Table]', Semester),
              Result & ";"
       ) & "New Semester Option",
       ";"
   ),
   !IsBlank(Result)
)

Note: Please make sure you have set the "Data row limit for Non-delegable queries" option to maximum  value -- 2000 within Advanced settings of App settings of your app.

 

Above formula may be subject to Delegation limit in PowerApps canvas app, if the amount of your SQL Table records is more than 2000, please consider bulk-load your SQL Table records into a collection in your canvas app, then use the collection as data source in your canvas app.

Please check and see if the following thread solution could help in your scenario:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Pulling-in-large-ish-SQL-tables/m-p/243777#M...

I assume that you have a auto-increment number type column in your SQL Table, acted as 'Primary Key' in your SQL Table. You could use the following formula to bulk-load your SQL Table records into a collection (I assume that there are 30,000 records in your SQL Table😞

Concurrent(
    ClearCollect(col1, Filter('[dbo].[YourSQLTable]', recordID >= 1 && recordID <= 2000)),
    ClearCollect(col2, Filter('[dbo].[YourSQLTable]', recordID >= 2001 && recordID <= 4000)),
    ClearCollect(col3, Filter('[dbo].[YourSQLTable]', recordID >= 4001 && recordID <= 6000)),
    ClearCollect(col4, Filter('[dbo].[YourSQLTable]', recordID >= 6001 && recordID <= 8000)),
    ClearCollect(col5, Filter('[dbo].[YourSQLTable]', recordID >= 8001 && recordID <= 10000)),
    ClearCollect(col6, Filter('[dbo].[YourSQLTable]', recordID >= 10001 && recordID <= 12000)),
    ClearCollect(col7, Filter('[dbo].[YourSQLTable]', recordID >= 12001 && recordID <= 14000)),
    ClearCollect(col8, Filter('[dbo].[YourSQLTable]', recordID >= 14001 && recordID <= 16000)),
    ClearCollect(col9, Filter('[dbo].[YourSQLTable]', recordID >= 16001 && recordID <= 18000)),
    ClearCollect(col10, Filter('[dbo].[YourSQLTable]', recordID >= 18001 && recordID <= 20000)),
    ClearCollect(col11, Filter('[dbo].[YourSQLTable]', recordID >= 20001 && recordID <= 22000)),
    ClearCollect(col12, Filter('[dbo].[YourSQLTable]', recordID >= 22001 && recordID <= 24000)),
    ClearCollect(col13, Filter('[dbo].[YourSQLTable]', recordID >= 24001 && recordID <= 26000)),
    ClearCollect(col14, Filter('[dbo].[YourSQLTable]', recordID >= 26001 && recordID <= 28000)),
    ClearCollect(col15, Filter('[dbo].[YourSQLTable]', recordID >= 28001 && recordID <= 30000))
);
ClearCollect(colCombined, 
    col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15
)

then you could modify the formula in your ComboBox Items property to following:

Filter(
   Split(
       Concat(
              Distinct(colCombined, Semester),   // use colCombined collection as data source here
              Result & ";"
       ) & "New Semester Option",
       ";"
   ),
   !IsBlank(Result)
)

 

Please try above solution, check if the issue is solved.

 

Best Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xida-msft ,

 

My Current formula is: Distinct(Sort(Filter('[travel].[CourseDesc]',Label2.Text = email1),Semester,Ascending),Semester)

 

No, I do not want to add a new option within the Combo Box.

 

I'm using SQL server View as a data source. Where I have columns Semester, semesterID, Faculty, Email, Etc.

each semester has at least 2000+ rows.

 

Best,

Omi

v-xida-msft
Community Support
Community Support

Hi @omi18 ,

Do you want to limit the ComboBox to have the newly added semester value? Could you please share more details about it?

Could you please share a bit more about the semester value within your SQL View?

 

If you want to limit the ComboBox to have the newly added semester value (single one option), please try the following workaround:

Set the Items property of the ComboBox to following:

FirstN(
    Distinct(
          Sort(
               Filter('[travel].[CourseDesc]',Label2.Text = email1),
               semesterID,     // use 'semesterID' as Sort column
               SortOrder.Descending   // sort records in Descending
          ),
          Semester
    ),
    1
)

 

If you just want to show the Semester options from latest to the oldest in this ComboBox, please try the following formula within the Items property of ComboBox:

Distinct(
          Sort(
               Filter('[travel].[CourseDesc]',Label2.Text = email1),
               semesterID,     // use 'semesterID' as Sort column
               SortOrder.Descending   // sort records in Descending
          ),
          Semester
    )

 

Please try above solution, check if the issue is solved.

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

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
Users online (3,429)