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

Any Patch Experts Here?


Hi

I am trying to do a patch insert or update, depending on values held.

I am using a variable called FindID to do a lookup to see if the record exists in the Sharepoint list.

If(VarMode="Edit",Set(FindID,First(MyCollect.ID)),Set(FindID, LastAddedItem.ID);

Set(myItem,LookUp(ListName,ID = FindID);

 

However,
=FindID is saying name isn't valid. This identifer isn't recognised.

 

Any help appreciated.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Any Patch Experts Here?

@sienna28 

The "baked in" logic in the formula is that - if there is a DataTableID, then we use that, if not, then we use the LastAddedItem.ID, if not then myItem will be blank and therefore we will create (defaults) a new record with Patch.

So, based on that, if myItem is blank, then we know we just added a record and therefore we should set the LastAddedItem to the just added record in patchResult.

This is totally based on the fact that you call this "LastAddedItem".  If we wanted the last updated item, then we would not have the If(IsBlank(MyItem), Set... part there, we would just simply Set it.

 

Yeah, try something with that label. That seems a little strange as it should be showing something.  Also, perhaps try renaming the variable in your formula to lclMsg and then update your label to that.  (I, personally, use the lcl prefix to any Context variable so that I know, very quickly, that it is a context variable local to that screen only)

_____________________________________________________________________________________
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.

View solution in original post

14 REPLIES 14
Highlighted
Super User
Super User

Re: Any Patch Experts Here?

@sienna28 

There are a couple ways to go about what you are trying to do, but with the method you have, there are some errors in your formula.  Consider the following instead:

If(VarMode="Edit",
Set(FindID, 
If(VarMode="Edit", First(MyCollect).ID, LastAddedItem.ID
) ); Set(myItem,LookUp(ListName,ID = FindID);

Which can be simplified to the following:

Set(myItem,
   LookUp(ListName, 
          ID = If(varMode="Edit", 
                  First(MyCollect).ID,
                  LastAddedItem.ID
                 )
         )
   )

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.
Highlighted
Helper V
Helper V

Re: Any Patch Experts Here?

Thanks for your reply.

 

I added the code, but it seemed to throw up errors.

Highlighted
Super User
Super User

Re: Any Patch Experts Here?

@sienna28 

Your second formula is the one that is more direct to the source, so I will look at that one.

I am not sure why you would put this in an If statement to look at the "Edit" value in two places...that is not making sense.

Set your formula to this and see if that produces any error:

Set(myItem,
   LookUp(ListName, 
          ID = If(varMode="Edit", 
                  First(MyCollect).ID,
                  LastAddedItem.ID
                 )
         )
   )
_____________________________________________________________________________________
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.
Highlighted
Helper V
Helper V

Re: Any Patch Experts Here?

It may help if I explain what I am attempting.

 

On the 1st screen I am trying to do an insert and update (it could be that they want to update the record they just inserted).

 

On another screen which has a list of records, I click one from a datatable - I was trying to pass the ID of the record to the 1st screen so that it could be edited.

 

I am finding that a second record gets inserted rather than updating the one on the screen.

 

That is why I had an edit variable flag. It appears I may be going about it the wrong way.

Any pointers appreciated.

 

Thank you

 

 

 

 

Highlighted
Super User
Super User

Re: Any Patch Experts Here?

@sienna28 

I think you might be overcomplicating the process.  Especially when you start creating variables to track and hold and so forth.  You have to remember that variables are your responsibility.  So, if you want something to update based on a variable...you have to constantly keep it updated.

 

So, based on your plan...

Are you are using an EditForm on the 1st screen, or is this a form of your own setup?  If not, then we can adjust for that too.

 

_____________________________________________________________________________________
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.
Highlighted
Helper V
Helper V

Re: Any Patch Experts Here?

I'm not using forms in this app.

I created a blank screen and added controls so that I could use the patch command.

 

I have a main screen which allows insert and update of records to a Sharepoint list.

 

Another screen contains a data table with a list of records entered. Clicking on one would take me back to the main screen for editing and saving. It loads the record onto the main screen fine, but as you say I may be going wrong by maintaining an edit variables.

Highlighted
Super User
Super User

Re: Any Patch Experts Here?

@sienna28 

That is all good.  Very easy to implement your own form with control, you just do need to do a little more overhead on it.

 

As I think through your scenario, I wonder how one distinguishes between a new and update.  In other words, if a user clicks on an item in the datatable, then they are taken to the "edit form" (referring to your custom form)/Screen 1

When they are done, I assume you have a "save" action.  Is there a "new" action someplace?  How does one create a new record?  Or, does one create only one record when they first come to the "edit form" and then from there they can click on items in the datatable and edit/update them?

 

 

_____________________________________________________________________________________
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.
Highlighted
Helper V
Helper V

Re: Any Patch Experts Here?

I think I cracked it.

Your bracketing formula helped a lot.

When a record is clicked on the other screen in the data table, I set a variable for the ID called DataTableID.

In the main screen I then check to see if this has a value - if it does then I do the lookup with that value. If not then I use the last inserted ID. The record is then updated and patched using either of those ID's. The reason it was creating a second record was because I was not resetting it to blank from the main menu if the new record option is selected. There are probably a million ways to write this more elegantly than I have, but I am a beginner who is learning. Thanks for your help and pointing me in the right direction.

 


Set(myItem,
LookUp(MyData,
ID = If(!IsBlank(DataTableID),
DataTableID,
LastAddedItem.ID
)
)
)
;

If(!IsBlank(myItem),
UpdateContext({msg:"Updating"});
UpdateContext({patchResult:
Patch(MyData,
{ID: If(!IsBlank(DataTableID),
DataTableID,
LastAddedItem.ID)},
{
Title:txtClientName.Text,
WriteOffTotal:Value(WriteOffAmt.Text),
AccountReference :txtAcctRef.Text
}

)
});

UpdateContext({msg:If(Not(IsBlank(patchResult)) And IsEmpty(Errors(MyData)),"Update Patch succeeded","Update Patch failed")});

Refresh(MyData);

,


UpdateContext({msg:"Adding Rec"});
UpdateContext({patchResult:
Patch(
MyData,
Defaults( MyData ),

{
Title:txtClientName.Text,
WriteOffTotal:Value(WriteOffAmt.Text),
AccountReference :txtAcctRef.Text
}

)

});

UpdateContext({msg:If(Not(IsBlank(patchResult)) And IsEmpty(Errors(MyData)),"Insert Patch succeeded","Insert Patch failed")});

Refresh(MyData);
Set(LastAddedItem,First(Sort(MyData,ID,SortOrder.Descending)))

)

Highlighted
Super User
Super User

Re: Any Patch Experts Here?

@sienna28 

Sounds good! 

So here would be a little different way to put that formula together with the same results:

Set(myItem, LookUp(MyData, ID = Coalesce(DataTableID, LastAddedItem.ID)) );
UpdateContext({msg:If(!IsBlank(myItem), "Updating", "Adding" Rec}),
UpdateContext(
   {patchResult:
      Patch(MyData,
           Coalesce(myItem, Defaults(MyData)),
           {
		Title:txtClientName.Text,
		WriteOffTotal:Value(WriteOffAmt.Text),
		AccountReference :txtAcctRef.Text
           }
          )
   });
UpdateContext({msg: "Update Patch " & If(!(IsBlank(patchResult)) && IsEmpty(Errors(MyData)), "succeeded", "failed")});
If(IsBlank(MyItem), Set(LastAddedItem, patchResult))

This combines several areas in your formula that were repeated based on If conditions into a bit more manageable and maintainable ways.

You really don't need the Refresh of the datasource since you are patching it directly.

For the LastAddedItem, you really only need to grab that when you actually "add" an item.  

Theoretically, the above formula is the same results that you had with the longer...just for example as you learn more about PowerApps as you mention.

Also, it was written freehand, so there may be some syntax errors, but the functionality is there.

 

_____________________________________________________________________________________
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.

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,165)