Filtering expression

[jaichith]

Dear friends,

                      I have made an expression to select list of rows with certain conditions ( This expression is used in filter dashboard to select values/ rows based on certain criteria).

I face some problem with the expression. I have used 4 conditions  in IFS function in each block ( Totally 4 Blocks are there). Here in general argument is to take blank values/ rows or not.

Condition 1.      ([_THISROW].[INCLUDE EMPTY ORDER DATE]="ONLY EMPTY"),ISBLANK([ORDER DATE]) ----- This works ( Selecting only blank values)

Condition2 : AND(ISBLANK([_THISROW].[ORDER START DATE]), ([_THISROW].[INCLUDE EMPTY                                                                                                ORDER DATE] = "YES")), ISBLANK([ORDER DATE]),  

                                ------------ Not working Expected. (Want to select blank values rows if this row order date is blank and include empty order date is = " YES"

Condition 3 : AND(ISBLANK([_THISROW].[ORDER START DATE]),ISBLANK([_THISROW].[INCLUDE                                                                                                                                       EMPTY ORDER DATE])), TRUE,

       ----- Not Working as expected ( Want to select rows if 2 columns are blank in this  table)

Condition 4:  

                        (isnotblank([_THISROW].[ORDER START DATE])),
                                    OR(
                                      IFS(
                                           ([_THISROW].[INCLUDE EMPTY ORDER DATE]="YES"), ISBLANK([ORDER DATE])
                                             ),
                               ([ORDER DATE]>=[_THISROW].[ORDER START DATE])
                                          )

                                                                                                  --- It returns only blank values ( Empty ) and                                      doesn't works this sub expression ([ORDER DATE]>=[_THISROW].[ORDER START DATE])

       ( Want to select values if [_THISROW].[ORDER START DATE] is not blank and [_THISROW].[INCLUDE EMPTY ORDER DATE]="YES") the select BLANK ([ORDER DATE]. or ([ORDER DATE]>=[_THISROW].[ORDER START DATE]).

The expression full is here

select(CONSIGNMENTS[CONSIGNMENTS ID],
AND(

#Block 1

ifs(
                    ([_THISROW].[INCLUDE EMPTY ORDER DATE]="ONLY EMPTY"),ISBLANK([ORDER DATE]),

                  AND(ISBLANK([_THISROW].[ORDER START DATE]), ([_THISROW].[INCLUDE EMPTY ORDER                                                                                                           DATE] = "YES")), ISBLANK([ORDER DATE]),

                     AND(ISBLANK([_THISROW].[ORDER START DATE]),ISBLANK([_THISROW].[INCLUDE EMPTY                                                                                                                                              ORDER DATE])), TRUE,

                         (isnotblank([_THISROW].[ORDER START DATE])),
                                    OR(
                                      IFS(
                                           ([_THISROW].[INCLUDE EMPTY ORDER DATE]="YES"), ISBLANK([ORDER DATE])
                                             ),
                               ([ORDER DATE]>=[_THISROW].[ORDER START DATE])
                                          )
                  ),

# Block 2

ifs(
                         ([_THISROW].[INCLUDE EMPTY ORDER DATE]="ONLY EMPTY"),ISBLANK([ORDER DATE]),

                 AND(ISBLANK([_THISROW].[ORDER END DATE]), ([_THISROW].[INCLUDE EMPTY ORDER                                                                                                                    DATE]= "YES")), ISBLANK([ORDER DATE]),

                    AND(ISBLANK([_THISROW].[ORDER END DATE]), ISBLANK([_THISROW].[INCLUDE EMPTY                                                                                                                                                  ORDER DATE])), TRUE,

                                        (isnotblank([_THISROW].[ORDER END DATE])),
                                                   OR(
                                                       IFS(
                                            ([_THISROW].[INCLUDE EMPTY ORDER DATE]="YES"), ISBLANK([ORDER DATE])
                                                            ),
                                                    ([ORDER DATE]<=[_THISROW].[ORDER END DATE])
                                                               )
                    ),

# Block 3

ifs(
                 ([_THISROW].[INCLUDE EMPTY DISPATCH DATE]="ONLY EMPTY"), ISBLANK([DATE OF                                                                                                                                                                               DISPATCH]),

             AND(ISBLANK([_THISROW].[DISPATCH START DATE]), ([_THISROW].[INCLUDE EMPTY                                    DISPATCH DATE] = "YES")), ISBLANK([DATE OF DISPATCH]),

          AND(ISBLANK([_THISROW].[DISPATCH START DATE]),ISBLANK([_THISROW].[INCLUDE EMPTY                                                                                                                                               DISPATCH DATE])), TRUE,
                                      (isnotblank([_THISROW].[DISPATCH START DATE])),
                                 OR(
                                        IFS(
                             ([_THISROW].[INCLUDE EMPTY DISPATCH DATE]="YES"), ISBLANK([DATE OF                                                                                                                                                                                          DISPATCH])
                                             ),
                                       ([DATE OF DISPATCH]>=[_THISROW].[DISPATCH START DATE])
                                      )
                  ,

# Block 4

IFS(
     ([_THISROW].[INCLUDE EMPTY DISPATCH DATE]="ONLY EMPTY"),ISBLANK([DATE OF DISPATCH]),

           AND(ISBLANK([_THISROW].[DISPATCH END DATE]), ([_THISROW].[INCLUDE EMPTY DISPATCH                                                                                        DATE]== "YES")), ISBLANK([DATE OF DISPATCH]),

              AND(ISBLANK([_THISROW].[DISPATCH END DATE]), ISBLANK([_THISROW].[INCLUDE EMPTY                                                                                                                                         DISPATCH DATE])), TRUE,

                       (isnotblank([_THISROW].[DISPATCH END DATE])),
                           OR(
                                  IFS(
                         ([_THISROW].[INCLUDE EMPTY DISPATCH DATE]="YES"), ISBLANK([DATE OF DISPATCH])
                                         ),
                            ([DATE OF DISPATCH]<=[_THISROW].[DISPATCH END DATE])
                                        )
     )

)
)

@AleksiAlkio @dbaum @Suvrutt_Gurjar please help me with this.  

[jaichith]

app UI where entries are made

[jaichith]

@MultiTech could please help with this?

[MultiTech]

I'll be honest, I didn't even try to wrap my head around the formulas; I took your post and fed it to my appsheet Ai assistant Appster and it gave me the following answer.   

• (Is it correct?)

 ---------------------------------------------------------------------------------------------------------------------

 Firstly, it's important to be aware that using multi-step `SELECT()` function could lead to potential performance issues due to its high computation overhead. As a best practice, it's more efficient and ideal to manage data using References, Reverse References, Dereferences, List-Dereferences, the Current User system, or Data Subsets.

Let's break down your problem and analyze how we can optimize it.

As per your requirements, we aim to do the following:

1. If 'ORDER START DATE' in the current row is not blank and 'INCLUDE EMPTY ORDER DATE' is "YES", select rows with 'ORDER DATE' as blank or 'ORDER DATE' greater than or equal
   to 'ORDER START DATE'.
2. Otherwise, select all consignments.

However, your current formula seems overly complicated with repeated `IFS` conditions which makes it confusing and more prone to inefficiency.

By simplifying the requirements and grouping situations collectively, we can drastically optimize your formula:

SELECT(CONSIGNMENTS[CONSIGNMENTS ID],
       AND(
           OR(
               ([_THISROW].[INCLUDE EMPTY ORDER DATE] = "YES"),
               ([_THISROW].[ORDER START DATE] <= [ORDER DATE]),
               ISBLANK([_THISROW].[ORDER START DATE])
              ),
           OR(
               ([_THISROW].[INCLUDE EMPTY ORDER DATE] = "YES"),
               ([_THISROW].[ORDER END DATE] >= [ORDER DATE]),
               ISBLANK([_THISROW].[ORDER END DATE])
              ),
           OR(
               ([_THISROW].[INCLUDE EMPTY DISPATCH DATE] = "YES"),
               ([_THISROW].[DISPATCH START DATE] <= [DATE OF DISPATCH]),
               ISBLANK([_THISROW].[DISPATCH START DATE])
              ),
           OR(
               ([_THISROW].[INCLUDE EMPTY DISPATCH DATE] = "YES"),
               ([_THISROW].[DISPATCH END DATE] >= [DATE OF DISPATCH]),
               ISBLANK([_THISROW].[DISPATCH END DATE])
              )
         )
)

This formula covers all the above cases and should significantly improve the performance of your AppSheet app.

Remember this is just a solution based on the complexity of your query, yet using `SELECT()` is not advised due to mentioned reasons. If you find this solution not satisfactory or posing challenges in application, feel free to reach out for further assistance.

---------------------------------------------------------------------------------------------------

You can learn more about my AppSheet Ai project here: www.MultiTechVisions.com/Appster

[jaichith]

bro, 

      there is one condition which has been excluded in your code spinet. 

([_THISROW].[INCLUDE EMPTY DISPATCH DATE]="ONLY EMPTY"),ISBLANK([DATE OF DISPATCH]).

This part of expression decides to get only empty get values  (excluding not blank values). 

this select () function i am using it to GENERATE PDF FILES. This select function collects consignments based on the given filter condition criteria. 

Bro, I am struck with this. I request you to go through the expression which I have post and provide me a solution. 

[MultiTech]

Yup... it looks like it just left that part out. 😂

• I wonder if there's any logic for why it did that?  Like is there something that covers that in the logic it provided? 
  • Or did it just literally just exclude that?

I fed your reply back to Appster, it provided the following monster formula. haha

----------------------------------------------------------------------------------------------------------------

I got it, thanks for the clarification.

Let's reintroduce your 'ONLY EMPTY' criteria into the simplified formula. Also, though I understand your need to use `SELECT()` here, keep in mind its potential performance issues, especially when working with larger data sets. Here's the modified version of the formula:

SELECT(
       CONSIGNMENTS[CONSIGNMENTS ID], 
       AND(
           OR(
              AND([_THISROW].[INCLUDE EMPTY ORDER DATE] = "ONLY EMPTY", ISBLANK([ORDER DATE])),
              AND([_THISROW].[INCLUDE EMPTY ORDER DATE] = "YES", OR(ISBLANK([ORDER DATE]), [_THISROW].[ORDER START DATE] <= [ORDER DATE])),
              AND(ISBLANK([_THISROW].[INCLUDE EMPTY ORDER DATE]), ISNOTBLANK([ORDER DATE]), [_THISROW].[ORDER START DATE] <= [ORDER DATE])
           ),
           OR(
               AND([_THISROW].[INCLUDE EMPTY ORDER DATE] = "ONLY EMPTY", ISBLANK([ORDER DATE])),
               AND([_THISROW].[INCLUDE EMPTY ORDER DATE] = "YES", OR(ISBLANK([ORDER DATE]), [_THISROW].[ORDER END DATE] >= [ORDER DATE])),
               AND(ISBLANK([_THISROW].[INCLUDE EMPTY ORDER DATE]), ISNOTBLANK([ORDER DATE]), [_THISROW].[ORDER END DATE] >= [ORDER DATE])
           ),
           OR(
               AND([_THISROW].[INCLUDE EMPTY DISPATCH DATE] = "ONLY EMPTY", ISBLANK([DATE OF DISPATCH])),
               AND([_THISROW].[INCLUDE EMPTY DISPATCH DATE] = "YES", OR(ISBLANK([DATE OF DISPATCH]), [_THISROW].[DISPATCH START DATE] <= [DATE OF DISPATCH])),
               AND(ISBLANK([_THISROW].[INCLUDE EMPTY DISPATCH DATE]), ISNOTBLANK([DATE OF DISPATCH]), [_THISROW].[DISPATCH START DATE] <= [DATE OF DISPATCH])
           ),
           OR(
               AND([_THISROW].[INCLUDE EMPTY DISPATCH DATE] = "ONLY EMPTY", ISBLANK([DATE OF DISPATCH])),
               AND([_THISROW].[INCLUDE EMPTY DISPATCH DATE] = "YES", OR(ISBLANK([DATE OF DISPATCH]), [_THISROW].[DISPATCH END DATE] >= [DATE OF DISPATCH])),
               AND(ISBLANK([_THISROW].[INCLUDE EMPTY DISPATCH DATE]), ISNOTBLANK([DATE OF DISPATCH]), [_THISROW].[DISPATCH END DATE] >= [DATE OF DISPATCH])
           )
)
)

This formula checks for every combination of your criteria, including the 'ONLY EMPTY' condition. Please give it a try and let me know if it accomplishes what you need.

------------------------------------------------------------------------------------------

I'm gonna ask it to simplify....

[MultiTech]

Thank you, that's perfect; now can you simplify the formula, while still retaining every aspect of the logic of the formula?

----------------------------------------------------------------------------------------------------------------

We can make this formula simpler by consolidating duplicate checks and removing redundant conditions.

Here's a simplified version of the same formula:

SELECT(
 CONSIGNMENTS[CONSIGNMENTS ID], 
 AND(
 OR(
    AND(
        [_THISROW].[INCLUDE EMPTY ORDER DATE] = "ONLY EMPTY", 
        ISBLANK([ORDER DATE])
    ),
    AND(
        OR(
            [_THISROW].[INCLUDE EMPTY ORDER DATE] = "YES",
            ISBLANK([_THISROW].[INCLUDE EMPTY ORDER DATE])
        ),
        OR(
            [_THISROW].[ORDER START DATE] <= [ORDER DATE], 
            ISBLANK([ORDER DATE])
        )
    )
 ),
 OR(
    AND(
        [_THISROW].[INCLUDE EMPTY DISPATCH DATE] = "ONLY EMPTY", 
        ISBLANK([DATE OF DISPATCH])
     ),
     AND(
         OR(
            [_THISROW].[INCLUDE EMPTY DISPATCH DATE] = "YES", 
            ISBLANK([_THISROW].[INCLUDE EMPTY DISPATCH DATE])
         ),
         OR(
            ISBLANK([DATE OF DISPATCH]), 
            [_THISROW].[DISPATCH START DATE] <= [DATE OF DISPATCH]
         )
     )
    )
 )
)

Please remember to validate and test this formula within your app to make sure it matches your required logic. In case of issues or errors, please let me know so I can assist you further.

[MultiTech]

Can we simplify any further? Possibly by completely restructuring the formula? - For example, we might create a series of nested IF() statements where each branch executes a specific SELECT() for that branch? - Or some other restructuring of the formula?

-----------------------------------------------------------------------------------------------------

Yes, restructuring the formula to use nested `IF()` statements with specific `SELECT()` for each branch could certainly simplify the formula and enhance its legibility. Here's a possible restructure:

IFs(
[_THISROW].[INCLUDE EMPTY ORDER DATE] = "ONLY EMPTY",
	SELECT(CONSIGNMENTS[CONSIGNMENTS ID], ISBLANK([ORDER DATE])),
	
[_THISROW].[INCLUDE EMPTY ORDER DATE] = "YES",
	SELECT(CONSIGNMENTS[CONSIGNMENTS ID],
		OR(
			ISBLANK([ORDER DATE]),
			AND(
				[_THISROW].[ORDER START DATE] <= [ORDER DATE],
				[_THISROW].[ORDER END DATE] >= [ORDER DATE]
			)
		)
	),
	
[_THISROW].[INCLUDE EMPTY DISPATCH DATE] = "ONLY EMPTY",
	SELECT(CONSIGNMENTS[CONSIGNMENTS ID], ISBLANK([DATE OF DISPATCH])),

[_THISROW].[INCLUDE EMPTY DISPATCH DATE] = "YES",
	SELECT(CONSIGNMENTS[CONSIGNMENTS ID],
		OR(
			ISBLANK([DATE OF DISPATCH]),
			AND(
				[_THISROW].[DISPATCH START DATE] <= [DATE OF DISPATCH],
				[_THISROW].[DISPATCH END DATE] >= [DATE OF DISPATCH]
			)
		)
	)
)

This formula first checks if the "INCLUDE EMPTY ORDER DATE" or "INCLUDE EMPTY DISPATCH DATE" of `_THISROW` is set to "ONLY EMPTY", it selects only records where the corresponding date is blank.

If the "INCLUDE EMPTY..." field is set to "YES", it selects records where either the corresponding date is blank or the row's date is in between the start and end dates from `_THISROW`.

If "INCLUDE EMPTY..." field is blank, it directly selects records where the row's date is in between the start and end dates from `_THISROW`.

Please note that this is just one way to structure the formula, you would still need to validate this formula within your app to make sure it matches your required logic. If there are issues, please let me know so I can assist you further!