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

Unable to patch SharePoint from cascading dropdown

Hi guys, 
I am having an issue with patching a sharepoint list using cascading dropdowns. 
(Area dropdown filters Question Dropdown)
The columns I am trying to patch into are Lookup Columns in Sharepoint. Looking up Question and Area from a list called "Inspection Items & Areas". 

I get the following errors  "Invalid Argument Type, expecting a record value but of a different schema"
"Missing Column Your Formula is missing a column "Id" with a type of number" 


Cascading-Dropdowns.PNG

Here is the code for filtering the dropdown.
Question's Items clause: Distinct(Filter('Inspection Items & Area', Area in DataCardValue4.SelectedItems), Question)
Area's Items clause:  Distinct('Inspection Items & Area', Area)

Collection Code: 

Collect(TEST, {AuditID:Value(AuditIDTest.Text), QuestionCascade: QuestionDD_3.Selected, Response: Value(ResponseSlider_3.Value), InspectionDetails: DetailsData_3.Text });

ResetForm(FrmInspectionItems_2);


Here is the patch code that is not working:  ForAll(TEST, Patch('Inspection Results', Defaults('Inspection Results'), {AuditID:AuditID, QuestionCascade:QuestionCascade, Response: Response, InspectionDetails: InspectionDetails }) );


Thanks in Advance 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Unable to patch SharePoint from cascading dropdown

Hi @RossBroadley ,

Based on the formula you provided, I think you have some misunderstanding with the formula I povided.

 

Firstly, for your Collect formula. If the 'QuestionDD_3' represents the Question Dropdown box in your Edit form, please modify your formula as below (please the following modification carefully):

Collect(
        InspectionsAreaTest, 
        {
           AuditID: Value(AuditIDTest.Text), 
           QuestionCascade: QuestionDD_3.Selected.Result,   /* <- Please type QuestionDD_3.Selected.Result rather than QuestionDD_3.Result */
           Response: Value(ResponseSlider_3.Value), 
           InspectionDetails: DetailsData_3.Text
        }
)

For your Patch formula, the QuestionColumn I mentioned above is actually the column in your 'Inspection Items & Areas' list, which your Question column in your 'Inspection Results' list reference values from:

ForAll(
      InspectionsAreaTest, 
      Patch(
            'Inspection Results',
             Defaults('Inspection Results'),
            {
               Question: { 
                          Id: LookUp('Inspection Items & Areas', QuestionColumn = QuestionCascade, ID),
                          Value: QuestionCascade
                        },
               Response: Response,
               InspectionDetails: InspectionDetails
            }
      )
)

On your side, you should replace the QuestionColumn with your own column in the 'Inspection Items & Areas' list.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 REPLIES 12
shailendra74
Level 8

Re: Unable to patch SharePoint from cascading dropdown

Hi @RossBroadley 

 

for lookup fields you need to write code as below in Patch function:

 

Patch(TEST,
         Defaults(TEST),
        {LookupFieldName:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                        Id: QuestionCascade.Selected.ID,
                        Value:QuestionCascade.Selected.Title}
              }
         )

Thanks.

RossBroadley
Level: Powered On

Re: Unable to patch SharePoint from cascading dropdown

Hello @shailendra74
The first line of the patch function containing the azure connector. What exactly does that do? I've not used or seen this in patch functions before. 
Also, the ID field. I don't have that field within my sharepoint list (Besides the default sharepoint ID field). 
Am I right in assuming that we're setting ID to the value of the Selected question? Even though it's an item and not an ID? (Silly question perhaps, new to filtering and patching)

shailendra74
Level 8

Re: Unable to patch SharePoint from cascading dropdown

Hi @RossBroadley 

 

You can use the default SP ID

 

Thanks.

RossBroadley
Level: Powered On

Re: Unable to patch SharePoint from cascading dropdown

@shailendra74 
Oh, I see. 

{LookupFieldName:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
What does this line of code do? I've never seen it before. In fact that patch statement is very different to what I've seen, so I'm very confused haha! 

RossBroadley
Level: Powered On

Re: Unable to patch SharePoint from cascading dropdown

@shailendra74 
Do you have any resources I could look at for patching into lookup columns and cascading dropdowns?
I've had a look at trying to implement the code. But I'm not sure what needs to be changed, or what it should be changed to. 

This is what I've tried. Unsure of the syntax etc for this.

ForAll(InspectionsAreaTest, Patch('Inspection Results',
Defaults('Inspection Results'),
{QuestionCascade:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: QuestionCascade.Selected.ID,
Value:QuestionCascade.Selected.Title},
Response:Response,
InspectionDetails:InspectionDetails
}
);

Community Support Team
Community Support Team

Re: Unable to patch SharePoint from cascading dropdown

Hi @RossBroadley ,

Do you want to patch the Cascading Dropdown box value (LookUp filed) into your SP List?

 

Based on the formula you provided, I think there is something wrong with it. I have made a test on my side, please consider modify your formula as below:

 

Collect(
InspectionsAreaTest,
{
AuditID: Value(AuditIDTest.Text),
QuestionCascade: QuestionDD_3.Selected.Result, /* <-- Modify your formula here */
Response: Value(ResponseSlider_3.Value),
InspectionDetails: DetailsData_3.Text
}
); ResetForm(FrmInspectionItems_2); ForAll( InspectionsAreaTest, Patch( 'Inspection Results', Defaults('Inspection Results'), { Question: { /* <-- Modify your fomrula here */ Id: LookUp('Inspection Items & Areas', QuestionColumn = QuestionCascade, ID), Value: QuestionCascade }, Response: Response, InspectionDetails: InspectionDetails } ) )

Note: The Question column in your 'Inspection Results' list LookUp values from the QuestionColumn in your 'Inspection Items & Areas' list. The Question represents the LookUp field in your 'Inspection Results' list.

 

Please consider take a try with above solution, then check if the issue is solved.

 

More details about setting a value for LookUp field in a SP list, please check the following threads or blog:

https://powerusers.microsoft.com/t5/General-Discussion/Lookup-Field-Default-Value-Error/td-p/79481

https://powerapps.microsoft.com/en-us/blog/default-values-for-complex-sharepoint-types/

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RossBroadley
Level: Powered On

Re: Unable to patch SharePoint from cascading dropdown

Thank you very much @v-xida-msft for the excellent information. 
I will give this code a try tomorrow and let you know how I get on. 
Much appreciated!

Community Support Team
Community Support Team

Re: Unable to patch SharePoint from cascading dropdown

Hi @RossBroadley ,

Please consider take a try with the solution I provided above, then check if the issue is solved.

 

If you have solved your problem, and my above solution is helpful in your scenario, please go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RossBroadley
Level: Powered On

Re: Unable to patch SharePoint from cascading dropdown

Hi @v-xida-msft 
I modified by code as suggested, but I'm still unable to patch my selections from Question and Area into the Inspection Results list. 

I have a feeling that there's an issue with the dropdown column as well. I'm unable to use the .result property. The only property that works with my dropdown is .selecteditems, the = operator also doesn't work. Only able to use 'in'.  
Here is the dropdown code:
Distinct(Filter('Inspection Items & Area', Area in AreaDD_Cas.SelectedItems), Question) 

In the collect code. I am unable to use .result, I get the error 'Name isn't valid. This identifier isn't recognised
Here is the collect code I have tried: 


Here is the code I have added 


Collect(
InspectionsAreaTest,
{
AuditID: Value(AuditIDTest.Text),
QuestionCascade: QuestionDD_3.Result,
Response: Value(ResponseSlider_3.Value),
InspectionDetails: DetailsData_3.Text
}
);

ResetForm(FrmInspectionItems_3);

In the collect code, the error goes away if I use .SelectedItems but I'm pretty sure that's the incorrect data type for this. 



I am also getting a bunch of errors with the patch function as well. 
The errors I am getting are:
"Invalid argument type. Expecting a record value, but of a different schema"
"incompatible type. the 'Value' column in the data source expects a 'text' type and you're using a 'error' type."
"The function 'patch has some invalid arguments'"
"Name isn't valid. This identifier isn't recognized" 

Here is the patch code I've tried.
ForAll(
InspectionsAreaTest,
Patch(
'Inspection Results',
Defaults('Inspection Results'),
{
Question: {
Id: LookUp('Inspection Items & Areas', QuestionColumn = QuestionCascade, ID),
Value: QuestionCascade
},
Response: Response,
InspectionDetails: InspectionDetails
}
)
);

Thank you very much for your help




Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 51 members 4,936 guests
Please welcome our newest community members: