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.
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?
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 )
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.
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 )
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.
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.
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.
Hi @nsgma !
Glad to know the samples above helped a little !
Do you mind sharing a screenshot of this problematic formula
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 )