cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cleve
Helper I
Helper I

Patch Collection to Dataverse with a Lookup Column

One line in my patch statement is not working for me.

The line I commented out is giving me errors.

 

//Patch data to commission table
ForAll(
    col_commission,
    Patch(
        commission,
        Defaults(commission),
        {
            broker: drop_gal_commission_broker.Selected,
            commission: Value(ThisRecord.indiv_commission),
            //line_ref: LookUp(line, ref = ( Value(Last( Sort(line,'Ref #',Ascending)).'Ref #') + Value(ThisRecord.line) )),
            side: ThisRecord.side
        }
        )
    );//End commission table patch

 

In the commission table, the line_ref column is a lookup to the line table.

 

The lookup function doesn't seem to recognize the table. Autocomplete doesn't kick in at all after I type in the connected table's name. If I remove the lookup and attempt to patch the value from the collection, I get an error that I need to pass a record instead of a number. 

 

If I reference the same expression in a text label control, it resolves just fine (Ex. Value(Last( Sort(line,'Ref #',Ascending)).'Ref #') ).

 

The line record won't exist until it gets created in a previous section on this same onSelect expression. Because of this, I need to refer to the record rather than store the record in a control like I did with the broker. Any help with this would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
cleve
Helper I
Helper I

This is what got the job done:

First(Search([@line], ThisRecord.line, ref)),

ref being the column name for the line #

 

I had to patch the value from the label where the line number had been calculated to the col_commission collection for this to work.

 

The line number is a sequential value, so we added the index of the current item to the last result in the Dataverse table to calculate the new line numbers. This won't be a good solution for systems with multiple users. In that case, you would need to calculate the value of the line number at the time of the patch.

View solution in original post

2 REPLIES 2
cleve
Helper I
Helper I

I tried this instead, but it did not work.

line_ref: LookUp(Choices(commission.'Line Item'), line_ref = lbl_gal_commission_line.Text)

 The text value from the previous method is stored in the label control referenced above.

Now the error is on the column name reference within the lookup function:

line_ref. it says "Name isn't valid. This identifier isn't recognized."
Obviously this is the correct 'identifier' because it is the same column/field we are trying to pass a value to in the first place...

cleve
Helper I
Helper I

This is what got the job done:

First(Search([@line], ThisRecord.line, ref)),

ref being the column name for the line #

 

I had to patch the value from the label where the line number had been calculated to the col_commission collection for this to work.

 

The line number is a sequential value, so we added the index of the current item to the last result in the Dataverse table to calculate the new line numbers. This won't be a good solution for systems with multiple users. In that case, you would need to calculate the value of the line number at the time of the patch.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,242)