[_THISROW_BEFORE] + [_THISROW_AFTER] IN VC

[Luis_Rodriguez_]

HI. Im having a mentalblock with the following situation:

Im recording the bills of the different sales I made in a day on a sheet. The rows can have the same bill number cause a client can buy many items. So I want a VC that show the position of that row in the bill and reset when another bill is registered. Like this:

item	name	document
1	apples	bill-1
2	banana	bill-1
3	pear	bill-1
1	apples	bill-2

Im using [_THISROW_BEFORE] + [_THISROW_AFTER] in the formula of the VC but it dosent work. its like just calling the same row or using [_THISROW] instead.

Dou you have any suggestion?

[WillowMobileSys]

Since a VC is not an "editable" column in the same way normal columns are, it will never have a BEFORE state.

[Sirfyaad · Accepted solution]

COUNT(
FILTER(
"Your_Table_Name",
AND(
[Bill Number] = [_THISROW].[Bill Number],
[_ROWNUMBER] <= [_THISROW].[_ROWNUMBER]
)
)
)

[scott192]

If document is a ref to the bill and the table is called documents.  And item is a number field on a table called items.  Then just put an initial formula for item that is COUNT([document].[Related Items])

It's quick and simple, but it will fall down should you start to delete items off the bill and add new ones...

[WillowMobileSys]

I didn't offer a solution before, was distracted and meant to come back.   

Yes!  As @scott192 suggests, I would recommend assigning the Item # at the time of entry as a separate "normal" column. You may need to include a "+ 1" to @scott192 's expression.

If you allow Deletes, then simply re-compute the Item number for the remaining rows using actions or a Bot.

[Luis_Rodriguez_]

THanks @WillowMobileSys , @scott192 and @Sirfyaad . your solution was accurate.