Hi, folks!
Is there a “codan” way for a Pack formula that receives a table column as a param to evaluate just once and return an array with as many values as rows in the table that are actually written in their corresponding rows? I am pondering a port of this Apps Script moving average custom function but can’t find an obvious way to fit this behaviour in the way Packs work 
.
Not sure whether this makes sense in Coda at all, maybe I am too biased by my appsscripting background.
hi @Pablo_Felip
I know close to nothing (yet) about packs, but when you write something like this on the canvas (after pressing “=”, to open the formula editor:
tableName.columnName (thus the name of the table and then the name of the column)
hope it helps, cheers, chistiaan
Thanks for your suggestion, Christiaan.
Pushing the whole column into the Packs function is not a problem in itself, but if it returns an array, the result does not extend the same as (for instance) when using ArrayFormula() in Google Sheets just because of the nature of Coda’s formulas (this is just a test formula that simply returns the sum of the current value plus the one in the row immediately above:
You can easily code around this problem, but I feel that the result is not acceptable, not only because an extra param is needed to identify the row (I think ) but mainly because the formula gets unnecesarily evaluated as many times as rows in the table.
I think you need to return an object that Coda can understand as a table and that would be a schema. But even if this does return the format suitable for creating a table, it may not be useful for your business requirements. One must ask - what will you do with the table once it’s been created? Simply pass updates to it? If so, a schema is probably the right pathway.
If you intended to directly modify the cells in the generated table, a schema/synctable may not work for you. In that case, I think you need to use the Pack to update a table using the API.
I hope there are others in the community who will jump in because I’m not an expert in these matters.
hi @Pablo_Felip ,
is your issue is with the Index as the outcome of this thisTable.Find(thisRow) ?
as you know this one generates an ordered list of consecutive numbers. You can use Rank() to have the same result, but the issue remains I am afraid
When you create a new table, sometimes people use the Value For New rows logic to generate a list of consecutive numbers via thisTable.columnName.Max()+1,but I am not sure if this is any help for you.
Cheers, Christiaan
I’ve never implemented a sync table, but I understand that they are typically used to display cohesive data from, for instance, an external service. This is not my case, I just need to show a calculated column next to its source data column, as shown in this demo video:
Well, I will design my custom formula in the codan way and calculate one value (row) at a time.
execute: async function ([vector, rowIndex], context) {
return rowIndex == 1 ? vector[0] ?? 0 : (vector[rowIndex - 1] ?? 0) + (vector[rowIndex - 2] ?? 0);
},
The original code uses iterative calculations in some types of moving averages for better efficiency, let’s see if I still can take advantage of it in this implementation, but I doubt it.
However, I still have to pass the row index to the formula (rowIndex param), which seems quite dirty. There must be some other way to do this, will double-check the Packs SDK (and also the sync tables), I am sure I must have skipped some relevant information.
Not really, unfortunately. In fact Rank() is not working for me because values may repeat and this function returns the position of the first occurrence. My custom formula needs to know which row is being calculated every time it is called to be able to get the rest of the values needed to perform the calculation of the moving average for that data point.
you are right @Pablo_Felip ,
I see two alternatives that only work when you create new rows from scratch:
Coda has no function that automatically provides a list with the numbers you see in light grey on the left side of the table.
cheers, Christiaan
Thanks for confirming, @Christiaan_Huizer.
Still not sure about how I will implement this, a sync table is probably the cleanest way, as suggested by @Bill_French, since it does not require a helper index parameter and I could perform all calculations in one go, it seems, but does not fit my original goal on the whole… and I don’t find in the SDK docs any hint to do it some other way.
Will follow up if/when I have something to share, thank you both for your help.
Hi @Pablo_Felip - Nice to see you in the community! As to your original question, it isn’t possible for a single formula to return multiple rows like it is in Sheets. This is the case for a few reasons:
Find() as you’ve found to get an index, but in general Coda tables are more like a collection of records than a grid of cells, making a spatial-based overflow complicated.I don’t think a sync table is a good workaround for this sort of problem, but I don’t know your goal that well.
Thanks for the warm welcome and also for clarifying this, @Eric_Koleda.
I understand that my original intent simply does not fit well in the way Coda tables work. For now I think then that I’ll resort to a formula that receives the whole column, to “see” the whole series of input data, and a second index parameter to be able to return just the calculated moving average value corresponding to every data point.
It is a pity, because some types of MAs can be more efficiently calculated iteratively
result[n] = f(result[n-1])
instead of in the more straightforward way, that involves using previous data points
result[n] = f(data[n], data[n-1], data[n-2]...)
which is not possible unless a formula could return multiple rows.