2 Replies Latest reply on Feb 5, 2016 9:18 AM by siplus

    Log Report

    slindsay14

      I am working on a script that will run a inventory position report. Have been focusing getting the report from our product log which creates a record in a related table when a item is invoiced. What I want to do is call up totals from the log on a specific date going backwards. Problem is there may not be a log entry for every item on the particular date so I'm thinking I need to call up the last entry in the log prior to the date in question.  I've experimented with different variations of Last, Max, Get(RecordNumber), Get(RecordID) and other Get functions with no success yet, but not sure I am using them correctly. Any suggestions ? 

        • 1. Re: Log Report
          Mike_Mitchell

          I suggest you create a new relationship from the parent table (invoices?) to the log, adding a "<=" predicate to the graph. Set a global date field equal to the date you want, and the related records will be only those items occurring on or before that date.

           

          It will then be a simple matter to aggregate whatever field you want; just put a summary field in the log table and call it from the parent.

          • 2. Re: Log Report
            siplus

            If you're scripting it, you can also set $$Variables that will show up as merge fields on your report.

             

            You will set them via ExecuteSQL's, like

             

            Set Variable $$TotalFailures;

             

            ExecuteSQL("SELECT SUM(Failures) FROM ProductLog WHERE LogEntryDate < ?";"";""; gChosenDate + 1)