Use Appsheet Expressions to get result of a DB SQL Statement

[Henry_Scott]

Hi Community

We need to represent a data set in a Slice OR a combination of a Slice and further Appsheet Expressions to send Emails to the resulting dataset of Customers.

We need a filtered data set of the following Records:

For all the Permits in the [Permit] Table that has a value of “Year” in the [PermitIndicator] Column
And
For all the Permits in the [Permit} Table for the same [VisitorID] (VisitorID is a REF Field)
Include the record with the newest [PermitDate] for [VisitorID], Only if it has a value of “Expired” in [PermitStatus] Column

In SQL on an Azure SQL Table our SQL Developer would have written it as follow…we need the Appsheet Expression equivalent as mentioned or using the Appsheet IDE functionality with Expressions to achieve it, to send Emails:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER ( PARTITION BY VisitorId ORDER BY PermitDate DESC) RowNum from permit where PermitIndicator = ‘Year’ AND PermitStatus = ‘Expired’) AS A WHERE RowNum = 1

The ROW_NUMBER PARTITION BY part is key.
It groups the Permits according to the VisitorID and sort it per PermitDate

Appreciate any guidance how to achieve this in Appsheet

[Henry_Scott]

Reading up in the community I think using Expressions in this post could deliver a solution, but not sure how to put it together to achieve the result I want as per my original post
https://community.appsheet.com/t/faq-filter-lookup-maxrow-minrow-ref-rows-and-select/24216/89

[Darmund]

So you might be able to accomplish your task with using an ORDERBY() and SELECT() function.

You can create a SELECT() expression to find all of the unique Permit records where the Permit Indicator is “Year” and the Permit Status is expired:
SELECT(Permit[ID], AND([PermitIndicator] = “Year”, [PermitStatus] = "Expired), TRUE)

help.appsheet.com

SELECT()

Gather column values from row in table or slice

You can then order those Permit records by their Permit Date from most recent:
ORDERBY(
SELECT(Permit[ID], AND([PermitIndicator] = “Year”, [PermitStatus] = "Expired), TRUE),
[PermitDate], DESC)

help.appsheet.com

ORDERBY()

Sort row references

My main concern is if the distinct feature of the SELECT expression will remove records with multiple VisitorID columns. If there are other values that are in the Permit Status column than you may need to add to the expression. I suggest you create a virtual column in the Permit table and test the results for yourself.

[Henry_Scott]

@Darmund thank you for the input. I will ask our Developer to try this approach

[Henry_Scott]

Henry_Scott:

PARTITION BY VisitorId

@Darmund . Chatted to my Developer and he mentions that your suggestion excludes the “PARTITION BY VisitorId” requirement. Only after this is applied, must the [PermitStatus] = “Expired”) be applied.
Not sure how one will do this in Appsheet?

[Steve]

Wow, that SQL statement looks way overly engineered.

Can you explain in plain language–without any SQL or AppSheet or other technical jargon–what you want to accomplish?

For instance, it looks to me like you want the latest expired permit for each visitor for this year.

[Henry_Scott]

Hi @Steve
Yes, that SQL is how the Azure DB SQL developer would do it on the SQL DB Maybe incorrect, but below is the Use Case.

Use Case:
So what we have is a [Visitor] Table and a [Permit] Table with VisitorID as the Ref between the two Tables. What we want to achieve in Appsheet is to send an EMail, using the [Permit] Table as the base, to our Visitors to tell them that their Year(Annual) Permit is not valid anymore.

We create a Visitor record (once off) for Steve and also a Permit for the current Year and he visits my Trail Park throughout the Year to ride his Mountain Bike. Steve can buy a Day Permit or take out a Year Permit (which this Use Case explains)that allows him to ride as much as he pleases for the whole year for a set fee. Over the past couple of years, Steve subscribed annually to 2019, 2020, and a 2021 Year Permit. Every year the PermitStatus Column is set to “Valid” when a Permit Record is created and based on a formula when the 365 days have run out the PermitStatus Column is set to “Expired”. So, Steve, we have 3 x Record Entries in the [Permit] Table for you and then obviously the same or less for other Visitors…one for each year. So summary dataset of the [Permit] Table below (we have more columns)

VisitorID, PermitID, PermitDate, PermitStatus
SteveID, PermitID123,20/01/2019,Expired
SteveID, PermitID445,04/01/2020,Expired
SteveID, PermitID331,10/01/2021,Valid
HenryID, PermitID991,09/01/2019,Expired
HenryID, PermitID885,02/01/2020,Expired
JohnID, PermitID001,01/01/2019,Expired
JohnID, PermitID110,04/01/2020,Expired
JohnID, PermitID221,20/01/2021,Valid

We want to send out a Weekly Email to only the Visitors whose “latest” Year Permit has “Expired”
In the scenario above HenryID will get an EMail and SteveID and JohnID will not if the Bot is executed before expiry, but after 10/01/2022 SteveID and after 20/01/2022 JohnID will also need to get an EMail. Any subsequent Visitors with a similar data set but with “Valid” Year Permits (the ones that buy on 01/01/2022) will not get Emails. Only one EMail should be sent if the scenario is TRUE and not one for each Permit Record

We store the Email in the [Visitor Table], but I have created a Virtual Column in the [Permit] Table to also store the Email, so that we can let the BOT run of this [Permit] Table.

Hope this explains the Use Case

[Steve]

My suggestion would be to add a column named LatestPermit (e.g.) of type Ref to Permits to the Visitors table. The column’s value for each row would be set using this expression:

ANY(
  ORDERBY(
    FILTER(
      "Permits",
      ([_THISROW].[VisitorID] = [VisitorID])
    ),
    [PermitDate],
      TRUE
  )
)

You could make the column a virtual column and use the expression as the column’s App formula, but that would get very expensive very fast as the data sets grow. Instead, I would suggest making LatestPermit a normal column and use the expression as the normal column’s App formula. Note that you will then need to back-fill the column for existing Visitors table rows.

With that done, each visitor’s latest permit status can be checked with [LatestPermit].[PermitStatus]:

("Expired" = [LatestPermit].[PermitStatus])

There are other alternatives, too.

[Henry_Scott]

@Steve Thanks for the guidance. Let’s try it.

[Henry_Scott]

Steve:

NY(
  ORDERBY(
    FILTER(
      "Permits",
      ([_THISROW].[VisitorID] = [VisitorID])
    ),
    [PermitDate],
      TRUE
  )
)

@Steve question as a result of my LIMITED Appsheet development capability…
We can do an Actual Column and backfill the data in the Visitor Table for each record, but just want to understand how this Column will get updated on the Visitor Table when we create new Year Permits in the Permits table? Will it update at the point of saving the Permit record and because it has a Ref to the Visitor Table?
…and updating of this Field as the clock runs out of the backfilled Visitor records?

[Steve]

Good question!

Theoretically, because the Permits table is a child table to the Visitors table, an update to a row in Permits should prompt a recalculation to the parent row in Visitors. I say “theoretically” because I have not personally confirmed that behavior. Note that the update to the Permits table must be made in the app to trigger this automatic update; if the change to Permits is made directly to the data source (e.g., by directly updating the database), this automatic behavior will not be triggered. If this automatic update does not happen, there are other approaches to take.

[Henry_Scott]

@Steve ok noted. Was my assumption as well and I also have “Is Part Of” set. I will validate and if it does not work ask for your guidance on other ways to achieve it…maybe run a Bot at night to update?

[Steve]

I don’t believe Is part Of is required, but–again–I’ve not personally verified.

Yes, a bot to update on some interval would be an alternative. Or you could use a stored procedure (if available) in your database to use the AppSheet API to trigger the app update.

[gregdiana1]

Hi Henry,

I have perused the responses and unless I am have misunderstood matters it appears to be a simple matter based on the [Permit] table of data subset you have provided.

Also, the table you provided conflicts as it only requires those with an Expired PermitStatus in the current year. HenryID, as such, does not qualify as he has no 2021 entry? So you need to be clear about this!

Thus as I understand matters you simply have to query the [Permit] table and filter the PermitStatus column for all PermitStatus rows which are expired (AND) in the current year (2021) to obtain the primary key VisitorID.

Note current year = YEAR(TODAY()), so no need for ORDERBY

In the table you have provided none will be returned for 2021! as Steve and John are both valid and Henry does not yet exist in 2021!

Otherwise, you must simply query the last entry for each VisitorID ?

MAXROW(“Permit”,“PermitDate”,(YEAR[PermitDate]=YEAR(NOW()))

or

MAXROW(“Permit”,“PermitDate”,([PermitDate]=[_THISROW],[PermitDate]))

However, any VisitorID row from the [PERMIT] table which has an Expired entry in the PermitStatus Column in the current year (2021) needs to be e-mailed.

However, earlier on in your explanation you noted "… when the 365 days have run out the PermitStatus Column is set to “Expired”

This means that any client may only have a single entry in each year

Surely if this is indeed the case then why not simply use this same formula to also trigger and issue an expired e-mail notice every week until the last entry for each VisitorID row in the PermitStatus column becomes Valid again?

It appears you might possibly be reinventing the wheel unless I am misunderstanding matters.

Kind regards

Greg Diana