Imagine we have a table that contains students’ personal information: name, ID, classroom, etc. And now I’m making an absent record table. Obviously I’d need reference columns to retrieve students’ data from the previous table.
But due to my limited understanding of Grist, a column can either be a reference column or a look-up column. It can’t be both at the same time.
The reason why I want a two-in-one column is because sometimes I might forget the student’s name but do remember their ID, and sometimes the other way around. So if both the name column and the ID column can function as reference columns and look-up columns, I can type in either of the two columns, and the rest information would be automatically populated.
In short, is it possible to make a column behave like a reference column and a look-up column at the same time?
In this post you see my reply to someones elses question, but in this page I have the same problem. The data is send with contact.id or company.id. Sometimes it couldn’t find the correct id, so I added a widget to the page which will give me the contact if ID is filled but if not filled I can use the filters to look for the id. And yes I added much more fields before where you can look for id’s and use trigger with if filled use this id or else this, but this solution was better looking and better for me. Edit cell value from another table? - #3 by hera
Please share your page and I can help. In fact, you have allready what I would suggest. You have the students page as a widget (I would add some filters to look)
One simple option could be to create a combined column in the students table.
That contains both the name and the id.
Then you could select (and fuzzy search) for both in the absent records table.
So for example:
$Name + " " + $ID
this column you could hide to make it cleaner.
then in the Absent Records, change the visible column to this new column (i call mine slug).
Another tip, i also maintain a users database, i often have the issue that i do not know what is the last and what is the first name (we’re quite international), so i made two columns one for first and one for last name, then in the combined column (slug) i capitalize the last name:
