Patch with foreign key / look up

(5) Share

Report

Posted on by cbrenn

I am trying to create a copy of the selected record. This is in a model-driven app, using a new command and using PowerFx.

This is also using dataverse tables.

I am currently getting error messages referencing field names that are not in the patch statement. So the errors are not always helpful.

I am trying to create a new record the same as this example.

// Sample

Patch('Account', Defaults('Account'),

{
'Name': "New Company",
'PrimaryContact': LookUp('SystemUser', 'SystemUserId' = "your_systemuserid")
}

)

Questions:

For the entity (Account in this example) - should it be the table name or the plural name?
- My working patch (that doesn't have a lookup column) works with plural but doesn't work for singular

For the lookup column in this example -('Primary Contact') should this be the schema name or the logical name?
- AI Searches have shown "always use the logical name" but I'm losing confidence as this column in dataverse has a space in the name but this example does not.
- The Accounts.Primary Contact column has a logical name of 'primarycontactid' in dataverse :(
- For 'PrimaryContact' - I've tried
  - my table name such as 'table name' (foreign key display name)
  - the schema name for this column such as 'prefix_ColumnName'
  - the logical name for this column such as 'prefix_columnname'

Help!

// Mine:

I removed the name column as it is an autonumber column and we are using Defaults(). Is this the correct way?

I have tried so many different versions and don't have an example but one of them is this

Patch(
   'tablename', // in plural form
   Defaults('tablename'), // in plural form
   {
       'Column Name': LookUp('Other_Entity', ThisRecord.prefix_tablekey =prevfix_tableid)
   }
)
'*key' is how I named my foreign key columns

'*id' is the primary key columns

I found a comment where somebody said

I was able to patch it much simpler than I thought,
just pass the record itself as the value for the lookup column.
My main issue was typing the wrong column name for an hour lol.

So I tried this

Patch(
    'tablename', // plural
    Defaults('tablename'), //plural
    { 
        'schema name on tablename': LookUp('display name used on tablename', ThisRecord.prefix_thistablekey=prefix_othertableid) 
    }
) 

Categories:

Dynamics 365 general

I have the same question (0)

All responses (3)

Answers (2)

Verified answer

Adam_Travers 340 on at

Like (3)

Report
Copy link

Link copied!

I had fun with this when tasked with creating a Clone Opportunity button. Im not sure if this is any help but here is the Power FX Query I created to copy a selected Opportunity record which works fine, you maybe able to cross reference it with what you are trying to achieve at Account level.

Patch(

Opportunities,

Defaults(Opportunities),

{

'Opportunity Name': "Copy of " & Self.Selected.Item.'Opportunity Name',

Owner: Self.Selected.Item.Owner,

Type:Self.Selected.Item.Type,

'Opportunity Originating Campaign':Self.Selected.Item.'Opportunity Originating Campaign',

Contact:Self.Selected.Item.Contact,

'Bytes Specialist':Self.Selected.Item.'Bytes Specialist',

Revenue:Self.Selected.Item.Revenue,

'Gross Profit':Self.Selected.Item.'Gross Profit',

'Billing Options':Self.Selected.Item.'Billing Options',

'Billing Number':Self.Selected.Item.'Billing Number',

'Product Details':Self.Selected.Item.'Product Details',

'Additional Comments':Self.Selected.Item.'Additional Comments',

Reseller:Self.Selected.Item.Reseller,

'Reseller Contact':Self.Selected.Item.'Reseller Contact',

Solution:Self.Selected.Item.Solution,

Propositions:Self.Selected.Item.Propositions,

'Sub Propositions':Self.Selected.Item.'Sub Propositions',

'Product Type':Self.Selected.Item.'Product Type',

Vendor:Self.Selected.Item.Vendor,

'Programme Type':Self.Selected.Item.'Programme Type',

'Purchase Route':Self.Selected.Item.'Purchase Route'

},

If(IsType(Self.Selected.Item.'Potential Customer',Contacts),

{

'Potential Customer': AsType(Self.Selected.Item.'Potential Customer',Contacts)

}),

If(IsType(Self.Selected.Item.'Potential Customer',Company),

{

'Potential Customer': AsType(Self.Selected.Item.'Potential Customer',Company)

})

);

Notify("A copy of the selected Opportunity has been created. Please update the necessary fields.")

Was this reply helpful? Yes No
Verified answer

cbrenn 34 on at

Like (2)

Report
Copy link

Link copied!

Adam, Thank you.

It appears that your lookup column is 'Potential Customer'?

I'm not done with this but I did resolve one hurtle. It turns out that my second table was not available - (the lookup column reference).

To add them: Once you are on the 'edit command bar' screen there is a tool bar across the top of the screen - click 'Open component library'. From there you can then add your data tables. I am working with dataverse and a model-driven app with the tables in the solution but when you "leave" and enter into the command edit world you don't have access to your tables anymore. Surprise.

Alas, with the tables added this below now checks out, without errors, however no record is created.

ForAll (

Self.Selected.AllItems,

Patch (

Assignments,

Defaults(Assignments),

{

'Building Officer': ThisRecord.'Building Officer',

Name: "C " & ThisRecord.Name

}

)

)

Result: Works

This simplified version based on yours.

Patch (

Assignments,

Defaults(Assignments),

{

'Building Officer': Self.Selected.Item.'Building Officer',

Name: "Copy of " & Self.Selected.Item.Name

}

)

Result: Works

Was this reply helpful? Yes No
Adam_Travers 340 on at

Like (0)

Report
Copy link

Link copied!

That’s correct as I’m pulling it from the Opportunity table. The missing data table caught me out too and also having to define mandatory fields.

Was this reply helpful? Yes No