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#M71518
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,
