I need QGIS to display combo boxes when editing for certain fields in a table, very similar to the functionality provided by the Relations properties in the project settings. It seems this is designed to simply have a value in one table refer to a list of possible values in another table, linked by foreign key.
However, my lookup table contains data for many different feature classes and more than one field between the two tables need to match. Any given field of a feature may or may not have subtype/domain values (not sure the correct terminology), so it's not absolute that the columns in two different features from the same feature class (table) will contain combo boxes for the same columns. It must be determined by simply querying the lookup table to see if that field has entries.
For any given feature, I need to query the lookup table for the following:
- if the 'featureclass' column of the lookup table has a value for the current featureclass (table/layer name with the vector data)
- if the 'code' column of the lookup table matches the 'code' column of the feature
- if the value of the given feature column exists in the lookup table's "field_name" column
I've developed a plugin that does just that. It loops through all of the fields of a selected feature and performs queries against my lookup table for each column. A query looks something like this:
SELECT *
FROM lookup_table
WHERE code='ABC'
AND LOWER(field_name)=LOWER('def')
AND LOWER(featureclass)=LOWER('my_feature_class')
If the result of the query is empty, then I build a simple QLineEdit widget for editing the text directly. If the result has values, I store them in a dictionary like this:
{ 1000: True, 1001: False, -999999: No 'Information' }
So where my feature has the value "1001" in the "def" column, I now build a QComboBox with the options "True", "False", and "No Information", and the combo box stores the integer codes as the data. When a user makes a change and clicks "Save", I manually execute a SQL command to update that record with the new integer value.
Is this functionality possible natively in QGIS using relations?