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

[vsslasd]

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

[v-xida-msft · Accepted solution]

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

[vsslasd]

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.

[v-xida-msft]

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,

[vsslasd]

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?:

[vsslasd]

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 );

[vsslasd]

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