Collection not populating

[ADumith]

Hello, first of all I want to apologize if what I am going to ask is very silly, but in reality it is the first time that I deal with PowerApps, I love this platform but I am still learning.

My problem is, I have a list of employees EmployeesDirectory and this list of calls contains information of the employees such as the day of the birthday, these fields (day, month and year) are of a single line text type.

I am building an application in PowerApps where I want to show the birthdays of the month, then I must filter my list by the month field called Mbirth and then order the records by day field called Dbirth, so I can show the list of people who have birthdays that month.

I have managed to connect the SharePoint list with the apps and the records are displayed, but reading I understand that it's better to use collections and configure the data source property of the gallery with the collection.

I have tried placing the code in OnVisible (Screen) and also in OnStart (App) but it doesn't work.

Set(varMonths, Text(Today(),"mm"));
ClearCollect(colBirthDay,Filter(EmployeesDirectory,Mbirth=varMonths));

But my problem is that the collection is not only not filled, even when I go into the properties I see some fields that are not the fields of my list.

Can anyone give me any suggestions as to what might be going on?

I put the code of the filter and the images of the collection.

Thank you very much, I hope you can help me.

[#2]

Hi @ADumith , I built a quick solution to demonstrate how you can achieve this. First I built a simple SP list:

I built a simple app and set the 'on start' property to this:

//set a variable to the current month
Set(varMonth, Text(Now(),"mm"));

//create a collection of all eployee records in ascending order by mbirth
ClearCollect(colBirthDay, Sort(EmployeesDirectory,month_of_birth,Ascending));

I then set the gallery items property to this:

//filter the gallery items to only show those with a birth month matching the current month
Filter(colBirthDay,month_of_birth=varMonth)

You would likely be better off converting the date of birth to a month item and filtering on the result rather than have a dedicated field in your list. Less to input and less to go wrong.

[#3]

Hello!@jonlake

Thank you very much for continuing to help me.

I already managed to get the collection to load data. Goal achieved.

But the records are still not displayed in the gallery. The data source is the collection, and the fields are properly mapped, in fact when I place only the collection the data is shown, but when placing the filter none is shown.

Without filter

Filtering.

Create a new collection to test and the behavior is the same.

Why the filter does not work?

Thanks again.

[#4]

Hi @ADumith, I recommend adding a button to test, just in case closing and restarting the app doesn't force the 'on start' setting of the variable. Something like this:

If that doesn't test the population of the gallery I would check the format and content of the month field in your SP list. I've attached a version of the test app I've built. Simply redirect to you SP list.

[#5]

Hello @jonlake 

I realized why the collection sometimes doesn't populated, when that happens I just save the app, close PowerApps and go back in.

The Dbirth and Mbirth fields are fields of type Single line of text.

It is possible to create a filtered and ordered collection at the same time and thus in the gallery I only map to the collection. What do you think about the idea?

By the way, thanks for sending me the app but due to security policies I can't upload it to our platform.

Thanks again,

[#6]

Hi @ADumith , you can do it at the gallery items level:

//filter the gallery items to only show those with a birth month matching the current month
Sort(Filter(colBirthDay,month_of_birth=varMonth),date_of_birth,Descending)

In my previous responses I've mentioned the Onstart action, which creates a sorted (but not filtered) collection:

//create a collection of all employee records in ascending order by mbirth
ClearCollect(colBirthDay, Sort(EmployeesDirectory,month_of_birth,Ascending));

[#7]

Hello@jonlake 

I'm really confused, there is no way for the filter to work, I don't know what else to try, I generated a new app to try and the result is still the same.

I send you the app, maybe you are able to see something that I don't see.

I don't know what to think. 

It' not make sense,

[#8 · Accepted solution]

Hi @ADumith , I downloaded and imported your app. I refreshed the source data to be my SP list and used the collection and filter elements I've shared and it seems to work fine. I would encourage annotating references wherever possible.

My SP list is structured like this:

My date source in the app is targeted at the list:

The 'OnStart' property is set to:

The gallery items is set to:

The first of your gallery fields is set to:

The second of your gallery fields is set to:

Without access to your source list I'm unsure what may be wrong at your end. I can use your app, redirect it and modify a few of the field names/targets and it appears to be working.

[#9]

Hello @jonlake 

Well, now I don't know whether to laugh or cry. 

On the one hand the app is fine, it works as expected, however I can't get it to work on my side.

Thinking a little more, if there was a problem accessing the SharePoint list, don't you think that the collection would not even be generated?

I imported the list from Excel, I didn't do it from scratch. Would have something to do with it?

I think I'll have to put a support ticket to Microsoft to review this case.

However, thank you so much for all the support you have given me.

[#10]

Hi @ADumith, I noticed that your list contained a number of field headings with ambiguous titles, e.g. Field_01, Field_02 etc. I would first try to simplify the list and give names to the columns that are appropriate for the data they contain. It's possible that you have more than one field named the same, which may be confusing your gallery filter/sort. If you can create the collection then your access shouldn't present a problem.

[#11]

Good evening @jonlake,

Oh! 

I hadn't realized it.  How do you think we can fix it?

 I use that list for a workflow for birthdays, so any changes I make will surely affect the workflow. 

Thank you for taking the time to review and go further. 

[#12]

Hi @ADumith , do you have fields in the list with the same title?

[#13]

Good morning @jonlake,

Look at the image.

Note that each field has its title.

But, when the collection is created, the title of the field is not brought, but rather an ambiguous title is placed, I mean, field_1 instead of FullName, field_2 instead of EmployeeEmail, etc.

I'm sure that's the issue source, so how to solve it.

Thanks again for your time and support

[#14]

Hi @ADumith , I can see now what's happening. The import from Excel brought in the Mbirth data with a field name of 'field_4'. Even if you rename it, in a Flow you need to use the original name. The original name can be seen if you view your list settings, then hover over the field in question At the bottom of the screen you will see the URL for that field with the original name at the end of the URL.

I avoid importing from Excel if at all possible but I appreciate it can sometime be a quick way to import data. A method which provides for greater control is to import the Excel data into an Access database, do any data cleansing there, and then export to your SP site as a list.

I can also see that you date of birth field is set to 'single line of text'. Should that not be a date field?

If your Mbirth data is in 'field_4' your gallery items should be set to:

//filter the gallery items to only show those with a birth month matching the current month
Sort(Filter(colBirthDay,field_4=varMonth),field_3,Descending)

[#15]

Hello @jonlake,

At first I thought that was the problem and it would be enough to do what you are recommending me; actually, that was what I was doing, but unfortunately that's not the problem.

I'll explain, I took the variable varMonth and assign the value 01 (January) and the filter worked!

The filter worked finally, so the problem is when I try to do the filter with the variable value or the field value, somehow there is no match between them.

Maybe if we change the type of the variable, I mean use a local one instead of global, or there is definitely a problem with the list and I have to redo it but from scratch, instead of importing it.

Any ideas?

[#16]

@jonlake 

I finally did it.

I decided to create a new list from a database as you suggested and also create a new app.

So everything works perfect now.

We won't know what the problem really was but I think it was a data source.

Thank you so much for all the follow-up you did to this case, now I am going with something a little more complicated and it is for work anniversaries, I will create another question in case I need it.

Again, thank you so much, I really appreciate it 

[#17]

Hi =@ADumith , I'm glad you found a solution. We all learn together, every day.

Regards, Jon