Add orders to database items & show last n items
summary
Here are some ways to add orders to database items or show last n items. The main idea is relate all the items to one father page, then get a batch of page ids and their orders. All the methods can rank items by created time initially, and the last method can also work for last edited time.
tags
Notion
Formula
Example
date
Feb 6, 2021
Chinese version
English version
Here are some ways to add orders to database items or show last n items. The main idea is relate all the items to one father page, then get a batch of page ids and their orders. All the methods can rank items by created time initially, and the last method can also work for last edited time.
Add Orders
Orders - son database
Initial Setting:
- Add a filter for son database. When add items, they will automatically relate to Father page. (If you have created some pages, you can select them all, right click, use
edit propertyto add relation to Father page in batch.
- Add formula property
id()(It is the PageID).
- Use the Father page to get the rollup of ids. Then the son pages rollup this value (because we cannot directly rollup a rollup property, we need to create a formula property in father db for son pages to rollup.
- Using Regex replace, calculate the rank of son page’s id (which is equals to the number of characters before it / 32 + 1).
Notice:
- The order is the same as relation order to Father page, which is created time by default. You can drag them to reorder.
- I add
round()to order formula because when we add a new item, the update of rollup may delay for a while. But since there are no match of this id, the result will + 0.9.... After round, it will be + 1, which is the correct result.
Show last n items
Here I use last 3 items as an example to show two ways.
Way 1: Get ids of last 3 items, judge if included
Way 1 of last 3 items - son db
Show all
Way 1 of last 3 items - father db
Name
relate to son
id_rollup
ids of last 3 items
Initial Setting:
- Add a filter for son database. When add items, they will automatically relate to Father page.
- Add formula property
id()
- Use the Father page to get the rollup of ids.
- In
ids of last 3 itemsformula, edit the last3to number of pages you want to show. Then we can usesliceto get ids of last 3 items.
- Rollup
ids of last 3 itmesin son pages. Add formulaLast 3 itemsto judge id in last 3 items or not.
- Add a filter:
Last 3 itemsis true.
Way 2: Get max timestamp one by one (can use in created time, last edited time or manual time)
Way 2 of last 3 items - son db
Show all
Way 2 of last 3 items - father db
Initial Setting:
- Add a filter for son database. When add items, they will automatically relate to Father page.
- Add created time (or last edited time, or manual time) and generate
timestamp().
- Get the max timestamp using Father page (rollup - Calculate - Max).
- Judge if equals to timestamp of son page. Get the max timestamp and label to 0.
- repeat step 3, 4 n times.
- items labeled 0 are the last n items
Notice: This method require different timestamps for every items (means the time property can not be the same). Possible improvement: add data to last four digits, like add different numbers manually, add the length of Name property, convert content of Name to numbers... Just need to keep every “timestamp” different.