Combo Box - 'Result' Field Workaround

[Johnnyboy1]

Issue

Combo box 'Depend on' function only allows for 'Result' when a formula is used on the item formula box.

Workings

Image 1 shows my sql view table which is linked to the app

Image 2 shows my first in the hierarchy combo box, with a distinct formula.

Image 3 shows my next combo box, sorted and distinct

Question

I would like for each combo box to be dependant on the previous and filter out only values with row id's from the first combo box (plant area, then the same for area to item). The issue is that you can only depend on the 'Result', as the result is 2 column values place together it cannot depend on any column value.

WorkAround

Is it possible to use a concatenatex and distinct formula together so that the result of the combo box is the same and can depend.

Distinct('[dbo].[vwPlantItemView]', AreaDescription & "-" & PlantArea)

If that is the case can you please assist with the formula as I cannot get it to work, the above is what I have so far.

The other option is to add 2 x columns to the view table with the concatenated values, but I believe you should be able to do it in the app.

Cheers

[Drrickryp]

Hi @Johnnyboy1 

I think that the solution to your current issue might be more easily resolved if you normalize your table. https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-engli... .  Once you have broken up your table and established the relationships between the resulting tables, the solution becomes obvious.  For more information about database design and PowerApps please see my series that starts with  https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An... 

[v-yutliu-msft]

Hi @Johnnyboy1 ,

Do you want to use Distinct function inside Sort function?

Could you tell me the fields' data type in your pic3?Equip Type and TypeDescription?

I've made a similar test not met any problems.

I use this formula in my combo box's Items:

Sort(Distinct(list3,Title&"-"&'Catalog Item'),Result)

list3 is my data source name, Title and Catalong Item is both text type.

So I think your problem should be about data type.

Please notice whether these two fields' data type are supported in powerapps.

Power Apps data type mappings

TABLE 4

SQL Server	Power Apps
bigint, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real	Number
char, nchar, varchar, nvarchar, text, ntext	Text
bit	Boolean
date, datetime/datetime2/smalldatetime, datetimeoffset, time	DateTime
uniqueidentifier	Guid

 Note

If a SQL Server data type is not listed in the table above, it is not supported. Examples of unsupported SQL Server data types include: binary(), varbinary(), image, cursor, rowversion, hierarchyid, sql_variant xml, Spatial Geometry Types, Spatial Geography Types, and table.

Here's a doc about sql data type in powerapps for your reference:

https://docs.microsoft.com/en-us/connectors/sql/

Best regards,

[Johnnyboy1]

Everything is nvarchar. Including the PlantItemView table.

See attachment A of sql data types.

It needs to be distinct as the column contains multiple values of the same plant area.

See attachment D for my PlantItemView table which is connected to the app

Combo box 1 (top hierarchy) Plant Item:

Sort(Distinct('[dbo].[vwPlantItemView]', TypeDescription & "-" & EquipType), Result)

Combo box 2 EquipType:

Sort(Distinct('[dbo].[vwPlantItemView]', TypeDescription & "-" & EquipType), Result)

Combo box 3 would be the plant items filtered by the 2 combo boxes above

I guess my question is do you think it is a formula error, or could it be that the first combo box result does not match any field within the view table?

[v-yutliu-msft · Accepted solution]

Hi @Johnnyboy1 ,

Do you mean that the Sort(Distinct(...)..) function could only sort based on Result column?

Do you want these feathers?

1)combo box1 get non-repeated TypeDescription & "-" & EquipType value

2)combo box2 filter based on selection in combo box1 and get non-repeated AreaDescription & "-" & PlantArea value

3)combo box3 filter based on selection in combo box1 and combo box2

If so, firstly let me explain why you met this problem:

The result that Distinct function returns is a table with only one column: Result.

So then you could only sort based on this column and could only get result column value.

To filter based on first two combo boxes, you need to get id value from these two results.

Try this:

1)combo box1's Items:

Sort(Distinct('[dbo].[vwPlantItemView]', TypeDescription & "-" & EquipType), Result)

2)combo box2's Items:

Sort(
   Distinct(
     Filter(
         AddColumns('[dbo].[vwPlantItemView]',
                    "T-E",TypeDescription & "-" & EquipType
                    ),
         T-E in ComboBox1.SelectedItems.Result
             ),
            AreaDescription & "-" & PlantArea
            ), 
Result)

3)combo box3's Items:

Filter(
AddColumns('[dbo].[vwPlantItemView]',
"T-E",TypeDescription & "-" & EquipType,
"A-P",AreaDescription & "-" & PlantArea
           ),
T-E in ComboBox1.SelectedItems.Result,
A-P in ComboBox2.SelectedItems.Result
)

Since the concatenatex value does not exist in the original table, you need to use AddColumns function to add columns with the  concatenatex value and then filter based on these new columns.

Best regards,

[Johnnyboy1]

Thanks you Phoebe,

Worked like a charm!

I had assumed that it had no lookup value from the table and the addcolumn part was missing.

I have one more Query.

I want the 3rd combo box to show all items, and only filter 'when selected'. ie if I select the last combo box and nothing is selected in the 2 above, I would still like it to show the options, and only filter on selection of the combo boxes?

I assume we need to add a IF and ISBLANK for the combo box filter?

[v-yutliu-msft · Accepted solution]

Hi @Johnnyboy1 ,

Yes, you need to add a justification.

Try this to set combo box3's Items:

Filter(
AddColumns('[dbo].[vwPlantItemView]',
"T-E",TypeDescription & "-" & EquipType,
"A-P",AreaDescription & "-" & PlantArea
           ),
If(IsEmpty(ComboBox1.SelectedItems),true,T-E in ComboBox1.SelectedItems.Result),
If(IsEmpty(ComboBox2.SelectedItems),true,A-P in ComboBox2.SelectedItems.Result)
)

Best regards,

[Johnnyboy1]

Once again Phoebe, thanks.

I am having one more issue.

I have managed to get the 3 drop down boxes to form my plant code (columns Area, Equip, Item), eg 221-BI-003, which concatenated with a dash form the primary key it will link to in sql (plantItem is in the view table the app is working off).

I can get this in a card or text box within the form

My question is when adding a new data card to the form I would like it to produce the plant item from the 3rd drop down box and display the PlantItem (in any visual suitable) and write that back to the blank column PlantItem in sql to the table dbo.events. My other parts of the form are set up already to write to this table.

For some reason as seen in the image uploaded when I add a data card into the form it doesn't give me the update option, it is a new.form. The data card is set to display.

I would also then like to have one more drop down referencing a fact table for users to select then write to the same table, column Operator.

For this one do I have to create a new from and link the submit from button to both forms to have multiple connections to tables?

Cheers