You’re offline. This is a read only version of the page.
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()
})));
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
#1
WarrenBelz
637
Most Valuable Professional
#2
stampcoin
570
Super User 2025 Season 2
#3
Power Apps 1919
473
Share
Report
All responses (
Answers (
