Sum calc tweak needed -- I know I'm close...

[Rich]

Greets, all:

 

I'm trying to present the total of all the unpaid invoices in a report that's generated in a join table (ACTIVITY_PtoC_JOIN) with ACTIVITY_PARENT joined with ACTIVITY_CHILD. I stripped down the SQL calc that's used in FileMaker Pro's native Inventory database (that's created when you choose, "Create New"). The fields I'm using for the SQL calc are Total__lcn (which captures the total of an invoice in ACTIVITY_PARENT), Paymount_Amount__lxn (used to capture how much was paid on the invoice (if anything), and of course the account number (AccountNumber__lxt); they're all in ACTIVITY_PARENT.

 

Let     (

           [     

           customerID = AccountNumber__lxt ;     

           decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;   

           SQL = ExecuteSQL    (                     

                     "                   

                     SELECT SUM ( b.Total__lcn )                     

                     FROM ACTIVITY_PARENT AS b                     

                     WHERE b.customerID = ? AND IsEmpty ( b.Payment_Amount__lxn )

                     "

                     ; ""  ;  "" ;  customerID

                    )

    ] ;     If ( SQL ; Substitute ( SQL; "." ; decimal ) ; 0 )

    )

 

I'm getting the dreaded question mark for the result, so although FMP is accepting the syntax the calculation, iteself, isn't working.

 

As an aside, what's curious is that in the FMP 16 SQL Reference guide, when assigning an alias for a table name, one should use the verb, AS, but in the FMP Inventory file, AS isn't used in their calc (and it works), so I don't know what's going on there.

As always, thanks in advance for your replies!

Ciao,

Rich

Edited April 12, 2019 by Rich
Minor fix

[comment]

It's hard to troubleshoot a query without having a file with the table/s being queried. Still, this part jumps out:

                     WHERE b.customerID = ? AND IsEmpty ( b.Payment_Amount__lxn )

That's Filemaker syntax, not SQL. I believe the correct syntax would be:

                     WHERE b.customerID = ? AND b.Payment_Amount__lxn IS NULL

[Fitch]

Regarding your aside, the "AS" is optional.

Also, is all that "decimal" stuff really necessary for you? The point of that would be if you're concerned about your file being deployed in a country that uses a comma vs. a dot for decimal notation.

[Rich]

Sorry for not getting back earlier on this--too many projects at work going on. : S

Thanks for the input, guys. I stripped the coding further as Fitch suggested so I ended up with this (which works):

 

Let  (

customerID = AccountNumber__lxt ;   

ExecuteSQL    (   "   SELECT SUM ( b.\"Total__lcn\" )  

FROM \"ACTIVITY_PARENT\" b  

WHERE b.\"AccountNumber__lxt\" = ?   "   ; "" ; "" ;   customerID      )

)

...and I suppose I could remove the Let statement, too, since it's down to one value, now...though...now my question is: I created a similar calc for Total_Paid__lcn; I want to create a Balance Due amount. I suppose I could create yet another calc field that'll subtract Total_Paid - Total = Balance Due, but having three, separate, calculations is a little klunky; I imagine there's a more elegant way of coding this where all this takes place in one calculation, just that I'm starting out with SQL and haven't quite figured out its syntax, yet.

Edited April 16, 2019 by Rich
Tidy things up

[comment]

Is there a reason why you're not using aggregate functions over a relationship? IIUC, this is done from the context of a Customers table, so doing simply:

Sum ( ACTIVITY_PARENT::Total__lcn ) - Sum ( ACTIVITY_PARENT::Total_Paid__lcn )

should give you the balance due amount you seek - assuming you have a relationship between the two tables matching on Account Number. 

Although it would be possible to do a similar thing using ExecuteSQL() as:

ExecuteSQL ("SELECT SUM(Total__lcn) 
FROM ACTIVITY_PARENT
WHERE AccountNumber__lxt=?"; "" ; "" ; 
AccountNumber__lxt)

- ExecuteSQL ("SELECT SUM (Total_Paid__lcn) 
FROM ACTIVITY_PARENT 
WHERE AccountNumber__lxt=?"; "" ; "" ; 
AccountNumber__lxt)

it is unnecessary complex and may slow down your solution. 

 

 

Edited April 16, 2019 by comment