Dropdown Default when using dynamic Helper Column

[Anonymous]

Hi. New to PowerApps and to this forum.

I have been reading for days and it has been a wealth of information to date. 

Attempting to transition my background from Microsoft Access & Excel VBA to PowerApps.

I have two dropdowns.

One dropdown with a list of 'Employee Names' and the other dropdown with a cascading list of 'Dates' for records for that particular 'Employee Name'.

I have used AddColumn in the 'Items' property of each dropdown to effectively make a 'double column dropdown' to dynamically display the Count of Unique Dates before the 'Employee Name' (ie '[002] John Smith') and Count of Unique Records before the 'Date' (ie '[004] 20/12/2019'). 

I have then used the dropdowns 'OnChange' event to UpdateContext with variables 'defName' and 'defDate' to remember the value of each dropdown to be used in the 'Default' fields.

This idea of this was to remember the 'Default' value so after adding records on another screen, and refreshing the data, the same item would still be selected on each dropdown when returning to the screen (both these dropdowns entries are not actually linked to a datasource).

This obviously stops working when the Record Count changes as the 'Default' no longer has the up to date value in the brackets (ie [002] John Smith now may be [003] John Smith).

I then tried changing the Update Context to remember the original dropdown Column values which don't change (ie the 'John Smith' and '20/12/2019') .... with the intention of building a more complicated Default which performs some form of Lookup back through the dropdown Items to find the new corresponding Default.

But I can't figure out how to do a Lookup back to the Items and don't want to duplicate the Items logic again in the Default. I have read about Collections and could possibly build a Collection for each dropdown to use in the Items which I could then also use in Lookup to set the Default.

But not sure if the Collection would then lose the dynamic update nature.

Any suggested alternative or more efficient methods? Thanks in advance.

[WillPage]

If you;re working on another screen, UpdateContext() doesn't work because context variables are specific to a screen. Use Set() instead to initialise and update a variable that you want to use on another screen.

Once you have the name in a variable, you can filter the data source using the LookUp() or Filter() functions (depending on whether the Name appears more than once in the list or not).

LookUp(EmployeeNames, Name = var_selectedName).... or whatever.

[Anonymous]

Thanks @WillPage for the help.

Agree what you have said with 'Set' versus 'Update' but I still don't understand how to use the Lookup() function as I can't just refer to 'Employee Names' as this is only contained in the Index for that dropdown.

How can I refer back to the Index or do I need to use a Collection or something different.

[WillPage]

Dropdown.Selected is an object that contains all the data for each column in its data source (Items property), not just the column you've chosen to display in the dropdown.

Therefore, if you're using its selected value to filter for something in another data source, collection or variable, you don't necessarily need to put it in a variable first, you just have to use it in the lookup:

Lookup('Data Source', Column = Drowdown.Selected.Column)

I might have lost you abit on what you're trying to achieve, but I guess I'm trying to say that the selected value of a dropdown or combo box (or indeed gallery) is just as good as a collection or variable to use in other places within the app.

[Anonymous]

@WillPage I think we are talking differently as I know in a dropdown I can read the value of any Column and can update a dataset back to any one of those Columns. I appreciate your assistance.

I will try to use a better example to explain. Sorry this is ending up like War and Peace.

Lets say I have a reference data set 'tbl_Employee' for all employee details and the value of Column 'Hdr_Name' are 'John Smith', 'Bill Smith' and 'Fred Smith'.

Lets say I have a data set 'tbl_Data' for employee'records with 'Hdr_Employee' (which duplicates values from 'Hdr_Name') and a bunch of other Columns.

So my 'tbl_Data' may have 4 records for 'John Smith', and 3 records for 'Bill Smith' and 0 records for 'Fred Smith'

And I have a dropdown using 'tbl_Employee' for Filtering the data set 'tbl_Data'

But rather than just wanting to refer to the 'Hdr_Name' in the dropdown I want to have a Count of Records before each name as I am effectively trying to have multiple column dropdown.

Then in the dropdown Items I have

AddColumn(tbl_Employee, "Hdr_Name_2", [ "&Text(CountRows(Filter(tbl_Data,Hdr_Employee=Hdr_Name)),"[$-en-US]000")&"] " & Hdr_Name)

So my Items now has two Columns 'Hdr_Name' and 'Hdr_Name_2'

Hdr_Name_2 will have values '[003] John Smith', [003] Bill Smith', '[000] Fred Smith'

Up to here this all works for me 

But I want to remember the selected value as Default for next time when I return after adding or editing records.

But this effectively will change because the Count of Records will change so I can't use Hdr_Name_2.

So then in the dropdown OnChange I can set a variable Set(txtDefault,Dropdown.Selected.Hdr_Name) using .Hdr_Name rather than .Hdr_Name_2 (as this will change)

Then in the dropdown Default I need Lookup('Data Source', Hdr_Name = txtDefault, Hdr_Name_2) to pull out the NEW updated Hdr_Name_2 for the original corresponding Hdr_Name.

The problem I cant figure out is 'Data Source' is being dynamically calculated in the dropdown Items list.

How can I refer back to that without duplicating all the logic in the Item separately again in Default.

Or does this create some kind of circular reference where Items and Default are intermingled?

[WillPage]

Try the OnChange like this

Set(txtDefault,Dropdown.Selected)

e.g. set the variable to be the whole record, not just the Hdr_Name column

Then your Dropdown.Default can be 

Lookup('Data Source', Hdr_Name = txtDefault.Hdr_Name)

.. but you set its Value property to Hdr_Name2.

Rember to Refresh('Data Source') after the new record is added.

[Anonymous]

@WillPage thats a very clever idea regarding the variable as the whole record.

I am still trying to get my head around this PowerApps approach where variables can be created on the fly rather than implicitly specified at the start in VB Code.

I have now got the dropdown solution working with Defaults.

I have placed two temporary text boxes to monitor whats going on.

One of which displays txtDefault

The other of which displays the result of 

Lookup('Data Source',Hdr_Name=txtDefault,Hdr_Name_2)

However the problem is still 'Data Source' is effectively a copy of all the text in the dropdown Items as I can't work out any other way of doing it.

Maybe I can learn from your example and resort to creating a variable 'dtaSource' each time the screen is accessed ... which builds 'Data Source'. Then I can make my Items collection directly 'dtaSource' and use 'dtaSource' in the Lookup as well.

P.S. Sorry I should have been using Code tags but new to the Forum.

[Anonymous]

The issue with the dtaSource variable idea is I need to keep refreshing it as the selections on the dropdowns change as it would no longer be dynamic?

[WillPage]

You can use the ClearCollect() function if you want to create a collection that's Dynamic, in as much as every time you run the function it clears out any previously held data.

e.g an OnChange property could do

ClearCollect(col_something, Dropdown.Selected)

Once you have the collection you can add data to it using Collect() or update it using Patch().

[Anonymous · Accepted solution]

@WillPage  I have now got this working based on some of your comments. Thank you very much.

Here is my solution.

It achieves what I want in terms of a single location to change the dropdown Items text rather than it being duplicated.

This effectively allows setting of a default for a dropdown where the displayed value from the 2nd Column is dynamically calculated whilst the 1st Column (which is unique and unchanged and effectively sets the true default) is hidden.

So regardless of what data I add, recalculating the 2nd  column, it still returns to the same default.

If I delete this record obviously it wont find it so will default to the first item.

I havent actually named them like (ie FirstColumn, SecondColumn etc) but written this to explain.

1. For each of the two dropdowns I removed the text from their Items.

2. In the OnVisible event for the Screen I used two UpdateContext (these dynamically update without any extra Refresh)

UpdateContext({dataEmployee:'The prior Text from DropdownEmployee.Items'});
UpdateContext({dataDate : 'The prior Text from DropdownDate.Items')

3. Changed the Dropdowns to DropdownEmployee.Items=dataEmployee and DropdownDate.Items=dataDate

4. For each of the Dropdowns made their OnChange events

UpdateContext({txtdefName:DropdownEmployee.Selected.FirstColumn})

UpdateContext({dtedefDate:DropdownDate.Selected.FirstColumn})

I have used UpdateContext rather than Set as these are always specific to this Screen.

5. For each of the Dropdowns made their Default

LookUp(dataEmployee,FirstColumn=txtdefName,SecondColumn)

LookUp(dataDate,FirstColumn=dtedefDate,SecondColumn)

I have tested this for multiple scenarios and cannot find any situation where it fails. Thanks again!