AnsweredAssumed Answered

kreport custom function - need 2 WHERE clause from 2 different tables

Question asked by ALoncao on Jul 29, 2015
Using Sugar Pro 6.5.20 and Kreporter 3.06.  I have a custom Sales module that includes fields product, period, and quantity with relationship to Contacts.  I need to display the data in pivot-format as sub-panel on the Contacts Detail view, i.e., grouped by product as row value and periods as columns with quantity as the values.  Since you can't publish a pivot-style report, I have to programmatically create the pivot. I'm trying to use custom function SELECT SUM(qty) FROM Sales....WHERE related contact = (value from Contacts table or Sales relationship table) and product = (value from Sales table).  I've added a non-db field to the Sales table for contactid, but I cannot reference that in the custom function.  I'm sure I'm overlooking a simple solution.  Thanks.

Outcomes