REF_ROWS limited to"key values"

Tom_Cat · 01-02-2020 12:33 PM

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.

Steve

Tom_Cat

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)

Bahbus

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

Steve

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.

Tom_Cat

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?

Bahbus

You can create an action using LINKTOVIEW or LINKTOFILTEREDVIEW.

Tom_Cat

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

Bahbus

You should read all of those first.

Tom_Cat

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)

Bahbus

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.

Steve

Screenshot?