Create List of Ref without a direct link

AppSheet AppSheet Q&A
1

Hi All,

Is it possible to create a list of ref without a direct link? (https://www.appsheet.com/samples/How-to-further-filter-a-ref-list-reverse-ref-column?appGuidString=95668af3-44fd-4acd-9a92-e56c14977e84)

Context:

  • I have a table A and C that do not have link to each other
  • I have a table B is a mapping table between A and C (B has a link to A and another link to C)

Goal: I would like to see the list of C under each record A in detail view.

Is it possible without having to create a dynamic dashboard?

2

So, B has two ref columns, to A and C?
That means A should have an auto-generated virtual column called “Related Bs”, this is a list of B records ref’d to A. So to get a list of C records ref’d by that list of B records, you’d use:
[Related Bs][refC]
Where “refC” is the column name in table B that is the ref column to C.

3

Thanks Marc. I just have a layout issue now. It doesn’t display the actual rows.

I end up having this:

2X_b_b56c1f469503770ba4a288b8823c6e95581767ec.png
2X_b_b56c1f469503770ba4a288b8823c6e95581767ec.png865×910 21.4 KB

This is how I set it up:

2X_9_99d9bd371415b584cf3c5da7883a2d4ce9f30a75.gif

Note I was able to remove the null value with the expression [Related Bs][refC]:
[Related Objective_Completion_Logs][Working_Teaching_Tips]-LIST("")

I also tried to have this field as a List of Ref but it automatically turns it back to List of EnumList.

4

any help on this one? I have the list but I am not able to display like a REF_ROW.

2X_8_8797c4326361bbd6cd7af2260ae4273b109fa91d.png
2X_8_8797c4326361bbd6cd7af2260ae4273b109fa91d.png769×879 23.1 KB

5

Element Type should be Ref.

6

I tried and the system always brings it back to EnumList after I save.

teambelair:

I also tried to have this field as a List of Ref but it automatically turns it back to List of EnumList.

7

You’re right, it does. I’m afraid I’m not sure what’s going on here.

1 Like
8

List is only allowed for lists constructed directly from list-producing functions (e.g., REF_ROWS(), SELECT(), FILTER()) in columns that are not user-modifiable, if I recall correctly. For all intents and purposes, EnumList is equivalent to List, so there’s no problem using EnumList. I have no idea why AppSheet has this distinction.

1 Like
9

Thanks @Steve.

I would like the user to see the list C records when viewing a record A and click on individual record C to access its details. Obviously it’s what List of ref offers but if I can’t do that, is there an alternative?

10

Please post a screenshot of the column configuration of the Working_Teaching_Tips column.

11

2X_a_ac56b39fac0d71e70116e373ea1f9c3234db6655.png
2X_a_ac56b39fac0d71e70116e373ea1f9c3234db6655.png1860×1597 138 KB

2X_e_eb4838a8284597eda9f72da6e33ef7d7aa3dc3e4.png
2X_e_eb4838a8284597eda9f72da6e33ef7d7aa3dc3e4.png1962×1566 213 KB

12

@Steve: Was the screenshot helpful?

Was thinking of another solution.
Context: Table A and C has no linkage. Table B is a mapping table between A and C.
Let’s say I create an Enumlist ref of C in Table A. What would be the best way to display ref of C under a record A?

2X_d_ddd42b6f014689937128905949334aad561e7967.png
2X_d_ddd42b6f014689937128905949334aad561e7967.png3840×1683 514 KB

13

I think the general strategy for such a situation is that any fields in C that you’re wanting to see from the context of A, you should create de-ref columns for in B.

A fairly typical example of a many-to-many relationship is:
Orders ← Line Items → Products
Where you create an order, and add line-item records corresponding to instances of products from a database. Obviously you’d want a list of products in your order, so you include a column in Line Items that de-references the display name from Products.

Unfortunately it doesn’t seem like it is currently possible to build a ref list like how you’re wanting. So the question is, what function exactly are you looking for, and can we build it another way?

2 Likes
14

Table A contains Classes
Table C contains Activities

I made a dashboard that allows admin to build kid classes. First tab is Table A, second tab is Table C. When browsing Table C, the user can add Activities in the class via an inline action (+). I have a table B that have records created via this action.

Tomorrow, I am thinking getting rid of this Table B to simply have an EnumList Ref of Table B in Table A. I’m anticipating generating class with random activities and thought it would be easier to deal with a list in Table A rather than generating a bunch or records of Table B.

Hope this helps.

Thanks!

15

Try as the App formula for a virtual column in Classes:

SELECT(
  Table B[Activity],
  ([_THISROW].[Class] = [Class])
)
2 Likes
16

@Steve: Forgot to thank you for this. That worked fine. List of Ref with the expression you suggested.

1 Like