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!

[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.

[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 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.

[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.

[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.

[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")
                )
              )
            )
          )
        )
      )
    )
  )
)

[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]

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?

[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.

[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.