Adding multiple rows of data through a button via formula map

[erik]

Hi everyone!

We are new to Coda and had our team workshop already. We have made great progress on our doc since then, but are now running into an issue. I have built out a test doc as an example I can share with everyone so you can see the problem.

The goal I think is simple, while a little complicated to explain so I hope I’m saying this right.

The layout looks like this:

Databases:
Customer Database,Platform,Client + Template

Templates:
Apples,Potatoes

The goal is to hit a button on the “Customer Database” which add all the rows from the platform of that customer (Apples, Potatoes). Right now I have it so the button works for one platform, and cannot figure out how to do a “if platform equals potatoes”, apply potatoes template. “If platform equals apples”, apply apples template.

The other issue I’m running into is when I hit the button a second time, it duplicates all the data. So instead of having 4 rows I’ll have 8, etc. We need to have the formula map check if the data already exists for that client, and if so move on to the next row.

Here is a link to the Doc:

I hope this makes sense. Please let me know if you have any questions and thank you for any assistance ahead of time as it’s very much appreciated.

[Federico_Stefanato]

Hi @erik (and everyone in the team),
welcome to Coda Community!

I did some changes in your doc: I hope I correctly got your point (have a look at the Assign - Alt button)
Basically, I added a single Template table that has a reference to Platform.
This way, you can dynamically select the template through its platform in the Customer Database table.

The second point was just a wrong filtering: you already picked up the correct formula.

Let me know if this helps.
Cheers!

[erik]

Hi Federico,

Thank you for responding, very much appreciated! I think this is exactly what I am looking for. I’m going to input the changes in our real doc later today / tomorrow and see if I get the expected results. Thanks again, I’ll keep you posted.

Have a great night!

[Federico_Stefanato]

Great to know!

[erik]

Hi Federico,

We are so close I think. The issue I’m having in production is for the client template output the display column is showing “client name,platform” instead of client name,item and the item is showing the platform as well. Not sure where I went wrong here.

https://gyazo.com/d6e62dc6121fdcc90f20bc85ebb07e2d

[erik]

I think I got it =) Doing some testing now.

[erik]

Ok, all the rows add fantastic in the production doc, no issues. I’ts still giving me duplicates though. Screemshot of formula below:

https://gyazo.com/f4c856c4484d7d6b2cb59169eb2d9847

Any thoughts on what I’m missing @Federico_Stefanato?

Thanks!

[Federico_Stefanato]

Hi @erik,
formula seems ok… however it depends on the data underlying.
If you have duplicates (in the Client + Template table, I assume) means that the condition is not unique.

If we are sure that Customer is a Lookup column, I would dig into the Monitor Name column and see if they have the same data type and they actually match their actual content.

As usual, having a direct look into would help.

Let me know!

[erik]

Here is a screenshot sample of duplicate data. Not sure if this helps.

Let me look at creating another test doc with the same column names, etc. I’ll just change out the client names.

Thanks!

[erik]

Please let me know if you see the other update. It says the post was hidden, it appears it did not like where I was originally sharing the image from. Not sure how to reach out to the admins about that.

[Paul_Danyliuk]

I have a feeling that

Monitor Name = CurrentValue

within the AddOrModifyRows is not behaving as you expect, since CurrentValue resolves not to a row in Monitors Template table but to the currently evaluated row within the Client + Template table (as evident by color coding, although sometimes that’s wrong too), hence the check is always false.

One of the reasons I don’t like AddOrModifyRows.

I’d suggest rewrite the formula with explicit conditional instead:

[Monitors Template].Filter(...).FormulaMap(
  If(
    [Client + Template].Filter(...).Count() = 0,
    [Client + Template].AddRow(...),
    _Noop()
  )
)

That’s how I’d usually do it. No unnecessary modification too (and no spamming the activity log / touching the Modified() timestamps etc).

---

P.S. Actually no, that won’t work, since you cannot access outer CurrentValue from the inner Filter either. You’ll most likely need to implement this trick:

What to do when CurrentValue is overloaded

That’s a know thing: There’s a workaround with storing the outer CurrentValue in a cell somewhere (e.g. same row dedicated column, or a helper table). E.g. here’s the way I’m iterating over Value Drivers, then each of their Metric Uplifts, then for each of those I insert six rows with a Sequence().FormulaMap(). Note how I store _Current Value Driver and _Current Metric Uplift into a cell before using it in a nested FormulaMap: [image] [image] (Fun fact: in retrospective, in my case none o…

---

P.P.S. Might be worth looking into the possibility of replacing FormulaMap with individual buttons. on the rows from Monitors Template, then filtering and clicking those buttons instead. This eliminates the need of one level of CurrentValue because in those buttons you can read directly from thisRow, and you won’t need the trick then.

[Federico_Stefanato]

Hi @erik,
take into consideration what @Paul_Danyliuk suggested you.

However, I guess that one point in your formula is that you check Monitor Name = @CurrentValue (being @CurrentValue the current row) but then you set value of the target column with its column (Monitor Name).

Try to change the last Monitor Name with CurrentValue and it should work.

Let us know!

[Federico_Stefanato]

@Paul_Danyliuk

P.P.S. Might be worth looking into the possibility of replacing FormulaMap with individual buttons. on the rows from Monitors Template , then filtering and clicking those buttons instead. This eliminates the need of one level of CurrentValue because in those buttons you can read directly from thisRow , and you won’t need the trick then.

This is my favourite way.
Also much easier to test/debug

[Paul_Danyliuk]

@Federico_Stefanato

Try to change the last Monitor Name with CurrentValue and it should work.

Well I doubt that; I am almost completely sure that CurrentValue is always overridden in AddOrModifyRows to the current row in this table.

[Federico_Stefanato]

@Paul_Danyliuk

Well I doubt that; I am almost completely sure that CurrentValue is always overridden in AddOrModifyRows to the current row in this table.

Have a look, @Paul_Danyliuk:

[Paul_Danyliuk]

Sorry, not intuitive to me at all. I’m clicking the buttons but the rows added seem off (they all list both customers etc).

Did I mention before that I’m not that good with abstractions like apples and potatoes? I cannot deduce what the actual requirement is behind that.

P.S. nvm I think I know what they are trying to do.

[Federico_Stefanato]

That was the example shared by @erik hat I used to implement the formula.
I guess you have to read for the beginning.
Long story short: after setting up Templates with items per each Platform, in the Customer Database you assign all the items to each customer in the Client + Template table.

I struggled with names… but this is it

[Federico_Stefanato]

@erik

Ok, all the rows add fantastic in the production doc, no issues. I’ts still giving me duplicates though. Screemshot of formula below:

So, my point ids that this formula is incorrect because it should use CurrentValue instead of Monitor Name.
This way it works.

Redefining it a single row level-button is a suggestion that I’d consider (is what I usually do), anyway.

[Paul_Danyliuk]

Okay, I think I did it:

I ended up implementing the trick above because other solutions seemed less intuitive. A template-row-level button wouldn’t work because that button wouldn’t know which client you’re adding the row for (unless you assigned that client onto a Platform record, which would’ve been a spaghetti solution)

It copies data from the template into C+T rows, but if you’re fine with lookup formulas you can clean those copying up.

---

I know it may be a lot to take in for someone who’s not so experienced with Coda. Unfortunately, loss of outer CurrentValue is a known problem and there’s no beginner-friendly workaround for that. The most bulletproof and logical solution is the one I implemented; in some cases it may be avoided but not yours.

[erik]

Hey guys,

Thank you for all the help. There is a bunch to take in here, so I’m going to go over it a couple times and see if I can wrap my head around it. I’ll let you know if I have any questions. All the help is greatly appreciated!