Grandchild table and records (HELP)

AppSheet AppSheet Q&A
1

First let me say, that I have no experience at all programing, Im learning by trail and error, and reading this forum, and anything I can get my hands on…so please help me understand this isue, and thanks for your attention.

I have a table (Orders), with a second table (Orders details), once the order is ready to be send, I have another table (Shipments) in which you can assign several Orders to One Shipment, here is my question:

How I can assign and show, once you are view shipments, its details which are the details of all the orders assign to one shipment, since this a grandchild table with details also, I cannot figure how to show a table (shipments) with its details table.

I hope made myself clear explaining it
thanks
Daniel

2

Hi @DmelgarGt

First of all, welcome to the community !

Once you attach your orders to your shipments, which mean, in your order table, you have a column [shipment], type Ref
==> that will create a virtual column in your shipment table : [Related orders]

So, in order to get all the detail orders in your shipment, you will need to add a new virtual column:
==> Data/Columns, then:

3X_9_2_9281f21c1e38ccaae7c7e4d4bcca784757ba6b68.png

3X_6_e_6e1df299603cc43192f39b030ba5695c96708b2d.png
3X_6_e_6e1df299603cc43192f39b030ba5695c96708b2d.png768×554 26 KB

with this expression in the App Formula:

SPLIT(
    CONCATENATE([Related ORDERs][Related Order Detailss]),
    " , ")
3 Likes
3

Aurelien:

auto> SPLIT(> CONCATENATE([Related ORDERs][Related Order Detailss]),> " , ")> >

Thanks for you response…however failed to make it…(I guess I still dont have knowlege enough for it)…here are some more detailed info about my messy app…please excuse my structure…

3X_6_d_6d4fb11c935374ac306720c817fad11763b165bd.jpeg
3X_6_d_6d4fb11c935374ac306720c817fad11763b165bd.jpeg1366×768 168 KB

this is my tables structure

3X_e_f_ef752875d3025d9e8110dc99c4395d7f7fdd8264.png
3X_e_f_ef752875d3025d9e8110dc99c4395d7f7fdd8264.png1366×768 120 KB

Table columns “Orden de trabajo”

3X_8_3_834c9a2cf2adf40279ab03a1223d13d2e0d441b4.png
3X_8_3_834c9a2cf2adf40279ab03a1223d13d2e0d441b4.png1366×768 136 KB

table columns “OT items”

3X_5_9_59e346f395b70f01e1def6dafcea9b772b9184ff.png
3X_5_9_59e346f395b70f01e1def6dafcea9b772b9184ff.png1366×768 121 KB

Table Columns “Envio”

3X_e_c_ec9e466bcf829f7e36d7394ef9f073bdc5fa0941.png
3X_e_c_ec9e466bcf829f7e36d7394ef9f073bdc5fa0941.png1366×768 134 KB

4

Hi @DmelgarGt

What did you try so far ?
Can you also share structure of Envio Items ? I suspect this is the missing piece of the puzzle

2 Likes
5

Table “Envios”

3X_4_0_404ffd2eb83cde10655ffa71029f41c4573489ee.png
3X_4_0_404ffd2eb83cde10655ffa71029f41c4573489ee.png1366×768 138 KB

3X_a_6_a6473fefc56e96327eb10d51d71a219b3947ed43.png
3X_a_6_a6473fefc56e96327eb10d51d71a219b3947ed43.png1366×768 143 KB

Well in my head, tried to link table “orden trabajo” by using a Ref columm in “Envio”, it seems to work, I think, but when I tried to show or get details from “Orden trabajo”, I cant get those details from the table, I though that by relating both tables I could call the refered table by each column individually, and so create a table “Envios” with this refered columns and work my way along…( I hope this is clear).

Its my understanding that by linking (or refering) the tables, if changes were made to one this changes will reflect in the second one, maybe instead of referring try to copy all rows selected from table “orden Trabajo” copy them to “envios”, so this will be an independent table at all…(this I have try)

but as I said, since I have zero experience with databases or its estructure, dont really know which way to move on…

My idea is that “Orden trabajo” will have data about quantity, money, to whom and what we are selling, “Envio”, will have data about whom are we sending to, what are sending, and also payments for that “Envio”, make sense?..so an “Envio” could have several “orden trabajo”.

I did tried to find a way to get data from the referred table/columns with LIST(), FILTER(), SELECT(), but my inputs in those functions are wrong, cause I get messages that says cannot find table, or the type of data is not expected, etc…

can you tell me, how bad my table estructure is?

I hope you can help me fix and learn this…
thanks
Daniel

6

Hi @DmelgarGt

I’m pretty sure your structure is OK, or almost OK
I asked for your “Envio Items” structure screnshot, in order to better understand

3X_5_b_5bb938f3ce7a23610d8964a2d38292c824ae5528.png
3X_5_b_5bb938f3ce7a23610d8964a2d38292c824ae5528.png240×241 4.37 KB

2 Likes
7

Thank you for taking some time to help me

Table “Envio Items”

3X_2_e_2e103323d58da5f8b64a90596cfa024cf6809455.png
3X_2_e_2e103323d58da5f8b64a90596cfa024cf6809455.png1366×768 135 KB

8

Thank you.

So, from what I understand, your question is about to get a [Related OT Items in Envio] column, from your table Envio.

I suggest (80% sure about that, because I never had this configuration):

  • make a virtual column [VC_Related OT], Type List, base Ref, Source table Orden Trajajo, expression:
[Related Envio Items][OT ID]
  • make another virtual column [VC_Related OT Items in Envio],Type List, base Ref, Source table OT Items, expression:
FILTER("OT Items",
IN([OT ID],[_THISROW].[VC_Related OT])
)

I suggest you have a look to this documentation, in order to understand what I’m trying to achieve with these suggestions:

Let us know if that works for you !

3 Likes
9

Thank you very much for your sugestion, let me digest everything you said…, and I will let you know how it went

2 Likes
10

Im still trying to understand what you said, and I think finally got to make it work..!!!

so thank you very much,…still have to read it a few more times to finally understand it…I wish there were some more material or videos to understand how "deference expresions " works…cause is a concept really hard to get it, it is even harder without practical examples to learn from…

I will keep practicing and reading…thanks a lot