In firestore you can add a fields that has a type of "reference", this field points to another id in another collection.
when you query it from retool you get back something like this
{
"$ref": "companies/pOjUBNOX9lHAsVkA2OEQ"
}
this tells us that it is a reference field ($ref), and the other collection has a name of "companies" and the rest is a 20 character id
This does not look very helpful in a table, in my case I wanted the name of the company.
The solution I found was to create a custom column, and in the calculation field I put this snippet.
{{ getAllCompanies.data.filter(function(entry){return entry._id === currentRow.company.$ref.slice(-20)})[0].name }}
in this snippet I reference another query (that gets all the companies), and filter it to match the record ID.
one day I will figure out how to pass data to a query to return only the data i need.
I hope this helps someone else.
