How to store specific information of a pair of elements?

[Chao]

Say I have a table order, and a table item, one store information of orders, the other store information about items.

I now want to store an order has some number of items.

For example, in SQL world, I would have a table order_x_item, which has columns order_id, item_id, quantity.

In base row, all I can do is link order and item, so I know an order has a certain kind of item, but I have no idea how many.

Is there a way to make this work or it is impossible in the current baserow framework?

[frederikdc]

Hi,

Just like in SQL, Baserow has functions for sum(), avg() and count(). I think that the count function is the one you need.

Your order table will have a linked field to the item table. You can add a formula field to the order table and use the following formula: count(field(<name_of_the_linked_field>)). This will show the number of items that are linked to this order.

[Chao]

That does not solve the problem. Let’s consider the following case.

I have an order coming in, say it orders 10000 apples and 5000 oranges.

Count will give me a total of 2 item was bought, since there are only two kind of items, apple and orange. (note the table item just store the information about item, say, is_red, is_fruit, price etc).

But here I want to record the order has 10000 apples and 5000 oranges.

[frederikdc]

You will need an additional table that stores information about items in an order. I created a small simulation of how you can do this in baserow.

First there is the table with information about the items and a field that links to the table order_item

The table order_items contains. a link to an order and an item together with the quantity. The field Item_Name is a lookup field to make the data more readable. The field Summary is a formula field that concatenates the name of the item with the quantity.

Finally, I have the table orders. It contains a linked field to the order_items table. This content of this field is not readable, so I added a lookup field that refers to the summary field in the order_items table.