Create a inventory column to a sales table

I created a sales table that records every sale instance as a new row. But I am struggling to formulate a new column, that shows the remaining inventory quantity after each sales instance. ie: I start with 100 units and then I’d like to reduce that number based on the quantity sold as every sales row is added. Probably this is a simple thing to do but I can’t produce the right formula.

Can anyone help?

HI Yalcin,

It is not all that simple, because first you need to determine the previous row, which is not as simple in Coda as it is in spreadsheet.

Ste one is to decide which column in your sales table to use as the index to sort the table. The simplest might be to just use the creation date property.
Then you use the rank formula, to assign a ranking to each row.
Next, in the sales rows, you can then use ranking of thisrow minus one to determine the previous row and find the balance after the previous sale.

here is a more complete explanation of the different sort orders in a table, as well as an example of applying rank() to a table, and then use that t o cycle through the entries:

Regards
Rambling Pete

1 Like

I can’t believe it’s that complicated but I think you’re right. So trying to do it in a single table is the major hurdle - is that right? (because of the fact that the basic unit is a row, rather than a cell)

On another note - do you think having a different approach would work - ie: following up on the status of the inventory level quantity in another table? If yes, would that be more straightforward to formulate?

Hi Yalcin

The problem is having a running total. Whether it is one or multiple tables I don’t think is going to make a difference.

I cannot think of a different approach: you will always need to reference the old balance to get to a new balance.

P

Hey, @Yalcin_Arsan.
I’d definitely use another approach. Referencing the previous cell is something you should avoid at all cost.
Leave it for Excel users

What I do in my doc is whenever I add a row in the inventory table, I subtract the value 1 from another table where I keep the inventory level.
You need to use buttons and the formula bellow to perform more than one action:
runActions(table1.addRow… , table2.modifyRows…)

2 Likes

hi @Yalcin_Arsan ,

I agree with @Breno_Nunes that it is for multiple reasons better to split the sales and stock table and use buttons with a modifyRow logic.

You can create a set up as suggested by @Piet_Strydom , it is not too difficult

However it is an expensive calculation, each time all rows in the table are evaluated to generate the rowIndex and it will slow down your doc significantly over time when your table grows:

thisTable.Find(thisRow)

About how to modify a row, you can have a look below: :arrow_heading_down:

The buttons keep the doc also fast, you only press them (activate them) when needed, for the rest of the time the code stored in the buttons sleeps and does not consume anything.

Cheers Christiaan

3 Likes

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.