Count the # of instances a select list item has been selected in another table

[Adam_Alpert]

Hey fam.

I am trying to figure out how to create a forumula in a select list table column that would give me the count of times it has been “selected” in another table. Where the other table can have multiple selections in a given row.

[Bill_French]

@Adam_Alpert

create a forumula in a select list table column that would give me the count of times it has been “selected” in another table

Oddly, this is precisely a task I was working on today when I noticed your question.

At Stream It we’re surveying customers and one of the questions is a multi-select option involving preferred cooking devices. This results in some data that is best characterized as “raw” in every sense of the word. And this is not uncommon when trying to make sense of data where significant combinations are possible. This is a bit of a mess.

The goal, as you make clear, is to understand how each individual preference resonates and a count is required across all respondents and for each selection possibility. There may be a way to do this with formulas (@Paul_Danyliuk would surely know), but there is certainly a way to do it with a custom pack.

Objective

Using The Pack

The Pack Itself

Embarrassingly, the functional code in this pack is just one line of elegant javascript. It’s the last line -

return thisList.filter(x => ( x.toString().indexOf(thisText) > -1 )).length;

/*
   ***********************************************************
   SmartPacks: CountifySelect
   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();

//
// add the smartify phone number
//
pack.addFormula({
  
  //
  // pack name and description
  //
  name: "CountifySelect",
  description: "A formula that counts occurrences in a select field.",

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

  //
  // parameters
  //
  parameters: [
    coda.makeParameter({
      type: coda.ParameterType.String,
      name: "<selection>",
      description: "A text string of a given selection"
    }),
    coda.makeParameter({
      type: coda.ParameterType.StringArray,
      name: "<selectionList>",
      description: "The column containing the slect list to be tested for occurrences."
    }),
  ],

  //
  // execute CountifySelect
  //
  execute: function ([thisText, thisList]) {
    return thisList.filter(x => ( x.toString().indexOf(thisText) > -1 )).length;
  },

});

[David_Lawrence]

Hello! This is exactly what I’m trying to figure out today, but am hoping to do it with a formula (because I don’t know how to create packs!).

Using the example above to illustrate what I tried, I entered the following formula:
Customer Survey.filter(Contains(Selection, thisrow.Selection)).count()

Alas, it doesn’t work! Could anyone suggest how to solve his challenge with a formula?

[Bill_French]

I think you may be on to the right approach, but I sense that it is related to thisRow.Selection which is likely a list, right? If so, would it be something like:

Selection.Contains(thisrow.Selection)

[Bill_French]

@David_Lawrence

I don’t know how to create packs!

Happy to share the pack into a document that’s shared to me to see if if works for you.

[David_Lawrence]

That’s really kind of you, Bill! I’d love to give that a try.
-D

[Bill_French]

@David_Lawrence

give that a try.

No worries. I think you should probably create a new document with your data table in it and then add another table with the selection and count columns. Once that has been shared to me – bfrench@globaltc.com – as an editor, I will install the beta pack and apply the formula. I believe that’s the only way to run packs during the beta period.

Alternatively, you could just share the docs with me straightaway - just not sure if there’s anything sensitive about your information overall.

[David_Lawrence]

Thanks, Bill! I was actually able to solve my challenge with a slight tweak to my formula (supplied by Coda tech support). I don’t have it at my fingertips right now but will try to post it tomorrow in case it can help someone else.

[Simon_van_Eeden]

Hi David, I am very interested in the formula you found. Could you maybe share it here?

[Simon_van_Eeden]

I have found a functioning method in another post: Trying to count occurrences - #6 by Federico_Stefanato
[Community Topics].Filter(Tags.Contains(thisRow)).Count()