Understanding CurrentValue and use of List().First()

1

Hello,

I recently got some advice about an automation I was running that was resulting in “Exception Occurred” error when the automation would fail every day. I got advice regarding two unrelated topics: List().First() and CurrentValue in my function. I would love to get more insight on this.

(1)

For CurrentValue, I was told:

image
image528×715 22.5 KB

This didn’t make sense to me, as being MORE specific about which value you are talking about (thisRow versus CurrentValue) seems like it would help Coda so it doesn’t have to take the time to decide which I am referring to. If you have insight on the suggestion given to me I would love to learn more, as this would definitely need to be updated elsewhere in my doc if it is indeed unhelpful to specify CurrentValue.

The idea is possibly that CurrentValue somehow slows the calculation since it already assumes it is looking for rows in CurrentValue. Therefore, I am going to go into all my Formulas and remove “CurrentValue” from all of them, if I am referencing a Column in that row. For instance all CurrentValue = X can stay, but CurrentValue.ColumnName = X must now become ColumnName = X.

(2)

For List().First() I was told to use “” instead. I’ll have to look more into my browser history to find why I started using List().First(), but if my memory serves me correct, it was because “” was causing issues for me when it was expecting a table row in a result instead of a text value.

image
image1163×207 36.4 KB

Any insight would be appreciated, thanks!

2

hi @N_G ,

I read your question and since this week I wrote a function with CurrentValue as response to a question I took some time to describe the logic behind the function. Maybe it helps you in gaining a better understanding of how CurrentValue is applied:

best, Christiaan

2 Likes
3

Nice and very valuable explanation @Christiaan_Huizer :trophy:

4

Hey @N_G ,

I don’t think that the two issues you pointed out were the culprit why the automation failed, but these are valid to discuss. Where did you get that reply from btw?

  1. What I think the person was trying to tell you is this. If you want to get an item from the table that you already have a reference to, you don’t have to query (Filter()) the whole table again. You can simply use that reference to get what you need.

    I.e., instead of

    Tasks.Filter(CurrentValue = thisRow.Parent.GeneratedTask).First()

    you can simply write

    thisRow.Parent.GeneratedTask

    (given that GeneratedTask is a row reference and not a text value or so)

    Also it’s a bad idea to compare items by values of some text column. If you have two tasks in the system with the same Task name, such filter could return you the wrong one. Store/look up row references and compare by row references instead.

    CurrentValue doesn’t slow the calculation (it doesn’t go anywhere btw; when you type ColumnName you actually get CurrentValue.ColumnName most likely — just click the piece of the formula and see what it reads below). But filtering over the whole table of Tasks to find the one that you’ve already referenced or could have referenced — yes, it does slow down the calculation.

  2. You can safely use "" to denote a blank value, and it will be properly treated by coda as Blank. In column formulas it shouldn’t cause any issues. In canvas formulas you can have issues because Coda won’t be able to figure out the type, but neither it would with an untyped List(). It’s best to avoid working with blank states in canvas formulas overall and calculate these things in helper tables instead.

1 Like
5

Thank you for your replies @Paul_Danyliuk and @Christiaan_Huizer!

I do believe I understand how CurrentValue is applied. In your example CurrentValue is a Number/Text/Date. I am having trouble understanding a possible performance boost when dealing with CurrentValue as a row reference (When used against a list of rows such as Table.Filter([Filter that deals with CurrentValue]).FormulaMap([Output/Action that deals with CurrentValue])).

Specifically, when dealing with columns of a row reference to compare (in the case of .Filter() ) or to output (in the case of .FormulaMap() ), is there a performance boost in not specifying CurrentValue if it is already implied/assumed by Coda.

For instance:

ListOfTasksTable.Filter(CurrentValue.TaskName.Find(“Submit”))
versus
ListOfTasksTable.Filter(TaskName.Find(“Submit”))

This statement made me believe the answer it yes, unless I am misunderstanding their suggestion:
image

6

@Paul_Danyliuk , regarding " already have a reference to, you don’t have to query ( Filter() ) the whole table again", my function does seem to be doing this out of context, but what I have is a Table called TaskGenerator and a Table called Tasks.

TaskGenerator

  • Name: “Form 1040”, Parent: null, GeneratedNameIfCreateNow: [=thisRow.Name + " " + Today().Year()]
  • Name: “Create Form”, Parent: (reference to “Form 1040”)
  • Name: “Submit Form” Parent: (reference to “Form 1040”)

Tasks

  • Name: “Form 1040 2019”, Parent: null
  • Name: “Create Form”, Parent: (reference to “Form 1040 2019”)
  • Name: “Submit Form” Parent: (reference to “Form 1040 2019”)
  • Name: “Form 1040 2020”, Parent: null
  • Name: “Create Form”, Parent: (reference to “Form 1040 2020”)
  • Name: “Submit Form” Parent: (reference to “Form 1040 2020”)

When I click “Generate” in 2021, it will create a Task called “Form 1040 2021” from the TaskGenerator, and then it will create “Create Form”, and the Parent of “Create Form” is a template in TaskGenerator, but I need the parent of the Task “Create Form” to be the Task that was generated FROM the TaskGenerator row “Form 1040”, not the row itself… since I want its parent to be “Form 1040 2021” not “Form 1040 2019” so when I use Parent in my formula, one is TaskGeneratorParent and the other is TaskParent, and those are references to rows in two different tables.

7

It is hard for me to visualize this without having access to the doc. Mind sharing your setup either here publicly or privately with paul@codatricks.com?

The general rule of thumb is that you should avoid matching rows from different tables by values of some column, unless there are good reasons for that and you know what you’re doing (e.g. cross-doc scenarios or big data setups with need for archiving.) Instead you store references to other rows and read data from those references as needed.

Those two are the same, so no performance impact at all. CurrentValue is already there, just not displayed in your formula. By using it explicitly you just add yourself some clarity and disambiguation between thisRow.Column and CurrentValue.Column. BTW sometimes the formula editor would erroneously use the former instead of the latter, so your Column = thisRow.Column would actually be thisRow.Column = thisRow.Column, which is always true. Explicitly typing CurrentValue helps avoid this.

In your formula better type:

ListOfTasksTable.Filter(CurrentValue.TaskName.Find(“Submit”) != -1)

because Find returns a number (position of found substring or -1 if not found), and you don’t want any implicit conversion to boolean for that. I wouldn’t be sure that -1 auto-converted to false. AFAIK only 0 equals false, so you may have false trues. It’s another rule of thumb to not rely on implicit type conversions.

Comparing rows means comparing table/row IDs (text values basically). Comparing names from rows means reading those values from each row (dereferencing a column from a row) and also comparing text values. So even if your task names are unique, there’s still this added step of dereferencing a value from a row.

Also it seems like in your scenario (for a row in table A, rows in tables B and C are created when a button is clicked, and those rows correspond to that row in table A for the entire lifetime), it seems like you could ditch live Filter formulas entirely and simply save recently created rows into a Lookup cell of that row in table A. In your button you can do things like:

thisRow.ModifyRows(
  thisRow.RecordInTableB, TableB.AddRow(...),
  thisRow.RecordInTableC, TableC.AddRow(...)
)

i.e. — create a row in table B, create a row in table C, and save links to those rows into corresponding cells of thisRow where the button is clicked. Now that will be a performance boost because no cells will be unnecessarily recalculated when new rows are added to tables B and C.

8

@Paul_Danyliuk I created a new post with an example of CurrentValue breaking a formula. It is relevant to this post: Case where using currentvalue breaks the formula results - Misc - The Coda Community

If you open the example provided in that post, at the bottom I quote statements from the Coda team that would be nice to clarify regarding CurrentValue performance and reliability.