Jump to content
  • 0

Task that selects newest date only


matstein
 Share

Question

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.

image.thumb.png.05543a1c03a7a7993a44185d52483bf3.png

 

Thank you for any help someone can provide

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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.

image.png.aebc2cef41b6c60c5ecbe6e93d49f2bc.png

image.png.3ec6876d5149c68cd20509232a051b92.png

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.

image.thumb.png.f5243bc75f73f16313779168ce5d9a7f.png

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

 

 

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...