cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TiesP
Frequent Visitor

Sort gallery by next upcoming birthdays

Hey guys,

 

I have a gallery that is shows a employee register. I want to sort the gallery so that the next birthdays are standing in front of the gallery and if the birthday was yesterday then it will go back to the end of the list. I also have some birthdays field that are not filled. These always have to stand at the end of the list.

 

I think what I've done below is not correct because I'm filtering everyone out when past today. When employees didn't fill their birthday it is "01/01/0001 00:00:00".

 

 

 

Filter(
    Sort(
        Filter(EmployeeRegister; DateValue(Text(Today();"m/d")) >= Value(Text(DateValue(Verjaardag);"m/d")));
        Text(
            DateTimeValue(
                If(
                    Birthday = "01/01/0001 00:00:00";
                    "";
                    Birthday
                );
                "en-US"
            );
            "dd/mm/yyyy"
        );
        Descending
    );
    StartsWith(
        Name;
        EmployeeTxt.Text
    ) And If(
        !IsBlank(ComboBox1.Selected.Result);
        Functie = ComboBox1.Selected.Result;
        true
    )
)

 

 

 

Hopefully someone can help me. Thank you for your interest in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

Hi @TiesP ,

The below is again free-typed - I would be surprised if I have all the brackets exactly, but here is the idea

With(
   {
      wNow:
      Value(
         Text(
            Today();
            "mmdd"
         )
      )
   };
   Sort(
      Filter(
         Employeeregister;
         (
            IsBlank(EmployeeTxt.Text) || 
            StartsWith(
               Name;
               EmployeeTxt.Text
            )
         ) && 
         (
            IsBlank(ComboBox1.Selected.Result) || 
            Function = ComboBox1.Selected.Result
         )
      );
      With(
         {
            wDay:   
            With(
               {
                  wDate:
                  Value(
                     Text(
                        DateValue(
                           Birthday;
                           "en-US"
                        );
                        "mmdd"
                     )
                  )
               };
               If(
                  IsBlank(Birthday);
                  99999;
                  wDate < wNow;
                  wDate + 10000,
                  wDate
               )
            )
         };
         wDay
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

10 REPLIES 10
WarrenBelz
Super User
Super User

Hi @TiesP ,

Try

If(
   Value(Birthday)=0,
    . . . . 

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

TiesP
Frequent Visitor

Hi Warren, Thanks you for the reaction. I think I did'nt explain myself enough.

 

I have a gallery with all employees. The scenario I gladly want is. The person with the closest birthday to today has to stand at first and when the birthday is past today, then the person has to go to the end of the gallery. However, the persons who didn't gave up their birthday(equal to "01/01/0001 00:00:00") are always at the bottom of the gallery. So if I sort the list with SortDescending1 only the persons with the birthday filled in are moving.

 

As you can see I changed my code a bit. I have written comments to explain it a bit more.

 

SortByColumns(
    Filter(
        EmployeeRegister;
        StartsWith(
            Naam;
            EmployeeTxt.Text
        ) And If(
            !IsBlank(ComboBox1.Selected.Result);
            Funtion = ComboBox1.Selected.Result;
            true
        )
    );
    "Birthday"; ##Here i need to filter out the "01/01/0001 00:00:00" and specify that the 
                  next birtday with respect to today comes first 
    If(
        SortDescending1;
        Ascending;
        Descending
    )
)

 Hopefully this makes it a bit more clear

Hi @TiesP ,

If you want all the birthdays after today that are not blank, sorted in ascending order together with your other two filters allowing for them to be blank

Sort(
   Filter(
      EmployeeRegister;
      (
         IsBlank(EmployeeTxt.Text) ||
         StartsWith(
            Naam;
            EmployeeTxt.Text
      ) && 
      (
         IsBlank(ComboBox1.Selected.Result) ||
         Funtion = ComboBox1.Selected.Result
      ) &&
	  Birthday >= Today() && 
	  Value(Birthday) > 0
   );
   Birthday   
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

TiesP
Frequent Visitor

Hi Warren, 

 

Thanks again! I'm beginning to understand where I need to go. However, my birthday property is of a text value and only the day and month are filled(Like this: 08/17/2000 00:00:00). The year is standard on 2000. So is there a solution for formatting is to only date and month and making it a date value so we can use the ">" operator?

 

My code is below. I've tried to use DateValue, but cannot figure it out.

 

Sort(
    Filter(
        EmployeeRegister;
        (
            IsBlank(EmployeeTxt.Text) || 
            StartsWith(
            Name;
            EmployeeTxt.Text
        ) && 
        (
            IsBlank(ComboBox1.Selected.Result) || 
            Function = ComboBox1.Selected.Result
        ) && 
        DateValue(Birthday) >= Today() &&     ##Birthday is of text value
        Value(DateValue(Birthday)) > 0);      ##Only the date and month is filled 
        Birthday
    );
    If(
        SortDescending1;
        Ascending;
        Descending
    )
)

Again Thanks in advance, it would be really great if this succeeds.

Hi @TiesP ,

Your structure has certainly caused some challenges - I felt like one today - note this is free-typed so watch commas and brackets

With(
   {
      wNow:
      Value(
         Text(
            Today();
            "mmdd"
         )
      )
   },
   Sort(
      Filter(
         EmployeeRegister;
         (
            IsBlank(EmployeeTxt.Text) || 
            StartsWith(
               Name;
               EmployeeTxt.Text
            )
         ) && 
         (
            IsBlank(ComboBox1.Selected.Result) || 
            Function = ComboBox1.Selected.Result
         ) && 
         With(
            {
               wDay:
               Value(
                  Text(
                     DateValue(
                        Birthday;
                        "mmdd"
                     )
                  )
               )
            };
            !IsBlank(wDay) && wDay >= wNow
         )
      );
      If(
          SortDescending1;
          Ascending;
          Descending
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

TiesP
Frequent Visitor

Hi Warren, Thank you again for the reaction. This is the structure what we need indeed and I think we are really close. The syntax is correct and does not give any errors and it shows the employees in the right order. However, we are filtering everyone who is past today out of the register.

 

I made some changes in the code. I now sort on the value of a birthday("mmdd"). But if the birthday is past today then it has to go to the end of the gallery. However, the birthdays that are not filled in (Date: 01/01/0001 00:00:00 and value: 101) always have to be at the end of the gallery. So when switching between ascending/descending the gallery will only sort the employees that gave up their birthday.

 

With(
   {
      wNow:
      Value(
         Text(
            Today();
            "mmdd"
         )
      )
   };
   Sort(
      Filter(
         Employeeregister;
         (
            IsBlank(EmployeeTxt.Text) || 
            StartsWith(
               Name;
               EmployeeTxt.Text
            )
         ) && 
         (
            IsBlank(ComboBox1.Selected.Result) || 
            Function = ComboBox1.Selected.Result
         ) && 
         With(
            {
               wDay:
               Value(
                  Text(
                     DateValue(
                        Birthday;
                        "en-US"
                     );
                     "mmdd"
                  )
               )
            };
            !IsBlank(wDay) && wDay >= wNow
         )
      );
      Value(
            Text(
                DateValue(
                Birthday;
                "en-US"
                );
                "mmdd"
            )
        );
      If(
          SortDescending1;
          Ascending;
          Descending
      )
   )
)

 

I've tried many other possibilities, but I cannot figure it out. I hope we can figure this out together 🙂

WarrenBelz
Super User
Super User

Hi @TiesP ,

The below is again free-typed - I would be surprised if I have all the brackets exactly, but here is the idea

With(
   {
      wNow:
      Value(
         Text(
            Today();
            "mmdd"
         )
      )
   };
   Sort(
      Filter(
         Employeeregister;
         (
            IsBlank(EmployeeTxt.Text) || 
            StartsWith(
               Name;
               EmployeeTxt.Text
            )
         ) && 
         (
            IsBlank(ComboBox1.Selected.Result) || 
            Function = ComboBox1.Selected.Result
         )
      );
      With(
         {
            wDay:   
            With(
               {
                  wDate:
                  Value(
                     Text(
                        DateValue(
                           Birthday;
                           "en-US"
                        );
                        "mmdd"
                     )
                  )
               };
               If(
                  IsBlank(Birthday);
                  99999;
                  wDate < wNow;
                  wDate + 10000,
                  wDate
               )
            )
         };
         wDay
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

TiesP
Frequent Visitor

Thanks Warren! This is perfect! And you can be surprised, because all the brackets were correct 😉

Thanks @TiesP ,

Now the challenge for you - do you understand the logic of it all 😎

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,922)