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

[WarrenBelz]

Hi @winch ,

The Items of a drop-down need to be a Table, which the Distinct() value is, however the second option is a single value (which you can have in the Default).

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]

Thanks @WarrenBelz

I'm not sure I'm following, by "second option", do you mean the second portion of the formula I posted above, or are you suggesting there's another way to accomplish what I'm trying to do other than using the Distinct formula?

Best,

Winch

[WarrenBelz]

@winch ,

The formula

Distinct('Global Customer',ID)

is a Table statement (although it has one Field Result and may only have one item) and is valid for the Item of a drop-down (which is expecting a Table).

ThsiItem.GlobalCustomer

is a single value - you cannot mix data types.

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]

@WarrenBelz got it, thanks!  Makes sense now.

That goes to solve the first portion of my question.  The second portion being, how do I get the selected value for each of these dropdowns to be saved by the form into the SP list?  The list has "lookup" columns as data type.

I will post a more complete set of print screens a bit later.  For now, I wasn't able to get the data to save when I click my button (with a "SubmitForm" statement as OnSelect).

Best,

Winch

[WarrenBelz]

Hi @winch ,

My first piece of advice is to throw your Lookup columns away - you simply do not need them if Power Apps is going to be your interface to SharePoint and they will cause you all sorts of unnecessary grief. Simply do the same lookup in Power apps and write back to Text column/s.

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]

Hi @WarrenBelz,

My primary reason to use the lookup columns is that I can use them for data analysis from the backend, not for my users on the Power Apps platform.  The data is also part of a larger data structure which is used in other systems, so for now, I feel I'm kind of stuck with these, but I will consider this option if I cannot get it to work as intended.

Also, I was wondering which control  type should be preferred for applications like these: combo boxes or dropdowns?  Does it matter?

Thanks,

Winch

[WarrenBelz]

@winch ,

Combo boxes are far more powerful and really have no downside compared with drop downs, which are for simple one choice, one field selections.

[winch]

Hello again,

As promised, here is a more complete description of what I am trying to achieve, screen shots included in order to make it easier to understand.  I hope this will describe my issues better and assist you in resolving them.

As mentioned before, I'm building a form that is used to fill in service tickets.  The list of service tickets is built into a Sharepoint list, that is stored on my Sharepoint infrastructure.  I'm using Power Apps to customize the form for my users.  The Sharepoint list hosting the service tickets has several columns used to store the data pertaining to the service tickets.  Most of these columns are simple values, but a few are lookup columns, which are taking their values from other Sharepoint lists on the same site.  These lists are basically there to reuse common information that is shared across the entire business, so they can be updated independently and not hard coded into the app.

Here's a view of the of a few of the columns that are in this service tickets SP list:

As told, some of them are lookup columns that are sourcing their data from other SP lists on the same site.  These lists are being used in other applications, so I have to use them as the source of the data for the service ticket SP list but I cannot modify them.

In the Power Apps form, I'm using combo boxes (thanks @WarrenBelz )to get the data from the individual underlying SP lists:

The first combo box (cmbGlobalCustomer) is placed inside a data card (this is a canvas app that I'm modifying from the Sharepoint list "form setting" option and the data cards were placed by default by Power Apps Studio)

The data card has the following properties:

Default = ThisItem.GlobalCustomer

Update = cmbGlobalCustomer.Selected

And the combo box (cmbGlobalCustomer) the following properties:

Here, I'm having issues with which function should be favoured for the Items property: Distinct, Choices or simply the content of the SP list column?  (I had that working initially but made so many changes troubleshooting that I regressed a bit).

Once the user has selected the higher level customer, I need the second combo box, which identifies the customer at a more granular level (by its location) to show the choices that are only related to the top level customer.  This is to be displayed in a combo box (cmbCustomerLocation), which I filter using the Filter function:

This combo box has the following properties:

Items = Filter('Customer Location','Customer Name'.Value = cmbGlobalCustomer.Selected.'Global Customer')

Which works but for which I get a delegation warning, which shouldn't be a problem given the small size of my lists.

Again, this combo box is place in a data card.

The last combo box retrieves the "Customer name" and works exactly as the second level combo box (except that it gets its data from a the "Customer Contact" SP list).

So far, I was able to get all of these combo boxes filtered correctly, and display the proper information, but when I submit the form, nothing saves to the SP list.

The funny thing here is that I have got very similar lookup columns that are included in this list and that accomplish the exact same thing, but that were placed there automatically when I started editing the form.  These work flawlessly.  The only difference is that they are not being filtered.

Take a look at the following combo box:

It has its data source in another SP list called "tblApplications" with the following properties:

Items = Choices([@tblTechnicalRequest].Application)

And placed into a data card with the Update property set to DataCardValue11.Selected.

When I edit an item or create a new item in my service ticket SP list, selecting the application works and it gets saved.

Let me know if there are more information that I can provide you, and many thanks for the help you've provided so far!

Winch

[WarrenBelz · Accepted solution]

@winch ,

This is the fundamental problem with Lookup columns is when you start modifying the content of the controls that write to them (and why they are a <insert profanity here> in Power Apps for me). The content of a Lookup column is a Table with two columns, Value and Id, which are actually the Field and ID of the item looked up in the other table. When you leave the control alone, Power Apps takes care of this with the Choices() for the Items (which includes these values)

Choices([@tblTechnicalRequest].Application)

and simply writes the table item back.

DataCardValue11.Selected

When you modify the Items, you lose the Id and have to then get it back to successfully write to the field. Below is a rough syntax of what the Update would look like

{
   Value:DataCardValue11.Selected.'Customer Name'.Value,
   Id:
   LookUp(
      YourOtherList,
      YourCustomerField=DataCardValue11.Selected.'Customer Name'.Value,
   ).ID
}

They also have other issues with things like Delegation, Filtering, Sorting and GroupBy and are actually historical structures designed before Power Apps existed to interact with SharePoint and InfoPath. I have not used them for a long time.

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.