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

[Anonymous]

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!

[RandyHayes]

@Anonymous 

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.

[Anonymous]

@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!

[RandyHayes · Accepted solution]

@Anonymous 

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.

[Anonymous]

@RandyHayes 

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

Thanks again! 🙂