All,
I have an approval process built in a PowerApp that uses the Patch() formula to add and update some values in an Excel sheet that is connected to my App via OneDrive for Business.
The app previously used a Flow (or Automate) process to update the rows in Excel, but that locks out the excel sheet for up to 6 minutes, making my app throw an error for 6 minutes and essentially render it useless while the file is locked because it cannot update the data in the App. So my thought was to switch over to a Patch() function. However, what this is doing is changing my assigned Column Types in Excel from either Text, Number, or Short Date, to General, which is annoying and breaking my app and it makes it outrageously hard to manage the data and read and write to the source.
This Patch() below is my initial addition of the value to the sheet.
Set(
RegId,
Value(First(Sort(Cal,RegistrationID,Descending)).RegistrationID)+Value(1)
)
;
Patch(
Cal,
{RegistrationID: Value(RegId),
InUse: 0,
'Vehicle ID': ItemSelected.'Vehicle ID',
StartDate: 'H.StartDate.RequestForm.Value'.SelectedDate,
EndDate: 'H.EndDate.RequestForm.Value'.SelectedDate,
Group_Project: 'H.Group/Project.RequestForm.Value'.Text,
POC: 'H.POC.RequestForm.Value'.Text,
Approval: 0}
);
Patch(Comm,
{RegistrationID_Comm: RegId,
TeamsThreadID: Value(MessageID)}
);
ResetForm('H.RequestVehicle.Form');
Set(
RequestForm,
false
);
Refresh(Cal);
Refresh(Comm)
And here is my "Approval" button
If(
IsBlankOrError('AS.ApproverComments'.HtmlText),
( MicrosoftTeams.PostReplyToMessageV2(
"Text",
"Text",
LookUp(
Comm,
Value(NeedsApproval.RegistrationID) = Value(RegistrationID_Comm),
TeamsThreadID
),
{
content: "<p>Request Approved.</p>",
contentType: "html"
}
)),
);
Patch(
Cal,
Filter(
Cal,
Value(RegistrationID) = Value(NeedsApproval.RegistrationID)
)
,
{Approval: Value(1)}
);
Refresh(Cal)
After doing this, I go to my Excel sheet and it has changed all my types to General. Is there a way to keep the Patch function from doing this or is it an Excel problem?
My Data source example is below:
Table: Cal
| Registration ID | Vehicle ID | Start Date | End Date | POC | Project | Approval | Powerapps |
| Integer | Text | Short Date | Short Date | Text | Text | Integer | Gibberish |
Table: Comm
| Registration ID | Teams Thread ID | Powerapps |
| Integer | Text | Gibberish |
Thanks for any feedback.
Note some code and Table Headers have been modified for easy readability.
