Hi @LeventK,

Thanks for your time.

I’d be very grateful if you could spare a little more to help me apply your expression?

It feels like it belongs in this ValidIf in [asset_name] EnumList. BAseType Ref, (asset table)

I’ve tried it in a coupe of places but can’t get it to work.

I’ve also tried it on it’s own as an Initial Value

If you do have time, here’s how the current ValidIf flows:

- Produce a full list of asset names for this cabinet from rf_design table if survey branch number is blank i.e. if it’s the first survey on this cabinet

IFS(

ISBLANK([survey_branch_number]),

ORDERBY(

SELECT(asset[id],

IN([id],

SORT(

UNIQUE(

SELECT(rf_design[Source Asset ID],

AND(

[_THISROW].[select_asset_type]=[source_asset_type],

[main_asset_name] = [_THISROW].[select_cabinet_to_survey],

[lcs] = [_THISROW].[LCS]

))

+

SELECT(rf_design[Destination Asset ID],

AND(

[_THISROW-2].[select_asset_type]=[destination_asset_type],

[main_asset_name] = [_THISROW-2].[select_cabinet_to_survey],

[lcs] = [_THISROW-2].[LCS],

NOT(

[destination_asset_type]=‘Cabinet’

)))

),FALSE

)),TRUE

),[asset_name]

),

- if it’s a survey of single asset by this surveyor

AND(

[survey_type] = “Single Asset”,

COUNT(

SELECT(

survey[_ROWNUMBER],

AND(

[survey_branch_number] = [_THISROW].[survey_branch_number],

[surveyor_email] = USEREMAIL(),

(COUNT([survey_asset_id]) = 1)

)))>0

),

- then return the MAX survey_asset_id (one)

LOOKUP(

MAX(

SELECT(

survey[_ROWNUMBER],

AND(

[survey_branch_number] = [_THISROW].[survey_branch_number],

[surveyor_email] = USEREMAIL(),

(COUNT([survey_asset_id]) = 1)

))),

“survey”,"_ROWNUMBER",“survey_asset_id”

),

- if it’s a survey of multiple assets - of certain cable types - by this surveyor

AND(

[survey_type] = “Multiple Assets”,

COUNT(

SELECT(

survey[_ROWNUMBER],

AND(

[survey_branch_number] = [_THISROW].[survey_branch_number],

[surveyor_email] = USEREMAIL(),

(COUNT([survey_asset_id]) > 1),

OR(

[select_asset_type] = “CAT6A”,

[select_asset_type] = “Fibre”,

[select_asset_type] = “Jumper”,

[select_asset_type] = “Power”

))))>0

),

- then return MAX survey_asset_id (many)

LOOKUP(

MAX(

SELECT(

survey[_ROWNUMBER],

AND(

[survey_branch_number] = [_THISROW].[survey_branch_number],

[surveyor_email] = USEREMAIL(),

(COUNT([survey_asset_id]) > 1),

OR(

[select_asset_type] = “CAT6A”,

[select_asset_type] = “Fibre”,

[select_asset_type] = “Jumper”,

[select_asset_type] = “Power”

)))),

“survey”,"_ROWNUMBER",“survey_asset_id”

)

)

- this produces 6 asset names to chose from before a branch number is selected

- if branch 1 is selected then the three assets from this surveyor’s last survey are presented

- if branch 2/3/4/5 are selected, then your expression needs to kick into action:
- please could you advise where to insert it?

IFS(

ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,

“branch_A_number”, “branch_A_assets”)),

LOOKUP(“survey_branch_number”, “survey”,

“branch_A_number”, “branch_A_assets”),

TRUE,

IFS(

ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,

“branch_B_number”, “branch_B_assets”)),

LOOKUP(“survey_branch_number”, “survey”,

“branch_B_number”, “branch_B_assets”),

TRUE,

IFS(

ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,

“branch_C_number”, “branch_C_assets”)),

LOOKUP(“survey_branch_number”, “survey”,

“branch_C_number”, “branch_C_assets”),

TRUE,

IFS(

ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,

“branch_D_number”, “branch_D_assets”)),

LOOKUP(“survey_branch_number”, “survey”,

“branch_D_number”, “branch_D_assets”),

TRUE,

LOOKUP(“survey_branch_number”, “survey”,

“branch_E_number”, “branch_E_assets”)

)

)

)

)

- Many thanks…