Task that selects newest date only

[matstein]

We have a task that runs daily that will do a calculation based on current date and dates stored in the table.  The problem is the task isn't selecting the newest dates in the table to do the calculation.  It's selecting random dates that are stored in the table causing the calculation to be wrong.    Is there a way to force the task to find the newest dates for the records?  

I attached our trigger.  The Qtr_Inspection_Master table houses all the inspections done on different units and each unit has multiple dates that inspections were completed.    It's grabbing older Qtr_Inspection_Date lines instead of the newest causing the emails that are sent to be wrong showing inspections as past due even though the newest inspections with the newest dates are in the table.

 

Thank you for any help someone can provide

[Fleshzombie]

I was trying to accomplish something similar, I have a running log of check ins and I was trying to create a table to be emailed based on last check in for each employee that was 30+ days old.

I am easily able to pull the value of the last check in, in a Caspio report using a SQL query by using TOP 1 ORDER BY DESC, however I have not been able to successfully replicate this in a table variable for a task.

This is what I did to make it work based on your info above:

Create a table with list of "inspection jobsites" (assuming this is your unique identifier), column for "last inspection category", "last inspection unit", "last inspection date".

Create a triggered action on the table "Qtr_Inspection_Master" on data insert where it updates table "inspection jobsites"  fields: "last inspection category", "last inspection unit", "last inspection date" where inspection jobsite equals the inserted inspection jobsite.  If you do this the table "inspection jobsites" will always have the last inspection date in that table and will be updated anytime a new inspection is submitted for that site.

on the picture below:

UPDATE tbl_Tablet_Master_List would be your new "inspection jobsites" table

inserted.timestamp = QTR_Inspection_Date

WHERE (inserted.QTR_Inspection_Jobsite) equal (inspection jobsites table.Inspection_Jobsite (If you name it this)) 

Etc.

Then you can create a task to pull the data based on date ranges.  I can provide some screenshots of my triggers and tasks in a bit if that helps.

You can ignore the join below, it is just there to filter the results.  Mine is setup to send a list of all devices that are 30+ days since the last check in.

There may be an easier way to do it but I was not able to figure it out. I am no expert

 

 

 

[NiceDuck]

May you please try changing the block you are using for the static 10 and -20 from green ones to the blue ones? I think the green ones are for text value this your value  may be being recognized as text instead of number, thus causing the operation to be wrong.