cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Default in a dropdown in an SQL Form

I have been doing this in SP lists for a long time with no issue,  but now I have ventured to SQL.

 

I have a Form based on a table.  One of the fields I put a dropdown inside with hard coded items as there are only three items.  So ["Items1", "Item2", "Item3"]

 

No problems with this until I want to set the default to match the data card value which might be anyone of those three. In SP I would just set the Default of the dropdown to Parent.Default.  This does not work.

 

I have tried all the various solutions on this site but nothing works.  Most solutions are based on a lookup() but I cannot get them to work.

 

Any Ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Default in a dropdown in an SQL Form

Hi @leyburn19 

What you have there should work.

After you submit your form, can you confirm that the form correctly saves the value into the database table?

Also, can you confirm the data type of your column?

If for example the data type is char(10) and you save the form value "Item 2", SQL Server will store this as "Item 2    " (ie, with trailing spaces).

When you retrieve this record, the dropdown won't display "Item 2" as you would expect, because "Item 2" != "Item 2    "

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: Default in a dropdown in an SQL Form

@leyburn19 

You said: "In SP I would just set the Default of the dropdown to Parent.Default.  This does not work."  What do you mean by does not work?  Do you get an error?  Does nothing appear?

 

Most importantly, what code is in the the Default property of the parent card?

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Super User
Super User

Re: Default in a dropdown in an SQL Form

It simply does nothing.  The Default Property of the data Card is Parent.Default.

 

Looking at some of the messages in the forum,  it does seem to be a special need for SQL,  but even those answers I cannot get to work.

 

In SP I would have a simple text data card (which is what I have here in the SQL) .  I add a dropdown to that datacard and populate items with the simple answers I want in the text field. I make the default of this dropdown to be the Parent.Default and I make the default of the actual datafield to equal the dropdown. If the dropdown changes it changes etc. Very simple and clean

 

In SQL I cannot get this to work

Highlighted
Super User III
Super User III

Re: Default in a dropdown in an SQL Form

Hi @leyburn19 

What you have there should work.

After you submit your form, can you confirm that the form correctly saves the value into the database table?

Also, can you confirm the data type of your column?

If for example the data type is char(10) and you save the form value "Item 2", SQL Server will store this as "Item 2    " (ie, with trailing spaces).

When you retrieve this record, the dropdown won't display "Item 2" as you would expect, because "Item 2" != "Item 2    "

View solution in original post

Highlighted
Skilled Sharer
Skilled Sharer

Re: Default in a dropdown in an SQL Form

I view SQL Server as  a robust solution for mainstream apps.  For better long term sustainability I recommend using a lookup table.  For example if you need to add items to list or change wording in list.  For example:

 

CREATE TABLE dbo.CompanySector(
  CompanySectorID int NOT NULL IDENTITY PRIMARY KEY,
  CompanySector varchar(80) NOT NULL,
  RowOrder int NOT NULL,
GO

 

Create a reference on table where it is used:

 

ALTER TABLE dbo.Client
  ADD CONSTRAINT FK_Client_CompanySector_CompanySectorID
  FOREIGN KEY
    (CompanySector)
  REFERENCES
    dbo.CompanySector (CompanySectorID)
GO

 

Since the lookup rarely changes create a collection for lookup table in OnStart.  It improves performance.  If you are using SQL Server on-premise I recommend using SQL Profiler to see how PowerApps interacts with database. 

 

ClearCollect(
    colCompanySector,
    '[dbo].[CompanySector]'
);

 

On the Edit screen on DropDown for items use the collection.  For Default use this:

LookUp(colCompanySector,CompanySectorID = Parent.Default).CompanySector

 

I rename the dropdown to CompanySectorDD.  The Default field called CompanySector in Client table is set to:

 

CompanySectorDD.SelectedText.CompanySectorID

 

For the details screen create a view so you see the name of the sector rather than a number.  Views are very powerful. 

Highlighted
Super User
Super User

Re: Default in a dropdown in an SQL Form

You are absolutely correct - thank you.  I put Trim() around parent.default and it suddenly works.

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,671)