CurrentValue in Named Formulas and FormulaMap

1

Hello all,

I am new to Coda and I’m trying to figure out how to be able to use “CurrentValue” as seen in FormulaMap, i.e.:

=List("Dog", "Cat").FormulaMap(Upper(CurrentValue))

I was able to use it when applying FormulaMap to a specific piece of data, e.g.:

=List("Dog", "Cat").FormulaMap(Upper(CurrentValue + "!"))

which produces “DOG!, CAT!”, as expected.

But now suppose that I have a really long utility formula that I want to be able to apply to different data sets. I don’t want to keep copying and pasting the same utility formula. To use the example formula from above, creating this as a formula (named or not) doesn’t work: =Upper(CurrentValue + "!"). It says that there is no reference called CurrentValue.

I then thought that maybe you need FormulaMap in order for the language to generate a CurrentValue reference. However, I am unable to do =FormulaMap(... because it expects two arguments (the data being operated on, and the formula to be applied). I couldn’t find any way to reference the current data being operated on, e.g. thisData. (When typing this, the only option it gave me was actual concrete data like thisDocument.)

My goal is to be able to, say, give this formula the name yell:

=Upper(CurrentValue.ToText() + "!")

And then use it across different data sets like so:

=List("Dog", "Cat").FormulaMap(yell)
=List("Whale", "Dolphin").FormulaMap(yell)

Note that I am having the same problem with Filter. I can’t seem to define a simple filter formula like so, to be used on various tables:

=Filter(CurrentValue > 1000000 AND CurrentValue < 5000000)
1 Like
2

Hi Josh,

Thanks for the feedback on parameterized formulas. Thats not something we support today but are definitely thinking about for the future.

CurrentValue has two places where it really works well static lists and columns. It is intended as a way to operate on each individual instance with a few specific formulas like FormulaMap and Filter. It does require a specific piece of data to operate over, and without it as you noticed the formula will complain that it can’t find any reference called CurrentValue because it won’t work in the current context.

Just for reference if you have a numeric column in a table you could do something like [Table].[Number Column].Filter(CurrentValue > 1000000 AND CurrentValue < 5000000) which would return a list of all values in the specified range.

If you are just trying to write a filter for a table view you would want to use the column name instead of CurrentValue.

Hope that helps!

Jason

3

Has there been any progress on this in the past two years? I have some really terrible O(n^k) formulas that go on that I think this would help a ton with depending on the implementation.

It seems to me that naming a formula should work the same way so as to be fully backwards compatible, but maybe with the caveat of a new naming convention where if the formula name is followed by empty parentheses, it gets passed an argument that you can reference as thisArg within your formula (behaves similar to thisRow in the sense that you have access to it throughout the formula without running into CurrentValue collisions).

Essentially, this enforces the differentiation between what currently exists - named computed values - and the true definition of named formulas while introducing no breaking changes, no complex syntax, and satisfying other problems, like CurrentValue collisions in nested List formulas.

Example:

Formulas:

// SuperUserAuthStatus
= [People].filter([CurrentValue].[Person] = User()).First().[Positions].Contains("Super User")

// AuthStatus()
= [People].filter([CurrentValue].[Person] = User()).First().[Positions].Contains(thisArg)

Usage:

SuperUserAuthStatus

AuthStatus("Super User")

AuthStatus("Manager")
4

It would be very, very awesome to support parameterized formulas. For a bunch of reasons, but one of the strongest arguments for me is because it would let us avoid having to remember all the workarounds—just make formulas that do them and invoke those.

5 Likes
5

imho, if we can’t write our own functions, we either have to depend on the CODA people to write them for us, or we have to write a ton of code every time we need to use our special function. Since we can’t expect for CODA to supply every thinkable function and since we can’t be expected to write the almost same code over and over, I really urge the CODA developers to implement user functions and/or user commands.

I had started another thread on almost this same subject before I was pointed to this one, so I sort of duplicated this unintentionally - but I feel so strong about this that I am writing this little message in this thread as well.

I don’t mind work-arounds if I can standardize them, but I do mind repetitious work, in particular if I end up modifying my work-arounds sometime down the line and have to fix every instance of my work-around.

10 Likes
6

Parametrized formulas are a must

3 Likes
7

Often, I wish for local variables inside of Buttons. The WithName() formula was slightly helpful, but adds layers of confusion in practice. Parameterized functions would be even better.

8

The new packs (very soon to be released) will be of great help, since they will allow you to write your own javascript functions. You can use them throughout your doc and call them with parameters. The demo looked very promising.

2 Likes
9

Hey @joost_mineur – wow you had some foresight on this!

I just found the block party demo videos, and Bill French’s post.

Thanks to everyone above for supporting this feature a year ago, you’ve helped make an exciting future for us all!

Best,
Michael

1 Like
10

Yep - it is a game changer that makes it possible now for all of us to help ourselves. Imagine the benefit of taking all of these out-of-scope concerns and weight off the Codans so they can focus on what really matters.

As a sneak peek - here’s a simple Pack I developed to get my sea-legs.

Garbage In; Data Out

I really like clean data and despite all the efforts to encourage users to enter accurate information, there’s always someone or some process that gums up the works. So I started my Packs journey by framing an idea - what if columns were just smarter than humans?

Here’s a phone number smartifier that can handle lots of garbage while correcting the data instantly. This little gem also uses the Country code to determine how to format the phones numbers. You type in garbage numbers and it fixes them - basic, simple stuff.

image
image1000×840 40.5 KB

Here’s a look at the code for this pack - this is the code that provides a column formatting feature that allows you to smartify the data for a column as opposed to a single function with parameters shown in the example above.

/*

   ***********************************************************
   SmartPacks: A collection of smart data entry behaviors
   Copyright (c) 2021 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************

*/

// import the packs sdk
import * as coda from "@codahq/packs-sdk";

// create the pack
export const pack = coda.newPack();

//
// COLUMN FORMAT: smartify phone number 
//
pack.addColumnFormat({
  name: "SmartifyPhoneNumbers",
  instructions: "A column format that normalizes phone numbers.",
  formulaName: "SmartifyPhoneNumber",
  formulaNamespace: "Deprecated",
});

//
// add the smartify phone number
//
pack.addFormula({
  
  // pack name and description
  name: "SmartifyPhoneNumber",
  description: "A column format that normalizes a phone number.",

  // result type
  resultType: coda.ValueType.String,

  // parameters
  parameters: [
    coda.makeParameter({
      type: coda.ParameterType.String,
      name: "<text>",
      description: "A text string of phone number digits you'd like to normalize."
    }),
  ],

  //
  // execute the function
  //
  execute: function ([thisText]) {
    let newText = "";
    thisText = thisText.replace(/\./ig, ".").replace(/\-/ig, "").replace(/ /ig, "");
    var phoneRegex = /^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$/;
    if (phoneRegex.test(thisText)) {
        var formattedPhoneNumber = newText = thisText.replace(phoneRegex, "($1) $2-$3");
    } else {
        newText = "ERROR";
    }
    return newText;
  },

});
11 Likes