Error in Patch statement for date value

(0) Share

Report

Posted on by SumanKoduri

243

Hi all,

I'm trying to update a record in my table based on 2 fields

I'm facing problem in selecting data based on Tag id and date value. It's not taking date value

Patch( '[GLOBUS_APP].[T5010E_TAG_REQ_FAM]', LookUp( '[GLOBUS_APP].[T5010E_TAG_REQ_FAM]', C5010_TAG_ID = Gallery1.Selected.TAG_ID && C5010E_ACTIVE_FL = 1 && C5010E_LAST_TRANS_ID = Gallery1.Selected.C526_PRODUCT_REQUEST_DETAIL_ID && C5010E_PLACEMENT_DATE = Text(Gallery1.Selected.PLACEMENT_DATE,"[$-en-US]dd/mmm/yyyy") ), {C5010E_ACTIVE_FL: 0} );

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

Sort by

WarrenBelz 155,463 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @SumanKoduri ,
You are trying to compare a Text value with a Date value.
There are other patterns available, but the one I use is
Patch( '[GLOBUS_APP].[T5010E_TAG_REQ_FAM]', LookUp( '[GLOBUS_APP].[T5010E_TAG_REQ_FAM]', C5010_TAG_ID = Gallery1.Selected.TAG_ID && C5010E_ACTIVE_FL = 1 && C5010E_LAST_TRANS_ID = Gallery1.Selected.C526_PRODUCT_REQUEST_DETAIL_ID && DateDiff( C5010E_PLACEMENT_DATE, Gallery1.Selected.PLACEMENT_DATE )=0 ), {C5010E_ACTIVE_FL: 0} )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
SumanKoduri 243 on at

Like (0)

Report
Copy link

Link copied!

it still not updated in db and showing date field value as invalid

Was this reply helpful? Yes No
WarrenBelz 155,463 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@SumanKoduri ,
Blue lines are a Delegation error - nothing to do with validity of the code. Date filters are not delegable.
One workaround is to have a numeric field updated each time the date picker is changed set to
Value( Text( YourDateControlName.SelectedDate, "[$-en-US]yyyymmdd" ) )
then you can do a delegable search directly on this field
Value(Text(Gallery1.Selected.PLACEMENT_DATE,"[$-en-US]yyyymmdd")) = YourNumericFieldName

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @SumanKoduri ,

Could you please share a bit more about the "C5010E_PLACEMENT_DATE" field in your Table? Is it a Date time type column?

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

Patch( '[GLOBUS_APP].[T5010E_TAG_REQ_FAM]', LookUp( '[GLOBUS_APP].[T5010E_TAG_REQ_FAM]', C5010_TAG_ID = Gallery1.Selected.TAG_ID && C5010E_ACTIVE_FL = 1 && C5010E_LAST_TRANS_ID = Gallery1.Selected.C526_PRODUCT_REQUEST_DETAIL_ID && DateValue(Text(C5010E_PLACEMENT_DATE, DateTimeFormat.ShortDate)) = DateValue(Text(Gallery1.Selected.PLACEMENT_DATE, DateTimeFormat.ShortDate)) ), { C5010E_ACTIVE_FL: 0 } );

Please consider take a try with above formula within your app, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
SumanKoduri 243 on at

Like (0)

Report
Copy link

Link copied!

It is a date field

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @SumanKoduri ,

Yeah, Have you taken a try with the solution I provided above?

Based on the needs that you mentioned, I think the solution provided above could achieve your needs. Please take a try with it, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
SumanKoduri 243 on at

Like (0)

Report
Copy link

Link copied!

I tried but it still not updating active field to 0

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @SumanKoduri ,

Is the "C5010E_PLACEMENT_DATE" column a Date Time type column in your SQL Table?

Based on the issue that you mentioned, I think this issue may be related to Time Zone. If you create a Date Time type column in your SQL Table, when retrieving the date time value within PowerApps, the date time value may be rendered as a different date time value (due to Time Zone issue).

Actually, it is an known issue with date time field in SQL Table -- when using datetime (and friends) in SQL with PowerApps, then you may have the offset issues.

Please check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/working-with-datetime-values-in-sql/

Patch( '[GLOBUS_APP].[T5010E_TAG_REQ_FAM]', LookUp( '[GLOBUS_APP].[T5010E_TAG_REQ_FAM]', C5010_TAG_ID = Gallery1.Selected.TAG_ID && C5010E_ACTIVE_FL = 1 && C5010E_LAST_TRANS_ID = Gallery1.Selected.C526_PRODUCT_REQUEST_DETAIL_ID && DateValue( Text( DateAdd(C5010E_PLACEMENT_DATE, TimeZoneOffset(C5010E_PLACEMENT_DATE), Minutes), // Modify formula here DateTimeFormat.ShortDate ) ) = DateValue(Text(Gallery1.Selected.PLACEMENT_DATE, DateTimeFormat.ShortDate)) ), { C5010E_ACTIVE_FL: 0 } );

Please take a try with above formula, then check if the issue is solved.

You could also consider change the data type of the "C5010E_PLACEMENT_DATE" column in your SQL Table from Date time to datetimeoffset type, then you would not suffer from the Time Zone issue within your canvas app.

Best regards,

Was this reply helpful? Yes No
RatanKP 39 on at

Like (0)

Report
Copy link

Link copied!

Hi @SumanKoduri ,

Did you find any solution?? request to update the way you resolved as am also facing the similar kind of issue, I tried like below:
Patch(
'[GLOBUS_APP].[T5010E_TAG_REQ_FAM]',
LookUp(
'[GLOBUS_APP].[T5010E_TAG_REQ_FAM]',
C5010_TAG_ID = Gallery1.Selected.TAG_ID &&
C5010E_ACTIVE_FL = 1 &&
C5010E_LAST_TRANS_ID = Gallery1.Selected.C526_PRODUCT_REQUEST_DETAIL_ID &&
Text(DateValue(C5010E_PLACEMENT_DATE, "en-GB"),"yyyy-mm-dd","es-ES") =
Text(DateValue(Gallery1.Selected.PLACEMENT_DATE, "en-GB"),"yyyy-mm-dd","es-ES"))
),
{
C5010E_ACTIVE_FL: 0
}
);

My Actual Syntax as below:

1. Set(SDate,LookUp('MyTable','Task ID' = ThisItem.'Task ID' && Completed = 0,Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES"))); Result = 2023-01-22(as expected)

2. Set(STime,LookUp('MyTable','Task ID' = ThisItem.'Task ID' && Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES") = SDate && Completed = 0,'Start Time')); Result = 2023-01-22 13:09:59(As expected)
3. Patch('MyTable',LookUp('MyTable','Task ID'= 'Task ID' && Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES") = SDate && Text(DateTimeValue(Value('Start Time'),"yyyy-mm-dd HH:MM:SS")) = Text(DateTimeValue(Value(STime),"yyyy-mm-dd HH:MM:SS")) && Completed = 0),
{
'End Time':Now(),
Completed:1
}
); Result = No Lookup Data Found and am unable to Modify the SQL Data.
Note : As the Task ID, Date and Start Time are the primary Keys in SQL Table

4. Set(UpdateVar,LookUp('MyTable','Task ID'= 'Task ID' && Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES") = SDate && Text(DateTimeValue(Value('Start Time'),"yyyy-mm-dd HH:MM:SS")) = Text(DateTimeValue(Value(STime),"yyyy-mm-dd HH:MM:SS")) && Completed = 0,'End Time'));
Result = 2023-01-22 15:09(As expected).
Here in Step 4 (Set function) the same Lookup logic is working fine and in Patch function Step 3, the LookUp logic is not working, any suggestion Please.

Was this reply helpful? Yes No