Add row for every unique combination based on multiple columns

Hi everyone!

I’d like to be able to add row for every unique values combination based on multiple columns.

I have Product table

Product
Product 1
Product 2

I have Releases table

Release
2024
2025

I have Features table

Feature Product Release
Feature 1 Product 1 Product 2 2024
Feature 2 Product 2 2024 2025

I’d like to be able to add rows for every unique combination available where feature can be associated to multiple products and releases. I’m getting stuck with a nested foreach and even using if condition is unable to match values. Any help is greatly appreciated. Thanks!

Result table:
Feature Product Release
Feature 1 Product 1 2024
Feature 1 Product 2 2024
Feature 2 Product 2 2024
Feature 2 Product 2 2025

Make a button with a formula like:

Product.ForEach(CurrentValue.WithName(P,
  Features.ForEach(CurrentValue.WithName(F,
    Release.ForEach(CurrentValue.WithName(R,
      Result.AddRow(
        Result.Product, P,
        Result.Feature, F,
        Result.Release, R
      )
    ))
  ))
))

You can then also improve it by omitting the combinations you already have, so that you can click it again when you add something, i.e. replace the Result.AddRow(...) part with

If(
  Result.Any(
    CurrentValue.Product = P
    AND CurrentValue.Feature = F
    AND CurrentValue.Release = R
  ),
  _Noop(),  // do nothing
  // otherwise do the Result.AddRow(...)
)

P.S. In this code, P, F, R will be row references (not just text values) so columns in the result table should be single-select Relations to respective tables.

Thank you, @Paul_Danyliuk.

However, I’m getting a different result

It’s kind of tricky to have 3 nested loops but nested loops from look up tables may not work.
I am looping Products, Releases column values within Features table only because they should match the row contents. Still trying to figure out as well.

Thanks!

Nothing tricky there, it works if applied right.

Can you share the doc please? I’ll quickly fix it for you.
paul@codatricks.com

I’ve created a new doc and shared with you.
Thank you very much!

Yeah, I got your scenario. You didn’t just want all possible combinations from all tables — but only use the products and releases selected for that feature.

For that you just put Features on the outer loop, and replace Products and Releases respectively with F.Products and F.Releases (i.e. not all rows from the table Products, but only the selected ones for that F currentvalue feature).

Also I saw you wanted to include rows with empty selection if nothing was selected for either a product or a release — this can be achieved by telling Coda to use a list of one blank value if the original list is blank, i.e. instead of F.Products.ForEach(...) use F.Products.IfBlank(List("")).ForEach(...)

Here’s the full code:

Features.ForEach(CurrentValue.WithName(Feature,
  // For each product OF THAT FEATURE:
  Feature.Products.IfBlank(
    // If the list itself is blank (i.e. 0 products) fake up a list of one blank value
    List("")
  ).ForEach(CurrentValue.WithName(Product,
    // For each year OF THAT FEATURE
    Feature.Releases.IfBlank(
      // (or a blank item if no releases):
      List("")
    ).ForEach(CurrentValue.WithName(Release,
      // If there's already such combination, do nothing, otherwise add row
      If(
        Result.Any(
          Feature = Feature
          AND Product = Product
          AND Release = Release
        ),
        _Noop(),
        Result.AddRow(
          Result.Feature, Feature,
          Result.Product, Product,
          Result.Release, Release
        )
      )
    ))
  ))
))

There’s still one thing that can be improved. This will add rows like these when there’s no product or no release for a feature:
image
but will not remove them once there is a product or a release. If you need that, let that be your own homework :wink:

2 Likes

This is great, @Paul_Danyliuk! I just learned that I can continue to access from the outer loop. Thanks so much!

1 Like