4

I am trying to find a expression to fill in values automatcally, based on content of a table.

The field with the input is called RegNr. It is an abbreviation of two or three letters, like ABC or CF, followed by a space and a numerical code like 1234 or 43.

In my table, there are various columns, one is named Centre and represents the abbreviations and one is called Reg# which represents the numerical codes.

My goal is to find a expression that whenever I put in a RegNr (combination of Centre and Reg#) I want another field to get the value of a column of my table called NatNo.

I already tried to find various solutions but don't get it running. I think the function just needs to separate the input based on the part left or right of the space and then refers to the different columns of the table to give back the value of another column.

enter image description here

The red frame is the part that should be filled in automatically.

Does anybody have a idea?

Improve this question
3
  • Don't really understand your data structure. Do we speak about one or two layers? Would be helpful to provide sample data to understand the question. Anyway: Did you have a look at value map widgets? Maybe also virtual fields? One of those should help you solve the problem
    – Babel
    Commented Mar 4, 2023 at 13:41
  • I have two layers in my project. One is a pointlayer that should contain the autofill-function so whenever i enter the RegNr the NatNo should appear automatically in the field below. The second layer is just a regular spreadsheet that contains a big amount of data where the function should get the data from. Because the layer will be edited in the field with qfield, the option of value map as a kind of a dropdown menu to chose data from, seems to be pretty unhandy when working with around 9500 datasets. I haven't worked with virtual fields yet, how could they solve the problem?
    – Elias
    Commented Mar 4, 2023 at 13:51
  • 1
    IN the 2nd layer, use a virtual field to create a dynamic attribute that concatenates Centre and RegNo attributes to a composed value.
    – Babel
    Commented Mar 4, 2023 at 21:13

1 Answer 1

Reset to default
7
  1. Select your spreadsheet layer and open Field Calculator. Select 'Create virtual field', output field name RegNr, field type text, length whatever you need, and enter the following expression: "Centre" ||' '||"Reg #"
  2. In your points layer go to Properties > Attributes Form and select the Value Relation widget type for your RegNr field.
  3. Select Layer = (name of spreadsheet layer), Key column = RegNr and Value column = RegNr.
  4. Select 'Use completer' if you don't want a long drop down list and want to type in the value and have it autocomplete/show only valid values. Either way, you can only add in values that are present in the spreadsheet.
  5. While staying on the Attributes Form tab, now go to the widget settings for your NatNo field in your points layer, and under 'Default value', select 'Apply default value on update' and enter this expression (change spreadsheet layer name as needed)
attribute(get_feature('spreadsheet','RegNr',current_value( 'RegNr')),'NatNo')

This assumes you have a one-to-one or many-to-one relationship with RegNr and NatNo (i.e. each row in your spreadsheet has a unique RegNr and a corresponding NatNo).

enter image description here

Improve this answer
2
  • Thank you so much ! This is the perfect solution for my project and worked just absolutly fine ! :)
    – Elias
    Commented Mar 6, 2023 at 6:11
  • Don't forget to mark the question as answered if it solved your problem, which will help others with similar issues down the track.
    – she_weeds
    Commented Mar 6, 2023 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.