CountIf and count above or button

[nsgma]

Hello,
I started CODA yesterday and I was trying to have a column with an unique code per row.

I tried to count the number rows with a “starting year (column)” equal to the current row.
I tried:
countif(list(thisTable.[Starting year]),CurrentValue=thisRow.[Starting year])
and different variations but none returns anything correct. The best I could achive is the total number of rows. What would be the correct syntax to achieve that?

This would be the the first step on a more complex calculation.
I would want to know how many rows with the same “starting year” as the current row and are above the current row (or are older).
The aim would eb to generate a unique sequential code upon the creation of the row…

I understand that I am approaching this as in Excel, and since the tables can be sorted an filtered in different ways this kind of dynamic formulas may not be possible or useful.

So probably, I would need to create a button with a formula that produces the code on the moment of creation, but it results is not a formula but a string… Would this be the way to go?

thanks,N

[Terry_Stagg]

Firstly, Welcome to to Coda and the Maker Community!

@nsgma

I started CODA yesterday and I was trying to have a column with an unique code per row.

By this do you mean you want a randomly generated code unique for each row? I have a great solution for that if that’s what your needing.

[Pch]

Hi @nsgma and Welcome to te Community !

Ok so, one thing I can tell you is that on the long term, it is better to avoid the use of CountIf() if you can (as it’s better to build a doc to avoid performance issues from the start than having to optimize formulas later IMHO )

Source of the screenshot: Somewhere in Optimizing slow formulas in your doc | Coda Help Center

As for the rest… Could you share a sample/mock-up doc of your actual setup with us ?
(or at the very least, some screenshots)

It’ll be easier for us to help you if we can visualise where the problem might be

[Piet_Strydom]

HI nsgma

Welcome to Coda!

As Pch said above, it would be good to provide an example with the exact requirement.

If you are simply looking for a serial number in your table, here is a solution for that:

[nsgma]

Hello @Terry_Stagg , @Pch , @Piet_Strydom,
Thank you for your replies.
I was really enthusiastic for CODA for about 24 hours, then I realized that it only uses american date formats, so it immediately disqualified itself from contention as an usable tool for planning in most of the world. It is a pity.

I appreciate your help and the time you spent. I thought I had tried also the “filter” path to countif, but I may have not found the exact correct syntax.
What I was attempting was the automatic generation of a code is this format YYii where, YY is the year and ii is a sequential index. I guess a button that writes the code as a string, would be the way to do it, rather than a dynamic formula.

In any case, it does not matter, the date format is a dealbreaker.

Thank you once again. N

[Pch]

@nsgma

it only uses american date formats

Not exactly … Yes, US Date format is the default one but there is a bunch of other formats available as options (Including ISO and EU ones)
(I’m not saying that sometimes you don’t need to rely on a workaround or another to get a desired format though… and there are still improvements to be made when it comes to internationalisation … but options exist )

You can now also select the 1st day of the week too to the doc level and account level …

[nsgma]

Thanks, @Pch
I am back on board
I got confused by this European date formats default option
I couldn’t really understand how it was possible that date formats were not a part of CODA, but what they are actually saying is that it would be good to choose the format for the account, which makes sense.
N

[nsgma]

So here is an attempt to describe what I am trying.
In a table, I have a column that has a series of codes say: 2101, 2102, 2201, 2203, 2203
When I insert another row, I want this column to see 2204. However, when 2023 comes I want the first row in that year to be 2301.
N

[Pch]

Hi @nsgma !

Glad to know you’re back with us !

@nsgma

However, when 2023 comes I want the first row in that year to be 2301.

If I understand correctly your “code” is composed of the last 2 digits of the year and the number of that day in the year (From 1 → 365 or 366) ?

So :

Jan. 1 (2023) -> row number 2301
Jan. 2 (2023) -> row number 2302
Jan. 3 (2023) -> row number 2303
etc ... until the end of the year

This would give a more unique value to the “row number”

Is that it or something more like this where the “code” would be composed by the last 2 digits of the year and the Nth date for that year in the table

I can add explanations about the samples above if necessary … I just would like to know if I’m on the right track or not

[nsgma]

Hi Pch
This was really helpful. I had a working formula with a helper column because I was going the max( ) fuction route instead of count. The reason being that is some past years I may some gaps on the list (jumping from 1903 to 1908) and this way can manually insert a higher number and restart from there. But the filter is a very elegant way of not being bound to insert the year sequentially either.

However , I don’t seem to be able to make it work with max.

Projects.[ProjNr (helper)].Filter(Projects.[Starting year]=thisRow.[Starting year]).Max()
Projects…Filter(Projects.[Starting year]=thisRow.[Starting year]).Max(Projects.[ProjNr (helper)])

Any ideas?

[Pch]

Hi @nsgma !
Glad to know the samples above helped a little !

Do you mind sharing a screenshot of this problematic formula

@nsgma

Projects.[ProjNr (helper)].Filter(Projects.[Starting year]=thisRow.[Starting year]).Max()

Please understand that’s it’s really not easy to guess a proper answer without seeing anything from your actual setup .

It would really be helpful if you could share a dummy doc (a copy of your actual doc reproducing its setup but with anonymised datas) so we could see what you’re trying to accomplish and finally help you out !
(Screenshots can help too though … But sharing a dummy doc is always the best way to go )