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

how to refer to the field/column name when you are trying to update it in a patch statement

I'm having an issue with my patch statement. I'm trying to see if a Timestamp has either a blank value or equals 1900. I'm not able to evaluate the "timestamp" column in the patch statement. I have a redline under Timestamp stating "Name is valid" ... I've tried disambiguation [@Timestamp] but that doesn't work either.

 


Any ideas?:

 

If(IsEmpty(Filter('[dbo].[PA_Approvals]',
Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID &&
EquipmentId=Equipment_Gallery.Selected.Equipment_ID &&
ApproveType="Equip Tasks"))=false,

Patch('[dbo].[PA_Approvals]', First(Filter('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")),
        {
            Service_Call_ID: ServiceCall_Gallery.Selected.Service_Call_ID,
            EquipmentId: Equipment_Gallery.Selected.Equipment_ID,
            PRM_Asset_Id: Equipment_Gallery.Selected.PRM_Asset_Id,
            ApproveType: "Equip Tasks",

            Timestamp: If(Timestamp=DateValue("1900-01-01","MM/DD/YYYY"), Now() ),
            UserId: Mid(Lower(User().Email), 1,(Find("@",User().Email)-1))
        }
));

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: how to refer to the field/column name when you are trying to update it in a patch statement

Hi @vsslasd ,

Do you want to check if the Timestamp column value of a specific record is blank or equals 1900?

Based on the formula that you provided, I think there is something issue with it. If you want to directly reference the Timestamp column value of the current record you want to update within the Patch formula, I afraid that there is no way to achieve your needs in PowerApps currently.

If you want to reference the Timestamp column value of the current record you want to update, please take a try to use the following formula:

First(
Filter('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")
).Timestamp

Or

LookUp(
'[dbo].[PA_Approvals]',
Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks"
).Timestamp

I have made a test on my side, please take a try to modify your formula as below:

If(
   IsEmpty(
           Filter('[dbo].[PA_Approvals]',
           Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId=Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")
   )=false,
   Patch('[dbo].[PA_Approvals]', First(Filter('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")),
        {
            Service_Call_ID: ServiceCall_Gallery.Selected.Service_Call_ID,
            EquipmentId: Equipment_Gallery.Selected.Equipment_ID,
            PRM_Asset_Id: Equipment_Gallery.Selected.PRM_Asset_Id,
            ApproveType: "Equip Tasks",
            Timestamp: If(
Text(
First(Filter('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")).Timestamp,
"[$-en-US]mm/dd/yyyy"
)=Text(DateValue("1990-01-01"),"[$-en-US]mm/dd/yyyy"), Now()), UserId: Mid(Lower(User().Email), 1,(Find("@",User().Email)-1)) } ) )

Or

If(
   IsEmpty(
           Filter('[dbo].[PA_Approvals]',
           Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId=Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")
   )=false,
   Patch(
'[dbo].[PA_Approvals]',
LookUp('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks").Timestamp, { Service_Call_ID: ServiceCall_Gallery.Selected.Service_Call_ID, EquipmentId: Equipment_Gallery.Selected.Equipment_ID, PRM_Asset_Id: Equipment_Gallery.Selected.PRM_Asset_Id, ApproveType: "Equip Tasks", Timestamp: If( Text( LookUp('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks").Timestamp, "[$-en-US]mm/dd/yyyy" )=Text(DateValue("1990-01-01"),"[$-en-US]mm/dd/yyyy"), Now()), UserId: Mid(Lower(User().Email), 1,(Find("@",User().Email)-1)) } ) )

Best regards

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: how to refer to the field/column name when you are trying to update it in a patch statement

Hi @vsslasd ,

Do you want to check if the Timestamp column value of a specific record is blank or equals 1900?

Based on the formula that you provided, I think there is something issue with it. If you want to directly reference the Timestamp column value of the current record you want to update within the Patch formula, I afraid that there is no way to achieve your needs in PowerApps currently.

If you want to reference the Timestamp column value of the current record you want to update, please take a try to use the following formula:

First(
Filter('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")
).Timestamp

Or

LookUp(
'[dbo].[PA_Approvals]',
Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks"
).Timestamp

I have made a test on my side, please take a try to modify your formula as below:

If(
   IsEmpty(
           Filter('[dbo].[PA_Approvals]',
           Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId=Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")
   )=false,
   Patch('[dbo].[PA_Approvals]', First(Filter('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")),
        {
            Service_Call_ID: ServiceCall_Gallery.Selected.Service_Call_ID,
            EquipmentId: Equipment_Gallery.Selected.Equipment_ID,
            PRM_Asset_Id: Equipment_Gallery.Selected.PRM_Asset_Id,
            ApproveType: "Equip Tasks",
            Timestamp: If(
Text(
First(Filter('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")).Timestamp,
"[$-en-US]mm/dd/yyyy"
)=Text(DateValue("1990-01-01"),"[$-en-US]mm/dd/yyyy"), Now()), UserId: Mid(Lower(User().Email), 1,(Find("@",User().Email)-1)) } ) )

Or

If(
   IsEmpty(
           Filter('[dbo].[PA_Approvals]',
           Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId=Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")
   )=false,
   Patch(
'[dbo].[PA_Approvals]',
LookUp('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks").Timestamp, { Service_Call_ID: ServiceCall_Gallery.Selected.Service_Call_ID, EquipmentId: Equipment_Gallery.Selected.Equipment_ID, PRM_Asset_Id: Equipment_Gallery.Selected.PRM_Asset_Id, ApproveType: "Equip Tasks", Timestamp: If( Text( LookUp('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks").Timestamp, "[$-en-US]mm/dd/yyyy" )=Text(DateValue("1990-01-01"),"[$-en-US]mm/dd/yyyy"), Now()), UserId: Mid(Lower(User().Email), 1,(Find("@",User().Email)-1)) } ) )

Best regards

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Advocate V
Advocate V

Re: how to refer to the field/column name when you are trying to update it in a patch statement

Thank you.

I think your solution will work, but I'm not 100% convinced ISEmpty really truly works. I have had to put a Refresh(TableName) before the IsEmpty command, and another one after the Patch Statements, and sometimes duplicate records are created.

 

I think the only work around is to use a stored procedure that will check first and do either an update or an insert.

 

And unfortunately, I have to use flow instead.

 

Highlighted
Community Support
Community Support

Re: how to refer to the field/column name when you are trying to update it in a patch statement

Hi @vsslasd ,

Have you solved your problem?

Based on the needs that you mentioned, I think the solution I provided above could achieve your needs.

In addition, I also think MS Flow could achieve your needs as an alternative solution.

 

If you have solved your problem, please go head to click "Accept as solution" to identify this thread has been solved for other users.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate V
Advocate V

Re: how to refer to the field/column name when you are trying to update it in a patch statement

Kris, 

I'm thinking it PowerApps needs to utilize more of the capabilities in the SQL Merge statement for patching Updating and Patching Inserts, it let's SQL Server handle the processing and decisions (exists or does not yet exist) where it can handle this more efficiently than PowerApps can. Do you agree?:

 

merge.png

Highlighted
Advocate V
Advocate V

Re: how to refer to the field/column name when you are trying to update it in a patch statement

This seems to provide me with 100% accurate results. The issue is that this is a stored procedure which  requires a Flow to run, which slows things down:

It takes about 12 seconds to process from the PowerApps Interface, which is too long, and yet it is a simple stored procedure. The stored procedure takes miliseconds to execute. Its Flow that slows it all down.


ALTER Procedure [dbo].[usp_PA_Approval]
@Service_Call_ID varchar(17)=Null,
@EquipmentId varchar(31)=Null,
@PRM_Asset_Id int=Null,
@ApproveType varchar(15)=Null,
@UserId varchar(15)=Null,
@Timestamp datetime=null

as

Begin


Merge dbo.PA_Approvals as A
Using (Values(@ApproveType,  @Service_Call_ID, @PRM_Asset_Id, @EquipmentId,@Timestamp,@UserId)) as s(ApproveType, Service_Call_Id, PRM_Asset_Id,EquipmentId,Timestamp,UserId) on
s.ApproveType=A.ApproveType and
s.Service_Call_Id=A.Service_Call_Id and
s.PRM_Asset_Id=A.PRM_Asset_Id
When Matched Then
Update Set
A.Timestamp=Case when A.Timestamp<=1901 then @Timestamp else convert(Datetime,'1900-01-01 00:00:00') end ,
A.EquipmentId=@EquipmentID,
A.UserId=@UserId
When Not Matched Then
Insert (ApproveType, Service_Call_Id, PRM_Asset_Id,EquipmentId,Timestamp,UserId)
values (s.ApproveType, s.Service_Call_Id, s.PRM_Asset_Id, s.EquipmentId, s.Timestamp, s.UserId );

 

Highlighted
Advocate V
Advocate V

Re: how to refer to the field/column name when you are trying to update it in a patch statement

Hi Kris,

There seems to be something wrong with the Lookup one:

 

If(
   IsEmpty(
           Filter('[dbo].[PA_Approvals]',
           Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId=Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks")
   )=false,
   Patch(
        '[dbo].[PA_Approvals]',
         LookUp('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks").Timestamp,
        {
            Service_Call_ID: ServiceCall_Gallery.Selected.Service_Call_ID,
            EquipmentId: Equipment_Gallery.Selected.Equipment_ID,
            PRM_Asset_Id: Equipment_Gallery.Selected.PRM_Asset_Id,
            ApproveType: "Equip Tasks",
            Timestamp: If(
                         Text(
                               LookUp('[dbo].[PA_Approvals]', Service_Call_ID=ServiceCall_Gallery.Selected.Service_Call_ID && EquipmentId =Equipment_Gallery.Selected.Equipment_ID && ApproveType="Equip Tasks").Timestamp,
                              "[$-en-US]mm/dd/yyyy"
                         )=Text(DateValue("1990-01-01"),"[$-en-US]mm/dd/yyyy"), Now()),
            UserId: Mid(Lower(User().Email), 1,(Find("@",User().Email)-1))
        }
    )
)

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,578)