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

Cascading Dropdowns based on Lookup columns do not update Sharepoint List with SubmitForm action

I've read a number of articles and possts about cascading dropdowns, however suspect I am missing something in the following scenario as I'm unable to write values back to my Sharepoint List from the cascaded dropdown.

 

I have 3 Sharepoint Lists:

List1: Company

- CompanyID

- CompanyName

 

List2: Store (Many stores relate to one company)

- CompanyName (Lookup from List1)

- StoreID

- StoreName

 

List3: Inspection

- CompanyName (Lookup from List1)

- StoreName (Lookup from List2)

- Additional fields such as Date of Inspection, Status etc

 

Within PowerApp, I have an Inspection screen to record the status of an inspection, and it includes 2 custom dropdowns - I select Company dropdown (Dropdown_Company_New) which then successfully filters the Stores dropdown (Dropdown_Store_New) to the related Company.

 

To confirm, the Company dropdown references only the Company Sharepoint list, and the Store dropdown references only the Store Sharepoint list. The reason for not using a single CompanyStore Sharepoint List is that when creating a Company dropdown I'm unable to get a distinct list of the companies and end up with a very long repetitive list of company names, because multiple stores are linked to one company)

 

From what I've read, the following are the key attributes that need to be amended for the 2 dropdowns, their values as follows:

Company

Data Card

Company_Datacard

DataField:          "Company"

Default:             ThisItem.Company

DisplayMode:    Parent.DisplayMode

Update:             Dropdown_Company_New.Selected

 

Dropdown

Dropdown_Company_New

Items:               SortByColumns(Company.CompanyName, "CompanyName")

 

Store

Data Card

DataField:          "Store"

Default:             ThisItem.Store

DisplayMode:    Parent.DisplayMode

Update:             Dropdown_Store_New.Selected

 

Dropdown

Dropdown_Store_New

Items:               Distinct(Filter(Store_2,CompanyName=Dropdown_Company_New.Selected.Value),Store)

 

So, using just the above configs, I have an Inspection screen where I capture a number of details regarding the store inspection (Date, Status etc) and in addition, I can happily select a Company (from the Company List) and related Store (from the Store List). However, when my SubmitForm is executed (for the Inspection list) to save to my Sharepoint List, the Company and Store fields are empty.

I'm somewhat sure I understand why this is not updating the Company & Store fields, because my custom dropdowns are referencing different lists and not the Inspection list itself. I would have thought though, that relating each dropdown's DataField and Update attribute would however address this and send these values to the Inspection list.

 

As an additional approach, I've tried adding the Company and Store fields as additional datacards from my Inspection list, to the same screen, and then setting their "DefaultSelectedItems" attribute as follows:

If(
    EditForm_New.Mode = FormMode.New,
    {
        '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        Id: Value(
            LookUp(
                'Inspection',
                Title = Dropdown_Company_New.Selected.Value,
                ID
            )
        ),
        Value: Dropdown_Company_New.Selected.Value
    },
    Parent.Default
)

The above code works well to sync the values of the Company & Store fields to the same as is selected from my cascaded dropdowns. However, it's as if these values are also not persisted because if I then SubmitForm again, the Company and Store again remain empty in my Sharepoint Inspection list. If I however manually select values for the new Company and Store datacards then the values are indeed written to Sharepoint, so I know these new datacards do in fact work. How do I then get these datacards to retain/persist the values they were given from my cascaded dropdowns.