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?
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?
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.
@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
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().
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
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |