Sort gallery by next upcoming birthdays

[TiesP]

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.

[WarrenBelz]

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]

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

[WarrenBelz]

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]

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.

[WarrenBelz]

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]

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 · Accepted solution]

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.

[TiesP]

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

[WarrenBelz]

Thanks @TiesP ,

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