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

Error in Patch statement for date value

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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,

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

8 REPLIES 8
WarrenBelz
Super User
Super User

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.

it still not updated in db and showing date field value as invaliderror.png

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

v-xida-msft
Community Support
Community Support

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,

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.

It is a date field

table.JPG

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,

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.

I tried but it still not updating active field to 0

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,

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,383)