Only cards with lookup items are not saving

[GregGuth]

I have a form with about 50 fields on it and all are saving as expected except the 3 drop-down fields I am pulling from SQL Server. These fields are all bound together and one refines the next and so on. All 3 fields are required**

The first is our ItemType. Selecting it will refine/filter the options in the next drop-down called ItemClass.

So if someone selects the ItemType "Merchandise" pnly that class of products will be available in the ItemClass drop-down.

Then the ItemClass selected refines the last drop-down called ItemCategory.

My ItemType DataCardValue has been renamed "cbItemTop"

The Items has been set to: SortByColumns('[Administration].[EntityType]',"Name")

My ItemClass DataCardValue has been renamed "cbItemClass"

The Items has been set to: SortByColumns(Filter('[Administration].[Class]', RelatedTypeId = cbItemTop.Selected.Type_Id),"Name")

My ItemCategory DataCardValue has been renamed "cbItemCat"

The Items has been set to: Sort(Filter('[Administration].[Category]', RelatedClassId = cbItemClass.Selected.Class_Id),Name)

When I fill out the form and save it the form submits fine but when I go to SharePoint all 3 are missing values and SharePoint flags them as required fields missing. I look at the json for the save event and all fields with values are there except these 3 fields it as though they were never filled out. The crazy thing is they are required fields and it still saves.

I researched similar issues and someone suggested I add this to the "Update" on the cards:

{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:cbItemTop.Selected,
Value:cbItemTop.Selected
}

I did this and now my save errors out with a 400 error because required fields are missing. At least not it's seeing the fields but now nothing saves at all so I feel like I am going backward. Any suggestions? I am at a loss.

[WarrenBelz]

Hi @GregGuth ,

I am a SharePoint user, but I can see the reason this is not working and I think the solution is the same syntax. You are still trying to save a record to a record element. Two things - the SharePoint solution (note the SharePoint reference) would be

{
   '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
   Id:cbItemTop.Selected.ID,
   Value:cbItemTop.Selected.Name
}

but I suspect your SQL field is Text? If so, the Update probably should be

cbItemTop.Selected.Name

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 · Accepted solution]

Hi @GregGuth ,

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.

[v-xida-msft]

Hi @GregGuth ,

Which type columns in your SP List to store these Dropdown ("cbItemTop","cbItemClass", "cbItemCat") selected values? Using Text type column or Choice type column?

1) If you use three Text type columns in your SP List to store these Dropdown ("cbItemTop","cbItemClass", "cbItemCat") selected values:

You need to re-create your Edit form, connect it to your SP list, then enable desired field data cards in this Edit form. Then you could change these three Text field data card Control Type from original Text Input Box into "Allow Values" options as below:

Then specify proper column value as Display Column value in this Dropdown box from your '[Administration].[EntityType]' SQL Table. And set the Update property of the ItemType data card to following:

cbItemTop.Selected.Name   // Name represents the column you use as Display Column value in this Dropdown box, please replace it with actual column name

Set the Default property of the Dropdown box to following:

ThisItem.TextField1  // TextField1 represents the Text type field in your List to store the ItemType Dropdown box selected value, please replace it with actual column name

 same logic should be applied to the rest of two Dropdown fields.

2) If you use three Choice type columns in your SP List to these Dropdown ("cbItemTop","cbItemClass", "cbItemCat") selected values:

You need to re-create your Edit form, connect it to your SP list, then enable desired field data cards in this Edit form. Then unlock the ItemType data card, set the Update property to following:

{
   Value: cbItemTop.Selected.Name
}

Note: The Name represents the column you use as Display Column value in this Dropdown box, please replace it with actual column name from your '[Administration].[EntityType]' SQL Table. Same logic should be applied to the rest of Choice type fields.

Set the Default property of the Dropdown box to following:

ThisItem.ChoiceField1.Value

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

Best regards,

[GregGuth]

Warren,

That was it!!! Thank you so much for your solution. I tried others and they broke the form so bad it would not even save anymore!

-Greg Guth