How to make a column a "ref" to another table and allow multiple choices selected

AppSheet AppSheet Q&A
1

I have one table referencing another (customer table). i want my user to be able to choose mutiple customers.

This is a list of my users and the customers they are allowed to view.

2

Wouldn’t making the col a ENUMLIST type with base type REF work?

1 Like
3

I thought so - but I couldn’t see where to make the base type - Ref?

Correction - I was able to do this, but it doesnt allow the list to be an option. meaning it only allows me to “add” new choices. But not choose any from a list.

1 Like
4

Yours does not look like this?

TeeSee1_0-1652217651904.png
TeeSee1_0-1652217651904.png822Ă—950 39.5 KB

5

Yes. However when i do this nothing from the customer table shows up?

Tiger1_0-1652218141947.png
Tiger1_0-1652218141947.png779Ă—800 33.4 KB

Tiger1_1-1652218158805.png
Tiger1_1-1652218158805.png796Ă—660 19.9 KB

6

I sometimes wonder how dropdown lists are controlled myself. When setting REF doesn’t work I set VALID-IF or SUGGESTED VALUES expressions. This works everytime.

2 Likes
7

https://www.appsheet.com/templates/App-that-shows-an-EnumList-of-references-to-another-table?appGuidString=03522600-42f0-4015-a3b4-7da61f13a9d5

In this example, the author sets a VALID-IF expression.

2 Likes
8

Worked perfectly.

Thanks

1 Like
9

I spoke too soon. It gives me this error?

Tiger1_0-1652272845290.png
Tiger1_0-1652272845290.png775Ă—660 17 KB

FIXED - I had to choose the “key” to the table.

Thanks again.

10

Sorry last question:

How Do I make the new table bring the [Customer Number] with the corresponding [Customer Name] I just referenced?

11

you can use an expression..

LOOKUP([Customer Number], “customer table”, “customer number”, “customer name”)

This looks for the value [Customer Number] in “customer number” col of the table “customer table” and returns the value of “customer name” col of the matched row.

https://help.appsheet.com/en/articles/2357309-lookup

Does this answer your question?

2 Likes
12

@Tiger1 how did you solve this? I still get the empty list.

13

I did what @TeeSee1 suggested. I used Type - “Enumlist”. Then Reference to my table.

1 Like
14

yea i got that far. I was missing the Valif If = table[ID]

Screen Shot 2022-09-13 at 5.07.01 PM.png
Screen Shot 2022-09-13 at 5.07.01 PM.png691Ă—509 21.6 KB

15

Oh. I am sorry. Yes my valid if is this:

Tiger1_0-1663103512316.png
Tiger1_0-1663103512316.png774Ă—177 7.77 KB

Is it working for you now?

1 Like
16

@Tiger1 and @TeeSee1 The only thing missing is that whatever client i select from the multiple selection, do not get an automatic REF_ROWS virtual list like it usually creates when referencing to another table. Did you get to do that? If yes, how did you do it?

17

my table I reference DID get that “Ref_Row”

Tiger1_0-1663103764102.png

The table you are referencing doesn’t?

18

No, it does not. I’m basically selecting multiple customers in another
table call projects. Whatever customer i select in the multiple selection
should get a reference to that project in their row. But it’s not working.
It would be good to note that I’m REF to a slice to of table.

19

I thought you had to ref to another table. @TeeSee1 - any thoughts?

20

Ref_Rows only works between tables with a relation in the following way

Referenced Table A: Key A

Referencing Table B: Column of type Ref back to A (not of type EnumList)

@casitasrd you need to have a column in your customers table that looks something like below

SELECT (
 projects[project id],
 IN (
  [_THISROW],
  [customers]
 )
)

where [customers] is the field in table projects, of type ENUMLIST, referencing customers table, . This will give you a list of projects that a selected customer is associated with.

2 Likes