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

cnr · January 2, 2021, 4:58am

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.

Ryan_Martens1 · January 14, 2021, 10:24am

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

(Updated my doc above.)

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

Christiaan_Huizer · January 14, 2021, 10:45am

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

cnr · January 15, 2021, 8:32pm

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

Ryan_Martens1 · January 16, 2021, 4:52am

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

Connor_McCormick1 · July 29, 2021, 12:06am

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.

Input

1
2
3
4
5
6

Output

1
2
3
4
5
6

…
130.

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

Sequence(1, 130).FormulaMap(
  MyList.Nth(CurrentValue)
)

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.

Paul_Danyliuk · July 29, 2021, 10:52am

I’m just wrapping in an If():

If(
  X <= List.Count(),
  List.Nth(X),
  ""
)

Connor_McCormick1 · July 29, 2021, 2:52pm

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

RunActions(
  [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)
  ),
  thisRow.Table.Slice(2).FormulaMap(
    [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.

Paul_Danyliuk · July 29, 2021, 2:56pm

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.

Connor_McCormick1 · July 29, 2021, 2:58pm

Ooh, tell me about that

Paul_Danyliuk · July 29, 2021, 2:59pm

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

Connor_McCormick1 · July 29, 2021, 3:03pm

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

But I still want to learn about black magic

Paul_Danyliuk · July 29, 2021, 4:02pm

There you go:

Connor_McCormick1 · July 29, 2021, 4:57pm

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?

Paul_Danyliuk · July 29, 2021, 6:14pm

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

Connor_McCormick1 · July 29, 2021, 6:16pm

Is there any sort of hacky approach?

Paul_Danyliuk · July 30, 2021, 11:59am

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

Scott_Collier-Weir · July 30, 2021, 3:41pm

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

Connor_McCormick1 · July 30, 2021, 4:42pm

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

Connor_McCormick1 · July 30, 2021, 5:05pm

Can you drop a link to your doc?