cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Slevin3900
Frequent Visitor

Patching an existing record in a Dataverse table from within a Forall

I have a collection from a Dataverse table; 'Tickets'

 

ClearCollect(col_TicketsToBeRedeemed,
FirstN( Sort( Filter( Tickets, Redeemed_Date = Blank() And Ticket_Owner = "John@Smith.com"),
Issued_Date, Ascending), 1 ));

 

 With this collection 'col_TicketsToBeRedeemed', I need to loop through each individual record and update a field called 'Redeemed_Date' in the DB table.

 

ForAll( col_TicketsToBeRedeemed, 
            Patch( Tickets,
                LookUp( Tickets, ThisRecord.Id = col_TicketsToBeRedeemed[@Id]),
                    { Redeemed_Date: Now() }
            )
);

 

The initial collection 'col_TicketsToBeRedeemed' was created from the same table I am attempting to Patch to. 'Tickets'

The column headers in the collection and table are the same.

The field 'Id' is a Autonumbered field type.

There is a Unique Identifier field 'ticketsid' which I've also attempted to match on but no luck.

I have tried many many different iterations of this in hopes of getting to work without success.

 

Any assistance would be much appreciated.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Slevin3900
Frequent Visitor

@MichelK  I figured out it.  The column names causing the grief and by correcting I was able to successfully get to work.

ClearCollect(col_TicketsToBeRedeemed, 
    FirstN( Sort(
       Filter( Tickets, Redeemed_Date = Blank() And Ticket_Owner = varCustomer.Email ), 
                Issued_Date, Ascending), varCustomer.QuantityToRedeem )
);
ForAll(col_TicketsToBeRedeemed As Row,
    Patch( Tickets, LookUp( Tickets, Id  = Row.Id),
         { Redeemed_Date: Now() } );
)

 

View solution in original post

4 REPLIES 4
MichelK
Resolver II
Resolver II

Hello @Slevin3900 ,

First of all, in the following expression :

ClearCollect(col_TicketsToBeRedeemed,
FirstN( Sort( Filter( Tickets, Redeemed_Date = Blank() And Ticket_Owner = "John@Smith.com"),
Issued_Date, Ascending), 1 ));

The FirstN( Collection, 1 ) returns only one record. So if the objective is to update just the first one, there would not be a need for a ForAll, and you would just use the following code:

Patch(Tickets,First( Sort( Filter( Tickets, IsBlank(Redeemed_Date) And Ticket_Owner = "John@Smith.com"),
Issued_Date, Ascending))), {Redeemed_Date : Now()});

However, if you need to update all the matching records and the FirstN was unintentional, you could use an UpdateIf :

UpdateIf( Tickets,  IsBlank(Redeemed_Date) And Ticket_Owner = "John@Smith.com", {Redeemed_Date: Now()});

 

Let me know if this helps! 

Slevin3900
Frequent Visitor

I appreciate the response, the FirstN is intentional.  There could be multiple rows matching but only need to tag 'x'  number as being used.  In the filter provided some of the data is hardcoded where otherwise it would be dynamic.

The goal of the filter is to retrieve 'x' number of the oldest rows for an individual.

I have attempted the UpdateIf but unable to get 'x' number of rows which is important.

MichelK
Resolver II
Resolver II

Deal
Then can you try the following?

 

ClearCollect(col_TicketsToBeRedeemed,
FirstN( Sort( Filter( Tickets, Redeemed_Date = Blank() And Ticket_Owner = "John@Smith.com"),
Issued_Date, Ascending), 1 ));
UpdateIf( col_TicketsToBeRedeemed, true, { Redeemed_Date: Now() } );
Patch( Tickets, col_TicketsToBeRedeemed ); //Could be Collect instead of Patch, forgot which one does the job

 

Slevin3900
Frequent Visitor

@MichelK  I figured out it.  The column names causing the grief and by correcting I was able to successfully get to work.

ClearCollect(col_TicketsToBeRedeemed, 
    FirstN( Sort(
       Filter( Tickets, Redeemed_Date = Blank() And Ticket_Owner = varCustomer.Email ), 
                Issued_Date, Ascending), varCustomer.QuantityToRedeem )
);
ForAll(col_TicketsToBeRedeemed As Row,
    Patch( Tickets, LookUp( Tickets, Id  = Row.Id),
         { Redeemed_Date: Now() } );
)

 

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,307)