Display refs vertically instead of horizontally?

[coldcoffee]

Hello 🙂 I have scoured the documentation, for two days, so i am hoping to not get just links to the appsheet's own info pages i've exhausted what they can offer me. They aren't always detailed enough, or written for newcomers like myself

My question has two parts:

1. Have I set this up correctly? I have two tables with 1-1 reference, set up like this:

• Parent - contains a nonvirtual row in the DB, called RelatedChild, whose type is Ref (to the child)
• Child - contains a nonvirtual row in the DB, called RelatedParent, whose type is Ref (to the Parent, and "isapartof" is selected Yes)
• Parent and Child both have Virtual RefRows to each other

2. On the Parent's detail view, I would like to display the data from the child Vertically rather than Horizontally (what the inline view provides.) I only have one Child per Parent so it would be nice to see the details stacked instead of having to scroll horizonally or leave some of them out and need to click to view it all. Is this possible?

Thank you

[TeeSee1]

You do not need a non-virtual column referencing the child in the parent table for what you want to do. The system generated virtual column - RefRows should do everything you need.

To display columns from the related child row simply create virtual columns in the parent table with App Formula like

INDEX([Related Childs][column from the Child you want to show], 1).

Note that this uses list dereferences (I suppose you also read this help doc) and INDEX is needed to get the first element, which in your case is the only element in the list but still needed if you want a single value.

[coldcoffee]

Thank you. Getting rid of unnecessary references should help me a great deal. So, does the "isapartof" checkbox get selected in the non-virtual child table ref back to the parent?

I did read quite a bit about de-references, I think having too many refs has slowed me down in terms going through trial and error. There was at least one help doc that told me I needed a non-virtual reference in both directions

INDEX is definitely something I have not tried! I guess it's counterintuitive for me to use any type of list expression to retrieve just one item. Hopefully I will be back here to mark this as solved 🙂

[coldcoffee]

Partially Solved! I am almost there, thank you

INDEX([Related Childs][column from the Child you want to show], 1).

This expression is pulling the column i need from the related child table, but that childColumn actually stores a uniqueID - whose label lives in another (non-parent--non-child) table - I need to display the label

• 1.    parentTable = my orders
• 2.    childTable = each row is one-to-one for each parent order and contains a set of 10 enum or enumlist dropdowns - (house characteristics)
• 3.    categoryTable = just the names and IDs for the 10 lists above
• 4.    characteristicTable = all the values to be selected for all of the 10 dropdown lists above (their IDs, their labels, and the ID for the above category they are associated with)

So in my parentTable i will have a virtual column (or maybe ten virtuals if needed?) concatenating the user-selected values of the 10 characteristics plus the category type.

• INDEX([Related childTable][DwellingType], 1)
• INDEX([Related childTable][WindowTypes], 1)
• INDEX([Related childTable][RoofType], 1)
• etc

**At the moment RoofType and friends are displaying characteristicTable's uniqueID instead of its label

The list will look like this -

• Dwelling: condo
• Windows: casement, sliding, skylight
• Roof: shingle
• Unusual Characteristics: indoor pool, no bathroom

Thank you for your patience! Sorry if I included way too much info

[TeeSee1]

You got the idea. Except the ENUMLIST columns. There you cannot get the labels directly by list dereferencing. So you need something like

SELECT(
 characteristics[label column],
 IN(
  [key],
  INDEX(
   [related childs][characteristics ENUMLIST],
   1
  )
 )
)

. Or so I thought. But for some reason, which I cannot figure out, I cannot get this field to be displayed. (actually it does appear momentarily right after the relevant child record's ENUM filed is edited but disappears from a Detail view of the parent. It IS displayed in the form view.) I do not have a solution here..

So my another question is, why do you even have a parent-child architecture, if the relationship is always  1 to 1? Why not store everything in the 'parent' table. Then categories and characteristics labels are already displayed... 

You still need to create VCs with category and characteristics concatenated to get the layout you want..

[TeeSee1]

Ah, a classic mistake..

select(characteristics[Label],
 IN(
  [characteristics table key], 
  LOOKUP(
   INDEX([_THISROW].[Related childs],1),
   "child",
   "child key",
   "enumlist col containing characteristics"
  )
 )
)

 [related childs], which is a col in the parent table, inside the INDEX is ambiguous because the SELECT statement is based on the characteristics table. 

Now the list is displayed but I still think consolidating the parent and the child tables makes sense in this case unless you have other reasons to retain the current structure.

[coldcoffee]

Thank you, I will test this out today.

At the moment it is just one-to-one but I think I will be adding more child tables - one of which will be "events" using an imported google calendar db. (side note, I am doing that because I have found no way yet to display more than one field in different colours on the monthly display in Appsheet's calendar). There will be a 3rd child table for data which is very unique to each individual parent order, such as year built, inside and outside measurements, selling price.

I thought separate childs would be helpful because after an order is complete, each parent is migrated to one or more archive tables - for research, and some of those destination tables don't require saving all of the data collected. e.g. the data concerning the scheduling of appointments and communication with clients is not needed when searching past orders based on location or price range, but still needs to be saved from an admin point of view. The app is a work in progress. The client adds new fields regularly. I am forever updating the actions that copy the rows to the various archive tables. Right now, if it was all in one table, it has 120 columns and I haven't yet added in the automated messaging to customers.

We are only at about 4000 records right now x  eventually 150ish columns, and I am really not sure at what point the columns x records may slow things down. We add from 400-1000 new records a year. I want to consider efficiency - we have the daily use of the active records for scheduling, route planning, communication, billing, etc. vs preparing the researched reports for the customers which means searching our archived records and using the data saved from archived reports.

Do Refs between tables slow things down or speed things up?

Basically, when I came to this job, the app was already started. Some of the table structure was already in place, such as having 3 tables, one for current, one for archived, and one for cancelled orders - where I personally would have stored it in one table with a status field to filter that.  My client is concerned about speed.

I would be VERY pleased to learn that I am wasting my time chunking it up. If you think the functioning of the app wouldn't be impacted by keeping everything in fewer tables, that would definitely make my life easier, to drop some of the more challenging Refs, and I could move on to other projects! 🙂 Again I have included so much info, apologies.