REF_ROWS limited to"key values"

AppSheet AppSheet Q&A
1

Hi, so I have two tables:
Table A has a column called Serial No.
Table B has a column called Serial No.

Table B feeds to a calendar, table A feeds to a list-view type of thing.
I want to open up the card from the list-view from table A and have below some “referenced dates” that are from Table B, matching the Serial No.
Using REF_ROW only lets me select the column “TransactionID” from TableA which will not match anything on Table B. I need to do something like REF_ROW but select “Serial No.” from Table A and return the Table B.[Event] that matches Serial No.

2

Hi Thank you for this. This almost works. Is there a way for SELECT() to return multiple values? Like can it return DataSet[ColumnA] - DataSet[ColumnB] with that format (a dash in between the answers)

3

Actually what it sounds like you’d want to do, would be to customize the Inline view associated with this column reference.

4

Tom_Cat:

Is there a way for SELECT() to return multiple values?

Nope.

An approach would be to use FILTER() in the app formula of a normal or virtual column to get a Ref to the corresponding row in Table B, then construct the desired output using CONCATENATE() and dereference expressions:

CONCATENATE(
  [Table B Row Ref].[ColumnA],
  " - ",
  [Table B Row Ref].[ColumnB],
)

where Table B Row Ref is the column with the Ref to the row in Table B.

5

Hi Steve,
Thanks for your help.
I am having a hard time, I can get the “SELECT()” statement to work, but this SELECT statement is used in a virtual column, my original plan was for this to “link” to the other detail views.
So for example you open Serial no. 1234, and it links to it’s child-events that coincide with the Serial No. 1234. So far I can show the virtual column that shows the names of the child-events but I cannot click on them and “go” to them. Is there a way to make this a clickable link?

6

You can create an action using LINKTOVIEW or LINKTOFILTEREDVIEW.

7

Thank you Bahbus, but how do I select that view? LINKTOVIEW(SELECT[TableA].[SerialNo.]=[_THISROW].[SerialNo.]) ??

8

You should read all of those first.

1 Like
9

Hi Bahbus,
Thank you! that LINKTOFILTEREDVIEW() seems to do the trick.
One additional question, if I wanted to add multiple filters to LINKTOFILTEREDVIEW() how would I go about it? I tried the following:
LINKTOFILTEREVIEW(“TableA_Detail”, AND([Serial No.] = [_THISROW].[Serial No.], [Event] = “Due Date”))
However that gives me a gigantic text entry as the result that seems to be code gibberish (aka an error)

10

Without knowing all your tables columns and stuff, I’m not entirely sure why it would give you the error, especially because I don’t know what the error is. But using AND() or OR() would be how you use multiple filters - depending on the case.

11

Tom_Cat:

However that gives me a gigantic text entry as the result that seems to be code gibberish (aka an error)

Screenshot?