Solved: Expression/ character limit?

Jouko_Manninen · 12-31-2020 06:38 AM

Is there a limit on the amount/ length of expressions/ charatcers in behaviour expressions?

I have quite lenghty IF’s selection branch which at some point stops displaying if the expressions are valid or not. (It doesn’t show the text anymore but it does show the green circle with v on it but this happens whether or not the code is valid)

Only when I save, it then informs if there’s an error or not.

But this isn’t the main issue, now I have a problem that the code that works, stops working when I add one more if statement replacing one of the True or false result. And I’m quite sure that the if statement I’m adding should be valid.

Heres the current expression that works:

SUM(
         SELECT(
                        Order[Reserved] , OR ( [Mount]=[_THISROW].[Part name] , 
                                                              [Iris]=[_THISROW].[Part name] ,  
                                                              [Generic]=[_THISROW].[Category],
                                                              IF (OR(AND([_THISROW].[Version] > 0 , NOT([_THISROW].[category] = "Optional")) ,
                                                                            AND(ISBLANK([_THISROW].[Version]), NOT([_THISROW].[category] = "Optional"))),

                                                                      IF ( OR(ISBLANK([_THISROW].[Dedicated Part?]) ,
                                                                            [_THISROW].[Dedicated Part?]=FALSE),

                                                                          IF([_THISROW].[No Dedicated parts?] ,

                                                                               IF(AND([Category] = [_THISROW].[Category] ,  
                                                                                             ISBLANK([_THISROW].[Size])),

                                                                                   [Category] = [_THISROW].[Category] ,

                                                                                   IF(AND([Category] = [_THISROW].[Category] , 
                                                                                                 [Size] = [_THISROW].[Size]) ,
                                                                                       TRUE,
                                                                                       FALSE
                                                                                       )
                                                                             ),



                                                                               IF(AND([No Dedicated brand?] = [_THISROW].[No Dedicated brand?] ,
                                                                                             [Category] = [_THISROW].[Category]),

                                                                                    TRUE,
                                                                                    FALSE
                                                                                    )
                                                                              ),



                                                                          IF ([Brand] = [_THISROW].[Brand] ,

                                                                                IF(AND([Category] = [_THISROW].[Category] ,  
                                                                                              ISBLANK([_THISROW].[Size]),
                                                                                              [Brand] = [_THISROW].[Brand]),

                                                                                [Category] = [_THISROW].[Category] ,

                                                                                IF(AND([Category] = [_THISROW].[Category] , 
                                                                                     [Size] = [_THISROW].[Size] ,
                                                                                     [Brand] = [_THISROW].[Brand]) ,

                                                                                     IF([_THISROW].[No Dedicated parts?] ,
                                                                                               TRUE, 
                                                                                                IF([No dedicated parts?] = [_THISROW].[No Dedicated lens?] ,
                                                                                                     TRUE,
                                                                                                     FALSE
                                                                                                     )
                                                                                          ),
                                                                                     FALSE
                                                                                     )
                                                                             ),
                                                                                FALSE
                                                                                )
                                                                           ),
                                                                     FALSE
                                                                    )       
                                                             )
                       )
          ) * [Quantity per lens]

And when I try to add another If statement in the TRUE of this section:

IF(AND([No Dedicated brand?] = [_THISROW].[No Dedicated brand?] ,
             [Category] = [_THISROW].[Category]),
TRUE,
FALSE
)

It gives an error.

Jouko_Manninen

I closed the project and re-opened it and now it doesn’t come up with the error. So I can’t re-create the issue. I’ll return if the problem comes back.

Jouko

View solution in original post

Marc_Dillon

I reformatted your expression. Well, I tried to, I think I got lost in some indentation near the end. Suggestions to clean up the expression are forthcoming.

SUM(
  SELECT(
    Order[Reserved] , 
    OR ( 
      [Mount]=[_THISROW].[Part name] , 
      [Iris]=[_THISROW].[Part name] ,  
      [Generic]=[_THISROW].[Category],
      IF (
        OR(
          AND(
            [_THISROW].[Version] > 0 , 
            NOT([_THISROW].[category] = "Optional")
            ) ,
          AND(
            ISBLANK([_THISROW].[Version]), 
            NOT([_THISROW].[category] = "Optional")
            )
          ),
        IF( 
          OR(
            ISBLANK([_THISROW].[Dedicated Part?]) ,
            [_THISROW].[Dedicated Part?]=FALSE),
            IF(
              [_THISROW].[No Dedicated parts?] ,
              IF(
                AND(
                  [Category] = [_THISROW].[Category] ,
                  ISBLANK([_THISROW].[Size])
                  ),
                [Category] = [_THISROW].[Category] ,
                IF(
                  AND(
                    [Category] = [_THISROW].[Category] ,
                    [Size] = [_THISROW].[Size]
                    ) ,
                  TRUE,
                  FALSE
                  )
                ),
             IF(
              AND(
                [No Dedicated brand?] = [_THISROW].[No Dedicated brand?] ,
                [Category] = [_THISROW].[Category]
                ),
              TRUE,
              FALSE
              )
            ),
          IF(
            [Brand] = [_THISROW].[Brand] ,
            IF(
              AND(
               [Category] = [_THISROW].[Category] ,  
               ISBLANK([_THISROW].[Size]),
               [Brand] = [_THISROW].[Brand]
               ),
             [Category] = [_THISROW].[Category] ,
             IF(
               AND(
                 [Category] = [_THISROW].[Category] , 
                 [Size] = [_THISROW].[Size] ,
                 [Brand] = [_THISROW].[Brand]
                 ) ,
               IF(
                 [_THISROW].[No Dedicated parts?] ,
                 TRUE,
                 IF(
                   [No dedicated parts?] = [_THISROW].[No Dedicated lens?] ,
                   TRUE,
                   FALSE
                   )
                 ),
               FALSE
               )
             ),
           FALSE
         )
       ),
     FALSE
    )       
  )
)
) * [Quantity per lens]

What error?

Don’t believe so.

MultiTech

You’re right; there was at one point (I ran into that one ), they extended it to so crazy big number.

But there IS a limit on how much the formula validator can handle.

I’ve got one formula that the expression assistant says is okay no matter what I do to it; it’s just given up on the formula and let’s it through.

Just checked; that formula is 304 lines long - with 9,250 characters

On another note, from the looks of your formula, perhaps you might benefit from utilizing references

Jouko_Manninen

Same here with the formula validator.

What do you mean with utilizing references?

This is pretty simple two table reference but with a ridiculously complicated selection hierarchy between these tables. (Other holds orders and the other is just the parts stock.) I also have some slices to help with the selections.

MultiTech

Whenever I see crazy long formulas like this, where you’re basically including a bunch of conditional statements inside checking that one value matches another in a corresponding table - usually it seems that references (and the corresponding reverse references created) help solve some of the complexity.

With references, you can create list dereferences to pull out subsets of data, lists from related child records, instead of having to do a hard lookup with SELECT().

Bahbus

This is part of the reason I moved the BASE64 encoded fonts for my SVGs to their own read-only table. Cause its like 30k characters.

Jouko_Manninen

I closed the project and re-opened it and now it doesn’t come up with the error. So I can’t re-create the issue. I’ll return if the problem comes back.

Jouko

Marc_Dillon

I did some work to your expression, still trying to figure it all out. I encourage you to examine what I’ve changed it to, side-by-side with the original and try to see the changes I made. I’ll explain a couple. To start, you had a lot of unnecessary logical checks of the same thing, like:

IF( a=b , IF( AND( a=b , x=y…

No need to check that a equals b twice in this scenario.

Also if you just do:

IF( a=b , TRUE , FALSE)

That can just be shortened to a simple:

a=b

Here is the new expression so far:

SUM(
  SELECT(
    Order[Reserved] , 
    OR ( 
      [Mount]=[_THISROW].[Part name] , 
      [Iris]=[_THISROW].[Part name] ,  
      [Generic]=[_THISROW].[Category],
      
      IF(
      
      	AND(
        	[_THISROW].[category] <> "Optional" ,
          OR(
          	[_THISROW].[Version] > 0 ,
            ISBLANK([_THISROW].[Version])
          	) 
        )
 
        ,
        
        IF( 
          [_THISROW].[Dedicated Part?] <> TRUE
          ,
          IF(
            [_THISROW].[No Dedicated parts?] ,
            IF(
              AND(
                [Category] = [_THISROW].[Category] ,
                ISBLANK([_THISROW].[Size])
                ),
                
              TRUE,
 
              AND(
                [Category] = [_THISROW].[Category] ,
                [Size] = [_THISROW].[Size]
                )     
              ),
       
            AND(
              [No Dedicated brand?] = [_THISROW].[No Dedicated brand?] ,
              [Category] = [_THISROW].[Category]
              )
            )
          ,     
 
 
 
          IF(
            AND(
              [Brand] = [_THISROW].[Brand] ,
              [Category] = [_THISROW].[Category]
              )
            ,
            IF(               
             ISBLANK([_THISROW].[Size])               
             ,
             TRUE 
             ,
             IF(                
               [Size] = [_THISROW].[Size]                  
               ,
               IF(
                 [_THISROW].[No Dedicated parts?] 
                 ,
                 TRUE
                 ,                
                 [No dedicated parts?] = [_THISROW].[No Dedicated lens?]                 
                 ),
               FALSE
               )
               
             ),
           FALSE
          )
        )
      ,  
      FALSE  
      ) 
    )
  )
) * [Quantity per lens]

One part that I’m not sure how to fix without your input is in the middle:

IF(
  [_THISROW].[No Dedicated parts?] ,
  IF(
    AND(
      [Category] = [_THISROW].[Category] ,
      ISBLANK([_THISROW].[Size])
      ),
                
    TRUE,
 
    AND(
      [Category] = [_THISROW].[Category] ,
      [Size] = [_THISROW].[Size]
      )     
    ),
       
  AND(
    [No Dedicated brand?] = [_THISROW].[No Dedicated brand?] ,
    [Category] = [_THISROW].[Category]   
    )
  )

In the condition for the inner IF, you have an AND that includes [category]=[category]. Then for the else clause of that IF, you again check [category]=[category]. That else clause will never return TRUE, since you already know that [category] does not equal [category]. Not sure what your requirements here are, but something is either wrong, or just inefficient.

Jouko_Manninen

Hi! Thank you for the niput! I sure will check your suggestions since I have no experience with expressions/ coding so I’m sure theres A lot to improve/ streamline.

This whole expression nightmare is basically A ”hierarcial” selection rule set between two tables, (with some exclusion Logic) so some of those if’s statements that seems to refer itself, is avtually checking the Same named column at another table. (And might have unecessary checks that I have thrown in just to make sure)

And some are just ”placeholder” until I find A better solution to continue further.

There’s few branches to end up in different outcomes and I’ve been playing around with the order of those branches to get what I want and that does leave some ”unecessary” trues and falses just to get that result to be used at another selection branch as A decision maker if it should follow this or that branch.

The code works at the moment, (expression checklist does not) and I was wondering if theres some limits of how much code there can be.

And when I Tried to add more code to this, it broke down but seems to work now when I restarted the browser so no idea what might have Caused the error.

Jouko

Steve

Here’s my reformatting try.

(
  SUM(
    SELECT(
      Order[Reserved],
      OR(
        ([Mount] = [_THISROW].[Part name]),
        ([Iris] = [_THISROW].[Part name]),
        ([Generic] = [_THISROW].[Category]),
        IF(
          OR(
            AND(
              ([_THISROW].[Version] > 0),
              NOT([_THISROW].[category] = "Optional")
            ),
            AND(
              ISBLANK([_THISROW].[Version]),
              NOT([_THISROW].[category] = "Optional")
            )
          ) ,
          IF(
            OR(
              ISBLANK([_THISROW].[Dedicated Part?]),
              ([_THISROW].[Dedicated Part?] = FALSE)
            ),
            IF(
              [_THISROW].[No Dedicated parts?],
              IF(
                AND(
                  ([Category] = [_THISROW].[Category]),
                  ISBLANK([_THISROW].[Size])
                ),
                ([Category] = [_THISROW].[Category]),
                IF(
                  AND(
                    ([Category] = [_THISROW].[Category]),
                    ([Size] = [_THISROW].[Size])
                  ),
                  TRUE,
                  FALSE
                )
              ),
              IF(
                AND(
                  ([No Dedicated brand?] = [_THISROW].[No Dedicated brand?]),
                  ([Category] = [_THISROW].[Category])
                ),
                TRUE,
                FALSE
              )
            ),
            IF(
              ([Brand] = [_THISROW].[Brand]),
              IF(
                AND(
                  ([Category] = [_THISROW].[Category]),
                  ISBLANK([_THISROW].[Size]),
                  ([Brand] = [_THISROW].[Brand])
                ),
                ([Category] = [_THISROW].[Category]),
                IF(
                  AND(
                    ([Category] = [_THISROW].[Category]),
                    ([Size] = [_THISROW].[Size]),
                    ([Brand] = [_THISROW].[Brand])
                  ),
                  IF(
                    [_THISROW].[No Dedicated parts?],
                    TRUE,
                    IF(
                      ([No dedicated parts?] = [_THISROW].[No Dedicated lens?]),
                      TRUE,
                      FALSE
                    )
                  ),
                  FALSE
                )
              ),
              FALSE
            )
          ),
          FALSE
        )
      )
    )
  )
  * [Quantity per lens]
)

Steve

In my opinion…

I cannot believe how convoluted this expression is. I cannot believe it “works”. I suspect you haven’t rigorously tested it. The code is not supportable. When you return to this code after a few weeks, it’ll make no sense to you. Anyone else who has to step in and maintain your code will not be able to understand it. Putting this expression into any production app is entirely irresponsible.

…in my opinion.

Jouko_Manninen

Thank you for your points!

Will print and frame this to my Office wall for the guys to think again why they shouldn’t put an artist to do the engineers work!

MultiTech

@Jouko_Manninen I’m just taking a look at the formula that @Steve proposed; he’s right, I"m having a hard time figuring out what’s going on.

I think you would benefit from learning about the other formulas you can use; like:

FYI for Steve

It’s hard to search for IN() in the documentation:

Searching for “in” or “in()” doesn’t produce a result; I have to search for list, then open that article, scroll to the bottom where you’ve listed other useful functions - THAT’s where I finally find it.

Another thing that would help, might be to learn how you could leverage Slices to create sub-sets of data.

I notice you’ve got a lot of “dedicated parts” stuff going on;

If that were me, I would have a slice “Dedicated_Parts” that holds those in a separate list for easy reference.

Your formulas look a lot like mine when I first started; you’ve got TRUE/FALSE as the result of your IF() statements - you don’t need to include the IF(), you can leave things with just the logic operators (you just have to figure out the correct logic order).

Jouko_Manninen

Yeah, I have the dedicated parts table. The complications and confusion starts not with the dedicated parts itself, but with the Logic how to tell the other part not to get selected if the dedicated part should be selected instead.

But if you can check my other post below, i try to break down what I’m trying to achieve.

And if this could be handled by enumlist instead. (Point 1. In the post)

Thanks again for your suggestions!

Jouko

Jouko_Manninen

Yeah, I’m very aware that it’s A Mess, it worked on certain point but those dedicated parts are killing me. And the further it Goes, the more it fails, It worked because some crucial variables were missing and the rest was just thrown in there.

It’s work in progress and I’m constantly changin and iterating it closer to the desired result that hopefuly can then be cleaned and optimized, if just I could it get remotely doing what I want. But not there yet.

Steve is on point with the comment on not to be able to figure it out A week later what is going on since that’s exactly how I’m working with this, few hours A week Here and there if I’m lucky and basically restarting every time

So strategy change:

Slices seems most straight forward Method for me at the moment without the need to go too deep in the coding. (This is also part of figuring out how and what information is necessary for the tables to get this selection logic to work to begin with)

With that in mind, is there A way to filter A selection ”name” from A enum list? That would be another and much straight forward and cleaner solution for what I’m trying to achieve.

Something like:
Select this column name from this column enum list? And preferably, these column names , not just one name?

Select ”Nikon” from ”Nikon” , ”Leica” , ”Canon” , etc…

This way I could ”prefill” all parts to get selected by all lenses within the enumlist, and start excluding them by name when it is required. (Maybe)

Because I’m not having problem to get things selected, issue is to not get something selected when dedicated parts are involved. Which is the Mess with the dedicated parts.

(No need to try to solve this, just trying to map out what I’m trying to achieve)

I’m simply trying to look solution for this:
Is this part dedicated part? (This is true/false question)
If yes, use these selection variables.
(Pretty simple and straight forward)

If not: (this I’m struggling with)
Does this part have A dedicated part?
If true
(This means that this part has been duplicated
Since dedicated part is always A duplicate from the original)

So i search if this part name can be found twice in the table, (or sliced to table called dedicated parts) then it should check is it dedicated for the brand, or the lens, and then it should check if the lens that is selecting this part, should select this, or the dedicated part and by brand or lens name.

Because there’s also hierarchy for brand and lens. If dedicated part is for lens, it should overrides selection if the dedicated part is for brand.

If this part Don’t have dedicated part, then use this these selection variables.
Which also is pretty straight forward.

Theres also A version Numbers to TaKe account but I haven’t got that far yet. That’s one of the placeholders at the moment in code, but I think it’s the first thing to ”sort” out before going down the rabbit hole?

It is most likely going to be Same problem than with the dedicated parts since they are also A duplicate from the original part, and then somehow should only get selected if the version Number column is empty or is the highest of those parts that have multiple versions.

And on top of this, theres going to be hundreds of parts to ”filter through” and maybe 10 lenses at A time (in A week) to run through this selection. (When lens is work in progress it should launch the code. I’m already noticing that it takes some time to get updatedbin the table. Appsheet side syncing takes few seconds at the moment but the updating to the table goes few seconds per part. And there is going to be at least hundred per lens,

And this even isn’t the original topic I started with but if I get some suggestion on points

& 4. I think I can reconsider my approaches (and life choises)

Happy new year!

Jouko_Manninen

This is how simple this would be if I wouldn’t need to deal with the dedicated parts:

IF(AND([Category] = [_THISROW].[Category] , ISBLANK([_THISROW].[Size])),
[Category] = [_THISROW].[Category] ,
IF(AND([Category] = [_THISROW].[Category] , [Size] = [_THISROW].[Size]) ,
TRUE,
FALSE )

Marc_Dillon

This is equivalent to:

AND(
  [Category] = [_THISROW].[Category] ,
  OR(
    ISBLANK([_THISROW].[Size]) ,
    [Size] = [_THISROW].[Size]
    )
  )

Just FYI

Jouko_Manninen

Thanks!

I think I’ll check these ”cleanups and optimizations” out after I have found the solution to the main issues which is finding the correct structural/ approach to this whole thing because now I Wouldn’t understand what this does If I would try to read it mixed in somewhere between the code! I need to stick with the dummy way so I can read and understand it properly

Jouko_Manninen

This is in context in the app what the code does in this column, it counts the amount of parts being ”reserved” when the correlating lens is in work in progress.

Jouko_Manninen

Ok,

so now I cleaned up the if’s as Marc Dillon suggested, and it’s more readable now. (At least for me)
I left out the part for now that deals with the exclusions with dedicated parts since that’s what I need to rethink how to solve and making this over complicated:

LEFT OUT
If this part is not dedicated part but it has dedicated part:
(This means that this part has been duplicated
Since dedicated part is always A duplicate from the original)

So i search if this part name can be found twice in the table, (or sliced to table called dedicated parts) then it should check is it dedicated for the brand, or the lens, and then it should check if the lens that is selecting this part, should select this, or the dedicated part and by brand or lens name.

Because there’s also hierarchy for brand and lens. If dedicated part is for lens, it should overrides selection if the dedicated part is for brand.

Here’s current solution which works. (Except now the original part gets also selected even if
only the dedicated part should be selected, but I guess we can live with this for now since this is anyway just a reference to tell us how much parts we should have in the storage in near future.

And the version selection being a placehorder until I find a solution for that

SUM(
  SELECT(
    Order[Reserved] , 
    OR ( 
      [Mount]=[_THISROW].[Part name] , 
      [Iris]=[_THISROW].[Part name] ,  
      [Generic]=[_THISROW].[Category], 
      IF(
      
      	AND(
        	[_THISROW].[category] <> "Optional" ,
          OR(
          	[_THISROW].[Version] > 0 ,
               ISBLANK([_THISROW].[Version])
          	) 
               ),

          IF( 
            OR(
                  ISBLANK([_THISROW].[Dedicated Part?]) ,
                  [_THISROW].[Dedicated Part?]=FALSE),

              AND(
                      [Category] = [_THISROW].[Category] ,
                 OR(
                       ISBLANK([_THISROW].[Size]) ,
                       [Size] = [_THISROW].[Size]
                       )
                ),

            IF(
               AND(
                        [Brand] = [_THISROW].[Brand] ,
                        [Category] = [_THISROW].[Category] ,
                  OR(
                        ISBLANK([_THISROW].[Size]) ,
                        [Size] = [_THISROW].[Size]
                        )
                        ),

               TRUE,
               [Dedicated ID] = [_THISROW].[Dedicated ID]
               )
        ),
        FALSE  
      ) 
    )
  )
) * [Quantity per lens]

So few questions to start tackling the dedicatep part clone selection issue:

Dillons suggestions with the IN() and SWITCH() might be this but can they work the way described above? (That I don’t need to type the name to look for but it can be taken from the column)
I think I had this kind of approach at somepoint but it kept giving the can’t compare text to list or something.

Jouko

Jouko_Manninen

@Steve Is it possible to move this to another topic that I started while back: Hierarchial selection with “exclusion”

Since all this is now more that category than my original issue. (Which got answered already)

Steve

There’s no way (to my knowledge) to move a thread to another topic–other than just posting new comments in the other topic.

Jouko_Manninen

Still will get super complicated with the exclusion ifs or slices or whatever. So now I figured that because if there’s going to be
A: Higher version from part
B: Dedicated part for Brand
C: Dedicated part for Lens

This always means that then this (original) part has a duplicate.

And dedicated lens overrides dedicated brand, but both can have a higher
version number, and it always should select the highest version number

So maybe I first search if there’s a duplicate for this part by adding a column [Has duplicate?]
And if this is True then slice all of them to a new table duplicates

So if there’s no duplicate, then this match for [Category] or [Category] + [Size] is enough

If there is:
Then always add 1000 for each match for [Category]+[Size]+[Brand]+[Lens]
and + the version number and only select the one with highest number.