Generate a drop-down list of unique results using data that sits in two different columns

[Jake_Naude]

Question: Is it possible to generate a drop-down list of unique results using data that sits in two different columns?

This one is bending my head… I have it working on one column but can’t figure out how to use it on two columns at once?

I have a table = RF_Design_Assets
It has a column with asset names = RFdesign_SOURCE_Asset_Name
And a second column with different asset names = RFdesign_DESTINATION_Asset_Name
There is also a ‘category’ column = RFdesign_Main_Asset

I have a form that uses a combination of the above and two columns in another table = Survey:
Select_Asset_Type
Select_Cabinet_To_Survey

To generate a drop-down list in the form and populate a 3rd column in the Survey table below:
Asset_Name

Asset_Name uses the following expression:

SORT(SELECT(RF_Design_Assets[RFdesign_SOURCE_Asset_Name], AND([_THISROW].[Select_Asset_Type]=[RFdesign_SOURCE_Asset_Type], [RFdesign_Main_Asset] = [Select_Cabinet_To_Survey]))[,Yes])

This expression works but the resulting drop-down list excludes items from column RFdesign_DESTINATION_Asset_Name

I hope this makes sense? Thanks in advance… Cheers…

[Suvrutt_Gurjar]

Hi @Jake_Naude ,

Jake_Naude:

This expression works but the resulting drop-down list excludes items from column RFdesign_DESTINATION_Asset_Name

I believe you may wish to mention, since your expression does not contain anything related to the column RFdesign_DESTINATION_Asset_Name , any specific reason you have in mind that you are expecting the values from that column to be incuded in the list coming out of the expression you have shared?

Jake_Naude:

SORT(SELECT(RF_Design_Assets[RFdesign_SOURCE_Asset_Name], AND([_THISROW].[Select_Asset_Type]=[RFdesign_SOURCE_Asset_Type], [RFdesign_Main_Asset] = [Select_Cabinet_To_Survey]))[,Yes])

Also you have referred a column [RFdesign_SOURCE_Asset_Type] in the expression, which you have not described.Also is there a similar column [RFdesign_DESTINATION_Asset_Type] corresponding to the column RFdesign_DESTINATION_Asset_Name

I believe more description on the above will help the community to help you better.

[Jake_Naude]

Hi Suvrutt_Gurjar,

Thanks for your reply. I see what you mean… My ask needs clarification…

Essentially I’m looking for an expression that will return a unique list from data that sits in two separate columns.

The basic use case is this:

A form is presented to Surveyors that connects to the Survey table.

The form asks for Survey[Select_Cabinet_To_Survey] which should then filter against RF_Design_Assets[ RFdesign_Main_Asset].

The form also asks for Survey[Select_Asset_Type] which should then filter against RF_Design_Assets[RFdesign_SOURCE_Asset_Type].

At this point the data in the following two columns needs to be looked at and presented as one drop-down option in the form:

RF_Design_Assets[RFdesign_SOURCE_Asset_Name]
This column holds asset names that are at the starting end of a cable and some of these asset names in this column are the same as in [RFdesign_DESTINATION_Asset_Name], but mostly different.

RF_Design_Assets[RFdesign_DESTINATION_Asset_Name]
This column holds asset names that are at the finishing end of a cable and some of these asset names in this column are the same as in [RFdesign_SOURCE_Asset_Name], but mostly different.

The final result would be a drop-down list of unique assets presented in Survey[Asset_Name]

And in plain language: When a Surveyor selects a network cabinet + an asset type that is connected to that cabinet, a unique list of asset names will be presented to the Surveyor of items that are in the SOURCE and DESTINATION columns.

I hope that helps to clarify.

I’m new at this stuff so please bear with me… Cheers…

[Suvrutt_Gurjar]

Hi @Jake_Naude,

Thank you for an elaborate description. This helps a lot. Does following expression help you?

SORT( UNIQUE(SELECT(RF_Design_Assets[RFdesign_SOURCE_Asset_Name], AND([_THISROW].[Select_Asset_Type]=[RFdesign_SOURCE_Asset_Type], [RFdesign_Main_Asset] = [_THISROW].[Select_Cabinet_To_Survey])) + SELECT(RF_Design_Assets[RFdesign_DESTINATION_Asset_Name], AND([_THISROW].[Select_Asset_Type]=[RFdesign_SOURCE_Asset_Type], [RFdesign_Main_Asset] = [_THISROW].[Select_Cabinet_To_Survey]))), TRUE)

Also please take a look at the UNIQUE funtion

help.appsheet.com

UNIQUE()

Unique items in list

[Jake_Naude · Accepted solution]

Hi @Suvrutt_Gurjar,

Thank you for your very prompt response.

I had looked at UNIQUE() and and a number of other functions before but it is only now that I see how you’ve applied it that it makes sense. The use of “+” is also a revelation to me and I now see how to sensibly join two expressions together!

Your offer does exactly what I asked for! Many thanks…

Solved!

SORT( UNIQUE(SELECT(RF_Design_Assets[RFdesign_SOURCE_Asset_Name], AND([_THISROW].[Select_Asset_Type]=[RFdesign_SOURCE_Asset_Type], [RFdesign_Main_Asset] = [_THISROW].[Select_Cabinet_To_Survey])) + SELECT(RF_Design_Assets[RFdesign_DESTINATION_Asset_Name], AND([_THISROW].[Select_Asset_Type]=[RFdesign_SOURCE_Asset_Type], [RFdesign_Main_Asset] = [_THISROW].[Select_Cabinet_To_Survey]))), TRUE)

[Suvrutt_Gurjar]

Hi @Jake_Naude,
You are welcome.

Good to know that expression works as per your requirement.