Collections and Patch with dropdown lists

[heshan_pandit]

Hi there

I have a collection created in my app. It uses the current date (the month) to create a collection based on years from 2020 to Today(). The collection headers are Year start, Year end and YearTitle.

I use the YearTitle as items in a dropdown list.  When the app starts, the collection is created and the first item appears at Year: one in the title. However, the app requires that you choose a year from the dropdown list and then an action occurs.

However, what I would like is to create a "-----" or blank item in the dropdown list so that users go to choose the year that they need.

So I went to the line of code that creates the collection and for the next line of code, used the patch function to add a record which was only the YearTitle as "-----" (5 dashes). When I review the collection in the variables section on powerapps, there is a record for year 1, year 2, year 3 and year 4 and then the record for "-----" (no year start or year date for this one). 

I then went the dropdown control and put in "-----" in to the default setting.

However, the dropdown still defaults to the first record YearTitle in the collection (i.e. Year 1) and my "-----" is the 5th in the list.

The code is below - the collection I am referring to is called MainsequenceYear2. The DifferenceYear is the difference between the year now and 2019. MainsequenceYear is the list of years between 2019 and now.

The patch line is at the end

If(Month(Today())>=4,UpdateContext({offset:0}),UpdateContext({offset:-1}));

UpdateContext({DifferenceYear:Year(Today())-2019+offset});

ClearCollect(MainsequenceYear,Sequence(DifferenceYear,2020));

ClearCollect(MainsequenceYear2,ForAll(MainsequenceYear,{YearName:Text(Value),YearStart:Text("1/4/"&Value),YearEnd:Text("31/3/"&Value+1),YearTitle:Text("Year: "&(Value-2019))}));

Patch(MainsequenceYear2,Defaults(MainsequenceYear2),{YearTitle:"----"});

How do I make the "-----" appear first on the dropdown list or first record in the table. 

PS. the collection is made by using clearcollect, sequence and subtracting the current year from 2019 and then making entries - as it is still 2023, there will be (2023-2019 😃 4 entries (hence year 1 to Year 4)

Thanks

Heshan

[eisuan]

@heshan_pandit 

On your dropdown code:

SortByColumns(MainsequenceYear2,"YearTitle")

[heshan_pandit]

Thanks for the quick response.

I tried this but....

Other parts of my dropdown control broke. Bits that I have not mentioned. The dropdown control OnChange essentially takes the value and creates some context variables that are used in another part of the app as dates to sort out a different collection of events (by selected date you see).

the code for onchange is:

UpdateContext({MainChosenYear: Dropdown1.SelectedText.Value});
UpdateContext(
{
MainChosenYear2: Value(
LookUp(
MainsequenceYear2,
YearTitle = MainChosenYear
).YearName
)
}
);
UpdateContext(
{
MainYearStringstart: Text(
"1/4/" & Last(
FirstN(
MainsequenceYear2,
MainChosenYear2 - 2021
)
).YearName
)
}
);
UpdateContext({startyearchoice: Last(
FirstN(
MainsequenceYear2,
MainChosenYear2 - 2021
)
).YearStart}
);
UpdateContext({endyearchoice: Last(
FirstN(
MainsequenceYear2,
MainChosenYear2 - 2021
)
).YearEnd});

When I switched to your suggestion, the dropdown1. selected.value errord. I changed it to .selected

Then the Last(FirstN  entries didnt like using the '='. Apologies for not mentioning it earlier. I didnt realise the dependencies.

Hesh

[heshan_pandit]

PS pleas substitute the 2021's to 2019. 

Hesh

[eisuan · Accepted solution]

@heshan_pandit 

It's a bit hard to follow the problem as you give just a code.

• What app is used for ?
• What you trying to achieve with dropdown selections ?
• Make screenprints of your app, so it will be easier to visualize

So far what I can understand Last(FirstN entries didnt like using the '='. did not like it as Dropdown does not have value if you put SortByColumn.

In this case try using

If(Isblank(Dropdown1.Selected.Value), "", <Other wise you code here>))

[heshan_pandit]

HI there

before I send all the pictures and answers to your questions, I have solved my issue.

I have a sharepoint list of reportable events . The important entry is the submission date. I have an app that is a dashboard collecting statistics from the sharepoint list data. The dropdown helps to produce numbers by year  - year one, two etc. 

I initially set up the years as fixed variables along with the dates (my years stared 1st april and ended 31st march the year after)

However, this required manual entry of dates going forward, something I know I would forget to do

I worked out how to create an automatic year / date category creation using collections. The app creates a collection of start dates and end dates, labels them as year 1, 2,3 etc and names then Year:1, Year 2 etc.

My dropdown for selecting which year, is populated by that dates collection. It will also have the appropriate start and end date for that year.

Until I asked for help yesterday, the untidy part of what I had created what that on opening the app, the dropdown had "Year 1" automatically as the default label. However, as the user hadnt actually picked "Year: 1", none of the variable / sort / filter actions to collect year contextualised data had been processed (the user selects the year and hits a button to create the stats and the results are laid out on the dashboard)

I wanted a default "Please choose" on opening the app and was trying to work out how to add a "----" or "<please choose>" message.

I learned that creating the dates collection and patching an additional entry in to the same colleciton labelled "-----" created the appropirate label in the dropdown but it would not appear on top of the list (i.e. on opening)

However, I realised that if I initially create the "-----" entry to the dates collection and patch the rest of the proper dates (using the formulae that I had) then the "----" label appears first. 

Then it was just a case of entering in a validation code to prevent users trying to create stats based on the "-----" label entry

And it works now.

however, what you suggested strangely got me thinking along the right track and I thank you for it.