cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cleve
Advocate I
Advocate 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
Advocate I
Advocate 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
Advocate I
Advocate 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
Advocate I
Advocate 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.

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Users online (3,670)