Sort by column value unless row is Pinned to top

[jonbowles84]

I have a table "noticeboard" where rows are sorted by [Posted] which is just time row was created. I would like to have the ability to pin a row in the noticeboard to the top, so if its pinned it remains at the top no matter the value of [Posted] and the rest are sorted by [Posted] still.
I cant seem to find a solution to this?

[Kirk_Masden]

I would make a virtual column that is DateTime with the following sort of formula:

if([Pin]=TRUE,[Your original DateTime column]+10000,[Your original DateTime column])

That should add a decades to the date of any pinned items, making them continue to be "newest" until 2048 or so when they will be replaced by other newer items.

[Steve]

Or just add the Pin column as the first sorting column?

[Kirk_Masden]

Wow!  Of course!  That's the way to go. 🙂

[jonbowles84]

Thanks @Steve, but what sort of column should the [Pin] column be, I was trying to figure out a way for it to be Yes/No column with only 1 yes. Or some other way to only have 1 pinned.

[Steve]

it would be a Yes/No column. It's up to you to mark only one row as pinned. If you mark several, each one you mark will be pinned at the top.

[jonbowles84]

Yes, I was playing with 2 actions, one to make all existing Yes's into No's and another to make the current row into a yes both run simultaneously, but couldnt get it... All good, I will just do it manually, might be usefull to have more than 1 pinned anyway, thanks for your help.

[jonbowles84]

Thanks @Kirk_Masden I was thinking something like this, but I really only want 1 to be pinned...

[jonbowles84]

I have a new problem now with this. I have an [Author] column which is supposed to be the author of the post on the "noticeboard" table, which works fine with this formula:
ANY(SELECT(Staff[Full Name], USEREMAIL() = [Email]))
Problem is, when I run the "pin to top" action it changes the value of [Author] to the person who "pinned to top" (changed value of [Pin] to TRUE)
any simple way to fix this?
Actually it does the same to [Posted] column which is supposed to be the time the post was created but becomes the time it was "pinned to top"

[Joseph_Seddik]

What do you need an action for? As @Steve recommended, you should just add a new column, name it "Pin", type: Yes/No, and in the View Options you add it as the first column in "Sort By" section. 

If you have a table view, you can also enable Quick edit, in this way the column will show with a small checkbox that the user can simply click to have this pin row effect. 

[jonbowles84]

Ive just got a actions to change the value of the column depending on whether I want it pinned or not. Works fine, other than the problem with the [Author] and [Posted] values changing when the value of the [Pin] column is changed...

[Joseph_Seddik · Accepted solution]

OK, I see. Thank you.

Move the expression from App Formula to Initial Value, and add the following expression as an "Editable if" constraint: ISBLANK([_THIS])

By the way, since you have a Staff table, instead of the ANY(SELECT()) expression you mention you should do the following:

1. The "Author" column should be a ref to your Staff table, where the "email" column should be the key. 
2. In your Staff table, make the "Full Name" column as Label. 
3. Now, the initial value expression should simply be: USEREMAIL(). 

[jonbowles84]

Brilliant, thankyou!