How to get `Nth` to return `Blank` if index is not found

Yeah, this specific lookup is likely not more efficient. When I’m back at my computer I’ll have to look back at what I thought would be more efficient.

Yes, an if statement solves the problem at the price of elegance.

So apparently it is working now. In my memory, this formula brought up an error before, and now it still has an error but continues the calculation.

thisRow.Amount+thisTable.Nth(Find(thisRow,thisTable)-1).[Running Total]

The inelegant if statement is gone :slight_smile:

(Updated my doc above.)

So @cnr you are telling me that my three formulae in this doc are equally efficient?

For my understanding, The Running Total is the delta between N and N+1 ?

if that is the case an alternative might be:

thisTable.[Running Total].Slice(1,Find(thisRow,thisTable)).Sum()`

best, Christiaan

Still haven’t had a chance to play with this. I’ll get back to you

But yes, it is just the first row that returns the error, as you note

Yes. Assuming “Running Total” is the name of the column with the raw amounts (ie, not a self-reference) - in my table the column called “Amount”.

But yes, that’s a more elegant solution :slight_smile:

1 Like

Once again need Nth to return Blank if index not found.

This time it’s because I have an input list of unknown length, and an output list that has to be 130 entries long.


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6


  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6


How do I make it so that when I iterate through my list with:

Sequence(1, 130).FormulaMap(

it doesn’t return this error:

One way this could work is Nth could have a second parameter which is what it returns if the index is not found. E.g.
List(1,2,3,4).Nth(5, false) would return false.

I’m just wrapping in an If():

  X <= List.Count(),

In my actual use case that won’t work. Actual formula is this horrible thing:

  [All Outputs].AddRow(
    [All Outputs].[CSV Source], thisRow,
    [All Outputs].Header, true,
    [All Outputs].[Column 1], thisRow.Table.Nth(1).Nth(1),
    [All Outputs].[Column 2], thisRow.Table.Nth(1).Nth(2),
    [All Outputs].[Column 3], thisRow.Table.Nth(1).Nth(3),
    [All Outputs].[Column 4], thisRow.Table.Nth(1).Nth(4),
    [All Outputs].[Column 5], thisRow.Table.Nth(1).Nth(5),
    [All Outputs].[Column 6], thisRow.Table.Nth(1).Nth(6),
    [All Outputs].[Column 7], thisRow.Table.Nth(1).Nth(7),
    [All Outputs].[Column 8], thisRow.Table.Nth(1).Nth(8),
    [All Outputs].[Column 9], thisRow.Table.Nth(1).Nth(9),
    [All Outputs].[Column 10], thisRow.Table.Nth(1).Nth(10),
    [All Outputs].[Column 11], thisRow.Table.Nth(1).Nth(11),
    [All Outputs].[Column 12], thisRow.Table.Nth(1).Nth(12),
    [All Outputs].[Column 13], thisRow.Table.Nth(1).Nth(13),
    [All Outputs].[Column 14], thisRow.Table.Nth(1).Nth(14),
    [All Outputs].[Column 15], thisRow.Table.Nth(1).Nth(15),
    [All Outputs].[Column 16], thisRow.Table.Nth(1).Nth(16),
    [All Outputs].[Column 17], thisRow.Table.Nth(1).Nth(17),
    [All Outputs].[Column 18], thisRow.Table.Nth(1).Nth(18),
    [All Outputs].[Column 19], thisRow.Table.Nth(1).Nth(19),
    [All Outputs].[Column 20], thisRow.Table.Nth(1).Nth(20),
    [All Outputs].[Column 21], thisRow.Table.Nth(1).Nth(21),
    [All Outputs].[Column 22], thisRow.Table.Nth(1).Nth(22),
    [All Outputs].[Column 23], thisRow.Table.Nth(1).Nth(23),
    [All Outputs].[Column 24], thisRow.Table.Nth(1).Nth(24),
    [All Outputs].[Column 25], thisRow.Table.Nth(1).Nth(25),
    [All Outputs].[Column 26], thisRow.Table.Nth(1).Nth(26),
    [All Outputs].[Column 27], thisRow.Table.Nth(1).Nth(27),
    [All Outputs].[Column 28], thisRow.Table.Nth(1).Nth(28),
    [All Outputs].[Column 29], thisRow.Table.Nth(1).Nth(29),
    [All Outputs].[Column 30], thisRow.Table.Nth(1).Nth(30),
    [All Outputs].[Column 31], thisRow.Table.Nth(1).Nth(31),
    [All Outputs].[Column 32], thisRow.Table.Nth(1).Nth(32),
    [All Outputs].[Column 33], thisRow.Table.Nth(1).Nth(33),
    [All Outputs].[Column 34], thisRow.Table.Nth(1).Nth(34),
    [All Outputs].[Column 35], thisRow.Table.Nth(1).Nth(35),
    [All Outputs].[Column 36], thisRow.Table.Nth(1).Nth(36),
    [All Outputs].[Column 37], thisRow.Table.Nth(1).Nth(37),
    [All Outputs].[Column 38], thisRow.Table.Nth(1).Nth(38),
    [All Outputs].[Column 39], thisRow.Table.Nth(1).Nth(39),
    [All Outputs].[Column 40], thisRow.Table.Nth(1).Nth(40),
    [All Outputs].[Column 41], thisRow.Table.Nth(1).Nth(41),
    [All Outputs].[Column 42], thisRow.Table.Nth(1).Nth(42),
    [All Outputs].[Column 43], thisRow.Table.Nth(1).Nth(43),
    [All Outputs].[Column 44], thisRow.Table.Nth(1).Nth(44),
    [All Outputs].[Column 45], thisRow.Table.Nth(1).Nth(45),
    [All Outputs].[Column 46], thisRow.Table.Nth(1).Nth(46),
    [All Outputs].[Column 47], thisRow.Table.Nth(1).Nth(47),
    [All Outputs].[Column 48], thisRow.Table.Nth(1).Nth(48),
    [All Outputs].[Column 49], thisRow.Table.Nth(1).Nth(49),
    [All Outputs].[Column 50], thisRow.Table.Nth(1).Nth(50),
    [All Outputs].[Column 51], thisRow.Table.Nth(1).Nth(51),
    [All Outputs].[Column 52], thisRow.Table.Nth(1).Nth(52),
    [All Outputs].[Column 53], thisRow.Table.Nth(1).Nth(53),
    [All Outputs].[Column 54], thisRow.Table.Nth(1).Nth(54),
    [All Outputs].[Column 55], thisRow.Table.Nth(1).Nth(55),
    [All Outputs].[Column 56], thisRow.Table.Nth(1).Nth(56),
    [All Outputs].[Column 57], thisRow.Table.Nth(1).Nth(57),
    [All Outputs].[Column 58], thisRow.Table.Nth(1).Nth(58),
    [All Outputs].[Column 59], thisRow.Table.Nth(1).Nth(59),
    [All Outputs].[Column 60], thisRow.Table.Nth(1).Nth(60),
    [All Outputs].[Column 61], thisRow.Table.Nth(1).Nth(61),
    [All Outputs].[Column 62], thisRow.Table.Nth(1).Nth(62),
    [All Outputs].[Column 63], thisRow.Table.Nth(1).Nth(63),
    [All Outputs].[Column 64], thisRow.Table.Nth(1).Nth(64),
    [All Outputs].[Column 65], thisRow.Table.Nth(1).Nth(65),
    [All Outputs].[Column 66], thisRow.Table.Nth(1).Nth(66),
    [All Outputs].[Column 67], thisRow.Table.Nth(1).Nth(67),
    [All Outputs].[Column 68], thisRow.Table.Nth(1).Nth(68),
    [All Outputs].[Column 69], thisRow.Table.Nth(1).Nth(69),
    [All Outputs].[Column 70], thisRow.Table.Nth(1).Nth(70),
    [All Outputs].[Column 71], thisRow.Table.Nth(1).Nth(71),
    [All Outputs].[Column 72], thisRow.Table.Nth(1).Nth(72),
    [All Outputs].[Column 73], thisRow.Table.Nth(1).Nth(73),
    [All Outputs].[Column 74], thisRow.Table.Nth(1).Nth(74),
    [All Outputs].[Column 75], thisRow.Table.Nth(1).Nth(75),
    [All Outputs].[Column 76], thisRow.Table.Nth(1).Nth(76),
    [All Outputs].[Column 77], thisRow.Table.Nth(1).Nth(77),
    [All Outputs].[Column 78], thisRow.Table.Nth(1).Nth(78),
    [All Outputs].[Column 79], thisRow.Table.Nth(1).Nth(79),
    [All Outputs].[Column 80], thisRow.Table.Nth(1).Nth(80),
    [All Outputs].[Column 81], thisRow.Table.Nth(1).Nth(81),
    [All Outputs].[Column 82], thisRow.Table.Nth(1).Nth(82),
    [All Outputs].[Column 83], thisRow.Table.Nth(1).Nth(83),
    [All Outputs].[Column 84], thisRow.Table.Nth(1).Nth(84),
    [All Outputs].[Column 85], thisRow.Table.Nth(1).Nth(85),
    [All Outputs].[Column 86], thisRow.Table.Nth(1).Nth(86),
    [All Outputs].[Column 87], thisRow.Table.Nth(1).Nth(87),
    [All Outputs].[Column 88], thisRow.Table.Nth(1).Nth(88),
    [All Outputs].[Column 89], thisRow.Table.Nth(1).Nth(89),
    [All Outputs].[Column 90], thisRow.Table.Nth(1).Nth(90),
    [All Outputs].[Column 91], thisRow.Table.Nth(1).Nth(91),
    [All Outputs].[Column 92], thisRow.Table.Nth(1).Nth(92),
    [All Outputs].[Column 93], thisRow.Table.Nth(1).Nth(93),
    [All Outputs].[Column 94], thisRow.Table.Nth(1).Nth(94),
    [All Outputs].[Column 95], thisRow.Table.Nth(1).Nth(95),
    [All Outputs].[Column 96], thisRow.Table.Nth(1).Nth(96),
    [All Outputs].[Column 97], thisRow.Table.Nth(1).Nth(97),
    [All Outputs].[Column 98], thisRow.Table.Nth(1).Nth(98),
    [All Outputs].[Column 99], thisRow.Table.Nth(1).Nth(99),
    [All Outputs].[Column 100], thisRow.Table.Nth(1).Nth(100),
    [All Outputs].[Column 101], thisRow.Table.Nth(1).Nth(101),
    [All Outputs].[Column 102], thisRow.Table.Nth(1).Nth(102),
    [All Outputs].[Column 103], thisRow.Table.Nth(1).Nth(103),
    [All Outputs].[Column 104], thisRow.Table.Nth(1).Nth(104),
    [All Outputs].[Column 105], thisRow.Table.Nth(1).Nth(105),
    [All Outputs].[Column 106], thisRow.Table.Nth(1).Nth(106),
    [All Outputs].[Column 107], thisRow.Table.Nth(1).Nth(107),
    [All Outputs].[Column 108], thisRow.Table.Nth(1).Nth(108),
    [All Outputs].[Column 109], thisRow.Table.Nth(1).Nth(109),
    [All Outputs].[Column 110], thisRow.Table.Nth(1).Nth(110),
    [All Outputs].[Column 111], thisRow.Table.Nth(1).Nth(111),
    [All Outputs].[Column 112], thisRow.Table.Nth(1).Nth(112),
    [All Outputs].[Column 113], thisRow.Table.Nth(1).Nth(113),
    [All Outputs].[Column 114], thisRow.Table.Nth(1).Nth(114),
    [All Outputs].[Column 115], thisRow.Table.Nth(1).Nth(115),
    [All Outputs].[Column 116], thisRow.Table.Nth(1).Nth(116),
    [All Outputs].[Column 117], thisRow.Table.Nth(1).Nth(117),
    [All Outputs].[Column 118], thisRow.Table.Nth(1).Nth(118),
    [All Outputs].[Column 119], thisRow.Table.Nth(1).Nth(119),
    [All Outputs].[Column 120], thisRow.Table.Nth(1).Nth(120),
    [All Outputs].[Column 121], thisRow.Table.Nth(1).Nth(121),
    [All Outputs].[Column 122], thisRow.Table.Nth(1).Nth(122),
    [All Outputs].[Column 123], thisRow.Table.Nth(1).Nth(123),
    [All Outputs].[Column 124], thisRow.Table.Nth(1).Nth(124),
    [All Outputs].[Column 125], thisRow.Table.Nth(1).Nth(125),
    [All Outputs].[Column 126], thisRow.Table.Nth(1).Nth(126),
    [All Outputs].[Column 127], thisRow.Table.Nth(1).Nth(127),
    [All Outputs].[Column 128], thisRow.Table.Nth(1).Nth(128),
    [All Outputs].[Column 129], thisRow.Table.Nth(1).Nth(129),
    [All Outputs].[Column 130], thisRow.Table.Nth(1).Nth(130)
    [All Outputs].AddRow(
      [All Outputs].[CSV Source], thisRow,
      [All Outputs].[Column 1], thisRow.Table.Nth(1),
      [All Outputs].[Column 2], thisRow.Table.Nth(2),
      [All Outputs].[Column 3], thisRow.Table.Nth(3),
      [All Outputs].[Column 4], thisRow.Table.Nth(4),
      [All Outputs].[Column 5], thisRow.Table.Nth(5),
      [All Outputs].[Column 6], thisRow.Table.Nth(6),
      [All Outputs].[Column 7], thisRow.Table.Nth(7),
      [All Outputs].[Column 8], thisRow.Table.Nth(8),
      [All Outputs].[Column 9], thisRow.Table.Nth(9),
      [All Outputs].[Column 10], thisRow.Table.Nth(10),
      [All Outputs].[Column 11], thisRow.Table.Nth(11),
      [All Outputs].[Column 12], thisRow.Table.Nth(12),
      [All Outputs].[Column 13], thisRow.Table.Nth(13),
      [All Outputs].[Column 14], thisRow.Table.Nth(14),
      [All Outputs].[Column 15], thisRow.Table.Nth(15),
      [All Outputs].[Column 16], thisRow.Table.Nth(16),
      [All Outputs].[Column 17], thisRow.Table.Nth(17),
      [All Outputs].[Column 18], thisRow.Table.Nth(18),
      [All Outputs].[Column 19], thisRow.Table.Nth(19),
      [All Outputs].[Column 20], thisRow.Table.Nth(20),
      [All Outputs].[Column 21], thisRow.Table.Nth(21),
      [All Outputs].[Column 22], thisRow.Table.Nth(22),
      [All Outputs].[Column 23], thisRow.Table.Nth(23),
      [All Outputs].[Column 24], thisRow.Table.Nth(24),
      [All Outputs].[Column 25], thisRow.Table.Nth(25),
      [All Outputs].[Column 26], thisRow.Table.Nth(26),
      [All Outputs].[Column 27], thisRow.Table.Nth(27),
      [All Outputs].[Column 28], thisRow.Table.Nth(28),
      [All Outputs].[Column 29], thisRow.Table.Nth(29),
      [All Outputs].[Column 30], thisRow.Table.Nth(30),
      [All Outputs].[Column 31], thisRow.Table.Nth(31),
      [All Outputs].[Column 32], thisRow.Table.Nth(32),
      [All Outputs].[Column 33], thisRow.Table.Nth(33),
      [All Outputs].[Column 34], thisRow.Table.Nth(34),
      [All Outputs].[Column 35], thisRow.Table.Nth(35),
      [All Outputs].[Column 36], thisRow.Table.Nth(36),
      [All Outputs].[Column 37], thisRow.Table.Nth(37),
      [All Outputs].[Column 38], thisRow.Table.Nth(38),
      [All Outputs].[Column 39], thisRow.Table.Nth(39),
      [All Outputs].[Column 40], thisRow.Table.Nth(40),
      [All Outputs].[Column 41], thisRow.Table.Nth(41),
      [All Outputs].[Column 42], thisRow.Table.Nth(42),
      [All Outputs].[Column 43], thisRow.Table.Nth(43),
      [All Outputs].[Column 44], thisRow.Table.Nth(44),
      [All Outputs].[Column 45], thisRow.Table.Nth(45),
      [All Outputs].[Column 46], thisRow.Table.Nth(46),
      [All Outputs].[Column 47], thisRow.Table.Nth(47),
      [All Outputs].[Column 48], thisRow.Table.Nth(48),
      [All Outputs].[Column 49], thisRow.Table.Nth(49),
      [All Outputs].[Column 50], thisRow.Table.Nth(50),
      [All Outputs].[Column 51], thisRow.Table.Nth(51),
      [All Outputs].[Column 52], thisRow.Table.Nth(52),
      [All Outputs].[Column 53], thisRow.Table.Nth(53),
      [All Outputs].[Column 54], thisRow.Table.Nth(54),
      [All Outputs].[Column 55], thisRow.Table.Nth(55),
      [All Outputs].[Column 56], thisRow.Table.Nth(56),
      [All Outputs].[Column 57], thisRow.Table.Nth(57),
      [All Outputs].[Column 58], thisRow.Table.Nth(58),
      [All Outputs].[Column 59], thisRow.Table.Nth(59),
      [All Outputs].[Column 60], thisRow.Table.Nth(60),
      [All Outputs].[Column 61], thisRow.Table.Nth(61),
      [All Outputs].[Column 62], thisRow.Table.Nth(62),
      [All Outputs].[Column 63], thisRow.Table.Nth(63),
      [All Outputs].[Column 64], thisRow.Table.Nth(64),
      [All Outputs].[Column 65], thisRow.Table.Nth(65),
      [All Outputs].[Column 66], thisRow.Table.Nth(66),
      [All Outputs].[Column 67], thisRow.Table.Nth(67),
      [All Outputs].[Column 68], thisRow.Table.Nth(68),
      [All Outputs].[Column 69], thisRow.Table.Nth(69),
      [All Outputs].[Column 70], thisRow.Table.Nth(70),
      [All Outputs].[Column 71], thisRow.Table.Nth(71),
      [All Outputs].[Column 72], thisRow.Table.Nth(72),
      [All Outputs].[Column 73], thisRow.Table.Nth(73),
      [All Outputs].[Column 74], thisRow.Table.Nth(74),
      [All Outputs].[Column 75], thisRow.Table.Nth(75),
      [All Outputs].[Column 76], thisRow.Table.Nth(76),
      [All Outputs].[Column 77], thisRow.Table.Nth(77),
      [All Outputs].[Column 78], thisRow.Table.Nth(78),
      [All Outputs].[Column 79], thisRow.Table.Nth(79),
      [All Outputs].[Column 80], thisRow.Table.Nth(80),
      [All Outputs].[Column 81], thisRow.Table.Nth(81),
      [All Outputs].[Column 82], thisRow.Table.Nth(82),
      [All Outputs].[Column 83], thisRow.Table.Nth(83),
      [All Outputs].[Column 84], thisRow.Table.Nth(84),
      [All Outputs].[Column 85], thisRow.Table.Nth(85),
      [All Outputs].[Column 86], thisRow.Table.Nth(86),
      [All Outputs].[Column 87], thisRow.Table.Nth(87),
      [All Outputs].[Column 88], thisRow.Table.Nth(88),
      [All Outputs].[Column 89], thisRow.Table.Nth(89),
      [All Outputs].[Column 90], thisRow.Table.Nth(90),
      [All Outputs].[Column 91], thisRow.Table.Nth(91),
      [All Outputs].[Column 92], thisRow.Table.Nth(92),
      [All Outputs].[Column 93], thisRow.Table.Nth(93),
      [All Outputs].[Column 94], thisRow.Table.Nth(94),
      [All Outputs].[Column 95], thisRow.Table.Nth(95),
      [All Outputs].[Column 96], thisRow.Table.Nth(96),
      [All Outputs].[Column 97], thisRow.Table.Nth(97),
      [All Outputs].[Column 98], thisRow.Table.Nth(98),
      [All Outputs].[Column 99], thisRow.Table.Nth(99),
      [All Outputs].[Column 100], thisRow.Table.Nth(100),
      [All Outputs].[Column 101], thisRow.Table.Nth(101),
      [All Outputs].[Column 102], thisRow.Table.Nth(102),
      [All Outputs].[Column 103], thisRow.Table.Nth(103),
      [All Outputs].[Column 104], thisRow.Table.Nth(104),
      [All Outputs].[Column 105], thisRow.Table.Nth(105),
      [All Outputs].[Column 106], thisRow.Table.Nth(106),
      [All Outputs].[Column 107], thisRow.Table.Nth(107),
      [All Outputs].[Column 108], thisRow.Table.Nth(108),
      [All Outputs].[Column 109], thisRow.Table.Nth(109),
      [All Outputs].[Column 110], thisRow.Table.Nth(110),
      [All Outputs].[Column 111], thisRow.Table.Nth(111),
      [All Outputs].[Column 112], thisRow.Table.Nth(112),
      [All Outputs].[Column 113], thisRow.Table.Nth(113),
      [All Outputs].[Column 114], thisRow.Table.Nth(114),
      [All Outputs].[Column 115], thisRow.Table.Nth(115),
      [All Outputs].[Column 116], thisRow.Table.Nth(116),
      [All Outputs].[Column 117], thisRow.Table.Nth(117),
      [All Outputs].[Column 118], thisRow.Table.Nth(118),
      [All Outputs].[Column 119], thisRow.Table.Nth(119),
      [All Outputs].[Column 120], thisRow.Table.Nth(120),
      [All Outputs].[Column 121], thisRow.Table.Nth(121),
      [All Outputs].[Column 122], thisRow.Table.Nth(122),
      [All Outputs].[Column 123], thisRow.Table.Nth(123),
      [All Outputs].[Column 124], thisRow.Table.Nth(124),
      [All Outputs].[Column 125], thisRow.Table.Nth(125),
      [All Outputs].[Column 126], thisRow.Table.Nth(126),
      [All Outputs].[Column 127], thisRow.Table.Nth(127),
      [All Outputs].[Column 128], thisRow.Table.Nth(128),
      [All Outputs].[Column 129], thisRow.Table.Nth(129),
      [All Outputs].[Column 130], thisRow.Table.Nth(130)

As you can no doubt tell, a related issue is that I don’t have programmatic control over the number of columns in a table.

Ah, the Excel export…

Well, if I had to create such a formula, I would descend to black magic as I call it, and construct an action programmatically instead.

1 Like

Ooh, tell me about that

Give me 10 min to finish my lunch and I’ll record a quick video for ya



Worthy of note, this isn’t exactly an excel export, it’s a csv conversion.

But I still want to learn about black magic

There you go:

1 Like

Oh this is sick. Black magic should be a native feature of the language. Very lispy

Ok follow up question: is there a way to formulaicly access column names and or create new columns?

No. This is not the Coda way of organizing the data.

Is there any sort of hacky approach?

Well, the only approach that comes to mind is this: make a button, reference the columns there, and out of action code extract column names using RegexExtract. However this won’t automatically pick up column name changes — you have to “touch” the button action (e.g. open the formula editor and close it) so that names are refreshed

What was the regexextract formula you used to isolate the column names amidst that text?

Here’s the javascript that Paul uses to get the column ids:

data = []
temp1.querySelectorAll('.columnHeaderRoot[data-column-id]').forEach(x => data.push(x.getAttribute('data-column-id')))

The way that you get temp1 as a variable is you use the select element tool on a column, then scroll up until you find a section called data-reference-type: "grid", then right click to copy that as global variable. It will automatically be named temp1

Can you drop a link to your doc?