Value counter in a column

[asif_raeen]

Please see the number pattern in the $Parent column. Its “variable” counter starts from value 5001 but I want this to fill in every value cell down the column until there is another instance of “variable” in the $Type column.

Please guide me to achieve this with a formula in grist?

[Travitron]

That may be possible if you can guarantee the sort order of your records, but my gut is that the value you want in the Parent column is better suited to be done in a Summary table. I.e., create a Summary table grouped by Type. Or, you could create a lookup table for the type, and then also have the Parent value assigned to each type (and could more-easily increment it there, since you could ensure that the Type is unique and sorted. Then, you could make the Type in this T-SHIRT table a Reference column from that new lookup table, and the Parent column here could also pull from that lookup table, like $LookupTable.Number.

[natalie-grist]

Hi Asif!

I have a solution for you here: Community #6149 - Grist

The formula in the Parent column is

previous_record = PREVIOUS(rec, order_by=None)
if $id == 1:
  return 5001
if $Type == "variable":
  return previous_record.Parent + 1
else:
  return previous_record.Parent

We use the PREVIOUS() function to find the previous record in the table.

First, we set the condition if the record ID is 1, then return 5001 as our Parent number. This let’s us start off with the desired value.

Next, if the value in the Type column is variable, we want to increase our Parent number by 1. So we check the value in the Parent column of the previous record then add 1.

Finally, if both statements above are false (it is not the first record and the Type is not variable), then we simply return the value from the Parent column from the previous record.

Thanks!
Natalie