cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Passing values to SP list using cascading dropdowns

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:

 

Untitled.png

 

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

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
WarrenBelz
Most Valuable Professional
Most Valuable Professional

@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.

View solution in original post

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.

 

winch_0-1627622953202.png

 

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

 

winch_3-1627623371848.png

 

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

 

winch_4-1627623747812.png

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

 

winch_5-1627623828537.png

 

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

 

winch_6-1627623972002.png

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:

winch_7-1627624237140.png

 

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:

 

winch_9-1627624698905.png


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):

 

winch_10-1627624997375.png

 

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:

 

winch_11-1627625336071.png


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.

 

winch_13-1627625884297.png

 

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

 

 

View solution in original post

16 REPLIES 16
WarrenBelz
Most Valuable Professional
Most Valuable Professional

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.

 

 

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
Most Valuable Professional
Most Valuable Professional

@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.

@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
Most Valuable Professional
Most Valuable Professional

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.

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
Most Valuable Professional
Most Valuable Professional

@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.

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:

 

columnsSPlist.png

 

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:

cmbGlobalCustomers.png

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:

 

cmbGlobalCustomerProperties.png

 

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:

 

cmbCustomerLocation.png

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:

 

cmbApplication.png

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
Most Valuable Professional
Most Valuable Professional

@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.

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!   This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions in the Forums to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Community MembersNumber of SolutionsSuper UsersNumber of Solutions @anandm08  23 @WarrenBelz  31 @DBO_DV  10 @Amik  19 AmínAA 6 @mmbr1606  12 @rzuber  4 @happyume  7 @Giraldoj  3@ANB 6 (tie)   @SpongYe  6 (tie)     Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Community MembersSolutionsSuper UsersSolutions @anandm08  10@WarrenBelz 25 @DBO_DV  6@mmbr1606 14 @AmínAA 4 @Amik  12 @royg  3 @ANB  10 @AllanDeCastro  2 @SunilPashikanti  5 @Michaelfp  2 @FLMike  5 @eduardo_izzo  2   Meekou 2   @rzuber  2   @Velegandla  2     @PowerPlatform-P  2   @Micaiah  2     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Week 3:Community MembersSolutionsSuper UsersSolutionsPower Apps anandm0861WarrenBelz86DBO_DV25Amik66Michaelfp13mmbr160647Giraldoj13FLMike31AmínAA13SpongYe27     Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Week 4:Community MembersSolutionsSuper UsersSolutionsPower Apps DBO-DV21WarranBelz26Giraldoj7mmbr160618Muzammmil_0695067Amik14samfawzi_acml6FLMike12tzuber6ANB8   SunilPashikanti8

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (933)