How to improve the expression?

AppSheet AppSheet Q&A
1

how to improve the below expression? Initially it works like a charm but when data gets bigger it slows down significantly.

Requirement: The list of students whose marks are not entered in the assessment table. as the data is entered, the list gets smaller for specific subject

SELECT(Students[Reg#], [Grade] = [_THISROW].[Grade])- SELECT(Assessment[Reg#],AND(
    [Grade] = [_THISROW].[Grade],
    [Subject] = [_THISROW].[Subject],
    [Category] = [_THISROW].[Category]
  )
)
2

If Reg# is the key column for the Students table, and Reg# in the Assessment table is of type Ref to Students, you should be able to do this:

SELECT(
  Students[Reg#],
  [Grade] = [_THISROW].[Grade],
  ISBLANK(
    SELECT(
      [Related Assessments][_RowNumber],
      AND(
        [Grade] = [_THISROW].[Grade],
        [Subject] = [_THISROW].[Subject],
        [Category] = [_THISROW].[Category]
      )
    )
  )
)
1 Like
3

Thank you dear @Steve for your response but it is not working as it should. Yes Reg# is Ref to Students in Assessment table. I want to filter out the students from Students table whose marks are not entered in the Assessment table. If a student’s marks are entered once for specific subject, then it should not appear in the list again.

4

I don’t think there is any way to improve that expression. Where are you using it?

2 Likes
5

I am using it in School Information System app for my school. I am using this expression in Valid if to limit the list.

6

Another version of this expression I have tried

SELECT(
  Students[Reg#],
  AND([Grade]=[_THISROW].[Grade],
   not( 
     [Reg#]=ANY(SELECT([Related Assessments][Reg#],AND(
          
        [Grade] = [_THISROW].[Grade],
        [Subject] = [_THISROW].[Subject],
        [Category] = [_THISROW].[Category]
      ))))))
7

So where are you experiencing this slowdown? Like in the UI when the form view opens, or after selecting the grade in a form view? How do you know it is this expression that is causing your slowdown? Two rather basic selects happening a single time really shouldn’t cause much of a slowdown unless we’re talking about like hundreds of thousands of records.

That other expression you just listed wouldn’t even work.

1 Like
8

Near about after entering 500 records, the list loads very slow. Yes, in the form view after selecting grade and subject the list of students loads.

The expression I have just posted is also working fine and filtering the records properly.

9

image
image485Ă—470 14.5 KB

Here the filtered list of students loads

10

Only 500? Nah, something else is going on here.

That new expression may work for some instances, but it is not logically correct, it won’t work for all.

2 Likes
11

1628 records exactly. But only this expression is applied in valid if. Nothing else is here worth mentioning.