Debugging reference action

[Kirk_Masden]

I’m trying to build a reference action that changes the value of a column in a different row on the same table. I have something that I think should work but doesn’t and I can’t figure out why. First here are the basics about my reference action:

For a record of this table: Main Data
Do this: Data: execute an action on a set of rows
Referenced Table: Main Data
Referenced Rows: filter formula that produces the proper key
Referenced Action: Reset value

The referenced action works so, for some reason, the reference action doesn’t seem to be able to invoke it properly. I’m using a very complicated filter formula but, as I’ve indicated above, it does produce the correct key when I test it so that shouldn’t be the problem.

Is using the same table for the reference action and the referenced action the problem? The referenced action is for a record that is not within the slice from which the reference action is accessed, but I didn’t think that would be a problem. I can’t figure out what I’m doing wrong and wonder it any of you have any ideas.

P.S. Better debugging tools for situations like this would be nice. The platform seems to be perfectly happy with what I have done but, nonetheless, refuses to do it.

[LeventK]

@Kirk_Masden
I will test this within my public sample app and get back to you. I haven’t used dataset execution referencing the table itself, but I will try.

[Kirk_Masden]

Thanks!

[LeventK]

@Kirk_Masden
I was just writing that it’s working correctly but you need to check your FILTER expression, but realized that you had already discovered that out.

[Kirk_Masden]

Thanks! I see that my filter expression seems to have something to do with the problem but am still confused about how to fix it. It appears to be valid when I test it.

[Kirk_Masden]

I just changed the complicated Filter() to a simple Select() and it worked so the problem seems to be with the expression in the triggering action (the reference action). I’m still puzzled. I’ll try to change the complicated filter expression to an appropriate Select() expression (which will also be complicated) and see if that works.

[Phil]

As you discovered the Reference action can act on the original table.
Just wanted to confirm that.

Adding better logging for DataChanges is on my list of things that need improving.

[Kirk_Masden]

Thanks! I hesitate to post my complicated filter() expression because I don’t expect others to be able to understand what I’m trying to do just by looking at the expression. Nonetheless, for your reference, here’s the expression that appears to be valid and produces the correct key number but fails to invoke the action:

filter(Main Data,[_RowNumber]=min(SELECT(Main Data[_RowNumber],and([Start at session no]<>number(concatenate(SELECT(Session[Session number], ([Key] = 1))))+([_RowNumber]+(3-1)-MAX(SELECT(Main Data[_RowNumber],[Start at session no]<=number(concatenate(SELECT(Session[Session number], ([Key] = 1)))))))/3,[_RowNumber]>MAX(SELECT(Main Data[_RowNumber],[Start at session no]<=number(concatenate(SELECT(Session[Session number], ([Key] = 1))))))))))

I tried to change this to a select() expression but failed. I’m not sure if I just made a simple error or if I’m not allowed to next select expressions. I’m I correct to think that I should try to make a select() expression instead of a filter()? In another app, I’ve used a filter that worked so I’m confused.

[Steve]

Formatted with some addition punctuation for clarity:

FILTER(
  "Main Data",
  (
    [_RowNumber]
    = MIN(
      SELECT(
        Main Data[_RowNumber],
        AND(
          (
            [Start at session no]
            <> (
              NUMBER(
                CONCATENATE(
                  SELECT(
                    Session[Session number],
                    ([Key] = 1)
                  )
                )
              )
              + (
                (
                  [_RowNumber]
                  + (3 - 1)
                  - MAX(
                    SELECT(
                      Main Data[_RowNumber],
                      (
                        [Start at session no]
                        <= NUMBER(
                          CONCATENATE(
                            SELECT(
                              Session[Session number],
                              ([Key] = 1)
                            )
                          )
                        )
                      )
                    )
                  )
                )
                / 3
              )
            )
          ),
          (
            [_RowNumber]
            > MAX(
              SELECT(
                Main Data[_RowNumber],
                (
                  [Start at session no]
                  <= NUMBER(
                    CONCATENATE(
                      SELECT(
                        Session[Session number],
                        ([Key] = 1)
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)

[Steve]

NUMBER(
  CONCATENATE(
    SELECT(
      Session[Session number],
      ([Key] = 1)
    )
  )
)

• This appears to get the Session number column value from the row with a Key column value of 1.

• The use of CONCATENATE() here is awkward, suggesting you want to merge a list with multiple values into a single Text value for conversion by NUMBER(), but NUMBER() will fail if the list included multiple items, suggesting you expect only a single value from SELECT().

• The SELECT() expression suggests you intend to return only a single row’s column value (assuming Key is the key column’s name).

• It therefore appears you’re using CONCATENATE() merely to convert the List returned by SELECT() into a single Text value.

• The value you’re fetching from the Sessions table is from the Session number column, the column’s name suggesting the value is originally a number.

• So it appears you’re fetching a Number, converting it to Text, then back to Number.

Assuming the above is an accurate interpretation, you can avoid entirely the seemingly unnecessary type conversions and the awkward use of CONCATENATE() by replacing the above snippet with:

ANY(
  SELECT(
    Session[Session number],
    ([Key] = 1)
  )
)

An ANY(SELECT(...)) expression can also be expressed with LOOKUP():

LOOKUP(
  1,
  "Session",
  "Key",
  "Session number"
)

Replacing your NUMBER(CONCATENATE(SELECT(...))) expression with my LOOKUP(...) expression:

FILTER(
  "Main Data",
  (
    [_RowNumber]
    = MIN(
      SELECT(
        Main Data[_RowNumber],
        AND(
          (
            [Start at session no]
            <> (
              LOOKUP(1, "Session", "Key", "Session number")
              + (
                (
                  [_RowNumber]
                  + (3 - 1)
                  - MAX(
                    SELECT(
                      Main Data[_RowNumber],
                      (
                        [Start at session no]
                        <= LOOKUP(1, "Session", "Key", "Session number")
                      )
                    )
                  )
                )
                / 3
              )
            )
          ),
          (
            [_RowNumber]
            > MAX(
              SELECT(
                Main Data[_RowNumber],
                (
                  [Start at session no]
                  <= LOOKUP(1, "Session", "Key", "Session number")
                )
              )
            )
          )
        )
      )
    )
  )
)

[Kirk_Masden]

I’m still trying to take in all of your generous help and advice, @Steve. I tried the formula you made for me and, again, it yielded the correct key number when I tested it but failed to trigger the action. Now I’m going to try to set up some virtual columns to simplify things. I’ll report back on my results. It does seem odd to me, though, that an expression that tests as OK fails to invoke and action.

P.S. The test itself took a long time to complete. I guess that has something to do with the failure.

[Steve]

At this point, we’re down to hardcore debugging. The goal is to start removing variables until we find the one that seems to trigger the problem. What I would do is replace your current Reset value action with one that sets the column value to a fixed value, like 0. If that works, it tells us the problem is with the Reset value action and not the reference action.

[Kirk_Masden]

As always, I deeply appreciate your expert advice, @Steve! As I wrote above, your comments and the nature of the action I am trying to implement have led me to the conclusion that, in this case, I should make the spreadsheet do most of the work. I added three columns to the spreadsheet which made it possible for the triggering action to be simplified as follows:

FILTER(“Main Data”,([_RowNumber] = MIN(SELECT(Main Data[_RowNumber],([Update needed]=1)))))

and the triggered action is simply

[Update value]

And it works!!!

It has taken me some time to learn how to write each of the following: ordinary spreadsheet formulas, expressions to use in virtual formulas, and list expressions that circumvent virtual formulas. Another thing I’ve had to learn is when to use each.

I live in Japan where there are several levels of politeness and varieties of honorific language. Learning the forms properly is one challenge but learning exactly when to use each form is even harder. I feel like decisions regarding when to employ various computational strategies in AppSheet can be similarly difficult.

Finally, I think that, thanks to you, my problem is solved. I wonder, though, if you think there’s a need to pursue this debugging matter a bit more in order to determine the exact reason for the successful test not actually triggering the action. If pursuing this matter some more would be of benefit to AppSheet I’m happy to cooperate but I think my own personal needs have been met. Thanks again!!

[Steve]

Kirk_Masden:

I wonder, though, if you think there’s a need to pursue this debugging matter a bit more in order to determine the exact reason for the successful test not actually triggering the action. If pursuing this matter some more would be of benefit to AppSheet I’m happy to cooperate but I think my own personal needs have been met.

I don’t think there’s a need to dig deeper unless it would serve you. In my experience, actions are very persnickety, and are difficult to troubleshoot. Find what works and stick with it unless there’s reason not to.

[Steve]

Nesting SELECT() expressions (putting SELECT() expressions within SELECT() expressions) is computationally expensive and potentially (very!) inefficient. Performance degrades very fast as the data set grows. In general, nested SELECT() expressions should be avoided as a best practice.

Your expression contains:

1. A FILTER("Main Data", ...) expression (a form of SELECT()) that inspects every row of the Main Data table.

2. A MIN(SELECT(Main Data[_RowNumber], ...)) expression that also inspects every row of Main Data for every row inspected by (1).

3. A LOOKUP(...) expression (also a form of SELECT()) that inspects every row of Session for every row inspected by (2).

4. A MAX(SELECT(Main Data[_RowNumber], ...)) expression that also inspects every row of Main Data for every row of (2).

5. Another LOOKUP(...) expression that inspects every row of Session for every row of (4).

6. Another MAX(SELECT(Main Data[_RowNumber], ...)) expression that also inspects every row of Main Data for every row of (1) in the worst case.

7. Yet another LOOKUP(...) expression that inspects every row of Session for every row in (6).

Each of those “for every row” is a multiplier to the number of operations done. The more operations, the slower your app runs.

Suppose Main Data contains 100 rows and Sessions contains 10 rows. Based on the above analysis, in the best case, your FILTER() expression examines (100 + (100 * 100) + (100 * 100 * 10) + (100 * 100 * 100) + (100 * 100 * 100 * 10)) = (100 + 10000 + 100000 + 1000000 + 10000000) = 11,110,100 rows.

In the worst case, your FILTER() expression examines (100 + (100 * 100) + (100 * 100 * 10) + (100 * 100 * 100) + (100 * 100 * 100 * 10) + (100 * 100) + (100 * 100 * 10)) = (100 + 10000 + 100000 + 1000000 + 10000000 + 10000 + 100000) = 11,220,100 rows.

Ideally, any repeated SELECT() expression would be moved to its own virtual column where it will be evaluated only once. A reference to the virtual column would then replace the snippet in the larger expression.

My proposed LOOKUP() expression occurs three times in your FILTER() expression. If we move it to its own virtual column (e.g., lookup_session_number) and replace every occurrence of my LOOKUP(...) with [lookup_session_number], it reduces the worst case to (10 + 100 + (100 * 100) + (100 * 100 * 100) + (100 * 100)) = (10 + 100 + 10000 + 1000000 + 10000) = 1,020,110 row examinations.

The MAX(SELECT(Main Data[_RowNumber], ...)) expression occurs twice. If we also move that to a max_rownumber virtual column, the total worst case count goes down to (10 + 100 + 100 + (100 * 100)) = (10 + 100 + 100 + 10000) = 10,210 row examinations.

MAX(
  SELECT(
    Main Data[_RowNumber],
    (
      [Start at session no]
      <= [_THISROW].[lookup_session_number]
    )
  )
)

The MIN(SELECT(Main Data[_RowNumber], ...)) expression occurs only once within the FILTER() expression, but that still means it gets evaluated once for every row FILTER() examines. If we move it to min_rownumber, the worst case bottoms out at (10 + 100 + 100 + 100) = 310 row examinations, down from 11,220,100!

MIN(
  SELECT(
    Main Data[_RowNumber],
    AND(
      (
        [Start at session no]
        <> (
          [_THISROW].[lookup_session_number]
          + (
              [_RowNumber]
              + (3 - 1)
              - [_THISROW].[max_rownumber]
            )
            / 3
          )
        )
      ),
      (
        [_RowNumber]
        > [_THISROW].[max_rownumber]
      )
    )
  )
)

[Mike_A]

@Steve - all I can say is WOW! This is why formula Syntax is so critical to developing with Appsheet. I keep bookmarking cases like this so I can find them later. I wish they would become part of the documentation (maybe a section with complex formula examples and tradeoffs?

[Kirk_Masden]

I agree entirely with your point about the documentation, as we’ve discussed before. In this case, I searched for documentation about referenced actions but didn’t find much. In this case, documentation specific to referenced actions wouldn’t have given me the wonderful sort of information that @Steve provide so generously, but it might have helped me narrow the focus of my debugging process more efficiently.

[Phil]

Could Security Filters be involved?

[Kirk_Masden]

No security filters on this app.

[Phil]

“Filter” is just a “macro” that is turned into a “Select” expression.

Whenever you enter a “Filter” expression, the expression system turns it into the equivalent Select expression. The expression system is always evaluating the expression as a Select. Filter simply saves you specifying the key column name.

[Kirk_Masden]

I see. Thanks. Is it possible that my expression is too complicated? I’m using a combination of select expressions to try to narrow down the records that I want to update but I wonder if I’m exceeding some sort of limit that I’m unaware of. It seems odd that the test produces the proper key but that it only seems to function properly when I substitute a simpler expression that produces the same key.

[Kirk_Masden]

It’s late here in Japan now so I’ll go to bed. I hope experiment with other approaches to building the appropriate select expression tomorrow. Any hints as to what kind of strategy is likely to be successful with a complicated expression will be appreciated.

[Kirk_Masden]

Fantastic! Your analysis is exactly correct about my intension and gets at a question I had but had tried to answer (inefficiently, using “collate”) through trial and error. I will definitely follow your instructions to put the new expression together.

Actually, this is a new version of my app. In the old version, most of this work was done on the spreadsheet side. That, however, meant that the action invoked by the expression would only work after a sync. With this particular action, I could live with that but, in general, I’ve been trying to avoid doing things on the spreadsheet side so that things happen immediately from users perspective.

Also, I’ve been trying to avoid virtual columns to prepare the calculations for this action because it was my understanding was that it would be more efficient to put everything in the action so that it only needed to be calculated when the action is invoked, instead of constantly, regardless of whether the action gets used or not. So, I’m a little confused by this part of your explanation:

Steve:

Ideally, any repeated SELECT() expression would be moved to its own virtual column where it will be evaluated only once. A reference to the virtual column would then replace the snippet in the larger expression.

I’m sure you are right but I’m just confused about why. I thought putting stuff in virtual columns would lead to more computation than putting them all in an action only used occasionally.

Finally, I wonder if I may not have stumbled upon some sort of bug in the AppSheet platform. The reason I say this is that my inefficient expression works when tested via the test button but not in the app itself. If my action were just very slow, but worked, I could chalk it all up to the inefficiency of my expression. However, since it doesn’t work at all (but yields the correct key when “tested”), doesn’t that indicate a problem in the AppSheet platform?

[Steve]

Kirk_Masden:

I thought putting stuff in virtual columns would lead to more computation than putting them all in an action only used occasionally.

Yes, this is a quandary. There are trade-offs to be considered, and it’s useful to know how AppSheet handles everything.

Virtual columns can be/are a problem because the are re-evaluated at every sync for every row to which they are attached. That definitely needs to be considered.

Actions are valuable because they are only evaluated when invoked. That also needs to be considered.

[Kirk_Masden]

This explanation has helped me a lot. Actually, with this particular action, perhaps I should set up columns to do more of the calculations on the spreadsheet side, as I have in previous version of my app. One of my goals is to reduce my sync time, which is fairly long now on my current version of this app. With this particular action, it’s not a big problem if it cannot be invoked correctly before the sync is complete. This particular action involves a maintenance task that needs to get done eventually but doesn’t need happen immediately every time. Based on what you have just told me about sync times and virtual columns, I’m going to add some columns to my spreadsheet to help with this task.

[Steve]

Another option, to get the efficiencies of both staged-calculation (that virtual columns provide) and on-demand calculation (that actions provide) is to use a series of actions to perform the staged calculations and store the results in normal (non-virtual) columns. The downside is you then commit storage in you spreadsheet that is infrequently used that will count against any limits you may have.

For this approach, create the columns I suggested, but as normal columns with the corresponding spreadsheet columns and no app formula, rather than as virtual columns. Then create actions of type Data: set the value of a column for each of the columns with the appropriate corresponding expression. Finally, create an action of type Grouped: execute a sequence of actions to invoke these calculation actions.

[Kirk_Masden]

Thanks for the suggestion. In this case, however, I think I’ll avoid this. One of my goals with this app is to reduce the need for the app to write to the spreadsheet.

At the moment, I’m working on the spreadsheet side solution. I’m trying to accomplish it with a minimum of columns, which means packing a good deal into one or two columns.

[Steve]

Details on the Reset value action, please.

Also, what is the expression for the Only if this condition is true property for both of these actions?

[Kirk_Masden]

The Reset value action had been working so I haven’t changed it yet but your question made me realize that I need to do that. So, with the understanding that I’m planning to fix this based on the suggestions you have already given me, here’s what is currently in the “To this value” spot:

number(concatenate(SELECT(Session[Session number], ([Key] = 1))))+([_RowNumber]+(3-1)-MAX(SELECT(Main Data[_RowNumber],[Start at session no]<=number(concatenate(SELECT(Session[Session number], ([Key] = 1)))))))/3

As you can see, it has some of the same inefficiencies. Currently, the “Only if this condition is true” spot is just “true”. Also, in the “3-1” part (and the 3 at the very end), the number 3 will need to be replaced with a lookup expression for a variable on another sheet. (I’m just using “3” as a placeholder for that value, for now.)

[Steve]

(
  LOOKUP(1, "Session", "Key", "Session number")
  + (
    (
      [_RowNumber]
      + (3 - 1)
      - MAX(
        SELECT(
          Main Data[_RowNumber],
          (
            [Start at session no]
            <= LOOKUP(1, "Session", "Key", "Session number")
          )
        )
      )
    )
    / 3
  )
)

[Thomas_Murphy]

This was very helpful for me. Thanks Steve!