Hello 
Feature(s) impacted
Workspaces
Observed behavior
In our project we have 3 databases and I wanted to pool 2 tables that are in different databases. For that I tried to create a workspace in which I have my Contract and Beneficiaries collection, in which I try to display all the Beneficiaries having their id in the Contract collection which has the field fkBeneficiaryId
Iām not sure if itās the right beneficiary when I click on his contract
but when I want to select all the Beneficiaries, I have nothing to display.
Expected behavior
Is there a way to display all the beneficiaries having their id in the Contract table without selecting one by one?
Context
- Project name: Nostrum Care v2
- Team name: Operation
- Environment name: Development
- Agent (forest package) name & version: āforest-express-sequelizeā: ā^8.0.0ā
- Database type: Postgres
Thank you in advance
Hi @jacques_liao ,
Do you want to have all beneficiaries for all Contracts or only for some contracts ?
Best regards,
Shohan
Hi @shohanr,
Yes I would like to have only the beneficiaries who have a contract and not to take into account the other beneficiaries.
Yours sincerely
Does your beneficiaries could have several contracts ? Or one beneficiary can only have one contract ?
yes, a beneficiary can only have one contract.
Ok then a solution (only if you have 1 to 1 relationship) would be to create a smart field (cf. the documentation on the beneficiary collection with the id of the benefiacyās contract.
Then you will have a new column and you will be able to filter on it for records without value on this column.
For a persistant filter you can create segment with this solution.
Hi,
I managed to create the new column but when I try to create a segment I canāt see the new column.
And also these 2 collections are in 2 different databases and in my opinion thatās why I canāt see the data.
Hi,
Do you see the column on the collection view?
Hi @jacques_liao ,
Did you activate the option
Filtering enabled
at true for this field ?
Hi, Yes
when I move my mouse over the
Filtering enabled
it prevents me from clicking on it
Hi @jacques_liao ,
In order to filter your smart field, you must 1) add the isFilterable to true in your agent for this field.
2) Implement the filter function for the field
You have an example in the docs right here..
Best regards,
Shohan
Hello @shohanr
now I have the filtering of this field
However when I try to put is blank or is present I get the following error
[forest] ļ³ļ³ļ³ Unexpected error: āfilterā method on smart field ānvContractsā must return a
condition
{
āstackā: āError: "filter" method on smart field "nvContracts" must return a condition\n
at _callee$ (/usr/src/app/node_modules/forest-express/dist/services/base-filters-parser.js:98:19)\n at tryCatch (/usr/src/app/node_modules/rege
nerator-runtime/runtime.js:64:40)\n at Generator.invoke (/usr/src/app/node_modules/regenerator-runtime/runtime.js:299:22)\n at Generator.next (/
usr/src/app/node_modules/regenerator-runtime/runtime.js:124:21)\n at asyncGeneratorStep (/usr/src/app/node_modules/forest-express/node_modules/@bab
el/runtime/helpers/asyncToGenerator.js:3:24)\n at _next (/usr/src/app/node_modules/forest-express/node_modules/@babel/runtime/helpers/asyncToGenera
tor.js:25:9)ā
}
here is my code
{
field: 'nvContracts',
isFilterable: true,
get: async (record) => {
const nvContracts = await models.nvContracts.findOne({
where: {
fkUserId: record.dataValues.fkUserIdKey
}
})
return nvContracts.id;
},
filter({ condition, where }) {
switch (condition.operator) {
case 'is_present':
return {
id: { [Op.ne]: null },
};
case 'is_blank':
return {
id: { [Op.eg]: null },
};
default:
return null;
}
}
}
thank you in advance
Hey @jacques_liao,
itās almost perfect, the only issue is that the operator name are not good itās actually present and blank:
{
field: 'nvContracts',
isFilterable: true,
get: async (record) => {
const nvContracts = await models.nvContracts.findOne({
where: {
fkUserId: record.dataValues.fkUserIdKey
}
})
return nvContracts.id;
},
filter({ condition, where }) {
switch (condition.operator) {
case 'present':
return {
id: { [Op.ne]: null },
};
case 'blank':
return {
id: { [Op.eg]: null },
};
default:
return null;
}
}
}
Donāt hesitate to look what is sent by forest when you use a filter, until we get a better documentation 
Hello @anon62739609,
Yes it was a mistake on my side sorry
{
field: 'nvContracts',
isFilterable: true,
get: async (record) => {
const nvContracts = await models.nvContracts.findOne({
where: {
fkUserId: record.dataValues.fkUserIdKey
}
})
return nvContracts.id;
},
filter({ condition, where }) {
switch (condition.operator) {
case 'present':
return {
nvContracts: { [Op.ne]: null },
};
case 'blank':
return {
nvContracts: { [Op.eq]: null },
};
default:
return null;
}
}
}
After correcting my error I have this error which is normal since my beneficiaries table does not contain a nvContracts field.
āstackā: "SequelizeDatabaseError: column beneficiaries.nvContracts does not exist
However how can I check the value of my smart fields is null or not?
thank you in advance!
itās ok i was able to do it via a search of both tables now my filter is present is working fine 
and I could create the segment via this smart field
Thank you for your help!!
Iām trying to catch up, sorry if Iām repeating . So to summarize, your ānvContractsā is on your āBeneficiariesā collection.
My suggestion, nvContracts should actually be a smart relationships. This will allow you to your contract inside a benificiary and easily filter on it.
So it should give something like that:
{
field: 'nvContracts',
type: 'String',
// In reference put the name of your contracts collection and it's primary key ("tableName.primaryKey")
reference: 'Contracts.id',
isFilterable: true,
get: async (record) => {
return await models.nvContracts.findOne({
where: {
fkUserId: record.dataValues.fkUserIdKey
}
})
},
filter({ condition, where }) {
const [,nvContractField] = condition.field.split(':');
const nvContracts = await models.nvContracts.findAll({
attributes: ['fkUserId'],
where: { [nvContractField]: where },
});
return nvContracts.map(contract => contract.fkUserId);
}
}
Something like that could do the trick I think.
Our latest agent really ease the work if you have time to migrate: Quick start - Node.js Developer Guide
Thank you for your feedback, Iām testing it all!
