Find specific [survey_status] for an asset that has many records

[Jake_Naude]

Evening… Please could someone point a newbie in the right direction?

I need to look at [asset_name] and find the most recent [survey_status] in the survey table below which has updates & adds permissions:

And then put it in [survey_status] against [rfdesign_source_asset_name] in the rf_design table below which only has updates permissions:

Thanks in advance…

[hru]

You might want to try below in an app_formula of a new Virtual Column of the rf_design table:

LOOKUP(
  MAXROW(“survey”,“_ROWNUMBER”,
    [asset_name]=[rfdesign_source_asset_name]
    ),
  “survey”,
  “id”,
  “survey_status”
)

[Aleksi]

Small adjustment…instead of saying
…=[rfdesign_source_asset_name]),
you would need to say
…=[_THISROW].[rfdesign_source_asset_name])

[Jake_Naude]

Thanks @Heru & @Aleksi,

Done that but getting the following error:

[Aleksi]

Either of these two columns are then an EnumList?

[Jake_Naude]

Hi @Aleksi …

Correct… [survey_status] in the survey table is Enum…

[Jake_Naude]

[Aleksi]

Enum field is not a list type. Only EnumList is.

[Aleksi]

Check your [asset_name] and [rfdesign_source_asset_name] columns

[Jake_Naude]

Hi @Aleksi,

Is there a way for me to ‘flatten’ the [asset_name] EmunList result and refer to just the value presented in that field…?

[Jake_Naude]

Hi @Aleksi,

Oops… You’re right… [Asset_Name] is EnumList

[Jake_Naude]

Hi @Aleksi,

I don’t know if this will help… This is what [asset_name] Valid If does:

SORT(UNIQUE(

SELECT(RF_Design[RFdesign_SOURCE_Asset_Name],
AND([_THISROW].[Select_Asset_Type]=[RFdesign_SOURCE_Asset_Type], [RFdesign_Main_Asset] = [_THISROW].[Select_Cabinet_To_Survey])) +

  SELECT(RF_Design[RFdesign_DESTINATION_Asset_Name],
  AND([_THISROW].[Select_Asset_Type]=[RFdesign_DESTINATION_Asset_Type], [RFdesign_Main_Asset] = [_THISROW].[Select_Cabinet_To_Survey]))), FALSE)

[Bahbus · Accepted solution]

LOOKUP(
  MAXROW(“survey”,“_ROWNUMBER”,
    IN([_THISROW].[rfdesign_source_asset_name], [asset_name])
    ),
  “survey”,
  “id”,
  “survey_status”
)

[Jake_Naude]

@Bahbus strikes again… That worked… Learning something new every day… Many thanks…

Thanks also to @Heru for pointing me in the right direction and @Aleksi for weighing in… Cheers…

[Jake_Naude]

Hi @Bahbus

Your solution works and shows the expected result when I look at the form i.e. [survey_status] shows “Completed”.

However, I’m making some kind of rooky error cause I only see the updated [survey_status] in my table if I update the record?

Please advise if it is possible to show updated [survey_status] for all rows without updating the record?

I was expecting to see “Completed” for CAT037 too and obviously nothing for anything else yet?

[hru]

For a normal column, you need to open the record and save.

I believed, you will need to update it again later, you might want to consider to use an Action for that.