cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AedenMaddok
Regular Visitor

SubmitForm function does not seem to be working with SQL.

Hello, I'm rather new to using Power Apps.  It has many great features and some not so great features.

 

I'm trying to build a simple app for CRUD (Create, Read, Update, Delete) operations in a locally-hosted SQL table.  The table consists of 5 fields:

  • ID - INTEGER (IDENTITY)
  • SSN - VARCHAR (25)
  • SendVerificationTo - VARCHAR (50)
  • DateSubmitted - DATETIME
  • DateProcessed - DATETIME

When inserting a new record, a user should only have to supply the data for the SSN field and the SendVerificationTo field as the ID and DateSubmitted should obtain appropriate values by default.

 

My app consists of a main screen for viewing the data in a gallery, a details screen for viewing the details of the recor, an editing screen to edit the data in a record, and a screen to add a record to the table.  I know that best practice is to perform all of these operations in one or maybe two screens, but this is my first power app and I want to ensure that I understand how all of the objects and controls work before combining them onto one form.

 

When creating a new edit form, I noticed that the fields I added to the edit form consist of two labels; I've watched many tutorial videos and never saw this behavior when creating a new edit form.  Is this behavior something unique to using a SQL data source?  I don't think you can edit the value of a label.  I considered this a minor setback and simply added a text field to each data card.  Another problem that only seems to happen with a SQL data source is that the form cards default to DisplayMode.View and I have to change them to follow the DisplayMode of the parent object.

 

I have got the data source connected created my pages complete with icons and buttons.  The main page and the details page are working great.

 

The edit page is able to display the data; however, when I edit the data and save it the data does not get updated and simply reverts back to what it was despite the form being submitted successfully.  The Edit Before and After image shows that I'm getting the form submitted successfully, but it doesn't update the data no matter which field I try to change.

 

The page to add the data is not working at all and I'm not able to get the form to submit successfully.  See the image with the error notification.  The App Checker doesn't seem to be able to give me any insight to my problems.  I'm trying to use the Power Apps Monitor to troubleshoot, but I don't have enough experience to know what to look for here.  Any help would be appreciated.

 

I've been banging my head against this long enough and I need help.

1 ACCEPTED SOLUTION

Accepted Solutions
kylzbaba
Resolver I
Resolver I

8 REPLIES 8
365-Assist
Power Participant
Power Participant

Can you please share the PowerFX code in the Save Button? I assume there is  a Patch Function.

I'm not exactly sure what you mean by "PowerFX code". This is all of the code I have in the OnSelect property:

 

Edit Form:

    SubmitForm (EditForm);

 

Add Form:

    SubmitForm (AddForm);

 

If that's not what you were looking for, please let me know.

kylzbaba
Resolver I
Resolver I

Sql can be a bit frustrating. I started using it recently myself. The first set of problems are usually with how you set up the tables.

 

Is there a primary key ?

There is not a primary key setup on this table.  Here is the code that was used to build the table:

CREATE TABLE [dbo].[Payroll_WageVerification] (
    [ID] BIGINT NOT NULL
        IDENTITY (1,1),
    [DateSubmitted] DATETIME NOT NULL
        CONSTRAINT [DF__Payroll_W__DateS__78BEDCC2] DEFAULT (getdate()),
    [SSN] VARCHAR(25),
    [SendVerificationTo] VARCHAR(50),
    [DateProcessed] DATETIME,
)

 

kylzbaba
Resolver I
Resolver I

Okay one of the rules for connecting sql tables to powerapps, the table must have a primary key. This may be the reason for current behavior. 

kylzbaba
Resolver I
Resolver I

https://youtu.be/CO6xfbjnYwc

Check out this video by Shane Young

AedenMaddok
Regular Visitor

Bingo!  That's what the problem was.  I didn't have a primary key setup on the table.  For those of you who stumble upon this problem, you can see the solution on Shane Young's video linked in the answer.  I recommend jumping to 21:28 on the video for the section that solved my problem.

 

My code to add the primary key:

ALTER TABLE Payroll_WageVerification
    ADD CONSTRAINT [PK_Payroll_WageVerification_ID] PRIMARY KEY CLUSTERED (ID)
kylzbaba
Resolver I
Resolver I

Nice, glad Shane and I could be of help 😃 

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (2,720)