Workspaces display all people with their id in another collection

Hello :wave:

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 :sweat_smile:

Hello @vince,

Yes it was a mistake on my side sorry :sweat_smile:

    {
      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 :slight_smile:
and I could create the segment via this smart field

Thank you for your help!!

2 Likes

I’m trying to catch up, sorry if I’m repeating :sweat_smile:. 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

1 Like

Thank you for your feedback, I’m testing it all!