cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

300 Required Fields in SQL. Make changes in SQL or Insert Fields on Powerapp?

I'm curious of what the community thinks is the best approach for the following scenario:

 

  1. SQL Table has 300 required fields
  2. There are only 15 fields that are relevant to powerapp.

Should changes be made to SQL table to allow null values or  should all of the fields be inserted into powerapp screen, and set 285 fields visible property to false?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User II
Dual Super User II

Re: 300 Required Fields in SQL. Make changes in SQL or Insert Fields on Powerapp?

Unless the SQL is on-premises, where stored procedures won't work with PowerApps, I would load just the fields you need to change into PowerApps and then use Flow to call a stored procedure with the 20 or so fields that need to be edited when writing back to SQL.  The stored procedure can pick up the values for the rest of the fields from SQL and leave them on the server.

 

But I tend to agree with @mdevaney , 300 required fields is too many.  Required fields should be driven by the business requirements, not because you want to guide the user's behavior.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

6 REPLIES 6
Highlighted
Super User
Super User

Re: 300 Required Fields in SQL. Make changes in SQL or Insert Fields on Powerapp?

@KUNGFUPANDA559
Having 300 required columns in a SQL table is insane. There is are too many possibilities for a record to fail at being inserted successfully. You should definitely allow the null values as much as possible.

Forgive my strong reaction but that’s how I truly feel!

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.
Highlighted
Helper II
Helper II

Re: 300 Required Fields in SQL. Make changes in SQL or Insert Fields on Powerapp?

@mdevaney  I share your concern.  The architecture of the database is the result of our software provider.  I'm a bit hesitant to begin altering the underlying table.  If that's what needs to be done so be it.

Highlighted
Dual Super User II
Dual Super User II

Re: 300 Required Fields in SQL. Make changes in SQL or Insert Fields on Powerapp?

Unless the SQL is on-premises, where stored procedures won't work with PowerApps, I would load just the fields you need to change into PowerApps and then use Flow to call a stored procedure with the 20 or so fields that need to be edited when writing back to SQL.  The stored procedure can pick up the values for the rest of the fields from SQL and leave them on the server.

 

But I tend to agree with @mdevaney , 300 required fields is too many.  Required fields should be driven by the business requirements, not because you want to guide the user's behavior.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Highlighted
Helper II
Helper II

Re: 300 Required Fields in SQL. Make changes in SQL or Insert Fields on Powerapp?

@Pstork1 That's a great idea (SQL is on-prem).  I hadn't thought to use a stored procedure to handle the heavy lifting. I appreciate your responses I'm going to mark this issue as solved.

Highlighted
Dual Super User II
Dual Super User II

Re: 300 Required Fields in SQL. Make changes in SQL or Insert Fields on Powerapp?

Unfortunately, the last time I looked there were limitations on the use of stored procedures with On-premises SQL.  So that may not be a solution.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Skilled Sharer
Skilled Sharer

Re: 300 Required Fields in SQL. Make changes in SQL or Insert Fields on Powerapp?

If you use SQL Profiler you will notice it does a few queries to make sure required fields are defined on page.  I doesn't seem to cache the result so it performs test on every INSERT/UPDATE.  I do make bit fields required.  I want it to be either 1 or 0.  I have several programs accessing table.  I want to avoid nulls.

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,390)