Small and medium business | Business Central, N...

"Unable to Perform CRUD Operations on Business Central SQL Server via Power Apps"

(7) Share

Report

Posted on by SV-30061135-0

📝 Question (Body):

Hello everyone,

I've connected my Business Central SQL Server database to Power Apps using an on-premises data gateway. I then built a canvas app using one of the tables (Job table).

However, I'm unable to perform CRUD operations (especially create or update). When I try to submit the form using SubmitForm(Form2), I get the following error:

Field '_x0024_systemCreatedBy' is required.
Field '_x0024_systemCreatedAt' is required.

I understand that fields like SystemCreatedAt and SystemCreatedBy are system fields, possibly auto-managed in Business Central. I tried manually assigning values to them in Power Apps (e.g., using Now() for SystemCreatedAt and the current user's email for SystemCreatedBy), but the error still persists.

❓ My Questions:

Are there standard ways to handle system fields like SystemCreatedBy or SystemCreatedAt in Power Apps when connected to a Business Central SQL Server table?
Is there a way to auto-populate or bypass these fields, since they’re typically handled internally in Business Central?
Has anyone faced similar issues with CRUD operations through SQL Server in Power Apps with system fields?

💡 Additional Info:

Power Apps is connected using a SQL Server gateway.
Table comes from Business Central On-Prem SQL Server.
Error occurs during form submission (insert or update).
App auto-generated the form from the data source.
I have already tried assigning values to the system fields programmatically in Power Apps.

📸 I've also attached an image showing the error clearly.

Any guidance or workaround would be highly appreciated. Thank you!

Categories:

Dynamics 365 Business Central

image.png

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Sohail Ahmed 11,136 Super User 2025 Season 2 on at

Like (2)

Report

adding more details

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/sample-ve-provider-crud-operations

✅ Mark this answer as verified if it helps you.

Was this reply helpful? Yes No
Suggested answer

Steven Renders 5,672 Moderator on at

Like (2)

Report

NEVER do CRUD operations directly in the BC Sql database! Please use the APIs instead.

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,331 Super User 2025 Season 2 on at

Like (2)

Report

These are audit fields and cannot be entered manually or generated through a configuration package.
Use OData or APIs instead of accessing them directly from the database.

Table System fields - Business Central | Microsoft Learn

Hope this helps as well.

Thanks.

ZHU

Was this reply helpful? Yes No
Suggested answer

Jeffrey Bulanadi 8,760 on at

Like (2)

Report
Hi,

The error you're encountering is tied to how Power Apps interacts with SQL Server tables that include system-managed fields like SystemCreatedBy and SystemCreatedAt. These fields are typically auto-populated by BC or the SQL backend, and Power Apps doesn’t always handle them gracefully when using direct SQL connections.

Here’s what’s happening:

When Power Apps tries to insert or update a record, it expects all non-nullable fields to be supplied — even system fields.

Fields like _x0024_systemCreatedBy and _x0024_systemCreatedAt are encoded versions of SystemCreatedBy and SystemCreatedAt, and they’re likely marked as NOT NULL in the SQL schema.

Since Power Apps doesn’t auto-populate these fields, and they’re required, the insert fails unless you manually supply values — which may not be valid or accepted by BC’s internal logic.

To resolve this:

Option 1: Modify the SQL Table Schema

If possible, alter the table to make these system fields nullable or set default values (e.g., GETDATE() for SystemCreatedAt). This allows inserts without explicitly supplying them.

Option 2: Use a Stored Procedure or View

Instead of inserting directly into the table, create a SQL view or stored procedure that handles the insert logic and auto-populates system fields. Then connect Power Apps to that object.

Option 3: Use Virtual Tables or Dataverse

If you’re open to a more structured integration, consider using Dataverse virtual tables or custom connectors that abstract away system field logic. This avoids direct SQL interaction and aligns better with BC’s metadata model.

Option 4: Use Power Automate as a Bridge

Trigger a flow from Power Apps that calls a Power Automate HTTP request to BC’s API or a custom endpoint. This lets BC handle the insert and populate system fields internally.

Helpful References:
Create PowerApps Application with On-premises SQL Server
Troubleshooting SQL Server Connection Problems – Microsoft Learn
Custom Virtual Table Provider with CRUD Operations – Microsoft Docs
Access BC Data via Power Automate HTTP – Dynamics 365 Lab
Community Thread – CRUD via SQL Gateway

If you find this helpful, feel free to mark this as the suggested or verified answer.

Cheers
Jeffrey

Was this reply helpful? Yes No
Gerardo Rentería Ga... 25,213 Most Valuable Professional on at

Like (1)

Report

Hi, good day
I hope this can help you, and give you some hints.

Troubleshooting: SQL Server Connection Problems - Business Central | Microsoft Learn

Exclude system fields, and always use api or ws.

Best Regards
Gerardo

Was this reply helpful? Yes No