Attempting to Create a Status Dashboard using Sharepoint List Data

[JWPowder]

Hello, I created a power app form that allows my operators to input the current status of their given machine. What I would like to do is create a dashboard that lists my machine assests, the current status, the time that status was last updated, and the elapsed time since that status was updated. 

This file already exists in an Excel format which I have attached a screenshot of. 

The part I am struggling with is creating a field that updates with the current status and time that the status was updated. These fields will need to update each time a new entry is made using the form.

Any assistance is greatly appreciated.

[RandyHayes]

@JWPowder 

If you're moving this over to SharePoint...you already have what you need.

SharePoint has a Modified and Modified By column be default that will have the date and time modified and who modified it.

I hope this is helpful for you.

[JWPowder]

I have already created the list in SharePoint where the form data dumps into. 

What I need I guess is some sort of lookup function that pulls the last record of data for each of the 15 "Machine #" fields.

I keep keep getting error codes when trying the Lookup function. "Label2_1" is the box on the left where "EXT 24" is. I want the box with the formula to return the most recent status that was input into the list for this Machine #. 

I hope that all makes sense. 

[RandyHayes · Accepted solution]

@JWPowder 

Yes, your formula is not correct.  A Lookup needs valid criteria to perform the lookup against columns in the datasource.  Label2_1 is a control, it is not criteria.

Your formula should be:

   LookUp(Sort(StatusBoardData_HILLIARD, Created, Descending), 'Machine #' = Label2_1.Text, Status)

This will produce the Status value from the most recent record for that Machine #

Also, if you have made Status to be something other than a Text column in your list, then the above will need to reflect that.  Like if it is a choice column, then the above should have Status.Value not just Status.

[JWPowder]

Thank you Sir. This has worked out perfectly. 

[JWPowder]

Hello Randy, I am now encountering a problem with the formula. For some reason it will not pull any data for these 3 machines. Label2_16 contains the machine name "ACM 40-6". 

Also, how can I calculate the elapsed time since the last punch in the column on the far right? The formula I currently have in there results in the incorrect fractional number. I would like it to display in "Hours:Minutes". 

[RandyHayes]

@JWPowder 

So first, looking at your pictures...I hope that you are not putting in a control for everything that is seen in each line and that you are using a Gallery for your dashboard.  Otherwise you've got a LOT of work to do and will have a pain maintaining this app.  Just mentioning!

As for the LookUp, perhaps you have a trailing or leading space in your datasource for that particular machine?  Or that the most recent entry in your list for that machine has no status.

You can try to replace the formula with the following:

Coalesce(
  LookUp(
    Sort(StatusBoardData_HILLIARD, Created, Descending), 
    StartsWith('Machine #', Label2_1.Text), 
    Status
  ),
  "-no status-"
)

As for the time in time format, you need to do more with the calculation for hours and minutes.  Also, never reference a control directly without the property that you want from it.  Label2_3 is the control.  Label2_3.Text is the text that you want.  Seems trivial, but these are the kind of things that will work in edit mode, but fail in play mode of the app.

Use the following formula for your duration:

With({_minutes: DateDiff(Now(), DateTimeValue(Label2_3.Text), Minutes)},
    RoundDown(_minutes/60, 0) & ":" & Text(Mod(_minutes, 60), "00") & ":00"
)

[JWPowder]

@RandyHayes , as expected, you were 100% correct. My text fields for the Machine # had trailing space that was throwing it off. 

As for your question about my controls...again, you are correct. Forgive me as I just made the switch from Excel VBA to Power Apps yesterday. Each one of those fields is a label with the formula you provided, albeit, slightly modified to pull data from the required columns in the datasource. I will play around with the gallery list template to see if I can get it to work the same as above. 

The formula for the time worked out almost perfectly, I just had to switch the values around shown below:

With({_minutes: DateDiff( DateTimeValue(Label2_3.Text), Now(), Minutes)},
    RoundDown(_minutes/60, 0) & ":" & Text(Mod(_minutes, 60), "00") & ":00"
)