Iterative counter help

1

Hello community,

I’m seeking some assistance building an iterative counter for a production log.

I have two tables of data. One table, Requests, collects data from a Sheets form submitted by customers. Each request submitted by the form contains a date stamp. I’m trying to calculate a running tally based on the year in the date stamp.

Each request received in 2019 would increase the counter by 1. Until 2020, when the counter will reset to 1.

This is what I have in mind: 20190001, 20190002, … 20190439, 20200001.

I’ve scoured the forums and found some filters and count formulas, but what I’ve written tallies the total number of requests as the same number in every row. I’m hoping to show the request sequence number uniquely in each row as a display column for a product tracker.

The Products table is a product tracker and operates works in a similar way as the Request table. Instead on a date stamp, I’m hoping to create product ids based on a deadline’s year and month plus an iterative counter for the deadline. Again, deadlines assigned in different months would reset the tally and iterate base on the month.

Like so: 19JAN001, 19JAN002, 19JAN003, 19FEB001, 19JAN004, 19FEB002

I’m providing an example frame to visualize what I’m hoping to achieve.

I really appreciate any help or ideas, truly, thank you!

2

Here you go.

The key formulas area:

RowID: thisRow.RowId()
Rank In Year: Rank(RowID, thisTable.Filter(Year=thisRow.Year).RowID)
RequestID: Format("{1}{2}", Year(Datestamp), LeftPad([Rank in Year],4,0 ))
1 Like
3

@James_Stewart

Here’s another approach, tested to make sure the rows could be moved without the counter changing.

4

@shishir,

Astonishing work!

Thank you for your help sir!

5

@Ander,

Thank you for your help sir!

Your mentioned a caution about the unique IDs, can you elaborate more?

6

@James_Stewart

Is Requests.Datestamp a unique value that’s being pulled in from some other data source, such that no two Datestamps will ever be the same?

7

I will use a Google Sheets spreadsheet to collect form inputs from users. It’s highly unlikely two datestamps will have the same date and time.

8

RowID() is guaranteed to be unique per row so the first solution should handle that.

9

@shishir

I also used RowId().

rowid_2019-02-20_1045
rowid_2019-02-20_1045.png997×454 37 KB

10

Ah I misread it. Yes that should work too!

1 Like
11

@James_Stewart @shishir

To avoid overcomplicating things, I was going to cut myself off here about unique row values, and just suggest that you be aware that it may become an issue at some point.

But since @shishir chimed in, I’ll go ahead and leave my thoughts because I want to really understand this.

So here’s what I was going to say:

Every RowId() is unique under the hood, but that has zero meaning to the people in your business – unlike the iterative counter code that you are building, which is perfectly understandable to the people in your business.

Nothing else in a table is unique unless you force it to be unique.

I assume your iterative counter code is also intended to be unique. However, it’s being built from columns which themselves are not required to be unique, so you can get duplicates, which I don’t think you want. In fact, your Products table currently has duplicates, which is why I had to incorporate RowId into the [month counter] formula – because it is a value that is unique to the row.

The problem with RowId(), as many people have discussed in this forum, is that it is somewhat arbitrary relative to one’s business logic. To my understanding it’s based on when a row is created relative to other rows. In a locked down transactional database, that’s generally what you want. But Coda’s not really to that point yet.

In the updated doc below, study the yellow column vs the blue column. Note that Rows 2 & 3 have duplicate Deadlines. Drag them to switch their positions and watch what happens to the different counters.

So, it depends on your needs. If you or someone you trust is going to administer your app, this becomes less important. (Although, even when it’s just me, I prefer locking things down, so that I don’t have to manually manage data hygiene.) However, if you are going to allow a team of employees to use this app, then you are 100% guaranteed to have data hygiene issues, and this particular one that we are discussing is likely to arise. How painful would that be to your business/customers if that were to happen? This might all be more academic than is justified by your current situation, or it might have an immediate significant impact. It’s probably a function of your transaction volume and/or financial value per transaction.

$.02

12

Right. The issue is the Find() function. This is actually a little known idiosyncrasy that I think our community discovered. Basically the Find() function uses the row position. This wasn’t quite intentional and there’s a lot of debate internally on whether it’s the right behavior.

Stepping back, your description of the challenge is very good: RowID() gives you a unique ID but it has no business meaning. And unless you have another system guaranteeing uniqueness, you have to generate a composite ID and use RowID() somewhere in the function. As a general practice, I’d probably advise against using Find() unless row position is something you directly want to key off of.

Here’s a quick example of the difference. Drag the rows around to see the behavior. Also change the dates to see how the Sort is applied in the last column.

4 Likes
13

@shishir

Thank you, that was very helpful.

14

You say that RowID is guaranteed to be unique per row, but I think it was Paul D who posted in another example where if you add rows quick enough, he wound up with duplicate rowid() numbers. So, has that issue been resolved?