3 Replies Latest reply on Nov 13, 2015 3:51 PM by user19752

    Execute SQL problem

    pekka.hilden

      I have pulled out some 35 000 records from a Postgre SQL database. Then I have converted date field in Excel using Excel's Join function from YYYY-MM-DD format to DD.MM.YYYY format. Then I imported the records to FileMaker. This is very basic relation like Company --< Sales. I have defined three fields in Company table as follows (Of course this could have been done more fluently in calculations but this is for the sake of helping my thinking).

       

      CurrentYear = Year(Get(CurrentDate))

      CurrentYearMinusOne = CurrentYear - 1

      CurrentYearMinusTwo = CurrentYear - 2

       

      Then I have three fields which are supposed to pull sales data from Sales table using the following Execute SQL formula;

       

      Field for first year sales is

      Substitute ( Let ( [

      query = "

       

      SELECT SUM (TotalEUR)

      FROM Sales

      WHERE year(InvoiceDate)=?

      AND kf_CustomerID=?


      " ] ;

       

      ExecuteSQL ( query ; "" ; "" ; CurrentYear ; kp_customerID)

       

      ) ; "." ; ",")


      Field for second and third year sales are

      Substitute ( Let ( [

      query = "

       

      SELECT SUM (TotalEUR)

      FROM Sales

      WHERE year(InvoiceDate)=?

      AND kf_CustomerID=?

       

      " ] ;

       

      ExecuteSQL ( query ; "" ; "" ; CurrentYearMinusOne ; kp_customerID)

       

      ) ; "." ; "," )


      Although I replaced "." with "," in Excel it doesn't seem to work and I need to substitute . with , in the calculation otherwise I get trillion level sales.

       

      Now the problem is that fields picking 2014 and 2013 sales seem to work but the 2015 sales are not picked at all. Oddly, some 10 sales records for 2015 are picked but not others. I am totally confused what is causing this. I have done some fields to follow that calculations are working fine and everything seems to be in line but something is going wrong. The bigger problem is that I cannot tell if even 2014 and 2103 figures are wrong ...