0 Replies Latest reply: Jul 18, 2013 3:27 PM by Mindy Kay RSS

    Show Invoice URL Link and Amount for Records Between Dates

      I'm building a table that pulls the Amounts and Invoices for records between two dates based on their Service Date.  However, if I get the correct rows pulled the Invoice Links don't show.  If I get the Invoice Links to show all the records for the particular Vendor are pulled with the Amounts outside the Date Range as zeros.  What's the Invoice Link code that will show only those invoices during that timeframe?

       

      Thanks,

      Mindy

       

      Attempt #1

      Date Range = 4/1/13 - 7/1/13

       

      Expressions:

      Invoice Link   =if({$<[Service_Date]={'>=$(vStartDate) <=$(vEndDate)'}>}[Invoice_No] & '<url>' & [Invoice_Link])

      Amount        =Sum({$<[Service_Date]={'>=$(vStartDate) <=$(vEndDate)'}>}Net_Act_Amt)

       

       

      ProjectAFE#Invoice LinkVendorService DateAmount
      BBDF 53H121560-Smith & Taylor, Inc4/2/13250,000
      BBDF 54H121572-Smith & Taylor, Inc5/23/135,900
      BBDF 60H121211-Smith & Taylor, Inc6/15/13275,327
      BBDF 65H131672-Smith & Taylor, Inc6/29/1333,185
      BBDF 71H131674-Smith & Taylor, Inc7/1/13125

       

       

      Attempt #2.

      Date Range = 4/1/13 - 7/1/13

       

      Expressions:

      Invoice Link   =[Invoice_No] & '<url>' & [Invoice_Link]

      Amount        =Sum({$<[Service_Date]={'>=$(vStartDate) <=$(vEndDate)'}>}Net_Act_Amt)

       

      ProjectAFE#Invoice LinkVendorService DateAmount
      BBDF 20H12121216074Smith & Taylor, Inc12/16/120
      BBDF 37H12137816070Smith & Taylor, Inc1/30/130
      BBDF 53H12156016084Smith & Taylor, Inc4/2/13250,000
      BBDF 54H12157216138Smith & Taylor, Inc5/23/135,900
      BBDF 60H12121116097Smith & Taylor, Inc6/15/13275,327
      BBDF 65H13167216095Smith & Taylor, Inc6/29/1333,185