Create "Slice" of one table referencing values in another tables form

[jonbowles84]

Hi, I am trying to create a slice that displays all data in a table where the values in a column are between the range of 2 values in a second tables form. I have attached a screenshot of what I am trying to achieve and the expression Im trying to use is:

FILTER(
  "Filter",
  AND(
    ([Chainage] > [Start Chainage Range:]),
    ([Chainage] < [End chainage range:])
  )
)

I hope that makes sense, any help would be greatly appreciated!!

[Suvrutt_Gurjar]

If you are using the expression as a slice filter, then it needs to evaluate as TRUE/FALSE for each row key. So you may wish to try with an expression something like

IN([Key Column], "The Filter Expression you have mentioned above")

https://help.appsheet.com/en/articles/2357277-in

As a side note you may wish to not use special characters such as ":" in your column names as at times those could clash with the reserved words that app editor uses. You may also wish to avoid function names for tables, slices such as "Filter" because those again could clash with reserved words. In your case the expression has become FILTER ( Filter.... ) which could be confusing to debug. 

[jonbowles84]

Hi, thankyou. I am completely new to AppSheet! I have removed the ":" from the column names 🙂 with your expression, could you give me a snippet to try, Ive tried quite literally what you suggested but it comes up with other errors:

IN([Chainage], "FILTER(
  "Filter",
  AND(
    ([Chainage] > [Start Chainage Range:]),
    ([Chainage] < [End chainage range:])
  )
)
")

[Suvrutt_Gurjar]

Please remove quotes. Those were just to indiatae as a placeholder. Please take a look at the article shared for examples and more help articles at https://www.appsheet.com/Support

IN([Chainage], FILTER(

  "Filter",

  AND(

    ([Chainage] > [Start Chainage Range:]),

    ([Chainage] < [End chainage range:])

  )

)

)

[jonbowles84]

Yay! that worked! Could I trouble you to go 1 step further and show me how to include this in the same

IN([Date], FILTER(

  "Filter",

  AND(

    ([Date] >= [From Date]),

    ([Date] <= [To Date])

  )

)

)

expression?

[Suvrutt_Gurjar]

Hi,

Since I am unaware of your exact table and column structure, I am afraid I may not be able to suggest a syntactically correct expression without knowing those details. Your expression, looks good in general.

[jonbowles84]

Thanks, it works, I just need to figure out how to combine the 2 expressions 

[TeeSee1]

I assume you just want additional constraints based on the dates to the existing ones. In this case, you can just add them within the AND() section without changing anything else in the initial filter condition.

A tip going forward...

You always need to pay attention to what an expression returns.

Since FILTER returns a list of 'KEY' col values, you do not want to compare [Date] field with the [_ROWNUMBER] of your Filter table. So retain [Chainage], which is of number type matching the type of [_ROWNUMBER].

Always consult relevant help documents. - https://help.appsheet.com/en/articles/2357308-filter

because I also made a lot of mistakes without understanding the exact definition of the functions.

[jonbowles84]

Thanks @TeeSee1 So Ive added it here: 

IN([Chainage], FILTER(

  "Filter",

  AND(

    ([Chainage] >= [Start Chainage Range]),

    ([Chainage] < [End chainage range]),

    ([Date] >= [From Date]),

    ([Date] < [To Date])

  )

)

)

and the result is empty, with a warning saying:  

Note: This expression could impact performance. (The value of column 'Chainage') is one of the values in the list (The list of values of column '_RowNumber' ....from rows of table 'Filter' ....where this condition is true: (ALL these statements are true: ........1: (The value of column 'Chainage') is greater than or equal to (The value of column 'Start Chainage Range') ........2: (The value of column 'Chainage') is less than (The value of column 'End Chainage Range') ........3: (The value of column 'Date') is greater than or equal to (The value of column 'From Date') ........4: (The value of column 'Date') is less than (The value of column 'To Date')))

[TeeSee1 · Accepted solution]

My apologies. I was not very careful at looking at your table definitions.

Assuming you are creating a slice of RAW SG APP based on the values of Filter table which has only one row, I believe this is what you want to have in the filter of your slice.

AND(
([Chainage] >= LOOKUP( "enter the key value of your Filter row" , "Filter" , "_RowNumber" , "Start Chainage Range" )),

([Chainage] < LOOKUP( "enter the key value of your Filter row" , "Filter" , "_RowNumber" , "End Chainage Range" )),

([Date] >= LOOKUP( "enter the key value of your Filter row" , "Filter" , "_RowNumber" , "From Date" )),

([Date] < LOOKUP( "enter the key value of your Filter row" , "Filter" , "_RowNumber" , "To Date" ))

)

where  "enter the key value of your Filter row" is typically 2 if you only have one row in your Filter table.

Another question. I do not see [Date] field in your RAW SG APP table. Where is it defined?

[jonbowles84]

Thanks @TeeSee1: I have Date in the RAW SG APP table, I have used your suggestion

, but still get a similar warning 🙂 

[jonbowles84]

This is the full warning:

Note: This expression could impact performance. ALL these statements are true: ....1: (The value of column 'Chainage') is greater than or equal to (One randomly chosen value from this list ( ............The list of values of column 'Start Chainage Range' ............from rows of table 'Filter' ............where this condition is true: ((The value of column '_RowNumber') is equal to ("2")))) ....2: (The value of column 'Chainage') is less than (One randomly chosen value from this list ( ............The list of values of column 'End Chainage Range' ............from rows of table 'Filter' ............where this condition is true: ((The value of column '_RowNumber') is equal to ("2")))) ....3: (The value of column 'Date') is greater than or equal to (One randomly chosen value from this list ( ............The list of values of column 'From Date' ............from rows of table 'Filter' ............where this condition is true: ((The value of column '_RowNumber') is equal to ("2")))) ....4: (The value of column 'Date') is less than (One randomly chosen value from this list ( ............The list of values of column 'To Date' ............from rows of table 'Filter' ............where this condition is true: ((The value of column '_RowNumber') is equal to ("2"))))

[TeeSee1]

I get this performance impact warning all the time. But unless you encounter real issues I would simply ignore it. 

The important thing is that you are getting what you want. (for that matter, you may have to get rid of the qutation marks around "2"?)

If you know that you are dealing with a large dataset (here) then you should definitely test your app before you release it to the users and if performance is not satisfactory, you should re-design your architecture. Perhaps you need to engage a professional consulting service in such a case.

[jonbowles84]

Thankyou!! Its all working now, Im looking at getting a developer involved but i always like to try figure it out a bit first of all! Thanks again! 🙂