Reversing REF

Hello All,

Hopefully I can get some help on this, I would greatly appreciate it.

So I have a book database app I am working on. There is two tables I am working with, BOOKS and SERIES.

BOOK table is a list of all book titles, there is a “Series” column in that table that REF to the SERIES table.

Basically what I want to do is reverse the REF.

To explain a bit more, I want when you click a title on the BOOK table for it to go to the book details and there be a virtual column showing the other books that are also in that series.

Here is an image of what I want it to look like. Is this something that can be done? I did a bunch of different things from the forum and the help center but kept receiving errors or weird results.

Thank you in advance for reading and let me know if further explanation is needed. Thanks!

You can create a list type reverse reference column in the Books table with an expression something like

SELECT(Books Table [Books Table Key Column], [Series]=[_THISROW].[Series])

Please have that column as type list, element type ref and referenced table name as “Books Table”

An alternative approach could be you could group the Books table’s summary view ( table or deck or gallery view) by Series so that the user can see the books belonging to one series in a grouped manner in the summary view itself.

3 Likes

If your tables are connected using references, you can pull the list of books from the series reference.

[Series_Ref].[Related Books]

You might need to run some formatting over the result you get, to make it work (sometimes this is needed):

SPLIT(CONCATENATE([Series_Ref].[Related Books]), " , ")


This will be much more efficient than executing a SELECT() for each book record in the table.

Any chance you get to make use of references, dereferences, list dereferences do so

  • The ref system is extremely efficient.
6 Likes

Thanks! Just a follow up question, will this enable me to be able to select one of the books or will it just be a non clickable list?

Thanks,

I guess I would have the same question I asked to Suvrutt, to you. Would it just be a list with non clickable items or would it behave like a normal REF (inline) column?

Both of these would produce clickable lists:

1 Like

Thank you all!

3 Likes