In parent-child relationship, a lookup column is created in Child entity referring to Parent entity. In Part 5 of series, we covered how to populate Lookup and set default parent value for selected child record. In this blog we will see how to update Lookup value when edit form is submitted or when update button is pressed.

CDS51

I have an edit form which displays product details (Product Name – Text, Company – Lookup) when a product is selected from Product Galley. Edit form by default comes with update button (✓) which on submit updates field values back to data source. But unfortunately, lookup value does not get updated when form is submitted as shown below:

CDS52

Let’s see how Lookup field value can be updated using Patch function. Patch function is used to update one or more records in a data source. You can specify columns to update instead of overriding whole record. You can also use Update or UpdateIf functions to update data. For more details on when to use which function refer here.

  • To update Company Lookup value, we will use Patch function along with SubmitForm. Syntax for Patch function is as below:

Patch(Data Source, Base record, Change record(s))

      • Data Source – source which contains record to be created or modified
      • Base record – (record type) refer one or more records to be created or modified. If Defaults function is used, record will be created
      • Change record – (record type) refer record properties to be modified. You can define more than one change record to be updated in sequential manner

 

  • Select update button and paste below formula to update lookup value along with SubmitForm. You can define multiple functions/actions separated by semi colon.

SubmitForm(EditForm2);Patch(‘Company Products’, LookUp(‘Company Products’,cr2a5_companyproductid=Gallery1.Selected.cr2a5_companyproductid),{cr2a5_Company1:LookUp(Companies,cr2a5_companyid=Dropdown3.Selected.cr2a5_companyid)})

CDS53

  • Now when update button is pressed, lookup value will also be updated along with other fields.

CDS54

Let’s break down above formula and see what exactly is happening.

  • SubmitForm(EditForm2) – Submit form function comes by default with Edit Form which updates fields values back to data source on submit
  • Patch function – with 3 parameters as below:
    • ‘Company Products’ – Data source for Products
    • LookUp(‘Company Products’,cr2a5_companyproductid=Gallery1.Selected.cr2a5_companyproductid) – Lookup function which looks for selected product from Product Gallery (Gallery1) in ‘Company Products’ data source based on company product id (cr2a5_companyproductid) and returns that record to be updated in Patch function
    • {cr2a5_Company1:LookUp(Companies,cr2a5_companyid=Dropdown3.Selected.cr2a5_companyid)} – refer to change record (enclosed in curly braces) with only Company lookup field (cr2a5_Company1) to be updated. Since Company is a lookup field, it can be updated with a record type value. Lookup function here returns selected Company record from drop down and pass it to cr2a5_Company1 parameter.

Note: Field logical names (cr2a5_Company1) are case-sensitive

Part 5