Two Tables and Multiple Fields for ExecuteSQL()

Discussion created by RobertWard on Dec 20, 2016
Latest reply on Dec 22, 2016 by beverly

I am trying to get my Executesql() calculation to evaluate a count as part of a reporting structure of "active" records based on a request date and type of service from a portal. My parent table (Client_Layout) has my status record field, named STATUS. My portal, located on my parent layout, contains a Date and a category field named Services. Since my fields are in two different tables, I am unsuccessful in getting this to work. I have tried using the UNION clause to join the two tables and two Where clauses, but no success. I have tried using the HAVING clause but no success either. Here is how my calculation looks now: Somewhat strangely I have two Select statements. This was just an attempt at trying something else out.


ExecuteSQL (



                              "Select STATUS, Select Count(SERVICE)

                               From CLIENT_LAYOUT UNION JAIL_PORTAL

                               Where STATUS = 'Active'               

                               Where SERVICE = 'Visit'  and \"DATE\" >= ? and \"DATE\" <= ? " ; "" ; "" ; $$YTDFrom ; $$DateTo )       



This calculation does work when I don't include the second table and the first Where clause where status = 'active'. But I need to combine

these two tables together where the status is equal to "active"


Appreciate any help. Thanks.