Patch complex columns to dataverse table - lookup,choice,single line of text, whole number, data and time.

[workingonit]

I have two dataverse tables: Supplier Items and Item Register.

To create a new row in the Item Register I am using a canvas form with a button to patch the form fields back to a dataverse table.

The Item Register contains fields of different types: lookup, choice, single line of text, while number, data and time.

I have trawled numerous examples of patching each of these field types and I cannot for the life of me manage to get all of the fields of the form OnSelect of the button to patch to the Item Register table in dataverse.

Can anyone offer any guidance here?

[timl]

Hi @workingonit 

I have some formula examples of how to patch those data types here. Hopefully, that will help you.

http://powerappsguide.com/blog/post/dataverse-how-to-patch-the-5-most-complex-data-types

If you're still stuck, let me know.

[workingonit]

Hi @timl ,

Thank you - I've had a look at the resource previously, and again just now. But I don't see how to utilise it? Why does every example have Name:"Contract Doc"? Obviously it's the primary key? But it's a text data type so it would change each time a form is submitted? My primary key is an autonumber data type, which is generated upon submission of the form when the new entry is created.

Also the syntax for every example you provide requires that you are specifying a match? I have a blank form with cascading selections, so I can't specify a match. I want the completed form to be submitted and the fields written back to the dataverse table.

I've attached a screen shot of the columns in my Item Register dataverse table, and the canvas app form (which is part of my model driven app). I want users to complete the form, with available selections dependent on other fields. e.g you choose the Project Name from the dropdown, this then provides a list of Item SKU's to choose from, which provides a Item Common Name to select from etc.

The user then clicks Submit and a new record it created on the Item Register.

[workingonit]

I've now been able to patch all fields correctly except for the lookups. OnSubmit the lookup fields are populated with the primary key value, instead of the selection made on the canvas app form (please see attached).

This is the syntax that I'm using:

Patch('Item Registers',Defaults('Item Registers'),
{
'Item SKU':ItemSKU_DataCardValue12.Selected,
'Item Project Name':ItemProjectName_DataCardValue8.Selected.Value,
'Item Common Name':LookUp('Supplier Items','Item Common Name'=ItemCommonName_DataCardValue9.Selected.'Item Common Name'),
'Item Variety':LookUp('Supplier Items','Item Variety'=ItemVariety_DataCardValue10.Selected.'Item Variety'),
'Item Scientific Name':LookUp('Supplier Items','Item Scientific Name'=ItemScientificName_DataCardValue11.Selected.'Item Scientific Name'),
'Item Seed Lot No':ItemSeedLotNo_DataCardValue13.Value,
'Item Quantity':Value(DataCardValue1.Text),
'Item Unit':ItemUnit_DataCardValue14.Selected.Value,
'Item Date of Purchase':ItemDateOfPurchase_DataCardValue16.Value
}
)

[timl]

@workingonit 

>> OnSubmit the lookup fields are populated with the primary key value, instead of the selection made on the canvas app form (please see attached).

You'll probably find that you are patching the lookup value correctly - the issue is that the Dataverse view in your screenshot is displaying just the primary key value.

If you create a form in your canvas app and attempt to retrieve the record that you patched, you should find that it works as expected.

[workingonit]

Thank you, you are correct the issue is that the Dataverse view is only displaying the primary key value. I've dismantled the tables to reduce the lookups so that I am now only using them for primary columns.