cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Lookup value from sharepoint and register the finish time

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.

Capture.JPG

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

@Vinoth1_ 

Okay...I needed to look over the logic more.  Yes, I realized that your logic for getting the initial record from your list is where the issue comes in.  Your Lookup is not distinguishing the right record.  In other words, your lookup will find the first record that matches the criteria....that will (after you have a complete start and finish record) be the exact same record...so the logic would then always think it needs to do a new record.

 

Change your formula to this:

 

With({dataItem:   // Your Record
    LookUp(SortByColumns(Downtime, "Start", Descending), 
        Operator = Operator_dropdown_1.Selected.Value && 
        Machine= Machine_dropdown_1.Selected.Value && 
        Reason.Value= Reason_dropdown.Selected.Value
    )},
    With({newTrue:   // Your criteria for a new record
        (IsBlank(dataItem.Start) && IsBlank(dataItem.Finish)) || 
        (!IsBlank(dataItem.Start) && !IsBlank(dataItem.Finish))
    },

        // Your Patch to the DataSource
        Patch(Downtime,
            If(newTrue, Defaults(Downtime), dataItem),
            Patch(
                {
                Operator:Operator_dropdown_1.Selected.Value,
                Machine:Machine_dropdown_1.Selected.Value,
                Reason:Reason_dropdown.Selected.Value
                },
                If(newTrue, {Start:Now()}, {Finish:Now()})
            )
        )
    ) 
)

 

 

Note, the new change is that the Lookup will find the first record based on the sorted list of Start times.  That should give you the most recent record.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

Highlighted

@Vinoth1_ 

Glad we got there!!

 

Yes, you can capture your Patch output:

With({dataItem:   // Your Record
    LookUp(SortByColumns(Downtime, "Start", Descending), 
        Operator = Operator_dropdown_1.Selected.Value && 
        Machine= Machine_dropdown_1.Selected.Value && 
        Reason.Value= Reason_dropdown.Selected.Value
    )},
    With({newTrue:   // Your criteria for a new record
        (IsBlank(dataItem.Start) && IsBlank(dataItem.Finish)) || 
        (!IsBlank(dataItem.Start) && !IsBlank(dataItem.Finish))
    },

        // Your Patch to the DataSource
        Set(glbDownRecord,
            Patch(Downtime,
                If(newTrue, Defaults(Downtime), dataItem),
                Patch(
                    {
                    Operator:Operator_dropdown_1.Selected.Value,
                    Machine:Machine_dropdown_1.Selected.Value,
                    Reason:Reason_dropdown.Selected.Value
                    },
                    If(newTrue, {Start:Now()}, {Finish:Now()})
                )
            )
        )
    ) 
)

 

Then just set the Text property of the Start and Finish labels (assumed labels as you'd not want to edit them) to glbDownRecord.Start and glbDownRecord.Finish accordingly.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

17 REPLIES 17
Highlighted
Super User III
Super User III

@Vinoth1_ 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

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 

 

Capture.JPG

Capture.JPG

 

the ideal result of the app expected was like this

 

Capture3.JPG

Thanks in advance.

Highlighted

@Vinoth1_ 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

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. 

Highlighted

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()})
        )
    )
)    

 

Capture4.JPG

 

Thanks in advance!

Highlighted

@Vinoth1_ 

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

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

@Vinoth1_ 

I think we posted at the same time.

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

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

 

Capture5.JPG

 

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

Capture4.JPG

many thanks for your support

Highlighted

@Vinoth1_ 

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()})
        )
    )
)    

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,899)