cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MH3
Post Prodigy
Post Prodigy

Sorting Gallery with Multiple Fields - SQL Data Source

Hi Everyone,
I have a SQL Data Source, in which I have an application which records daily task of employees, and I want to sort the gallery with the Date and TimeSlot together. 

Example: 
Sorting.png
I have different Data, on same date and time, but the sorting is not working correctly.




My Code:

 

SortByColumns(If(var1 && var2,Filter('[dbo].[TimeSheet]',UserId= MyUser),Filter('[dbo].[TimeSheet]',UserId= MyUser ,(Date>=DatePicker1_1.SelectedDate)&&(Date<=DatePicker1.SelectedDate))),"Date",Ascending,"TimeSlot",Ascending)

 


It is now sorting by TimeSlot, on each date, but I want to sort the gallery with Date and TimeSlot together.

Any help!

1 ACCEPTED SOLUTION

Accepted Solutions

@MH3 ,

OK - I was focused on your Sort issue and have now noticed something else - if Date is the actual name of the field, change it now (you will have to add another field and delete this one) as Date is a Reserved Word in Power Apps.

Also you might try this after doing this

Sort(
   If(
      var1 && var2,
      Filter('[dbo].[TimeSheet]',
      UserId= MyUser),
      Filter('[dbo].[TimeSheet]',
      UserId= MyUser && 
      Date>=DatePicker1_1.SelectedDate &&
      Date<=DatePicker1.SelectedDate
   ),
   Date,           //rename this field
   Ascending,
   TimeSlot,
   Ascending
)

 

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

13 REPLIES 13
WarrenBelz
Super User
Super User

Hi @MH3 ,

Your code

SortByColumn(
   .......
   .......,
    "Date",
   Ascending,
   "TimeSlot",
   Ascending
)

will do exactly that - sort by Date and then TimeSlot within that . What do you mean by "Date and Timeslot together"?

@WarrenBelz ,

 

Actually the issue is, the sort is working, but I have two entries of a same date and Same TimeSlot,

Like:

1) 11/05/2020, 9:00 - 9:30
2) 11/05/2020 9:00 - 9:30

like it should display the same date and same timeslot entries in ascending order by date and timeslot.

 

any help?

@MH3 ,

Do you mean you want a field displaying 11/05/2020 9:00 - 9:30?

Put a label in the gallery - I assume Timeslot is a date/time field as well

Text(Timeslot,"dd/mm/yyyy hh:mm")

 

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.

 

Sir @WarrenBelz ,

 

I understood what you said, but the thing is I dont want to display Time and Date together, I just wanted to sort the gallery, but the thing is it was working, that entry was on 11/06/2020,  that's why I was confused. 

 

 

@MH3 ,

OK - I was focused on your Sort issue and have now noticed something else - if Date is the actual name of the field, change it now (you will have to add another field and delete this one) as Date is a Reserved Word in Power Apps.

Also you might try this after doing this

Sort(
   If(
      var1 && var2,
      Filter('[dbo].[TimeSheet]',
      UserId= MyUser),
      Filter('[dbo].[TimeSheet]',
      UserId= MyUser && 
      Date>=DatePicker1_1.SelectedDate &&
      Date<=DatePicker1.SelectedDate
   ),
   Date,           //rename this field
   Ascending,
   TimeSlot,
   Ascending
)

 

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

Hi @WarrenBelz ,

 

I typed my fields in Double Quotes Like "Date" and "TimeSlot" what's the difference?

 

Hi @MH3 ,

I am using Sort rather than SortByColumns - it does not use the quotes.

 

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.

 

 

Hi @WarrenBelz , 

 

Sure, I will test this one as well. but can you checkout this Solution by -Sikyan:

I suggest you add a new Request Date DataCard in the form and remove the older one, since it's clearer to start from scratch.

Please try the following steps:

1. create a Timer, Set Duration:1000, Enable Repeat and Auto start, Disable Auto pause, Set OnTimerStart: Set(VarCurrent,Now())

2. unlock the data card, add a dropdown in it, rename to AmPmValue1,

set Items: ["AM","PM"]

set Default: If(Hour(VarCurrent) < 12, "AM", "PM")

3. modify property of HourValue:

Items: ["12","01","02","03","04","05","06","07","08","09","10","11"]

Default: Text(If(Mod(Hour(VarCurrent), 12) = 0, 12, Mod(Hour(VarCurrent), 12)),"[$-en-US]00")

4. modify property of MinuteValue:

Default: Text(Minute(VarCurrent),"[$-en-US]00")

5. Modify property of data card:

Update: DateValue1.SelectedDate + Time(If(AmPmValue1.Selected.Value = "PM", 12, 0) + Mod(Value(HourValue1.Selected.Value), 12), Value(MinuteValue1.Selected.Value), 0)



when I edit and existing record, it still shows current time, as the TimerOnStart is set to now(), I want to show the Selected Time in Edit mode which the user selected and Now()  in for New Form

Hi @MH3 ,

I am not sure how that differs from mine, but I have another hopefully simpler idea.

Put a label into your date card (you can hide it when you are finished) with the Text property 

Parent.Default

 I will call it DateText in the below. In your Patch

YourDateField:DateTimeValue(DateText.Text)

 

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.

 

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,840)