Select Query on Column Type Number

[MauriceWhelan]

I am using a Select Function to retrieve an initail value in my form, however, I want the field to remain as a Number type. I am getting an error unless I change the field type. Is there anything I can add to the expression to allow me to use the Select Function and maintain the field type as Number?

Thanks.

[Steve]

What is the expression? What is the exact text of the error message?

[MauriceWhelan]

Expression: Select(Product_Master_File[Length (m)],[Product Code]=[_ThisRow].[Product Code])

Error: Column Name ‘Length (m)’ in Schema ‘Operations_Master_Check_Schema’ of Column Type ‘Number’ has an invalid ‘Initial Value’ of ‘=Select(Product_Master_File[Length (m)],[Product Code]=[_ThisRow].[Product Code])’. The type of the Initial Value does not match the column type.

The [Length (m)] colum in the Product_Master_File is of Type Enum as it is part of a number of dependent dropdowns. Could this be this issue i.e. it is not of Type Number?

[Steve · Accepted solution]

The problem is that SELECT() produces a List result, but the column value should be a singular value. To fix, either wrap SELECT() in ANY():

ANY(
  SELECT(
    Product_Master_File[Length (m)],
    ([Product Code] = [_THISROW].[Product Code])
  )
)

Or replace SELECT() with LOOKUP():

LOOKUP(
  [_THISROW].[Product Code],
  "Product_Master_File",
  "Product Code",
  "Length (m)"
)

Both are equivalent.

[MauriceWhelan]

MauriceWhelan:

Select(Product_Master_File[Length (m)],[Product Code]=[_ThisRow].[Product Code])

Great, thank you @Steve