Updating a date field based on the selected value of a lookup field

[GITC]

Hi,

I'm very new to Power Apps and I've managed to get myself confused to the point where I'm not sure if this is possible!

I have an app that for a workshop that is supposed to track jobs and their status throughout the course of the job. When a new job is created, it will have a default job status value of "Not Yet Started". As the job progresses, the status will be updated to "WIP" before changing to "Completed". Occasionally, a job may have to go to "Waiting" if they are waiting on parts or materials. I have this working and it all works well, however I'm looking to add a date and time value for each time the job status changed. For example, if a job is started on 01/08/2020 at 8am (UK date style), the job status will change from "Not Yet Started" to "WIP" and I want a date field (date_started) to update in the back ground once this status changes. Is this possible? If it is not possible to update the date field on the change of the Job Status field, is it possible to have a button that will change the job status and update the date field at the same time?

Thanks

PG

[eka24]

It depends on where you have connected the Datasource. 

If it is connected to a Gallery, Insert a checkbox that will change the status. Assuming you already have a DateColumn to hold the date and time of change.

On the Uncheck of the Checkbox inside the Gallery Put:

Patch(Datasource,

ThisItem {Status: Textbox1.Text,

DateColumn: Now ())

Am assuming the new status is in Textbox1

Or

Patch(Datasource,

ThisItem {Status: "Complete",

DateColumn: Now ())

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[v-xida-msft]

Hi @GITC ,

How do you change the Status of a job in your canvas app?

If you want to update the date field once the Job Status field has been changed, I afraid that there is no direct way to achieve your needs. There is no Trigger supported in PowerApps canvas app to detect if the Job Status field of a record has been changed.

Regarding your needs, I agree with your second thought, you could consider add a Button in your canvas app, press it to change the Status field and update date field value simultaneously.

I assume that you add a Gallery in your canvas app to display all available records from your SP List, you could consider add a button inside the Gallery, name it as "Start Job", set the OnSelect property to following:

Patch(
      'Your SP List',
      ThisItem,
      {
          'Job Status': {
                          Value: "WIP"
                        },
           date_started: Now()
      }
)

Note: I assume that the 'Job Status' column is a Choice type column in your SP List, which stores status for each job.

Set the Visible property of the "Start Job" button to following:

If(        // display the 'Start Job' button only when the job is not started yet
   ThisItem.'Job Status'.Value <> "Not Yet Started",
   false,
   true
)

Patch function

Please try above solution, check if the issue is solved.

Regards,

[GITC]

@eka24 , @v-xida-msft  Thanks for reading and replying.

The canvas app uses a gallery to list the current jobs filtering out the completed jobs. Selecting a job in the gallery populates an edit form with various details of the job, including the Job Status. The date fields are hidden as they only need to be displayed on the SharePoint list.

I have tried to use the Patch function however, the code below  updates the Job Status field correctly, but creates a new blank record except the the date started field, which is correct.  

Patch(JobTracker,{'Job Status': "WIP" }, {'Date Started':  Now()})

I had thought that I could then use a variable (JStat) to change the value of the Job Status field and the Patch function to update the date field, but I get the same result as before with a new blank record created with the correct date and time in the Date Started field

UpdateContext({JStat: "WIP"}); Patch(JobTracker,{'Date Started':  Now()})

I think I'm almost there but I'm missing something when using the patch function that I cannot figure out. Any help or pointers would be grateful.

Thanks,

GITC 

[v-xida-msft · Accepted solution]

HI @GITC ,

Have you tried the solution I provided above?

Based on the issue that you mentioned, I think you have something misunderstanding within the Patch function. Regarding the needs that you mentioned, I think you want to update record listed in your Gallery, right?

If you want to update an existing record, the second argument of the Patch function should be tied to the specific record you want to update. So you should add a button inside your Gallery, then each item in this Gallery would own a button next to it. Set the OnSelect property of this button to following:

Patch(
      'Your SP List',
      LookUp('Your SP List', ID = ThisItem.ID),  // find the specific record you want to update using LookUp function
      {
          'Job Status': {
                          Value: "WIP"
                        },
           date_started: Now()
      }
)

Note: I assume that the Gallery has been bind to your SP List data source already.

If you want to add a new record in your data source, please try the following formula:

Patch(
      'Your SP List',
      Defaults('Your SP List'),
      {
          'Job Status': {
                          Value: "WIP"
                        },
           date_started: Now()
      }
)

Note: If you do not specify Defaults() function as the second argument of the Patch function, instead, specify the record which is not existed in your SP List, it would also add new entry in your SP List.

Please try above formula carefully, then check if the issue is solved.

Patch function usage

Regards,

[GITC]

Hi @v-xida-msft 

Thats worked great. I was adding the code to a button on the form and not in the Gallery. Once i added it in the gallery, it worked perfectly.

Thanks for the help,

GITC