Select() with SPLIT() source from other TABLE

AppSheet AppSheet Q&A
1

Hello,

I have been trying to solve the following problem for quite some time.

I have two tables as shown in the link.

Or shown directly here:

TABLE#1_ARP

ARP# TOPIC_CONNECTED_TO_ARP#
A 1.1 a
A 1.1 b
A 1.1 c
A 1.1 d
A 1.1 e
A 1.2 a
A 1.2 b
A 1.2 c
A 1.2 d
…

TABLE#2_TOPIC

TOPIC ARP#
TOPIC1 A 1.2 e , A 4.2 b , A 1.1 b
TOPIC2 A 1.2 j
TOPIC3 A 1.2 e
TOPIC4 A 4.2 b
TOPIC5 A 1.2 j
TOPIC6 A 1.2 e
TOPIC7 A 1.2 e
TOPIC8 A 1.2 e , A 4.4 f
TOPIC9 A 1.2 j
TOPIC10 A 1.2 j
TOPIC11 A 1.2 j
TOPIC12 A 1.2 e
TOPIC13 B 5. a
TOPIC14 B 5. a , B 5. b , B 5. c
TOPIC15 B 5. a
TOPIC16 A 3. b

What I am trying to achieve is to list all topics from TABLE#2_TOPIC that are connected to an ARP#. The list should be in TABLE#1_ARP under TOPIC_CONNECTED_TO_ARP#.

I assume I can use the SELECT() operator here. I What I’ve tried so far in TABLE#1_ARP and column TOPIC_CONNECTED_TO_ARP# (as virtual column).:

SPLIT(TOPIC[ARP#], " , ")

This lists all ARP#s in each line. Like that:

A 1.2 e , A 4.2 b , A 1.1 b , A 1.2 j , A 1.2 e , A 4.2 b , A 1.2 j , A 1.2 e , A 1.2 e , A 1.2 e , A 4.4 f , A 1.2 j , A 1.2 j , A 1.2 j , A 1.2 e , B 5. a , B 5. a , B 5. b , B 5. c , B 5. a , A 3. b

And then with:

IN( [TOPIC_CONNECTED_TO_ARP#], SPLIT(TOPIC[ARP#], " , ") )

Here I get a yes or no answer as to whether TABLE#1_ARP[TOPIC_CONNECTED_TO_ARP#] is included in TABLE#2_TOPIC[ARP#] or not.

Then I tried using the SELECT() operator to collect the TOPICS in each row of TABLE#1_ARP.

SELECT(TOPIC[TOPIC], 
     (IN([TOPIC_CONNECTED_TO_ARP#], 
          SPLIT(TOPIC[ARP], " , "))
          )
     )

Unfortunately, nothing else happens and nothing is listed. When I use the SELECT() function with TRUE,TRUE, all topics in TABLE#1_ARP[TOPIC_CONNECTED_TO_ARP#] are listed, regardless of whether they were assigned to TABLE#2_TOPIC[ARP#].

AI was no help here either. Maybe someone can help?

Thanks

2

Hello @OldNo1,

I was able to make it work following the instructions on this Video.

In a nutshell:

  • ARP
    • Type: enum
    • Data validation: TOPIC[ARP#]
  • TOPIC_CONNECTED_TO_ARP#
    • Type: Text
    • Data validation: TOPIC[TOPIC]

I hope this helps!

1 Like
3

Hello Jose,

Thank you for your reply! I don’t think that’s what I meant. I didn’t want to create a drop-down menu like in the video, but rather an automatic list. No manual entries should be made in TABLE#1_ARP[TOPIC_CONNECTED_TO_ARP#]. The result should look like this:

TABLE#1_ARP

ARP# TOPIC_CONNECTED_TO_ARP#
A 1.1 a
A 1.1 b TOPIC1
A 1.1 c
A 1.1 d
A 1.1 e
A 1.2 a
A 1.2 b
A 1.2 c
A 1.2 d
A 1.2 e TOPIC1, TOPIC3, TOPIC6, TOPIC7, TOPIC8, TOPIC12
A 1.2 f
…

The listing in a cell, preferably as a list one below the other.

TOPIC1
TOPIC3
TOPIC6
TOPIC7
TOPIC8
TOPIC12

Is that possible?

4

Please try

SELECT(TOPIC[TOPIC],
(IN([_THISROW].[TOPIC_CONNECTED_TO_ARP#],
SPLIT([ARP], " , "))
)
)

The above expression may need some tweaks based on the various column types involved in the expression.

The suggested expression assumes that [ARP] in the TOPIC table is a long text column with comma separated values. If it is an enumlist or a list column , we will not need SPLIT().

SELECT() expression could be possibly avoided if you mention if there is any reference relationship between the two tables.

2 Likes
5

Thank you for your help!
When I use the function all topics in TABLE#1_ARP[TOPIC_CONNECTED_TO_ARP#] are listed, as soon TABLE#2_TOPIC[ARP#] is in this list.

TABLE#2_TOPIC[ARP#] is an enumlist with Data validation to TABLE#1_ARP[ARP#], because it is a Dropdown to select at least one, up to multiple ARP#. I have tried without the SPLIT() Operator, but the result stays the same. All topics in TABLE#1_ARP[TOPIC_CONNECTED_TO_ARP#] are listed, as soon TABLE#2_TOPIC[ARP#] is in this list.

6

App formula expression (assuming ARP# is an EnumList):

FILTER("TABLE#2_TOPIC", IN([_THISROW], [ARP#])
1 Like
7

Good catch @Steve .

I somehow mistyped [TOPIC_CONNECTED_TO_ARP#] in place of just [ARP#] for Table 1 and if [ARP#] is key of the Table 1, the expression can be still further optimized the way you did.

2 Likes
8

@Suvrutt_Gurjar & @Steve, THANK YOU!

@OldNo1, Please do let us know if this works :slight_smile:

1 Like
9

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.