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()
})));
Any reason why you wouldn't store it as the raw int value and just display the correct format on your front ends?
Sorry, I should have mentioned that. The datatype of LocationID is currently nvarchar
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
WarrenBelz
637
Most Valuable Professional
stampcoin
570
Super User 2025 Season 2
Power Apps 1919
473