Create List of Ref without a direct link

[teambelair]

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=9...)

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?

[Marc_Dillon]

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.

[teambelair]

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

I end up having this:

This is how I set it up:

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.

[teambelair]

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

[Steve]

Element Type should be Ref.

[teambelair]

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.

[Steve]

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.

[Marc_Dillon]

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

[teambelair]

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?

[Steve]

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

[teambelair]

[teambelair]

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

[Marc_Dillon]

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?

[teambelair]

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!

[Steve · Accepted solution]

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

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

[teambelair]

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