Hello there,
I have two collections, let’s say facility and patient. I am trying to create a simple chart, where it shows number of patients per facility(name). Patient’s collection has facilityId and Facility collection has facility name.
I tried to use lookup field to join two collections, but when I am trying to connect id with facilityId, it isn’t populating anything. Just lookup field is being added.
Any help would be appreciated.
Hi @sunita_kodali -
It’s hard to say what’s going wrong without seeing some example documents from both collections. Your basic approach looks sound, but you’re probably doing something small wrong - can you please send more info?
Tom
Thank you for you response.
Example document:
Facility Table:
_id:ObjectId(‘1234abc’)
organizationId:“12ab”
facilityName:“xyz”
address:""
city:""
state:""
zip:""
Patient Table:
_id:ObjectId(‘xyz123’)
organizationId:“12ab”
facilityId:“1234abc”
firstName:“First”
lastName:“lastTest”
Trying to show on my charts, number of patients per facility name. I was able to do it per facilityId, but not with facility name.
Going over some of your responses, I saw ObjectId cannot be matched with string field. Do you think is that the issue I am going over too?
I also tried to deploy with advanced aggregation pipeline too. Still I couldn’t get what I need.
Thank you,
Sunita
Thanks - yes I think you figured out the problem. If you attempt to use a lookup where one field is a string and another is an Object ID, you won’t get any data. You can use Charts to convert the field in the main collection to the type used in the remote collection, and it should work - although it will be cleaner and faster if you could update your data to use consistent types.
Thanks for your time and support Tom.
Sunita
Morning Tom,
Hope your day is going well. Is there way I can convert all the existing documents field from string to objectId at once? Instead of doing one document at a time.
Thanks,
Sunita
I figured it out with “updateMany” option.
Thank you.
Hello Tom,
Is there a specific syntax for using lookup field in inject user specific filter.
return {userName:context.token.username} here userName is not look up field.
return {organizationId_lookup_organization_userName:context.token.username}, this syntax isn’t working for me.
Any ideas on how to fix this issue?
Thank you,
Sunita
Found the solution. Thank you.
Please share it so that others know. That is the best way to keep this forum useful.
return {“organizationId_lookup_organization.userName”:context.token.username}
lookup field should be in quotes.