Still struggling with having two fields that "relate" to each other.

[ShowMeTheAnswer]

So I have two fields.

Application and Process Step.

There is 12 Applications. They are contained in an SQL Table, it looks like this:

APP_ID APPLICATION

----------- ----------------

29 Assembly

20 BIW

There is another table containing 145 "PROCESS_STEPS".  It looks like this:

PROC_STEP_ID PROCESS_STEP_DESC APPLICATION_ID

------------                                         -------------------                                                   --------------

 142                             Swindon Pressing 25

Applications are related to Process Step by the "APPLICATION_ID" field.

I need both fields in the App. The Application field is fine.
The process step field doesn't work.
I've tried this in the "OnSelect" field, and also on the OnChange field for the APPLICATION:

ClearCollect(tempCollection,{PROCESS_STEP_DESC:""},Filter('[dbo].[PROCESS_STEP]', Dropdown6.Selected.APP_ID= APPLICATION_ID))

And then I put the resulting "tempCollection" in the items field for the Process_Step.

Result: Items in the PROCESS_STEP ARE correctly filtered, including correctly having a BLANK first selection.

Unexpected results:
Although the records are actually saved correctly back to the SQL tables, when you go back to the record, the dropdown appears blank.

An alternative approach of just putting the PROCESS_STEP table in the items field, means that the results are correctly displayed. BUT - then the list is of ALL the PROCESS STEPS, and NOT just the one for the value selected in the APPLICATION field.

Any ideas, I'll try and add a screen shot as well.

[tommyly]

I've found that collecting (from a data source) on the fly can be finnicky, because it collects at the mercy of the connetion to your datasource. And since you are executing it on the fly, your user is expecting instant results, so in your example, ClearCollecting on a dropdown's OnSelect event may have unpredictable results. If you are intent on doing this, you may want to initially ClearCollect your [dbo].[PROCESS_STEP] and [dbo].[MAIN] data sources into their own raw collections first:

ClearCollect(rawMain, [dbo].[MAIN]);
ClearCollect(rawProcess, [dbo].[PROCESS_STEP]

And then your future references to the [dbo].[PROCESS_STEP] (for example), would reference rawProcess instead. Of course, you'd still patch to your main data source (SQL table).

It seems like the only reason you are creating a collection is to add the blank spot (which has no functional value but is there as a design/aesthetic value). If you don't mind, you could use a single-select comboBox. Instead of using a blank spot, the comboBox will simply show nothing selected when... nothing is selected. If you do this, you could link to your data source and it would always have the most recent information (since the last refresh).

[PaulD1]

Not sure I fully follow all of the details, but given you are using SQL Server as your datasource, can you not create a View which performs the necessary data manipulation and returns a result set ready for display?

I prefer to do any 'complex' data manipulation in a View and just connect to that in PowerApps. It is generally easier and quicker (development and performance) to manipulate the data in SQL.

This only issue is that if you update the underlying tables you need to remember to refresh the View (views are not updatable via PowerApps and modifying data in the underlying table does not cause the View to automatically refresh - though there are times you can use this behaviour to your advantage!).

[ShowMeTheAnswer]

Yes, I did consider a view, and I'm already using one successfully in other parts of the application.

But what SQL to write for the view - how can I say in SQL

"Show me only the Process Steps for the Application the user has just selected in the PowerApps application"? I considered the "Group By" function, but can't really "Group By" a value the user has just selected?

[ShowMeTheAnswer]

I can try the other type of field you reccomend.

I'm not sure what the point of the "Raw" collection is; especially if it's just the table itself, I can just refer to the table for that.

To me the first value of the PickList does need to be blank; its just what normally happens (for example on just about every website..)

[ShowMeTheAnswer]

The ComboBox does seem to have a blank first entry so thanks for that.

Based on your remark, I moved the ClearCollect out of the field, and into the "OnVisible" event of the form.

The results then seem a little better....

Mostly it displays it OK. It's just sometimes there is a little delay before the field is correctly set (If that delay wasn't there I'd simply mark your answer as the solution).

For the moment I'll likely leave it like that; but I want to keep this question open for the moment in case anyone else has a suggestion.

[tommyly]

If you have lists that don't change very often (but are filtered, especially non-delegable filters), you can pull the entire contents of a table into memory by creating a raw collection. For example, certain syntax used on a SQL data source might yield a delegation warning, but if you first pull it into a raw collection (saved in memory), then you avoid the delegation warning. The only drawback is if the amount of data in the table is immensely large.

I think you are on the right path with creating your collections in the OnVisible event. It sounds like you are trying to create cascading lists. Here is an example:

Scenario 1:

dropDown1.Item =
  SortByColumns([dbo].[Country],
    CountryName, Ascending
  )


dropDown1.OnChange =
  ClearCollect(collectionCities,
    Filter([dbo].[Cities], countryID=dropDown1.Selected.ID)
  );
  Collect(collectionCities, Table({CityName: Blank()})


dropDown2.Default = ""
dropDown2.Item =
  SortByColumns(
    SortByColumns(collectionCities,
      CityName, Ascending
    ),
    CityName, [""]
  )

This seems to be similar to what you are doing. Your independent list [Country] is a straight reference to the table and your dependent list [City] is a dynamic collection created via ClearCollect. And then your dependent dropDown (dropDown2) is assigned to the collection. The only inefficiency with this is that everytime you change the Country, it invokes a fetch or network transaction to your SQL table to get the data (based on my undersanding anyway).

Scenario 2:

screen.OnVisible =
  ClearCollect(collectionCountry,
    [dbo].[Country]
  );

  ClearCollect(collectionCities,
    [dbo].[Cities]
  ); 
  Collect(collectionCities, Table({CityName: Blank()})
 

dropDown1.Item =
  SortByColumns(collectionCountry, CountryName, Ascending)


dropDown2.Default = ""
dropDown2.Item =
  SortByColumns(
    SortByColumns(
      Filter(collectionCities, CountryID = dropDown1.Selected.ID)
      CityName, Ascending
    ),
    CityName, [""]
  )

In this scenario, the independent column [Countries] and dependent column [Cities] are both in memory. Instead of dynamically creating a new collection each time to cascade (which can be inefficient), you have a raw City collection that includes all the cities--procedures in your case. You let PowerApps do the heavy-lifting by executing an in-memory filter (e.g. Filter(collectionCities, CountryID = dropDown1.Selected.ID)).

Scenario 3:

screen.OnVisible =

  ClearCollect(collectionCities, [dbo].[Cities]);
  Collect(collectionCities, Table({CityName: ""});

  ClearCollect(collectionCountry,
    DropColumn(
      AddColumns(AddColumn([dbo].[Country], "CountryID2", "ID"),
        "CityGroup", Filter(collectionCities, CountryID=CountryID2 || CityName = "")
      ),
      "CountryID2"
    )
  );

  Clear(collectionCities)


dropDown1.Item =
  SortByColumns(collectionCountry, CountryName, Ascending)


dropDown2.Default = ""
dropDown2.Item =
  SortByColumns( 
    SortByColumns( 
      Ungroup(
        ShowColumns(Filter(collectionCountry, ID=Dropdown1.Selected.ID), "CityGroup"),
        "CityGroup"
      ),
      "CityName", Ascending
    ),
    "CityName", [""]
  )

This is similar to Scenario2, except you are nesting your dependent records (City) within the independent records (Country). And then you use the Ungroup syntax to display the dependent records.

I'm sure there are a dozen other ways to do it, but these are just some ways I've implemented cascading items.

[PaulD1 · Accepted solution]

In SQL (pseudo code as I don't know your field, table names

Create view vwApplicationProcess

Select tblApplication.Application, tblProcessStep.PROCESS_STEP_DESC

From

tblApplication

Left Join tblProcessStep

On tblApplication.AppID=tblProcessStep.AppID

This gives you a data set with the Application and Process Step Description in two columns (The Application Name will repeat multiple times).

Connect the View as a Datasource.

In the first drop down (ddApplication) set the Items property as Distinct(vwApplicationProcess,Application)

In the second drop down set the items property to Filter(vwApplicationProcess,Application = ddApplication.Selected.Application)

Now, when you choose an Application from the first drop down, the second drop down should change to display only the related Process Steps.

If you mosify the data in the either the Appplication or Process table, be sure to refresh your View so that the changes are reflected.

[ShowMeTheAnswer]

Great thanks.

I will shortly be setting up another such relationship so, Ive got two options to try from this forum.

Bear with me its taking a little time to design it all.

[ShowMeTheAnswer]

Hi,

I've used your idea for COMPETITOR and BRAND table.

Just like my solution on the APPLICATION\PROCESS STEP, the way it works is a bit "quirky"; in this case the issue I'm seeing is that, occasionally if you update the field BRAND sometimes it "temporarily goes blank". However I'm hoping I'll be able to get away with it.

I'll try and include what I did below, might turn our to be a bit of a mess:

1) New view for Competitor and Brand relationship (also created for APPLICATION & PROCESS STEP but not used as yet):

Select tblCompetitor.COMP_ID, tblCompetitor.COMPETITORNAME, tblBrand.BRAND_DESCRIPTION, tblBrand.BRAND_ID
From
[dbo].[COMPETITOR] AS tblCompetitor
Left Join [dbo].[BRAND] AS tblBrand
On tblCompetitor.COMP_ID=tblBrand.COMPETITOR_ID

2) Specification for the "DataCard"
DataField: "COMPETITOR1_ID"
Display Name: "COMPETITOR1_ID"
Default: ThisItem.COMPETITOR1_ID

Update: DataCardValue3

3) Competitor Drop Down Specification:
OnChange: Refresh('[dbo].[vwCompetitorBrand]')
Items (as you suggested but with the addition of the blank first item):
Ungroup(Table({Item:Blank()},{Item:Distinct('[dbo].[vwCompetitorBrand]',COMPETITORNAME)}),"Item")

Value is set to "Result" (No idea were "Result" comes from, its not a column name, must be PowerApps thing?)

Default (otherwise change not sticky): LookUp('[dbo].[COMPETITOR]',Text(COMP_ID) = ThisItem.COMPETITOR1_ID,COMPETITORNAME)

4) 2nd Datacard specification:

DataField: "BRAND1"

DisplayName: "BRAND1"

Default: ThisItem.BRAND1 (BRAND1 is like, BRAND_ID really but it has the old name).

Value: DataCard21Value

5) Brand Dropdown field specification:

OnChange: Refresh('[dbo].[vwCompetitorBrand]')
Items: (Use your filter, but also have a blank first item avaliable):
Ungroup(Table({Item:Blank()},{Item:Filter('[dbo].[vwCompetitorBrand]',COMPETITORNAME = Dropdown3.Selected.Value)}),"Item")

Value: BRAND_DESCRIPTION

Default: If(ThisItem.BRAND1<>"",LookUp('[dbo].[vwCompetitorBrand]',Text(BRAND_ID) = ThisItem.BRAND1,BRAND_DESCRIPTION),"")

As I say wierd behaviour:
 - When updating BRAND especially can look breifly blank, then if you leave it its OK (Gonna try moving the refresh to screen level).

Other observations:
In theory I'd have thought I shouldn't need the LOOKUPS in the DEFAULT section, but it doesn't work (new value not saved) without them

I found it useful to have the ID's in the resultant view.

It's close enough for now, I'll mark yours as solution, but if you have another idea (and could help with the temporarily blank issue!) let me know!