Displaying values in Main Table from multiple rows of Child Table (based on Dropdown)

[Ryan_Mortimer]

Hey everybody! LONG time user, first time asker so I am hoping that somebody can point me in the right direction.

I am having some issues trying to display some data from a child table in my app.
I have done some digging around and I can’t find a solution, but I may be looking in the wrong areas.

I have a multi-destination booking app as the deliveries usually make a few stop overs.
In the main table/view, I have a column setup for Next Pick Up Address and Next Drop Off Address where I will need to display the relevant information from rows in my child table.

For example:

LEG 1
PICK UP FROM - 1A Fake Street, New York
DROP OFF TO - 56B Example Avenue, New York
STATUS - (Booked/In Transit/Completed)

LEG 2
PICK UP FROM - 56 Example Avenue, New York
DROP OFF TO - 1162 Sample Street, New York
STATUS - (Booked/In Transit/Completed)

If status = Completed for the first leg, I need the column(s) to show the information from the Leg 2.

Additionally, if possible, it would be great to be able to show the information from the FIRST Pick Up location and the FINAL Drop Off location displayed in their own columns as well.

I’m not sure if I am meant to do this via a workflow (by changing the values based on a set of rules) or if it is easier to do this through an expression.

[Steve]

Ryan_Mortimer:

If status = Completed for the first leg, I need the column(s) to show the information from the Leg 2.

ANY(
  ORDERBY(
    SELECT(
      [Related childs][child-key-column],
      ([child-status-column] <> "Completed")
    ),
    [child-leg-number-column]
  )
)

Ryan_Mortimer:

Additionally, if possible, it would be great to be able to show the information from the FIRST Pick Up location and the FINAL Drop Off location displayed in their own columns as well.

ANY(
  SELECT(
    [Related childs][child-pickup-location-column],
    ([child-leg-number-column] = MIN([Related childs][child-leg-number-column]))
  )
)

ANY(
  SELECT(
    [Related childs][child-dropoff-location-column],
    ([child-leg-number-column] = MAX([Related childs][child-leg-number-column]))
  )
)

Ryan_Mortimer:

I’m not sure if I am meant to do this via a workflow (by changing the values based on a set of rules) or if it is easier to do this through an expression.

If done by a workflow, the data wouldn’t update until the app syncs, so there’d be a delay. If done with an action, the data would update immediately.

[Ryan_Mortimer]

Hey Steve,

I think I have worked this out (loosely) but I have encountered a slight issue.

Using the following code, it won’t allow me to have my status column set as a enum or enum list.

ANY(
  ORDERBY(
    SELECT(
      [Related childs][child-key-column],
      ([child-status-column] <> "Completed")
    ),
    [child-leg-number-column]
  )
)

With my data, it is filled as

  ORDERBY(
    SELECT(
      [Related LEGS][JOB ID],
      ([TRIP STATUS] <> "Completed")
    ),
    [PICK UP FROM]
  )
)

I was assuming that the way around this would be to have a hidden column that inherits a value from the Enum list, but I feel like that might be the wrong way to go about it.

Additionally, if I was to allow say, 4 “legs” in total, is there a way that I can change this code to that application?

[Steve]

Ryan_Mortimer:

it won’t allow me to have my status column set as a enum or enum list.

Details? Is there an error? Can you provide a screenshot?

Ryan_Mortimer:

Additionally, if I was to allow say, 4 “legs” in total, is there a way that I can change this code to that application?

The code I provided doesn’t care about the number of legs, but I suspect it doesn’t handle how you have the legs of each trip described within a trip. Please explain how each leg is associated with a trip (parent/child?), and how you currently indicate that a given leg is the first leg, or that another leg is the third leg, etc.

[Ryan_Mortimer]

Hey Steve,

Thanks again for taking the time to help.

Here is the error message:

Column Name ‘Pick Up From’ in Schema ‘VEHICLES_Schema’ of Column Type ‘Ref’ has an invalid app formula ‘=ANY( ORDERBY( SELECT( [Related LEGS][JOB ID], ([TRIP STATUS] <> “Completed”) ), [PICK UP FROM] ) )’. Cannot compare List with Text in ([TRIP STATUS] <> “Completed”)

and the screenshot:

Steve:

Please explain how each leg is associated with a trip (parent/child?)

I quickly mocked up this diagram/table to show how I currently have this setup (Hopefully, this is enough information)

Steve:

and how you currently indicate that a given leg is the first leg, or that another leg is the third leg, etc.

Currently, I don’t have a “leg number” column, but this can be added.
The reason for this is that some deliveries are 1 leg, some are 4 legs (and everything inbetween)

[Steve]

This tells me the TRIP STATUS column is a list type, either EnumList or List. If so, 1) why???; and 2) replace ([TRIP STATUS] <> "Completed") with NOT(IN("Completed", [TRIP STATUS])).

[Ryan_Mortimer]

Thanks Steve, I’ll try that.

1. The delivery driver selects the job status based on the progress of the job.
   (I can add action buttons instead for this purpose if need be)

2. I will add that now and see how it goes!

Thanks so much again!

[Ryan_Mortimer]

Thanks so much Steve, this is a massive help!

This is the first time in years that I have ever had to reach out, so I was a little nervous about asking something that may have already been asked.

I’ll give it a go now!

Thank you a million again!

[Grant_Stead]

@Steve is the business!

[Ryan_Mortimer]

Hi everybody! I am reviving an old thread as I have been trying to achieve something for the last 2 days.

@Steve (the legend!) has helped me out more than he probably realises with this. But what I am now trying to achieve is different values from different columns in the child tables.

The code @Steve helped me out with was:

ANY(
  ORDERBY(
    SELECT(
      [Related childs][child-key-column],
      ([child-status-column] <> "Completed")
    ),
    [child-leg-number-column]
  )
)

This translates to the following (when filled with my data)

ANY(
  ORDERBY(
    SELECT(
      [Related LEGs][LEG ID],
      ([LEG STATUS] <> "Completed")
    ),
    [LEG NUMBER]
  )
)

However, I need to use this formula in multiple different columns to return the values of “LEG ETA”, “LEG REGION”, “DRIVER”

I can not for the life of me figure out how to return a value that isn’t the leg number without ruining the sorting function.

[Ryan_Mortimer]

Also, I feel like I am getting closer (albeit maybe in a more complex way)

INDEX(
  ORDERBY(
    FILTER(
      "LEGS",
  AND(
    ISNOTBLANK([LEG ETA]),
    ([JOB ID] = [_THISROW].[JOB ID]),
    ([LEG STATUS] <> "COMPLETED")
  )
    ),
    [LEG NUMBER],
    TRUE
  ),
  1
)

Almost works, however, it only returns the Child Table KEY value

I feel like I am missing something so simple that I will end up embarrassed here.

[Koichi_Tsuji · Accepted solution]

Not exactly sure what you are trying to achieve.

But for the column you have this expression (Assuming Virtual column) you set the column type to list, base type to ref, and reference to your “LEGS” table.
Then let me assume this column is named to be [VirtualColumn1]

Then this create another column and push expression for de-reference list

[VirtualColumn1][AnyColumnNameYourWantoPullFromLEGSTable]

Otherwise you use SORT() expression instead or ORDERBY()

[Ryan_Mortimer]

Essentially, (in a nutshell) would this mean creating a 2x virtual columns for every column I need to sort and return a value.

Thinking along the lines of:

• Creating Virtual Column with a “reverse reference” - returning me a list of values based on conditions.
• Creating a Virtual Column to return the individual result through dereference by using SORT/ORDERBY

[Koichi_Tsuji]

On the first virtual column you just simply get the list of IDs from your child table based on your condition.
Then use another VC to pull the list of value from this related child record using dereference list technique. If you want to apply sort, then sort this 2nd VC using SORT() expression, that s what I advised

[Ryan_Mortimer]

Thank you @tsuji_koichi, I will give this a go and report back.

Thank you very much for the insight and assistance.

(I always feel a little bit like hanging my head in shame when I have to come to the community to ask - especially if there is a simple solution).

[Koichi_Tsuji]

No problem. Let us know how it worked (hopefully it will get through)

[Ryan_Mortimer]

Okay a quick update! So I went back to basics on this and looked at it from a completely different angle thanks to your suggestions.

1. I have created a virtual column that referenced the child table with the following expression for sorting and filtering: (Saving me from running the sort and filter expressions on every field I require).

ORDERBY(
    SELECT(
      [Related LEGSs][LEG ID],
      ([LEG STATUS] <> "Completed")
    ),
    [LEG NUMBER]
  )

2. I then created another virtual column to extract the individual columns from:

ANY(
[LEG FEED][LEG REGION]
)

And then repeating this for other values such as:

ANY(
[LEG FEED][LEG BOOKING DATE]
)

ANY(
[LEG FEED][LEG ETA]
)

Etc.

I haven’t had time to functionally test this across all of the data and I need to assess the real world functionality of the “Status <> Completed”.

I’ll hold off for a little before I mark it as a solution - but for now, I think we have a winner @tsuji_koichi! Thank you very much.

Also, a big thank you to @Steve as always!

[Steve]

Ryan_Mortimer:

But what I am now trying to achieve is different values from different columns in the child tables.

Please explain this better/more.

[Ryan_Mortimer]

Sorry @Steve, I may have explained it in a backwards way. Apologies.

This is a sample piece of data for one vehicle and how the data is structured.

The child rows have data that is inherited by the parent.
The parent row has data that needs to flow back based on the “status” of the related Child record(s).

ANY(
  ORDERBY(
    SELECT(
      [Related LEGs][LEG ID],
      ([LEG STATUS] <> "Completed")
    ),
    [LEG NUMBER]
  )

Works in the fact that it returns the LEG NUMBER - but I need to return the values of the LEG ETA, LEG REGION, DRIVER, etc.

PS: I have managed to create a string of expressions that are exploring the child records.
For example: I have a virtual column that can tell me what “leg” this job is currently up to, as well as another progress. So for example, there is a virtual column where in the example data provided above there is a return for “Current Leg” of “3/3”.

Also, I want to take this chance to say thank you for always helping out and pointing me in the right direction.

[Ryan_Mortimer]

I think I may have identified a possible issue:

Would I be correct in assuming that seeing as I have the sort feature on the [LEG FEED] virtual column, ANY() will select the first available child record - not a “random” child record?

[Koichi_Tsuji]

Ryan_Mortimer:

ANY() will select the first available child record - not a “random” child record?

Correct. Any expression is always to get the first time from the list.

[Ryan_Mortimer]

I think we can close this one off as a winner!

Thank you @tsuji_koichi and @Steve for not only helping out but also for steering me in the right direction to learn for myself - this is immensely valuable knowledge.

Seeing as this thread was a 2 part process - should I flip a coin for who the solution goes to?

[Steve]

Ryan_Mortimer:

Seeing as this thread was a 2 part process - should I flip a coin for who the solution goes to?

Summarize your solution and give yourself the credit for piecing it all together!