Unanswered

Cascading drop downs saving on SharePoint list view but not when I open the saved item

(0) Share

Report

Posted on by Jmcnutt

My form is a List on a private SharePoint Online Teams site. I edited the form using Power Apps and created 3 cascading dropdowns for the location data. I created a second SharePoint Online List that exists on the same Teams site for the dropdown Items.

The main List that I want to save the entries to is called 'Security Occurrence Report'. The second List containing the location data is called 'Locations'.

I created 3 columns that are all single line of text fields for the cascading dropdowns (LocationM, LocationZ, and LocationS). I added those fields to the form then deleted the text input and added a new dropdown. I added a data connection for the Locations SharePoint List that has 3 columns (Title - used for LocationM, Zones, and Sub-Zones) and used the following formulas to create the cascading dropdowns:

LocationM - first column

ddMajor (name of dropdown):

Items: Ungroup(Table({Value: Blank()},{Value: Distinct(Locations, Title)}),"Value")

Default: Blank()

OnChange: Reset(ddZone); Reset(ddSub);

LocationM DataCard:

Update: {Value:ddMajor.Selected.Result,ID:LookUp(Locations,Title = ddMajor.Selected.Result).ID}

Default: Blank()

LocationZ - second column

ddZone (name of second dropdown):

Items: Ungroup(Table({Value: Blank()},{Value: Distinct(Filter(Locations,Title = ddMajor.Selected.Result),Zones)}),"Value")

Default: Blank()

OnChange: Reset(ddSub);

LocationZ DataCard:

Update: {Value:ddZone.Selected.Result,ID:LookUp(Locations,Title = ddZone.Selected.Result).ID}

Default: Blank()

LocationS - third column

ddSub (name of third dropdown):

Items: Ungroup(Table({Value: Blank()},{Value: Distinct(Filter(Locations,Zones = ddZone.Selected.Result),'Sub-Zones')}),"Value")

Default: Blank()

LocationS DataCard:

Update: {Value:ddSub.Selected.Result,ID:LookUp(Locations,Title = ddSub.Selected.Result).ID}

Default: Blank()

I also created a button using the following Patch command to submit the form (I get an error trying to submit using the "save" button on the top of the form):

Patch(
'Security Occurrence Report',
Defaults('Security Occurrence Report'),
{ Title: DataCardValue14.Text, //* this is a text field renamed 'Brief Description of Events'//
LocationM: ddMajor.Selected.Result, //* first cascading dropdown//
LocationZ: ddZone.Selected.Result,   //* second cascading dropdown//
LocationS: ddSub.Selected.Result,   //* third cascading dropdown//
Report_x0020_Type: DataCardValue15.Selected, //* dropdown//
Reported_x0020_by: DataCardValue17.Selected,   //* person lookup from company directory//
Security_x0020_Supervisor: DataCardValue18.Selected, //* person lookup from company directory//
Date_x0020__x0026__x0020_Time_x0: DateValue1.SelectedDate+ Time(Value(HourValue1.Selected.Value), Value(MinuteValue1.Selected.Value), 0), //* date and time column//
Date_x0020__x0026__x0020_Time_x00: DateValue2.SelectedDate+ Time(Value(HourValue2.Selected.Value), Value(MinuteValue2.Selected.Value), 0), //* date and time column//
Complainant: DataCardValue19.Text, //* single line text//
Person_x0020_of_x0020_Interest: DataCardValue20.Text,   //* multi line text//
Description_x0020_of_x0020_Event: DataCardValue21.Text,   //* multi line text//
Police_x0020_Incident_x0020__x00: DataCardValue16.Text,    //* single line text//
Value_x0020_of_x0020_Damage_x002: Value(DataCardValue23.Text),  //* number//
Value_x0020_of_x0020_Recovered_x: Value(DataCardValue24.Text), //* number//
OData__x0033_rd_x0020_Party_x0020_Invo: DataCardValue22.Value, //* yes/no //
Attachments: DataCardValue25 //* default attachment field//
}
);
ResetForm(SharePointForm1); RequestHide()

The form submits, closes and saves correctly but when I click an item to view it, the LocationM, LocationZ, and LocationS fields are all blank text boxes. The values appear on the email alert that is sent out and on the list view.

Not sure where to go from here, I've tried so many different types of cascading dropdowns and this is the closest I've come to getting it to work. One other problem I'm anticipating is permissions when sharing list items with people that aren't part of the private SharePoint Online Teams site, but we'll save that for another post.

Thanks for reading this far, please let me know if you need any other information or screen grabs.

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (1)

Verified answer

Anchov 1,986 on at

Like (2)

Report
Your drop downs are likely blank because you have not configured their default value, you will need to set the default to the item saved in your data source.
Something like:
If(IsBlank(ThisItem.SavedLocation), Blank(), ThisItem.SavedLocation)
Its a little more complicated if your drop down is a combo box full of object choices, but it will be a similar concept.

Was this reply helpful? Yes No
Jmcnutt 32 on at

Like (1)

Report

Thank you, that fixed my problem! It's been a long and difficult learning process and it's hard to find a full tutorial to create exactly what you need.

Out of curiosity, what would the difference have been if I made them combo boxes?

Was this reply helpful? Yes No
Anchov 1,986 on at

Like (1)

Report
With a combo box, you can set the data source to a multi-column table, collection, SharePoint list, etc. For example, a separate SharePoint list called locations that has other uses other outside of being used in your app ( columns: location name, franchise number, address, contact name etc.). When you select the item with a combo box, only the specified location name of the column gets written back to SharePoint list connected to your form, but maybe you need access to those other "hidden" columns else where in your form.

However, when your reopen the form and need to see the default selected item, you have to select the entire row of the location (the whole object), not just the location name.

You would set the DefaultSelectedItems property to something like:

If(IsBlank(ThisItem.Location), Blank(), Lookup(Locations, 'Location Name' = ThisItem.Location))

Hope this helps. There are other ways to handle this scenario, but wanted to give you an idea.

Was this reply helpful? Yes No
Jmcnutt 32 on at

Like (0)

Report

Encountering one final issue with the form. Everything saves if I use the Submit button with Patch command, however, if I used 'Edit all' and 'Save' the first of the cascading dropdowns (Location - Major/ ddMajor) does not save. I can see the Zone and Sub-Zone on the list view, but Major is blank. When I open the item, all 3 dropdowns appear blank and when I select the correct Major location again the other 2 dropdowns repopulate with the previously saved values. I can edit and grid view and select the value for Major from the Choice column and it will save successfully and display correctly if I go back into the item.

In summary, the only thing that's not working correctly is filling out the 'Location - Major' column by using the 'edit all' and 'save' buttons. Every other way of inserting values into the field works and the other column values that filter based on that column still save.

location fields.PNG

Was this reply helpful? Yes No
Anchov 1,986 on at

Like (0)

Report

What is Update property on the Datacard for Location - Major/ ddMajor?

Was this reply helpful? Yes No
Jmcnutt 32 on at

Like (0)

Report

Update property on the DataCard =
{
Value:ddMajor.Selected.Result,
ID:
LookUp(
Locations,
Title = ddMajor.Selected.Result
).ID
}

I tried changing it to SelectedText, Selected.Value, SelectedText.Result, and SelectedText.Value in a variety of combinations and none of those changed anything.

Was this reply helpful? Yes No
Anchov 1,986 on at

Like (0)

Report
If I recall, ID is case-sensitive and should be Id in the object name . Try:
{ Value:ddMajor.Selected.Result, Id: LookUp( Locations, Title = ddMajor.Selected.Result ).ID }

Was this reply helpful? Yes No
Jmcnutt 32 on at

Like (0)

Report

Same result. ddZone and ddSub both saved but ddMajor remains blank on the list view. I open up the item and all of them appear blank. If I edit and reselect the proper Major value, then the other 2 repopulate with their saved values automatically.

Was this reply helpful? Yes No
Anchov 1,986 on at

Like (0)

Report
I believe you will need to make sure that your object is formulating correctly. You can create a temporary button on your screen, and set the OnSelect to:
Set(varTemp, { Value:ddMajor.Selected.Result, Id: LookUp( Locations, Title = ddMajor.Selected.Result ).ID })
Press the button and view the results of the variable (highlight the variable) in studio (example viewing variable in studio)

If you are not seeing the expected values in your temporay variable, then they are not going to set in lookup correctly

Was this reply helpful? Yes No
Jmcnutt 32 on at

Like (0)

Report

It comes back with the expected Value and Id. Anything else I can test? I'm stumped.

Was this reply helpful? Yes No