cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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
Highlighted
Community Support
Community Support

Re: Limiting Combo Box Options

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
Highlighted
Community Support
Community Support

Re: Limiting Combo Box Options

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.
Highlighted
Helper III
Helper III

Re: Limiting Combo Box Options

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

Highlighted
Community Support
Community Support

Re: Limiting Combo Box Options

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

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (5,261)