How to place fields from two related tables onto embedded view
I have a view created as a layout element. Using a select table statement I am populating my view with field data. I have child table with related information I would like to display in some columns. I can't get this to work.
This code results in only the child table fields showing up:
let xStart := 'Start Date';
let xEnd := 'End Date';
select Orders where Prod_Date >= xStart and Prod_Date <= xEnd;
select 'Order details' where 'Order ID' = Orders.Order_ID
Can anyone explain to me how to get both table fields into the columns of my view? Any help would be appreciated!
6 replies
-
Jen,
When you use a View layout element, just select the parent table. You can select the fields/columns to display using the user interface just like you would with the table view. It looks like filtering works combined with the select statement, but "order by" is ignored so if you want to sort you'll have use the View's user interface.
-
Sean,
Do you mean after creating the view using a select statement to view the parent table, you click on the header of the view and select "show column..." and then select a child field to add to your view? If so, Ninox wants me to sum, or concatenate or somehow aggregate the child values. It won't display the values in a column of the view as is. I am guessing that I'm seeing this behavior because I'm not following your instructions as intended. Can you be a bit more specific?
Thanks! -
Jen,
You followed them correctly. I chose concatenate because it doesn't aggregate like the other options. Once you have it displayed in the View, you can click on that column header, edit the formula and remove the concat() function. I don't know why they give those options and not the raw field info.
-
Thanks, Sean for your information. I think the reason it wants to aggregate the child values is because there are multiple values in the related child table for each one record in the parent (a one-to-many relationship).
When I choose a text field from my child table and select concatenate, I get only blank values in that column and I can't edit anything by clicking on the header of the view. I wonder if you are using the cloud app like I am or one of the other apps? Perhaps this behavior is different for your version of Ninox? -
It makes sense to concatenate, however, the entries would need to be very short for all of the child records to be visible. Most likely, the user would have to click on the parent record which would pop up the form view letting them see all of the child records. I tested this on the cloud and on the Mac app and it worked in both. The screenshots are from the cloud version...
-
Love your screen shots, Sean. Now I see how you are editing the field. So the problem is my child table is exposing no data. I wonder if this is an error in my table relationship. I will try to troubleshoot that. I appreciate your answer to my question and will keep attempting to solve the underlying problem.
Thanks for all your time and for answering my question. I very much appreciate it and will use what I learned from you.
-Jen
Content aside
- 4 yrs agoLast active
- 6Replies
- 2940Views