1 Reply Latest reply on Apr 4, 2017 8:36 PM by philmodjunk

    Date Check




      I have a database which I would like to track if the delivery date for a given VFX shot changes. 


      I have a "Shot Record" layout in a "Shot Record" Table.  I have another table called "Vender Status Report".


      Each vendor sends a weekly report which gets ingested into my database in the "Vendor Status Report" table.  I have a shot layout in the "Shot Record" table which I do most of my work in.  In that layout, I have a tab which displays related info from the "Vendor Status Report".  It shows all the delivery dates and the table is sorted by the vendor report date.  I'd like to see if it's possible to have a field in the "Vendor Report" table that is a calculation which checks if the delivery date is the same as the delivery date in the last dated report.  Is that possible?  Is there a "If" calculation that can match a field to a related entry against the previous dated record.


      The attached image is of the tab in my "Shot Record" layout of the related "Vendor Report" table.





        • 1. Re: Date Check

          Yes and no.


          Yes this can be done. No, it's not just an If function. As long as you have a Vendor ID field in your report table, you can query that table for the same vendor ID, same project as that of the current report record but with a date less than the date of the current report record. Sort those records by date and you can find the most recent prior report In order to compare dates.


          You can use any of the following;


          1) A sorted self join on your report table

          2) ExecuteSQL

          3) a scripted Find and sort.