Custom form for Sharepoint list that can use cascading lookups and managed metadata

Question

I have created Sharepoint lists with many to one relationships using look up fields.

I would like to customise the add new item form to the list that joins everything together but have noticed that look up fields and managed data columns are not available to be selected.

Is there a way to set this up on the Sharepoint list side without having to create a full Powerapps interface? I am finding I have to recreate a lot of the functionality already available from Sharepoint lists out of the box. I just want to include three drop downs that automatically filter according to the selection made in the last drop down for when I add new records in the Sharepoint list.

Thanks,

Answer 1

Dear Maria,

Thanks for your updates and kindness.

I'm glad to hear that the issue has been solved by yourself and I appreciate your sharing the solution here as it may help other people who are experiencing some similar issues.

I'd like to summarize the issue as follows.

Issue Symptom:

================

Customer wants to edit a cascading list on a pre-built form with PowerApps. However, Patch formula doesn't work when adding the Managed Metadata field.

Issue Environment:

================

PowerApps

Suggestions:

================

Editing the pre-built form and creating a source list to create the cascading list instead of the Lookup field.

Customer's final solution:

====================

Change the list structure and using the Number field from the parent list to create a cascading list.

If it is convenient, please also send your feedback as follows as it may benefit the community process.

![Image](https://learn-attachment.microsoft.com/api/attachments/974db0b7-94b8-41e6-98a0-665778d3aa15?platform=QnA

.

Welcome to post a new thread at your convenience on our forum if you encounter any issues in your environment.

Thanks for your time and effort.

Sincerely

Cliff | Microsoft Community Moderator

Answer 2

Hi Cliff,

I ended up changing the data structure of my lists so they were no longer related via lookup fields. I simulated the relationships using a numeric field to store the parent list IDs. Once I did this, the patch was simplified and worked.

Thank you for your responses. I really appreciate it.

Kind Regards,

Maria.

Answer 3

Dear Maria,

Thanks for your updates and kindness.

To be simple, actually I think that you don't need to use the lookup column. May I know whether you create a source list for the cascading list?

The formula you list is for patching the look up column values to the list which may be not related to the cascading list.

I think that you may reformat the form. You can create a source list and then use the Distinct and Filter formula on the drop down control or the combo box control to create a relationship among all fields and then patch the data with the repeated section in the form.

Lookup fields may be a little complex with id and value needed. Instead I think that using a source list and creating a cascading list from it is simplier.

May I know how many fields you want to make a relationship? Could you upload some screenshots showing the list in SharePoint Online for our reference?

Note: Remove any private information before uploading the screenshots.

Thanks for your time and effort.

Sincerely

Cliff | Microsoft Community Moderator

Answer 4

Thanks Cliff,

It is a shame that this functionality is not available, I need help with the patch function that isn't behaving.

On my main screen I have a gallery where users select a category (from a sharepoint list of 42 categories).

Once that is selected another gallery of criteria (from another sharepoint list) is filtered according to the selection in the first. Each category can have many criteria.

Once the criteria gallery item is selected the user can then add many evidence records (which are displayed in another gallery below). The user has the option to add evidence upon clicking a button. They are redirected to a new form to enter the evidence and the values selected from the galleries on the first screen are used to prepopulate the form to enter the evidence.

I used the prebuilt powerapps form to create the form. For the cascading many to one drop downs, it has set up combo boxes where I can set " Depends on" property to set up the relationships using the lookup fields already in the sharepoint lists.

When I execute the below patch, the record is created in the Sharepoint list and the "Criteria" and the "Credit Type" columns are left empty although they are set up to be required fields in the Sharepoint list. The evidence is left as unassigned with no values for Category-Credit or Credit Type. This unusual patch record creation started once I added the Managed Metadata item for Trade "@odata.type':"Microsoft.Azure.Connectors.SharePoint.SPListExpandedTaxonomy"

Can you please help me understand why this is not working. Should I change the combo boxes to dropdown lists?

Thank you.

Patch( 'GS-EvidenceTracker',Defaults('GS-EvidenceTracker'),{//'Category-Credit':{'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",'Category-Credit':{Id:DataCardValueEvCatCredit.Selected.Id,Value:DataCardValueEvCatCredit.Selected.Value},//Criteria:{'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Criteria:{Id:DataCardValueEvCriteria.Selected.ID,Value:DataCardValueEvCriteria.Selected.Criteria},//'Credit Type':{'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",'Credit Type':{Id:DataCardValueEvCreditType.Selected.ID,Value:DataCardValueEvCreditType.Selected.Title},'Evidence Type':{Id:DataCardValueEvType.Selected.Id,Value:DataCardValueEvType.Selected.Value},'Evidence-Title':DataCardValueEvTitle.Text,'Link to Evidence':DataCardValueEvLink.Text,Responsible:{Id:DataCardValueEvResponsible.Selected.Id,Value:DataCardValueEvResponsible.Selected.Value},Trade:{'@odata.type':"Microsoft.Azure.Connectors.SharePoint.SPListExpandedTaxonomy",TermGuid:DataCardValueEvTrade.Selected.TermGuid,WssId:DataCardValueEvTrade.Selected.WssId,Label:DataCardValueEvTrade.Selected.Label,Path:DataCardValueEvTrade.Selected.Path,Value:DataCardValueEvTrade.Selected.Value},Subcontractor:DataCardValueEvSubcon.Text,'Planned Collection Date':DataCardValueEvPlannedDate.SelectedDate,'Actual Collection Date':DataCardValueEvActualDate.SelectedDate,'Submitted to ESDC?':DataCardValueEvSubmitESDC.Value,'Collection Status Comments':DataCardValueEvComments.Text});

If(// check if there were any errors when the item was submitted!IsEmpty(Errors('GS-EvidenceTracker')),// if true, show any error messageNotify("Criteria submission failed",NotificationType.Error),// else, go to success screenNavigate(scrnMainScreen));

Answer 5

Dear Maria,

I'm afraid that the dependent drop-down list can't be achieved by the out-of-the-box option. It needs to be achieved by PowerApps.

For how to create a cascading list, you can refer to the detailed information in my thread From SharePoint list to PowerApps to another SP list - Microsoft Community based on your requirements.

Your understanding is highly appreciated!

Sincerely

Cliff | Microsoft Community Moderator