Grandchild table and records (HELP)

DmelgarGt · 10-20-2021 03:24 PM

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

Aurelien

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:

with this expression in the App Formula:

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

DmelgarGt

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…

this is my tables structure

Table columns “Orden de trabajo”

table columns “OT items”

Table Columns “Envio”

Aurelien

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

DmelgarGt

Table “Envios”

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

Aurelien

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

DmelgarGt

Thank you for taking some time to help me

Table “Envio Items”

Aurelien

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 !

DanielGT

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

DmelgarGt

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