Get all rows from a table where a value is below a value

Wonder if somebody can help with this use-case

Table which I want to look up information from:

Item Priority
item1 3
item2 2
item3 1
item4 4
item5 5
item6 6
item7 8
item8 7
item9 9
item10 11
item11 10
item12 12

A rule tells that two items are occupied based on Priority column and cannot be chosen. Therefore items with priority 1 and 2 cannot be selected. The available items are therefore all items except item3 and item2.

I need to get the available rows to be used in another table. I want to display a comma separated list with all the available items. The list should be sorted alphabetically ascending order. item1, item4, item5, item6, item7, item8 etc.

Day1: Priority 1 and 2 cannot be chosen
Day2: Priority 1,2,3,4 cannot be chosen
Day3: Priority 1 cannot be chosen

Day14:Priority 1,2,3,4,5 cannot be chosen

I have considered using the Array Columns - but cannot get this working

Made a workaround - not very nice. Would like to do it a lot easier. So if you know how, please share :slight_smile:

Extra column added to table A


| Item| Priority| Priority_item|
| ----------- | ----------- | -----------|
| item1| 3| @03@item1|
| item2| 2| @02@item2|
| item3| 1| @01@item3|
| item4| 4| @04@item4|
| item5| 5| @05@item5|
| item6| 6| @06@item6|
| item7| 8| @08@item7|
| item8| 7| @07@item8|
| item9| 9| @09@item9|
| item10| 11|@11@item10|
| item11| 10|@10@item11|
| item12| 12|@12@item12|

Added a relation and lookup to get all items from Table A

Added a Array sort colum based on Priority_item which will get the items sorted alphabetically - meaning in priority order like :

@01@item3, @02@item2, @03@item1 etc

The Priority number was two (meaning taking out priority 1 and 2)
Added a Slice array column to go to third item (remember item numbers start at 0 - 0,1,2) in the array thereby removing the two first items

Added a Joined list to make it a comma separated list
Added a template column to remove @01@, @02,…@20@ with nothing

Result is:
item1, item4, item5, item6, item7, item8 etc

Would be great with an ORDER BY functionality

Can you explain why it changes to just “priority 1” here in the 3rd day?

I was thinking you can use a boolean column and make a relation based on a “true” value but I might need to understand your use case more.

Ya that’s what I was going to suggest:

1 Like

@ThinhDinh it has to do with reservation of items. On day 1 two people have made reservation. Day 2 three reservations. Day 3 one reservation.
Which items are available and in what sequence to make them available is based on the priority column. So on day3 item2 should be the first item in the list.

When I want to display the items in a comma separated list I’m challenged as the relation and lookup will just return the list in the order of the table and this is not sorted

If the table was sorted in priority order then the returned list would be fine. But the table is also used for other things and actually I would like the table to be sorted by items alphabetically as this makes the list easier to maintain

Does it make sense ?