Modifying a temporary collection using a checkbox in PowerApps with a Lookup function issue

[Ezblt]

Hello,

I am working on a SharePoint List that contains timesheets. The timesheets are saved with a status of "Saved". I have created a temporary collection called "FilteredTimesheets" that retrieves all timesheets with the status "Saved" at a given time. My goal is to move all hours from the "Saved" status to the "Billed" status per week, with the ability to choose which week to bill or not. I created another collection called "Week_data" using the "GroupBy" function on "FilteredTimesheets" to group timesheets by week.

I have a gallery that displays the weeks and corresponding hours for each week. I added a checkbox next to each gallery item.

My goal is to write a formula for the "OnCheck" checkbox that will allow me to move all timesheets from a given week to the "Billed" status if I check the box, and vice versa if I uncheck it. To do this, I want to modify the data in the temporary collection "FilteredTimesheets". For example, if I have 3 weeks saved, I want to have W1 and W2 with the "Billed" status, and W3 with the "Saved" status. Then, I will create a button that overwrites the data in the SharePoint List by adding a billing code for the billed timesheets, but I don't have an issue with that as it works.

I have tried to use this formula for the checkbox:

ForAll( Filter(FilteredTimesheets, Week = ThisItem.Week), Patch( FilteredTimesheets, LookUp(FilteredTimesheets, ID = ThisRecord.ID), { Status: "Billed" } ) )

However, I have a problem with the "LookUp" function because in order for the "Patch" to work, I need to identify a record. The only way I know to identify a record is to say that the ID of my "FilteredTimesheets" base is equal to the ID of the "ThisItem.Week_data" table. However, I cannot retrieve the ID in this way because the checkbox is linked to a "Week_data" data group and the ID does not exist in this configuration. If I want to retrieve an ID, I have to go through the "ThisItem.Week_data" collection and with an incremented "TextLabel" inside, I can only have "ThisItem.Title". I don't know how to formulate this for the checkbox.

Thank you in advance for your help. I have tried many solutions, but none of them are working.

[WarrenBelz]

Hi @Ezblt ,

I am not totally clear on your model here, but if you simply want to update everything in FilteredTimesheets where the field Week (not a good name for a field) equals the value in ThisItem.Week, try this

With(
   {
      wWeek:
      Filter(
         FilteredTimesheets, 
         Week = ThisItem.Week
      )
   },
   Patch( 
      FilteredTimesheets,
      ForAll(
         wWeek As aWeek,
         {
            ID: aWeek.ID,
            Status: "Billed"
         } 
      ) 
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Ezblt]

Hello @WarrenBelz,

Thank you for you answer, 

The formula doesn't work because aWeek isn't recognized. 

Have a good day

[WarrenBelz]

@Ezblt ,

Mistype - needs a capital A in As

[Ezblt]

@WarrenBelz 

The capital A resolve the issue but when I try totest the check box I have this message

"The data source supplied to the function is invalid".

I created FilteredTimesheets in the OnVisible of the page with this formula :

 

"Refresh(Timesheet_list);

ClearCollect(

FilteredTimesheets,

Filter(

Timesheet_list,

Status = "Saved"

)

)"

Best regards

[WarrenBelz · Accepted solution]

@Ezblt ,

If FilteredTimesheets is a Collection (I assumed it was a List), you would need this

With(
   {
      wWeek:
      Filter(
         FilteredTimesheets, 
         Week = ThisItem.Week
      )
   },
   ForAll(
      wWeek As aWeek,
      Patch( 
         FilteredTimesheets,
         Lookup(
            FilteredTimesheets,
            ID = aWeek.ID
         ),
         {Status: "Billed"} 
      ) 
   )
)

but that does not explain the error message - you need to ensure FilteredTimesheets exists when this code is run.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Ezblt]

The formula function now when I check the box one time datas are changed to Billed but the check isn't ticked I need to click on the check twice to see the tick on it. 

[WarrenBelz]

@Ezblt ,

That is a different subject to the Patch issue - I really cannot comment without fully understanding the process you are using. Is your posted question resolved ?

[Ezblt]

Not fully because I suppose if the Oncheck doesn't appear to be working properly I don't understand why I need to click on the icon twice to see the tickmark on it. Sorry if you don't understand what i want to explain.

[WarrenBelz]

@Ezblt ,

If your issue is that the checkbox resets, then that is correct unless you save the value to a field int he gallery each time you check a record. You really need to run that code from a button outside the gallery rather than a checkbox inside it, although I really cannot properly visualise what you are doing from what you have posted. I will be offline now for some time due to overnight and travelling.