Passing values to SP list using cascading dropdowns

[winch]

Hello!

I have been spending quite some time trying to achieve the following without luck.  Browsed through hundreds of posts in this forum and nothing seems to be working.

I have a Sharepoint list which contains the details of a service ticket.  To classify the service ticket, I have three lookup fields, each taking their source into other SP lists.  So I have a field for "Customer", "Location" and "Contact".  The SP list "Customer" contains only simple data fields (e.g. the name of the customer).  The SP list "Location" contains the various locations for all customers, hence, one column is a lookup column which draws its values from the "Customer" list.  The third list contains the details of contacts working at the customers.  It contains two lookup columns, which are respectively getting their values from "Customer" and "Location" lists.

I'm using Powerapps to customize the service tickets lists.  Users need to enter the details of the service ticket, starting with the customer name, which is displayed in a dropdown control sourcing its values from the "Customer" list.  Then, users select the location, by using a second dropdown, which sources its values from the "Location" list, but I'm filtering the available locations using the value picked in the first dropdown.  Same logic for "Contact", which sources its data from the "Contact" list filtering the options by the "Location" picked in the second dropdown.  I can make this work no problem.

My problem is when I'm trying to submit the form: nothing gets saved.  I have been playing around with the various properties of the datacard and the dropdowns, to no avail.

I have the datacard which contains the "Customer" information with the "Datafield" property set to the column name of my service ticket list, the update property set to  "Items" property set to:

"LookUp('Global Customer',Customer_x0020_Name=ddGlobalCustomer.Selected.Result)"

Which should pull the value from my "Customer" list ("Global Customer"), but it doesn't work.  I'm wondering if because I'm using complex data columns, I should be working with the ID of each record rather than names.

I'm also having a lot of issues getting the fields to display the data depending on the form mode.  What I would like to happen is for the fields to show "blank" or no values when the form is in "New" mode and show the actual data of the service ticket when the form is in "Edit" mode.  I'm drawing a blank on both counts.  I can definitely fetch the form mode, but when I'm trying to feed the "Items" property with a "If" statement based on the form mode, I'm getting errors.  For instance, in the dropdown for the "Customer" field, I have:

The funny thing is that I can made the "if true" portion of the formula work (as if when there's no "If" statement) and I can also make the "if false" portion work (the "ThisItem" portion.  I also checked that the component of the formula checking for the form mode works, and it does.  I also checked if the "Items" property can accept a "If" statement and haven't found anything to the contrary.  I'm in a deep need for help here!

Best,

Winch

[winch]

@WarrenBelz 

Thanks a lot, agree with you that they are indeed difficult to work with.  Your comment on the content of the lookup columns (Value and ID) really hits the nail on the head I believe.  I have been going around in circles thinking that I perhaps needed to pass the ID rather than the Value, you point seems to confirm this might be the case.

I will be giving it another go today and keep you posted on the progress.

Best,

Winch

[WarrenBelz]

Hi @winch ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[winch · Accepted solution]

Hi @WarrenBelz ,

Sorry for not responding earlier, I did not have much time in the past week to work on this.

Your suggestion was spot on: my controls are passing the correct data to my SP list and it works flawlessly.  I also spent more time to change all the dropdowns to combo boxes per your recommandation.

For the purpose of helping others, here's what my solution (or @WarrenBelz solution, shall I say) looks like:

First level customer: a combo box control which uses the Choices formula to get the values from my top level SP list.  I even added code to display the current value if the form is in Edit or View mode.

Values are passed to the service ticket SP list via the Update property of the data card, set to:

Second level customer: a combo box control with Items set to the following, which filters on the first level dropdown value:

Values are passed to the service ticket SP list via the Update property of the data card, set to:

And customer contact: a combo box control which gets its values from my third SP list, with Items set to:

Which was a bit more complicated in this case, since I needed to recompose a string so users would see both the first and last name instead of the first name only.  I also encountered an issue with the data structure that meant I needed to filter the contact SP list on the values of the two combo boxes, not only the second level one (poor data structure, I know...)

And the values are passed via the Update property of the data card as follows:

Now when testing the form, almost everything works:

- I can pick the first level customer, and all available choices are there

- I can pick the second level customer, and the list is filtered accordingly

- I can pick the contact, and the list is filtered accordingly

- I can submit the form and the data gets saved to the SP list (in the lookup columns) as it should be

My only remaining issue is when I try to edit the form.  If I open the form in Edit mode, I wanted my users to not only see the three fields with the right data, but I also wanted to allow for them to select another customer, in case there was a mistake made.  To make it clean, I wanted to have the two combo boxes to be cleared of their displayed data should the top level customer was to change.  Same goes for a change at the second level customer (in which case only the contact would clear).

I managed to accomplish this using context variables that flicker between false, then true then false again.  When the first level customer is change, I use the OnChange property to update two variables using UpdateContext:

And I set the Reset property of each of the two combo boxes to the context variable.  Did the same logic for changes made to the second level customer.  Works like a charm.

I've been struggling with one last thing, the displayed values in the "Contact" combo box, which for some reason does not show anything when I open the form in Edit mode.  The first two combo boxes are displaying the data, by way of the DefaultSelectedItems property (shown here for the second level customer):

Which I use to check if the value of the first level combo box matches the current record being edited, and if not, the Reset property causes the combo box to revert to the default value of { } and if so, Reset will trigger the display of the current value in the record.

I have applied the exact same logic for my "Contact" combo box, but it loads blank each time.  I've set up the DefaultSelectedItems property to:

The syntax is a bit more complicated due to my desire to display the full name of the contact rather than only the first name or last name, but this works, that's not where the issue is.  I troubleshooted it by throwing "true" and "false" text strings in the then else portions of the function so I can see when the condition is evaluated or not.

The behaviour is a bit strange: if I edit the form, I'll see the first and second level customers combo boxes filled with the data but a blank combo box for the contact (I know there's data in the column/record).  I also know from testing that the If statement evaluates the condition as false.  Then if I change the second level customer value to the same value, the statement turns true and then it will work.

This is what I get when I open the form (the data behind the redacted part is correct): the contact is empty.

Then if I clear the selected item in "Customer location" and select the same value, then the "Contact" will show.  I also tested that the contact that shows isn't he first one in the list, it is truly the contact that is saved in the service ticket SP list.

Quite puzzling!  Would appreciate any help!

Best,

Winch

[winch]

Hello again,

I'm not sure what has happened, but my solution no longer works...  Now I guess I undertand better why you don't really like the lookup columns @WarrenBelz!

Without making any changes, I'm getting in a situation where the form, when submitted, again does not record the values to the SP list.  There's no error being thrown out.  I have a notification set for the OnSuccess event of the form (which says that changes were recorded) and another set on the OnFailure event (which says changes were not recorded).  Neither are triggered, the form just closes and the changes are not saved.

I tripled checked if all the fields that are "required" in the list are filed, this is the case.  I also made changes to the underlying list (mainly cleaned up some wrong entries in them) and also flipped on the "cascade delete" option.  I noticed the form was no longer saving after that.  I returned to the original option of not having the "cascade delete" turned on for the lookups, but I'm not sure if I might not have screwed them over by creating indexes in those lists.

Any help would be appreciated!

Best,

Winch

[WarrenBelz]

Hi @winch ,

Rather than me wading through all of your posts, can you please post (in Text) the code that is not working updating the Lookup field.

[winch]

Hello @WarrenBelz ,

This is the code in the Update property of each of the datacard not recording to the SP list:

{Value:cmbCustomerLocation.Selected.'Customer Location',Id:LookUp('Customer Location','Customer Location' = cmbCustomerLocation.Selected.'Customer Location').ID}

Here you can see the result of the formula is indeed a record and not a text value or something else.

And the second one:

{Value:cmbCustomerContact.Selected.Contact,Id:cmbCustomerContact.Selected.ID}

Let me know if there's anything else you might need!

Can't thank you enough!

Winch

[WarrenBelz]

@winch ,

Is 'Customer Location' the name of your List or the name of the Field in the List (I hope they are not the same as this is a prime candidate for Ambiguity). 

Also the Items of cmbCustomerContact you have referenced earlier suggest the output is .Site - you have referenced .Contact - is there a reason for this?