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

 

 

17 REPLIES 17
Highlighted

Hi @RandyHayes ,

 

Thanks for help.

 

I tried the new formula, there is a new issue. We are near.

 

the formula is not looking up the registry and entering the finish time for the ID 80, but instead creating a new registry as ID 81 & updating it as finish time.

Capture8.JPG

Highlighted

Hi @RandyHayes 

 

like this, and when downtime registered for the next time it creates a new registry

Capture8.JPG

Highlighted

@Vinoth1_ 

Duh...my bad!  I believe I had a not where a not was not supposed to be 😉

Here...this formula:

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!
Highlighted

Hi @RandyHayes 

 

Thanks for help.

 

I tried. but still some issue

 

when a new registry is made it is starting the registry with Finish time instead of start time & then lookup & update the finish time.

Capture9.JPG

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

Hi @RandyHayes ,

 

Many thanks for your help. the formula workes cool.

 

one final question. is it possible to display the captured start & finish time when pressing patch button? 

example when pressed 1st time it shows the start time, when pressed the 2nd time it shows the finish time, and when a new registry is made it reset the text box of start & finish & show the new start time, and then the new finish time.

 

such thing possible??

 

 

Capture10.JPG

 

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

Highlighted

Hi @RandyHayes ,

Thanks again.

 

its working superb. 

 

Many thanks for your kind help all this time.

 

 

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 (7,471)