web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : ft3TKki9cR+QzQNAYTmxvR
Power Apps - Building Power Apps
Unanswered

Leading zero dropped when patching to SQL table

Like (0) ShareShare
ReportReport
Posted on 13 Oct 2023 18:27:40 by 22

Hello all,

 

I am currently experiencing an issue when patching 3 digit location codes to a SQL table. Some of these location codes start with a zero, but when patched to the SQL table, the zero is dropped. (Example, I patch "037" but SQL always receives "37". I don't believe that it is an issue with the table, as I can manually create a record that has a location ID with a leading zero.

 

I have tried patching as a number and as a string, but receive the same result. Below is the formula we have on the submit button.

 

//Create Collection
ClearCollect(
UpdateLocations,
[{LocationCode: 117,
Checkbox: 'Location 1 CRC checkbox'},
{LocationCode: 167,
Checkbox: 'Location 2 CRC checkbox'},
{LocationCode: 047,
Checkbox: 'Location 3 CRC checkbox'},
{LocationCode: 187,
Checkbox: 'Location 4 checkbox'},
{LocationCode: 157,
Checkbox: 'Location 5 CRC checkbox'},
{LocationCode: 147,
Checkbox: 'Location 6 checkbox'},
{LocationCode: "037",
Checkbox: 'Location 7 checkbox'},
{LocationCode: 623,
Checkbox: 'Location 8 checkbox'},
{LocationCode: 607,
Checkbox: 'Location 9 checkbox'},
{LocationCode: 297,
Checkbox: 'Location 9 checkbox'},]

);
//If checkbox is true and there is not an active record in GPCLocationAssigned SQL table, create an active record for GPC
ForAll(
UpdateLocations,
If(
Checkbox.Value = true && IsBlank(
LookUp(
GPCLocationAssigned,
GPCEmployeeID = Gallery1_1.Selected.Badge && LocationID = LocationCode && Active = true

)),
Patch(
GPCLocationAssigned,
{
GPCEmployeeID: Gallery1_1.Selected.Badge,
LocationID: Text(LocationCode),
ManualStartWhen: Now(),
ManualEndWhen: 0,
RecordCreatedBy: CurrentUserID.Text,
RecordCreatedWhen: Now(),
RecordModifiedBy: "",
RecordModifiedWhen: 0,
RecordDeleted: 0,
Manual: 1,
Active: 1}),


//If checkbox value is false and there IS an active record, change Active to 0 (false)
Checkbox.Value = false && !IsBlank(
LookUp(
GPCLocationAssigned,
GPCEmployeeID = Gallery1_1.Selected.Badge && LocationID = LocationCode && Active = true
)),
Patch(GPCLocationAssigned,
LookUp(
GPCLocationAssigned,
GPCEmployeeID = Gallery1_1.Selected.Badge && LocationID = LocationCode && Active = true),

{Active: 0,
ManualEndWhen: Now(),
RecordModifiedWhen: Now()
})));

 

  • cwebb365 Profile Picture
    3,294 Most Valuable Professional on 13 Nov 2023 at 18:20:02
    Re: Leading zero dropped when patching to SQL table

    Any reason why you wouldn't store it as the raw int value and just display the correct format on your front ends? 

  • NMatGWM2 Profile Picture
    22 on 13 Oct 2023 at 20:01:46
    Re: Leading zero dropped when patching to SQL table

    Sorry, I should have mentioned that. The datatype of LocationID is currently nvarchar

  • ANB Profile Picture
    7,162 Super User 2025 Season 2 on 13 Oct 2023 at 19:39:50
    Re: Leading zero dropped when patching to SQL table

    Hi @NMatGWM2 , What is the datatype of LocationCode in SQL? Is it integer? If yes, do you have a requirement to keep as integer? Can you convert the datatype of LocationCode to varchar and patch the data as string from PowerApps and check the behavior.

     

    -----------------------------------------------------------------------------------------------------------------------------

    I hope this helps.

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

    Thanks,
    ANB


     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Telen Wang – Community Spotlight

We are honored to recognize Telen Wang as our August 2025 Community…

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 637 Most Valuable Professional

#2
stampcoin Profile Picture

stampcoin 570 Super User 2025 Season 2

#3
Power Apps 1919 Profile Picture

Power Apps 1919 473