OData query; expand multiple tables/references

Marvelous, Vincent! Many thanks! This works fabulously!

An additional question; how do we set a filter on the "lowest" level? We only seem to be able to filter top-level nodes, not the nodes that are retrieved from references. Suppose that the lookup table driver contains a driver_full_name column. And we use this query:
https://our-fabulous.thinkwise-app.net/indicium/iam/our-app-id/car?$expand=detail_ref_car_car_driver($expand=lookup_driver_id)
The filter &$filter=contains(driver_full_name,'firstname')
or
&$filter=contains(detail_ref_car_car_driver/lookup_driver_id/driver_full_name,'firstname')
do not work. Can we get it to work like this? Or isn't it supposed to work like this?

Best regards,
-Alex.

Hello Alex,

Whenever you add an $expand parameter, let's say:

/x?$expand=y

You can add parentheses behind the expanded navigation property (y in this example is called a navigation property in OData). Inside of these parentheses you can add most of the regular OData parameters that you are familiar with, such as $select and $filter, separated by semicolons. These OData parameters will be applied to the expanded entity. So for example:

/x?$expand=y($select=id,name;$filter=name eq 'test';$expand=z)

So the $select, $filter and $expand parameters in my example above are applied to the entity that is referenced by the navigation property y. Similarly, you can add parentheses to the navigation property z as well, in which you can define OData parameters for that referenced entity.

I hope this explains how it works.

Thanks Vincent! Exactly what we needed 😃 Brilliant!

Hi Vincent,

I’ve also been trying this, but can’t really get it to work.

this is the expansion i want to achieve: transl_carriage_id($expand=transl_object_id), but all I’m able to get is a single level. when trying to expand a 2nd time it does not return any data.

accessing transl_object_id from the carriage table (from here it’s 1 level) does yield results.

I’d love any insight on how i can get this to work.

Hello ssital,

It is hard to answer this question without knowing your goal or your data model, so this is going to be a bit of a shot in the dark.

I suspect that the issue here might be caused by a misunderstanding of how the transl_ navigation property works. The transl navigation property is used to expand lookup display values and for this reason it does not necessarily refer to the direct lookup entity (1 level), but instead it refers to the entity that contains the display value of the lookup (N levels). By contrast, the lookup_ navigation property does always refer to the direct lookup entity (1 level).

I'll try to illustrate this with a table.

Consider these three tables and imagine that they have references between them on my_lookup_id. When expanding transl_my_lookup_id from entity A, you are not expanding A → B, you are expanding A → C, because C is the entity that contains the display value of the lookup.

So if your intention was to expand entity C in your result for entity A, then you have two options:

A → C directly:

/A?$expand=transl_my_lookup_id($select=my_display_value)

A → B → C using the lookup_ navigation property:

/A?$expand=lookup_my_lookup_id($expand=lookup_my_lookup_id($select=my_display_value))

If this does not help you, then please supply some more information on what you are trying to achieve, including a picture of the relevant entities and references in your data model.

Hi,

So let’s say i have 3 tables:

So I’m starting in table shipment and need to make my way down to the “SHIPS” table.

Doing an expand on “CARRIAGES” using lookup returns the all the data of that carriage (as it should).

But now i want to go a level lower using the “ship_id” that’s available in “CARRIAGE” but i haven’t been able to get that to work:

$expand=lookup_carriage_id($expand=lookup_ship_id)

$expand=transl_carriage_id($expand=transl_ship_id)

I’ve tried both lookup & transl navigation properties.

Hello ssital,

It looks like Carriages → Ships is a detail reference, correct?

Try to expand from Carriage to Ships by using the detail_ navigation property. The naming convention for a detail navigation property is detail_<ref_id> where <ref_id> is the ID of the reference between Carriage and Ships in the SF.

So for example:

/shipments?$expand=lookup_carriage_id($expand=detail_carriages_ships($select=ship_id, data_i_want))

As a rule of thumb, if you’re navigating up, use lookup_ and if you're navigation down, use detail_. If you're trying to find the display value of a lookup property, use transl_.

We are working on some more documentation regarding this by the way, I am aware that not all of this is thoroughly explained in our docs.

I hope this helps.

Hello ssital,

As an addition to the above, it looks like Shipments → Carriages is actually a detail reference as well, in this context. If so then the same logic applies there and it would be more like this:

/shipments?$expand=detail_shipments_carriages($expand=detail_carriages_ships($select=ship_id, data_i_want))