Expression to "lookup" value of a field in another layer

Question

I am mapping out the electrical grid infrastructure of my country. I have a Substations layer (with fid and name fields) and a Existing_Lines layer (with fid, substation_start, substation_end and label layers.

substation_start and substation_end represent the nodes to which the power line connects, so the example in the attached image shows the power line starting at "Botesland" substation and ending at "Welterede" substation. Both fields are related to the Substations layer as shown below (rows 11 and 12).

I want to include the field label as a label to display on the map next to the power lines showing the route of the power line. The label of the example should display "Botesland - Weltevrede" but when I use the expression concat("substation_start", " - ", "substation_end") in the calculator for the label field in the Existing_Lines layer, QGIS returns the string 1 - 2 as a value in the field.

I understand why it's doing this - the fields substation_start and substation_end contain integers with the fid value of the features it references in the Substations layer...

Therefore, I need an expression that will "Lookup" the value in the name field for the fid of the feature on the other layer...

Can anyone please help me find the right expression?

Answer 1

You can get a feature from another layer in an expression, and then query a particular attribute of that feature.

The feature is retrieved with the get_feature function like so:

get_feature( layer,  field,  field_value_to_match)

The attribute of interest is queried by passing the feature to the attribute function.

attribute(get_feature( layer,  field,  field_value_to_match), attribute_of_interest)

The following expression is used on the Existing lines layer.

concat(
    attribute(get_feature('substations', 'fid', "substation_start"), 'name'),
    '-',
    attribute(get_feature('substations', 'fid', "substation_end"), 'name')
)

Here I get the feature from substations where its fid is equal to the value in the substation_start field of the Existing_lines layer. Then I get the attribute value of the name field from that feature. Likewise with substation_end, and then concat them, with which you are already familiar.

Be careful with single and double quotes. "substation_start" in double quotes signifies the attribute value in the current layer (Existing lines). The single quotes around the other field names are required for the functions to work. You are passing the field name as an argument, rather than the attribute value.