FormulaMap() help: Not use any formula or button column

1

I have been trying to get a button formula to work for a while now but to no avail.
I have a Sale Order table and I need to fill a DB Clients table with the data from the former.
The thing is some clients have more than one order and that has to be filtered out, so I don’t get duplicates in the DB Clients table.
I’m sure there are other ways to accomplish this but I don’t want to use any formula or button column, as it would slow down my big doc.
The formula I wrote was:

Orders
  .FormulaMap(
    switchIf(
      CurrentValue.[Client ID].In([DB Clients].[Client ID])
        .Not(),
      [DB Clients]
        .AddRow(
          [DB Clients].[Client ID],
          CurrentValue.[Client ID],
          [DB Clients].Name,
          CurrentValue.[Client Name],
          [DB Clients].[Phone Number],
          CurrentValue.[Phone number],
          [DB Clients].[ZIP code],
          CurrentValue.[ZIP Code]
        )
    )
  )

I created the dummy doc bellow

2

I don’t see anything inherently wrong with it. What exactly is not working?

What I’d do probably is move the filtering out of FormulaMap, so that instead of Orders.FormulaMap() on all orders and then an If() inside, I’d have:

Orders.Filter([Client ID].In([DB Clients].[Client ID]).Not()).FormulaMap(
  [DB Clients].AddRow(
    ...
  )
)
3

Hi, @Paul_Danyliuk . Thanks for your help. I tried your way and it didn’t work either.

When you have two orders from the same client, the formula will not filter one out. You get duplicates in DB Clients.
If I try to run the formula again, it correctly doesn’t do anything, as the client was already added to the DB Clients.
It seems like DB Clients. Client ID doesn’t get updated while the formula runs and you get duplicates.

4

Oh I see, sorry for the misleading answer.

You could combine the two approaches then, first limit the orders to process with .Filter(...) and then additionally catch duplicates in recently created items with an inner

...FormulaMap(
  If(
    [DB Clients].[Client ID].Contains(CurrentValue.[Client ID]),
    _Noop(),
    [DB Clients].AddRow(...)
  )
)

But I am under an impression that something else is not working in your formula? Otherwise your initial solution would work. What error are you experiencing?

5

Hi, Paul. I got it to work now :smiley:
I think it’s a bug. All I did was to envelop the condition with RunActions().
The formula was not testing the inside condition in each iteration of FormulaMap() and, I believe, RunActions() forces it to check.
The formula that works is bellow. The only difference from the first one is the RunActions()

[DB Orders]
  .FormulaMap(
    RunActions(
      switchIf(
        CurrentValue.[Client ID]
          .In(
            [DB Clients].[Client ID]
          )
          .Not(),
        [DB Clients]
          .AddRow(
            [DB Clients].[Client ID],
            CurrentValue.[Client ID],
            [DB Clients].Name,
            CurrentValue.Name,
            [DB Clients].[Phone Number],
            CurrentValue.[Phone number],
            [DB Clients].[ZIP code],
            CurrentValue.[ZIP Code]
          )
      )
    )
  )
2 Likes
6

I renamed this thread because, I believe I found a bug in FormulaMap().
As you can see in the dummy doc, it doesn’t test a condition in each iteration even when the condition is inside FormulaMap(). I believe it’s not an expected behavior. You have to use RunActions() to get it to work properly.

1 Like
7

This is also a good example of the not-so-useful error messages.

“Unable to execute invalid action” doesn’t tell me much.

1 Like
8

Oh gosh, indeed! RunActions to recalculate the doc between iterations!

How could I forget the thing I even documented myself?

I don’t think it’s a bug but a feature. It’s a good thing that we can control it ourselves (sorta) whether we want to perform actions quickly (without recalculations) or want the doc to recalculate.

1 Like
9

I guess whenever I “discover” something, I have to check if you have written about it.
Thank you, @Paul_Danyliuk

2 Likes
10

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.