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!
Maybe this (with appropriate name substitutions)?
FILTER(
"Destinations",
([_THISROW].[Project ID] = [Location].[Shipment].[Project])
)
See also:
Thanks a lot, giving it a try first thing tomorrow and letting you know if it did the trick, thanks again!
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!
This is very confusing to me. Can you describe it in another way?
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!
Just a quick check if thereโs anyone that can help on this, still a bit stuck, sorry
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.
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.
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |