How would I populate a view with a 3 table join
Suppose I had the following tables:
Customer(CustomerID, CustomerName)
Order(OrderID, CustomerID)
OrderItem(OrderID, ItemID, Description)
In SQL I could create Customer orders with:
select a.CustomerName, b.OrderID, c.ItemID, c.Description
from Customer a, Order b, OrderItem c
where a.CustomerID = b.CustomerID and b.OrderID = c.OrderID
In Ninox, it seems you have to iterate through result sets as follows:
for a in select Customer do
for b in select Order where a.CustomerID = CustomerID do
for c in select OrderItem where b.OrderID = OrderID do
[OUTPUT?] a.CustomerName, b.OrderID, c.ItemID, c.Description
end
end
end;
Is there a better way of doing this programmatically?
How would I populate a View with the result set?
6 replies
-
very much interested in this solution - thinking a discord users group would be handy for live discussions outside of webinar and to discuss further :)
-
As far as I know, Ninox does not support JOINs yet (the
join()function is not the same as JOIN). If you want to do something similar in a View layout element, the only way I know how is by creating formula fields for your "N" tables in your "1" table and then you would the select statement in the view as you normally would, i.e., select Customer.I don't think you can specify which fields appear in a View programmatically. You have to use the UI to show/hide fields.
-
Here's Version 2 which provides the View...
Given
Customer(CustomerID, CustomerName)
Order(OrderID, CustomerID)
OrderItem(OrderID, ItemID, Description)
Define the target table as
Result(CustomerName, OrderID, ItemID, Description)
delete select Result; //empty Result table
for a in select Customer do //populate the Result table
for b in select Order where a.CustomerID = CustomerID do
for c in select OrderItem where b.OrderID = OrderID do
let z := (create Result);
z.(CustomerName := a.CustomerName);
z.(OrderID := b.OrderID);
z.(ItemID := c.ItemID);
z.(Description := c.Description)
end
end
end;
In a View Layout element set the Formula to
select Result
-
Nice solution! So, now I'm curious. Since you have come up with this workaround, are you satisfied with steps necessary to make it work or would you like the View to support JOIN commands directly?
-
I don't really understand the syntax of Ninox SQL. It seems to be a bit of a mashup of procedural, objective and declarative languages. is it based on any common implementation? For example, why is the statment "z.(OrderID := b.OrderID)" rather than " z.OrderID := b.OrderID"? In the absence of further clarity over the syntax, I'd like standard SQL with SELECT, JOIN's, UPDATE, INSERT, etc..
Also, I initially tried to get this working with Result as a sub-table of Customer. Not sure why it wouldn't work.
-
"You're so analytical! Sometimes you just have to let art... flow... over you.". Hopefully you'll recognize the quote and that it's all in fun.
When I started using Ninox, it was for my business, but now I'm hooked and enjoy tinkering around with it. I can't explain the syntax in your example so I just let it... flow... over me ;). I think the View layout element would be perfect for implementing standard SQL statements... no telling when or if it will be implemented. As for the sub-table, you could have the Order sub-table on the Customer form view, but you'd have to select an Order to drill down to OrderItem which would popup another form view.
Content aside
- 5 yrs agoLast active
- 6Replies
- 2212Views