MongoDB ObjectID Referenced Object in table

App Building
,
1

Hello - I am using a MongDB database with a referenced ObjectId to another object in another table.
To query the record, I'm using an aggregate lookup:

[
    { "$match": {
        "CongNo": {{localStorage.values.Congregation.CongNo}},        
        "Status": {$regex:"{{ selectFilterRequestsStatus.value == null ? "" : selectFilterRequestsStatus.value }}"}
        }      
    }, 
    {"$lookup": {from:"Territories", localField:"No", foreignField:"_id", as:"No"}},
    {"$unwind": "$No" },
    {"$lookup": {from:"Users", localField:"User", foreignField:"_id", as:"User"}},
    {"$unwind":"$User"}
]

Here's an example of what is returned:
image

All looks good. However, when I populate the table with the results, the referenced column shows blank:

image
image1142×442 33.2 KB

The selection menu options are correct and it even saves correctly, but when the table loads, it doesn't display the current selected value or array.

image
image1145×450 53.2 KB

I'm guessing I need to run a transform on the table data aggregate lookup query but am not sure how to do that. Any help? Thank you in advance.

2

I got this working by using a transformer on the results:

Please reply if you have a better way of doing this.

for(i=0; i < data.length; i++) {
data[i].No = data[i].No.No;
data[i].User = data[i].User.Name;
}
return  data

EDIT: This doesn't work because when it saves the record upon editing, it doesn't know the ObjectId.

Still looking for help on this one... :thinking:

3

Hey @finedesignz :wave:

When you say "it doesn't display the current selected value or array" do you click the value in the dropdown and the cell stays empty?

Here is a link to our docs on Data Conversion: https://docs.retool.com/docs/javascript-in-retool#data-conversion-1

In my example below I used formatDataAsObject to convert the data to an object of table column arrays:

1196×548 69.2 KB


Does this work for your use case?

1722×1130 135 KB