I am trying to do something that I assume is ...

[M_Bea]

I am trying to do something that I assume is very simple, but I am just not getting it. I have two tables: Hydrants and Inspections.

The Hydrants table has a Key of “HydrantNum” and numerous other fields that track details about each hydrant.

The Inspections table also has a “HydrantNum” field which has a ref to the Hydrants table. Other Inspection fields include “Inspection Date”, “Result”, etc. The inspections table can contain multiple inspections for each hydrant.

What I am trying to do is create a view that only displays the hydrants that have not had an inspection within the current calendar year. When an inspection is logged for a hydrant in the current calendar year, that hydrant should fall out of this view. Simple, right?

I tried using a Slice to accomplish this, but I couldn’t figure out the row filter expression. Does anyone have any suggestions for me?

[Lynn]

Hi, What distinguishes the difference between those that have been inspected and those that haven’t. Use that to create your slice and also the date. Its a bit hard to provide a formula without seeing your tables. What was the formula you tried that didn’t work?

[M_Bea]

Thanks for the reply Lynn. The hydrants that have been inspected have a record in the Inspections table with an Inspection date within the current year. The hydrants that have not been inspected either have no record in the Inspection table or a record is in the Inspection table but the Inspection Date was not in the current year.

Basically, what I am trying to figure out is how to write an AppSheet expression similar to the below SQL statement. The SQL query filters the Hydrant table to show only those rows where there is no related record in the Inspection table with an Inspection Date in the current year.

SELECT * FROM HYDRANTS

WHERE HYDRANTNUM NOT IN

(

SELECT DISTINCT HYDRANTNUM

FROM INSPECTIONS

WHERE YEAR([INSPECTION DATE]) =

YEAR(GETDATE())

)

I am also not sure where to even place this expression to accomplish my goal. I figured that using the expression in a Row Filter of a Slice was the most logical place, but I am not certain. Clearly, I am painfully new to Appsheet. Thanks for the help.

[pravse]

@M_Bea you’re doing great and your SQL background will help you a lot.

In your slice filter, try something like this: COUNT(

SELECT(Inspections[Id],

AND([_THISROW].[HydranrtNum] = [HydrantNum],

YEAR[InspectionDate] = YEAR(TODAY()))

) ) > 0

Actually, you could also do exactly what you have in SQL:

NOT(

IN ([HydrantNum],

SELECT(Inspections[HydrantNum],

YEAR([InspectionDate] = YEAR(TODAY()))))

I’m not sure if one will be faster than the other. Please do try both

[M_Bea]

Oh. My. Goodness! That did it! You don’t know how much time I spent trying to figure that out. Thank you so much.

FYI - The second query was erring out with the message “Parameter 1 of function Year is invalid”. Not sure what the issue was. I ended up using the first query and surrounding it in NOT().

[pravse]

or COUNT() = 0

The problem with the second expression was that I got the parentheses wrong. It should be YEAR([InspectionDate]). I’d still suggest you try that too and see which one is faster