QGIS Attribute Forms – How to Configure the Attribute Forms to Show the Historic Table Related to ‘n’ Records

Question

I have a point geometry layer that correspond to objects to street lamps. For each point I have the key field "CODE_ID".

On the other hand, I have a maintenance history table. This table stores the "CODE_ID" field, the "DATE" field and the "DESCRIPTION" field.

Both objects (geometry and table) have the key field "CODE_ID".

My goal is to configure the Attributes form of the geometry layer so that it shows in some way the maintenance history that has been done for each geometry object

My first approach to finding a solution is to create a Join between the geometry layer and the table:

Then I would like the Identify features tool form to somehow show that each point has 'n' related maintenance table records ordered by date.

For example, a solution for me would be to be able to see the joined field "STREET_MAINTENANCE_DATE" as a dropdown list, which when selecting a date shows me the corresponding content of the "DESCRIPTION" field.

How should I have the Attributes form configured for the "STREET_MAINTENANCE_DATE" and "STREET_MAINTENANCE_DESCRIPTION" fields so that they behave this way?

For now, for the "STREET_MAINTENANCE_DATE" field I have chosen the Widget Type Value Relation with the following information:

However, the result is not what was expected. The "STREET_MAINTENANCE_DATE" field appears as a disabled dropdown list.

I would like to ask you for your approach and your experience (best practices) to complete this kind of work. If it's possible, I would like to ask you for the maximum details of Attributes form configuration (options, expressions,…) necessary to achieve this.

Answer 1

You can create a relation between the geometry layer layer and the table having the extra data.

Go to project/properties/relations/add relation. Make sure to give a name to the relation.

Then open the geometry layer properties, custom form. Opt for the drag and drop designer. Add fields from the geometry layer, then drag and drop the relation you have just created.

Now when opening the geometry layer attribute table or the feature info, you will also see the related table information. You can switch between a list or a table