cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DropdownHelp
Level: Powered On

Creating a collection with a temporary date column, based on selected gallery items

Hi PowerApp community,

 

I was hoping that you could help me with something. I'll try to explain this as easy as possible.

 

I have an SQL Table, lets call it "Events", that shows all events that exist. A handful of these events are uncategorized, as in, missing a few information points. These uncategorized events, I have in a View called "UncategorizedEvents".

 

The PowerApp I have made, makes it possible for the responsible person to categorize the event. The event will then go away from the View, and into a new Table called "CategorizedEvents".

 

Long story short. When you are categorizing the event in the app, you have the option to choose "Related" event under "EventType". This opens up for the ability to find a previously categorized event, and connect the two events through the id value I just call "SQLid". When choosing the relate option, you dont have to fill in other information, because its identical to the previous event.

 

Now. When you choose "Related", a dropdown will unlock, with all events that has ever happened for that "Unit". These events can be 10 years old, or 1 day old, and I currently have them sorted by the date, so the newest is first.

 

However, I was wondering if there was a way, to sort the events in the list, so that the one with the date, closest to the date of the event you are currently categorizing was on top, as this is the most probable option?

 

My idea so far, was to make a ClearCollect where I take the "Start_date" of the current "SQLid" and subtract it with the "Start_date" of all potential events from the original Table, and then sort the list based on the smallest number.

 

Everything I have made so far, works perfectly. This is the only user complaint I have received, and I simply cannot figure out how to make it work.

 

Any other ideas, or does my idea work? and how?

 

Thank you very much in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Creating a collection with a temporary date column, based on selected gallery items

@DropdownHelp 

No problem on the ; - I'll keep it in mind on my repsonses for you.

 

So, let's review:

SortByColumns(
             AddColumns(
                     myFilterformula;
                        "dateRange";Abs(DateDiff('[dbo].[TableName]'.DateColumn;Gallery.Selected.DateColumn;Days))
             );
"dateRange";Ascending)

In red above...you need to put in whatever your original filter was here.  You had mentioned you had this working, just not sorting the dates the way you want, so, if so, use that same formula in the red above.  If you didn't have one, then this could be as simple as just the table name.  '[dbo].[TableName]'

In blue above...are these datetime columns??  If so, they should compare fine.  Are they the same name in both places?  If so, then let's start with just removing the table desingation in the first parameter.  They should compare fine then. If not, we will explore some disambiguation and potential additional columns.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
4 REPLIES 4
Super User
Super User

Re: Creating a collection with a temporary date column, based on selected gallery items

@DropdownHelp 

Sweet...I see a chance to use a function I don't get to use often - Abs  (more in a moment).

 

So, here is an approach to that - What I would do is add a column with a calculation of the date difference and then sort by the difference (ascending).

 

So, in pseudo-formula:

SortByColumns(
       AddColumns(
             yourCurrentFilterFormula, 
             "dateRange", Abs(DateDiff(yourItemDateColumn, theDateColumnInYourRelatedTable, Days))
       ),
"dateRange", Ascending)

What this will do is add a column to your table that will calculate the days between your current item and the items in the datasource/filter.  Using the Abs function will make them all positive.  So, if the date is past (negative days) by 10 days, then it will be a positive 10 in the list.  This will all help the Sort function to give us the most potential date at the top of the list.

 

I hope that is clear and helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
DropdownHelp
Level: Powered On

Re: Creating a collection with a temporary date column, based on selected gallery items

@RandyHayes 

 

Hi Randy! Thanks for your answer. Nice using the Abs formula, it should do the drill.

 

However, I am not getting it to work properly when inserting the formula you write, It currently looks like this:

 

(also, I use ";" instead of ",")

 

SortByColumns(
AddColumns(
myFilterformula;
"dateRange";Abs(DateDiff('[dbo].[TableName]'.DateColumn;Gallery.Selected.DateColumn;Days))
);
"dateRange";Ascending)

 

This is exactly what you wrote, just with my data.

 

I am not sure if it makes a difference but the date format that the SQL columns use is "2018-12-26 08:00:00.000" as an example.

 

Thanks again!

Super User
Super User

Re: Creating a collection with a temporary date column, based on selected gallery items

@DropdownHelp 

No problem on the ; - I'll keep it in mind on my repsonses for you.

 

So, let's review:

SortByColumns(
             AddColumns(
                     myFilterformula;
                        "dateRange";Abs(DateDiff('[dbo].[TableName]'.DateColumn;Gallery.Selected.DateColumn;Days))
             );
"dateRange";Ascending)

In red above...you need to put in whatever your original filter was here.  You had mentioned you had this working, just not sorting the dates the way you want, so, if so, use that same formula in the red above.  If you didn't have one, then this could be as simple as just the table name.  '[dbo].[TableName]'

In blue above...are these datetime columns??  If so, they should compare fine.  Are they the same name in both places?  If so, then let's start with just removing the table desingation in the first parameter.  They should compare fine then. If not, we will explore some disambiguation and potential additional columns.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
DropdownHelp
Level: Powered On

Re: Creating a collection with a temporary date column, based on selected gallery items

@RandyHayes 

 

Perfect! I just had to remove the table designation in the first parameter. Then it worked perfectly!

 

Thanks again! Smiley Happy

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 276 members 4,490 guests
Please welcome our newest community members: