Powerapps issues in using Patch & Lookup Function

[Jawaharprem]

Hi all,

Need help on this patch & lookup function by matching the uniqueid the rest should in same row.

the first query will update the assign date, unique_id to whom allocated is updating in SP correctly

the second query by lookup the unique_id the rest of should be update in same row in SP.

i have enclosed the form pic and sharepoint output pic for your reference.

This is my Query

The first query is working fine

Patch(Single_PP_Req_EU,Defaults(Single_PP_Req_EU),{Unique_ID:Concatenate(DataCardValue1,Left(User().FullName,4),"-",Text(Now(),"ddmmyy-hhmmss")),Allocated_To: User().FullName,ASSIGN_DATE:Text(Now(),"mm/dd/hhhh hh:mm:ss")});

The second patch is updating twice in SP

Patch(Single_PP_Req_EU,
LookUp(Single_PP_Req_EU, Unique_ID=DataCardValue1.Text),
{Requestor_Name: DataCardValue38.Text})

Thanks

Jawahar

[Drrickryp]

@Jawaharprem 

Why use a form and Patch instead of just SubmitForm(). 

[v-liwei-msft]

Hi @Jawaharprem 

According to the first formula you provided, Unique_ID is concatenated by DataCardValue1.Text and other text.
So in the second formula, LookUp searches for Unique_ID based on DataCardValue1.Text and the result is empty, Patch will add new record instead of update.
You can modify the second formula to:

Patch(Single_PP_Req_EU,
LookUp(Single_PP_Req_EU, DataCardValue1.Text in Unique_ID),
{Requestor_Name: DataCardValue38.Text})

Best Regards,

Levi

[Jawaharprem]

Hi, Bcz there is a box mentioned completed date, when i click submit button the current date and time should be update in complete box hence, it is possible in submit form()..

[Jawaharprem]

Hi Thanks, Iam Getting Delegation warning on bolded and under line item

Warning - "Delegation warning. The highlighted part of this formula might not work correct with column unique_id with large data set."

Patch(Single_PP_Req_EU,
LookUp(Single_PP_Req_EU, DataCardValue1.Text in Unique_ID),
{Requestor_Name: DataCardValue38.Text}) 

[v-liwei-msft]

Hi @Jawaharprem 

I think you can try this:

Patch(Single_PP_Req_EU,
LookUp(Single_PP_Req_EU, Concatenate(DataCardValue1,Left(User().FullName,4),"-",Text(Now(),"ddmmyy-hhmmss"))=Unique_ID),
{Requestor_Name: DataCardValue38.Text})

Best Regards,

Levi

[Jawaharprem]

Hi Thanks for your time, no errors but requestor name is not updated...

[poweractivate]

@Jawaharprem 

What's probably happening is that the second Patch will create a new Record with an empty Unique_ID when the match of an empty Unique_ID is not found, which might be happening somehow. You need to explicitly tell your app not to do this by rewriting your formula accordingly, especially the second Patch in particular. 

Rewrite the Patches to

1. 

Patch(Single_PP_Req_EU,{Unique_ID:Concatenate(DataCardValue1,Left(User().FullName,4),"-",Text(Now(),"ddmmyy-hhmmss")),Allocated_To: User().FullName,ASSIGN_DATE:Text(Now(),"mm/dd/hhhh hh:mm:ss")});

and

2.

With
(
   {_myRecord:LookUp(Single_PP_Req_EU, Unique_ID=DataCardValue1.Text)}
   ,If
   (
      !IsBlank(_myRecord)
      ,Patch
      (
          Single_PP_Req_EU
         ,{
             Unique_ID=_myRecord.Unique_ID
            ,Requestor_Name: DataCardValue38.Text
          }
      )
      ,Notify("No record found with ID=" & DataCardValue1.Text,Error)
      
   )
)

Does it work now and does it now stop the second record with no Unique_Id from being created on the 2nd Patch above especially? 

If not, check the below for an improved version - it may not work properly if you have a record with a Unique_ID
that is blank - you may need to delete that Record first and try again.

If the above works, you can improve the above further by doing this:

With
(
    {_myDCVOneText: DataCardValue1.Text}   
	If
	(   
	    IsBlank(_myDCVOneText)
	   ,Notify("DataCardValue1.Text cannot be blank",Error)	
	   ,With
		(
		   {_myRecord:LookUp(Single_PP_Req_EU, Unique_ID=_myDCVOneText)}
		   ,If
		   (
			  !IsBlank(_myRecord)
			  ,Patch
			  (
				  Single_PP_Req_EU
				 ,{
					 Unique_ID=_myRecord.Unique_ID //or just use _myDCVOneText instead of _myRecord.Unique_ID here
					,Requestor_Name: DataCardValue38.Text
				  }
			  )
			  ,Notify("No record found with ID=" & _myDCVOneText,Error)
			  
		   )
		 )
    )
)​

or if you prefer it,

With
(
    {_myDCVOneText: DataCardValue1.Text}   
	If
	(   
	    IsBlank(_myDCVOneText)
	   ,Notify("DataCardValue1.Text cannot be blank",Error)	
	   ,If
	   (
		  !IsBlank(LookUp(Single_PP_Req_EU, Unique_ID=_myDCVOneText))
		  ,Patch
		  (
			  Single_PP_Req_EU
			 ,{
				 Unique_ID=_myDCVOneText
				,Requestor_Name: DataCardValue38.Text
			  }
		  )
		  ,Notify("No record found with ID=" & _myDCVOneText,Error)
		  
	   )
		 
    )
)​

If the above works even better, you can try and find out why you are trying to send the Patch when DataCardValue1.Text could be Blank or have no value, and try to not do that anymore.

Even if you do find the root cause, the above version might help you the developer, and even an end user, understand why that happened and prevent it from happening - so the underlying cause can be ignored and left like that too if you want and if it's intended.

Even if you know the underlying cause, you still want to make sure you get no new Records created with an empty Unique_ID under any circumstances so you don't get that bizarre new record with an empty Unique_ID. You also may not want arbitrary records created with arbitrary ID's provided in DataCardValue1.Text based on what it seems like you're doing.

If you like the above, but don't want to see any notification messages, replace the Notify functions with either just false or Blank() depending on what property you are putting this in to make it not do anything on those If branches - usually it's probably false if it's, for example OnSelect

With
(
    {_myDCVOneText: DataCardValue1.Text}   
	If
	(   
	    IsBlank(_myDCVOneText)
	   ,false //Notify("DataCardValue1.Text cannot be blank",Error)	
	   ,If
	   (
		  !IsBlank(LookUp(Single_PP_Req_EU, Unique_ID=_myDCVOneText))
		  ,Patch
		  (
			  Single_PP_Req_EU
			 ,{
				 Unique_ID=_myDCVOneText
				,Requestor_Name: DataCardValue38.Text
			  }
		  )
		  ,false //Notify("No record found with ID=" & _myDCVOneText,Error)
		  
	   )
		 
    )
)​

Note that the above version may cause you not to understand why your Patch isn't working in some scenarios - change it back to the Notify functions if you want it to tell you why it is not patching it. once you  don't want it to tell you anything anymore, just change them back to false.

See if this works for you @Jawaharprem 

[Jawaharprem]

Hi, I worked on these but only first patch is working, for patch 2 lookup uniqueid should update, the other cards  and i have mentioned a unique_id card in powerapps form id is not listed there, updating in SP only hence the other card are unable to match by the unique id, i feel sorry to disturbing thanks..

[v-liwei-msft]

Hi @Jawaharprem 

Could you please tell me:
When you use

Patch(Single_PP_Req_EU, LookUp(Single_PP_Req_EU,DataCardValue1.Text in Unique_ID),{Requestor_Name: DataCardValue38.Text}),

was Requestor_Name successfully patched?

Best Regards,

Levi