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

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:

Q survey table 1

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

Q rf_design table 1

Thanks in advance… :slight_smile:

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”
)
1 Like

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

1 Like

Thanks @Heru & @Aleksi,

Done that but getting the following error:

Either of these two columns are then an EnumList?

Hi @Aleksi

Correct… [survey_status] in the survey table is Enum

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

Check your [asset_name] and [rfdesign_source_asset_name] columns

Hi @Aleksi,

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

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)

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…?

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

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

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

2 Likes

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?

Rooky Error 1

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

:slight_smile:

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.