Select from different tables

[Alberto_Giacobo]

Newbie question here:

4 tables: Projects, Shipments, Locations, Destinations

Projects may have Shipments sent to Locations, and Locations pertain to a Destination (e.g. Destination A has Location X, Y, think of it as a chain having multiple branches)

How do I create a virtual column in Projects which shows (as a list) the Destinations that have at least one shipment for the project in question?

I tried using Select but can’t seem to find the proper approach, still learning the ropes.

Would something like this be possible?

Thank you very much to the community and whoever may answer this!

• Alberto

[Steve]

Maybe this (with appropriate name substitutions)?

FILTER(
  "Destinations",
  ([_THISROW].[Project ID] = [Location].[Shipment].[Project])
)

See also:

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

help.appsheet.com

FILTER()

Select rows of table or slice

[Alberto_Giacobo]

Steve:

FILTER(
  "Destinations",
  ([_THISROW].[Project ID] = [Location].[Shipment].[Project])
)

Thanks a lot, giving it a try first thing tomorrow and letting you know if it did the trick, thanks again!

[Alberto_Giacobo]

Steve:

FILTER(
  "Destinations",
  ([_THISROW].[Project ID] = [Location].[Shipment].[Project])
)

Hmm, I’m very sorry, most likely it’s me doing something wrong, but can’t get it to work

The case is:

I’m trying to add the virtual column in Project

In Project I have already a virtual column which recalls Related Shipments as a list: REF_ROWS(“Shipment”, “Project”)

Shipment has:

ref to project
ref to location

Location has

ref to Destination

Destination has no ref (ok, Destination category but that doesn’t count) and has a virtual column where the Locations are listed ( REF_ROWS(“Location”, “Destination”) )

FILTER(
“Destination”,
([_THISROW].[Key] = [Location].[Shipment].[Project])
)

I tried something like the above but I think I’m off somewhere given the above structure.

Thanks a lot for your support so far, it’s helping me already get a better grasp!

• Alberto

[Steve]

This is very confusing to me. Can you describe it in another way?

[Alberto_Giacobo]

Absolutely, let me try again:

I have a project table, where I want to display the list of stores some items are sent. A store may have multiple locations and the actual shipments are tied to a specific location.

So shipments have as ref:

[project]
[location]

And locations pertain to a destination ( a location can belong to just a single destination, a destination may have multiple locations)

I would like to show for each project (so in the project view) the list of stores (destinations) that have even just one location that received even just a shipment for that project.

Better now? Sorry if I was not clear - and still may not be - and thank you very much for your help!

• Alberto

[Alberto_Giacobo]

Just a quick check if there’s anyone that can help on this, still a bit stuck, sorry

[Steve]

Maybe something like this (substitute column and table names s appropriate):

FILTER(
  "destinations",
  ISNOTBLANK(
    FILTER(
      "shipments",
      AND(
        ISNOTBLANK([project]),
        ([project] = [_THISROW].[project]),
        ISNOTBLANK(([location].[destination]]),
        ([location].[destination] = [_THISROW-1].[destination])
      )
    )
  )
)

This will be an expensive expression, so best to avoid using it in a virtual column.

[Alberto_Giacobo]

Thank you very much for your support and sorry for the late reply: I’ll soon be back on the project and attempt following your indication to sort this out.