I'm trying to build a todo app that has a vie...

(Kevin Biggar) #1

I’m trying to build a todo app that has a view that shows only the single next item to be completed. I can only create a slice that shows all uncompleted items. How can I elegantly filter for just the first item.

In other words, when you have identical rows in a table how can you filter to show only the first (ie top) row? Thanks!

(Dinh Nguyen Nguyen) #2

@Kevin_Biggar Assume that the next item to be completed is also the “oldest” incomplete item. Also, I assume that there is a column recording the date when the item was added

Based on this, you need to first have a way to sort the items based on their add dates. This can be done using the OrderBy expression:

OrderBy({list of keys of items to sort}, [Date])

Note that the list of keys of the items can be obtained using a SELECT expression, and you may also want to select only incomplete items.

Once you have sorted the items, you can pick the top n items (where n is a number) using the TOP() expression. Suppose you want to pick only the first item, you can use this:

TOP(OrderBy({list of keys of items to sort}, [Date]), 1)

The TOP expression gives you the row key of the oldest incomplete item. You can then compare this row key to the row key of each row in the slice filter. Note that the TOP expression creates a list, so you will need to use the IN() expression:

IN([Item key column], TOP(OrderBy({list of keys of items to sort}, [Date]), 1))

All of the expressions mentioned above can be found in the article below.

help.appsheet.com - List Expressions and Aggregates List Expressions and Aggregates help.appsheet.com

(Kevin Biggar) #3


Thanks for your very considered answer! Date probably won’t be a reliable way to order - can I use ‘rownumber’ instead?

And sorry to ask a complete newbie question - but where do you see this expression going? in the Row Filter Condition of a slice?

(Kevin Biggar) #4

@Kevin_Biggar So here is the final expression that seems to be working. To the newbies out there - yes it is in the row filter condition of a Slice.

IN([_RowNumber],Top(SELECT(Data[_RowNumber], [Progress] <> “Full”),1))

Reading from the inside nest outwards, this expression starts with SELECT which goes to the Progress column in the Data table and makes a list out of all the RowNumbers where “Full” appears. Then the TOP function returns just the top 1. Then the IN Function does some magic and turns that into a Yes/No that the filter condition requires.

The Rownumber has to be a ‘key’ for this to work.

At least that’s what I think is happening. Thanks Dinh for your help!

(Dinh Nguyen Nguyen) #5

@Kevin_Biggar Hi Kevin, glad the expression is working. However, if possible I suggest modifying it to avoid using the RowNumber column as the key column. You can read more about the reasons why as well as how to choose a good key column here:

help.appsheet.com - What is a Key? What is a Key? help.appsheet.com