Lookup value from sharepoint and register the finish time

[Anonymous]

Hi,

I am trying to complete an app to register the downtime registry.

the app intention is to work in this way. when the operator experience a downtime, they will register the start of downtime by choosing their name,machine,downtime reason and patch button, it will make a registry in sharepoint list, and when the downtime finish, operator will once again press the patch and it will lookup by operator name, machine & start time value and then register the finish time on that particular registry.

at the moment i am using these code

Set(varItem, LookUp(Downtime, Operator = Operator_dropdown_1.SelectedText.Value && Machine= Machine_dropdown_1.SelectedText.Value && Reason.Value= Reason_dropdown.SelectedText.Value ));
If(
    IsBlank(varItem)||(!IsBlank(varItem.Start)&&!IsBlank(varItem.Finish)), 
    Patch(                           
         Downtime,
         Defaults(Downtime),
         {
          Operator:Operator_dropdown_1.SelectedText.Value,
          Start:Now(),
          Machine:Machine_dropdown_1.SelectedText.Value,
          Reason:Reason_dropdown.SelectedText
         }
   ),
   Patch(
         Downtime,
         varItem,
         {
          Finish: Now()
         }
   )
)

the problem experienced now is that app is working good for the 1st registry of a downtime reported by an operator from a machine, when the same downtime registered by the same operator from same machine later on the day, it is creating a new registry as required, but it is not looking up and registering the finish time for the new registry of same downtime registry from same operator from same machine, instead it is creating again a new registry with start time.

any help is highly appreciated.

Thanks in advance.

[RandyHayes]

@Anonymous 

Your logic in the If statement seems a little off based on your description.

Please consider changing your Formula to the following:

With({dataItem: LookUp(Downtime, Operator = Operator_dropdown_1.Selected.Value && Machine= Machine_dropdown_1.Selected.Value && Reason.Value= Reason_dropdown.Selected.Value)},

    Patch(Downtime,
        Coalesce(dataItem, Defaults(Downtime)),
        Patch(
            {
            Operator:Operator_dropdown_1.Selected.Value,
            Machine:Machine_dropdown_1.Selected.Value,
            Reason:Reason_dropdown.Selected.Value
            },
            If(dataItem.ID>0, {Finish:Now()}, {Start:Now()})
        )
    )
)    
     

Note: a couple changes in the above for the dropdowns...I adjusted to eliminate the SelectedText property as it is deprecated and should not be used.

What the above essentially does is:

1) Patches the datasource.  If the dataItem is empty then no record was found and Defaults will be used (Coalesce)

2) The patch then determines if the dataItem has a value (ID will be above 0), if so, then it sets the Finish time.  Otherwise, it is a new record and the Start time is set.

I hope this is helpful for you.

[Anonymous]

Hi @RandyHayes 

I tried the code you recommended.

It is good in creation of record for the first time, but it is not creating a new record when the same operator, register same downtime from same machine.

it simply updaing the finish time of the first registry.

Actual registry now,

ex: operator: Renats, machine: Zagis 1, Reason: Lunch

Start: 26:10:2020 16:17   Finish: 26:10:2020 16:18

when the same operator after 3 hrs register the Lunch as downtime, it is not creating a new record, but app simply updating the finish time of looking up 

the ideal result of the app expected was like this

Thanks in advance.

[RandyHayes]

@Anonymous 

Apologies that I misunderstood your original requirement.  

I assumed you wanted to Update the original record with the finish time.

What you are stating is that you want a completely new record when the entry is made for the finish time?

If so, then I am not understanding your desired data sample.  It shows the first record with the Start and Finish time.  If this was a new record for the start...where did the finish time come from?

Then you show what appears to be the "finish" entry (second highlighted line) and it shows differing start and finish times.

Can you clarify a little?  The formula can be adjusted easily for what you need, but just need to understand completely what you need.

[Anonymous]

Hi @RandyHayes ,

the intention of the app will be this.

Example: each operator work for 8 hrs a day. so they will have 3 breaks. 2 * 15min break & 1 of lunch break 30 min.

lets assume the reason code for all these 3 different breaks will be " Break"

lets say operator name: John, working in mahcine: Cutting 1, having his 15 min brake between 9:00-9:15 am, Reason = Break

then the same operator having now lunch break, operator name: John, working in mahcine: Cutting 1, having his 30 min Lunch brake between 12:00-12:30 pm, Reason = Break

then the same operator having now the 2nd 15 min break, operator name: John, working in mahcine: Cutting 1, having his 15 min brake between 12:00-12:30 pm, Reason = Break

(i was thinking about app will register these 3 above cases as seperate registry with each has individual start & finish time) 

and sometimes the operator can also take the 4th break for 15 min. and such similar registry will be registered for the next day aswell and so on.

so the operator name, machine , Reason willl be constant. the only variable is the start & the finish time of each event & the ID of those registry.

so all those different breaks with start & finish time are registered as seperate new registry. 

[Anonymous]

Hi @RandyHayes ,

what kind of changes in the suggested formula required inorder to get the desired result like this

With({dataItem: LookUp(Downtime, Operator = Operator_dropdown_1.Selected.Value && Machine= Machine_dropdown_1.Selected.Value && Reason.Value= Reason_dropdown.Selected.Value)},

    Patch(Downtime,
        Coalesce(dataItem, Defaults(Downtime)),
        Patch(
            {
            Operator:Operator_dropdown_1.Selected.Value,
            Machine:Machine_dropdown_1.Selected.Value,
            Reason:Reason_dropdown.SelectedText
            },
            If(dataItem.ID>0, {Finish:Now()}, {Start:Now()})
        )
    )
)    

Thanks in advance!

[RandyHayes]

@Anonymous 

Okay...I'm following you so far, but I am lost a little bit on this scenario (and how I wrote the original formula).

The question is this...when does an operator "finish"?

You mention marking the finish time, and so, in my formula, I assumed you had some "button" for start and finish (presumably the same button).  

Here is the details I am missing from your scenario:

lets say operator name: John, working in mahcine: Cutting 1, having his 15 min brake between 9:00-9:15 am, Reason = Break

Is John going to tap a button at 9:00 and then again at 9:15?

then the same operator having now lunch break, operator name: John, working in mahcine: Cutting 1, having his 30 min Lunch brake between 12:00-12:30 pm, Reason = Break

Is John going to tap a button at 12:00 and then again at 12:30?

then the same operator having now the 2nd 15 min break, operator name: John, working in mahcine: Cutting 1, having his 15 min brake between 12:00-12:30 pm, Reason = Break

Again, is John going to tap a button at 12:00 and then again at 12:30 (note in your example here, this is the same time as his lunch break and indicates 30 minutes rather than the 15 minute break mentioned - I assume an oversight).

Also, I am not seeing what you mentioned reflected in your data example.  I see Renats Ponnis taking Lunch at 4:17 and then finish at 4:27.  Then another Lunch at 6:27 and finish at 8:00

[RandyHayes]

@Anonymous 

I think we posted at the same time.

Read my response to the other and let me know the key items in red.

[Anonymous]

Hi @RandyHayes 

Thanks for your help & interest.

lets say operator name: John, working in mahcine: Cutting 1, having his 15 min brake between 9:00-9:15 am, Reason = Break

Is John going to tap a button at 9:00 and then again at 9:15?

yes, the operator will push the button twice, when he start & when he finish

then the same operator having now lunch break, operator name: John, working in mahcine: Cutting 1, having his 30 min Lunch brake between 12:00-12:30 pm, Reason = Break

Is John going to tap a button at 12:00 and then again at 12:30?

yes, the operator will push the button twice, when he start & when he finish

then the same operator having now the 2nd 15 min break, operator name: John, working in mahcine: Cutting 1, having his 15 min brake between 12:00-12:30 pm, Reason = Break

Again, is John going to tap a button at 12:00 and then again at 12:30 (note in your example here, this is the same time as his lunch break and indicates 30 minutes rather than the 15 minute break mentioned - I assume an oversight).

sorry it was a typing mistake, John will have his 3rd break 15 min, 3:00-3:15

Also, I am not seeing what you mentioned reflected in your data example.  I see Renats Ponnis taking Lunch at 4:17 and then finish at 4:27.  Then another Lunch at 6:27 and finish at 8:00

the below image will be the desired data collection the app intend to perform

many thanks for your support

[RandyHayes]

@Anonymous 

Got it now.  So the only factor missing in the first formula was a check on the finish time.

Try this formula now:

With({dataItem: LookUp(Downtime, Operator = Operator_dropdown_1.Selected.Value && Machine= Machine_dropdown_1.Selected.Value && Reason.Value= Reason_dropdown.Selected.Value)},

    Patch(Downtime,
        If(!IsBlank(dataItem.Finish) && dataItem.ID>0, dataItem, Defaults(Downtime)),
        Patch(
            {
            Operator:Operator_dropdown_1.Selected.Value,
            Machine:Machine_dropdown_1.Selected.Value,
            Reason:Reason_dropdown.Selected.Value
            },
            If(dataItem.ID>0, {Finish:Now()}, {Start:Now()})
        )
    )
)