Hi I was making a simple Billing app on Google Appmaker. Created CloudSql models for Purchase Orders, Bills and Clients. These SQL tables are have relations to each other as a Client can have many PO-s and a PO can have many Bills.
All fine and dandy I added fields and relations. Put some tables on the pages to show and forms to enter new items.
Problem came when on one page I wanted to show a Overview table that would contain information about orders - containing field about Order itself, its clients and how much was already billed to such Purchase Order.
The table itself seemed like a good idea to use Calculated Data model named it (CalcSum) - I joined my different table and did some Calculations. All fine
SELECT
(Orders.Sum-SUM(Bills.Sum)) AS NotInvoiced,
SUM(Bills.Sum) AS Invoiced,
Orders.OrderCode,
Orders.Currency,
Orders.PODate,
DepCode,
ProjCode,
Client.Name AS Client,
Orders.Sum AS OrderAmount
FROM Orders
LEFT JOIN Bills ON Bills.Po_fk = Orders.Id
LEFT JOIN Client ON Orders.Client_fk = Client.Id
GROUP BY Orders.Id;
The problem that arouse now is following. One cannot use query filtering on calculated models. (as seen in this tutorial https://www.youtube.com/watch?v=jo-5AE5P1HA) I was thinking I could use my own query parameters although this looks cumbersome as I would have to define a lot of them and write even longer SQL. So I tried an example like this -- take the prevous code block and change the end of it to
GROUP BY Orders.Id HAVING DepCode LIKE '%:DepSearch%';
Then I added a searchContent textbox into the header - of this overview table and bound the box to @datasource.query.parameters.DepSearch. I put an onValue change to this box too to reload the datasource. So it is done quite like the youtube example only I have substituted my own quwery parameter instead of automatical filter term that would be available to a non-calculated model. I hoped it should work but I get console errors.
Exception: JDBC backend failure. More information: Failed to execute connection with error: Parameter index of '1' is greater than number of parameters, which is '0'..
Executing query for datasource CalcSum: (Error) : JDBC backend failure. More information: Failed to execute connection with error: Parameter index of '1' is greater than number of parameters, which is '0'..
at Orderid.Table1.Table1Header.Panel1.TextBox1.onValueChange:1:19
Executing query for datasource CalcSum failed.
I'm all out of ideas. How could I resolve this problem or are there any other ideas how to make an overview containing fields from these three models and including calculated fields that would be filterable in any other way. It seemed to me that the calculated model is the way to go for the first half of this problem but it seems to mess with the other part.
