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

 

 

16 REPLIES 16

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

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.

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

 

 

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

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.

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}
 
winch_1-1628890633551.png

 

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}

winch_2-1628890722665.png

Let me know if there's anything else you might need!
 
Can't thank you enough!

Winch
WarrenBelz
Most Valuable Professional
Most Valuable Professional

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

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 (1,960)