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

[Tiger1]

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.

[TeeSee1 · Accepted solution]

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.

[TeeSee1]

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

[Tiger1]

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.

[TeeSee1]

Yours does not look like this?

[Tiger1]

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

[casitasrd]

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

[Tiger1]

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

[casitasrd]

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

[Tiger1]

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

Is it working for you now?

[casitasrd]

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

[Tiger1]

my table I reference DID get that "Ref_Row"

The table you are referencing doesn't?

[casitasrd]

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.

[Tiger1]

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

[TeeSee1]

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.

[casitasrd]

@TeeSee1 @Tiger1 I already figured out with the help of the community here How to REF_ROW using a ENUM List base type Ref 

Thanks for your help anyways. I really appreciate it. It looks like we have similar answers.

[TeeSee1 · Accepted solution]

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.

[TeeSee1 · Accepted solution]

https://www.appsheet.com/templates/App-that-shows-an-EnumList-of-references-to-another-table?appGuid...

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

[Tiger1]

I spoke too soon.   It gives me this error?

FIXED - I had to choose the "key" to the table.

Thanks again.

[Tiger1]

Sorry last question:

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

[TeeSee1]

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?

[Tiger1]

Worked perfectly.

Thanks