cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
starship12
Level: Powered On

Error message when trying to create cascading drop downs

Hi all,

 

I am trying to create a cascading dropdown following this video https://www.youtube.com/watch?v=pkZG2boN7jQ I have constantly been getting error message below. All of my dropdowns in the powerapp are connected to SQL server. I do have a lot of lookup dropdowns though but no other dropdowns have this issue.

powerappse.jpg

6 REPLIES 6
Community Support Team
Community Support Team

Re: Error message when trying to create cascading drop downs

Hi @starship12 ,

Could you please share more details about the formula you typed within the Items property of the Dropdown box?

 

Based on the error message that you mentioned, I think this issue may be related to connection you created to your SQL Table from your app.

 

Please consider re-create a new connection to your SQL Table (the issue Dropdown box related to) from your app, then try it again, check if the issue is solved.

 

Also please consider take a try to turn off the "Improved app rendering (final validation)" option and "Explicit column selection" option within Advanced settings of App setting of your app, then re-load your app, then try it again, check if the issue has been fixed.

 

In addition, please also make sure that you have defined a Primary Key within your SQL Table the issue Dropdown box related to.

 

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.
starship12
Level: Powered On

Re: Error message when trying to create cascading drop downs

Hi @v-xida-msft , 

 

The first dropdown DivisionDropdwn_1 has this code: 

Distinct('[dwrept].[JobTitles]',Category)

 

and the second dropdown has this code which is the one giving the error message:

If(!IsBlank(DivisionDropdwn_1.Selected.Value), Distinct(Filter('[dwrept].[JobTitles]', Category = DivisionDropdwn_1.Selected.Value), Title))

This table does not have a primary key. There are only three columns in the table: category, title and active columns. I have tried your suggestions but am unfortunately still getting the error message

Community Support Team
Community Support Team

Re: Error message when trying to create cascading drop downs

Hi @starship12 ,

Based on the formula that you mentioned, I could not find any syntax error with it.

I have made a test on my side, and don't have the issue that you mentioned.

 

Please defined a PRIMARY KEY within your '[dwrept].[JobTitles]' SQL Table, then re-create a new app based on this SQL Table. Try your formula again, check if the issue is solved.

 

If the issue still exists, please take a try with the following workaround:

Add a ComboBox (DivisionComboBox) within your app, set the Items property to following:

 

Distinct('[dwrept].[JobTitles]',Category)

set the OnChange property of the DivisionComboBox to following:

ClearCollect(
                  RecordsCollection,
                  Distinct(Filter('[dwrept].[JobTitles]', Category = DivisionComboBox.Selected.Value), Title)
)

Add another ComboBox, set the Items property to following:

RecordsCollection

Note: Set the SelectMultiple property of above two ComboBox controls to false.

 

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.
starship12
Level: Powered On

Re: Error message when trying to create cascading drop downs

Hi @v-xida-msft ,

 

As suggested, I have created a new table with primary key and tried creating a new app with the cascading dropdown and collections but I am getting the same error message at this stage (Had to change it to result instead of values as it was not letting me): 

reca.jpg

Community Support Team
Community Support Team

Re: Error message when trying to create cascading drop downs

Hi @starship12 ,

Could you please share more details about the error message?

 

Based on the screenshot that you mentioned, I think there is something issue with your Filter function. Please check if you have specified a proper column name for filtering within your Filter formula.

 

Also please check if the connection you created within your app is invalid. You could add a Data Table control within your app, set the Items property to '[dwrept].[JobTitles2]', then check if the records could be displayed within the Data Table correctly.

 

If the issue still exists, please consider re-create your SQL Table with a PRIMARY KEY without any additional constraints. Then re-create a new app based on the new created SQL Table, check if the issue is solved.

In addition, there are some known limits/issues with SQL Server connector you need to know:
https://docs.microsoft.com/en-us/connectors/sql/#known-issues-and-limitations

 

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.
starship12
Level: Powered On

Re: Error message when trying to create cascading drop downs

Hi @v-xida-msft ,

 

I have tried your suggestion and can see the data on the data table. I have also recreated the table and added the primary key, still no luck. 

 

I created cascading dropdown using Sharepoint list as the data source for now