Two-way references are great and easy to implement, even in the same table. But in the same column? I can’t see now to do it, neither how to work around it.
Here’s my use case: we’re cataloguing objects in a museum. Each object has a unique number, a name and so on. AND… it has a field for related objects.
Here’s a totally made-up and oversimplified example:
ID
Name
Related objects
1
Dining table
2, 3
2
Set of four chairs
1
3
Tablecloth
1
Here’s a proper Grist version of the two possibilities I’ve found so far.
It’s easy to build this using two separate columns and very convenient as $Related_objects for the record 1 gets updated as we input data in records 2 and 3.
But how could I get the resulting data of those two columns into one?
I could create a third column to concatenate the two, but that brings confusion as the display field won’t be the input field anymore AND it won’t provide links to related objects.
I’ve thought of adding a junction table, but can’t wrap my head around it.
After you referred to me as an expert, I was determined to find a solution! It is with sincere sadness that I must admit, the two ways you listed are the best ways to do this right now. I thought I had a clever idea on how to do it but it turned out to be a bit of a mess
My idea was to have a lookup formula column (related-formula) that finds everywhere that object is listed as a Related Object. Then, Related Object would be a trigger formula combining values across the two columns. Basically a loop of formulas…CHAOS.
Maybe you could create a custom widget that would do all of this somehow? That’s far beyond my technical skill though!
I did share your use case with the team to see if this would be a feasible feature in the future.
Hey @natalie-grist !
Thanks for taking the time to dig into this.
Please don’t be sad. At least, it confirms I have a pretty good understanding of references (two-way or not).
I’ll have to create a custom widget anyway, for printing purposes. I should be able to concatenate the two related-objects fields. That will solve the presentation aspect of the matter.
Thank you for that as well.
And moreover, thanks for sharing your experiment. That provides insight regarding trigger formula vs reference lists. I’ll have to look into that a bit deeper (for other aspects of the project).
