FORMULA - How to find Record from team id

[Giannidimanno]

Hi guys,
how can I find a firt record in entity 'Periti' that have team id = '5f5509aec68fcfb4a' . I'm using the formula.
I'm trying something like this but not working
$id=record\findOne('Periti', 'createdAt', 'desc' ,'teamsIds=','5f5509aec68fcfb4a');

Is it wrong?

[item]

Hello,

https://docs.espocrm.com/administration/formula/#list

[Giannidimanno]

Hi,
thanks for your suggest.
I changed my formula like this
$id=record\findOne('Periti', 'createdAt', 'desc' ,'teamsIds=',list('5f5509aec68fcfb4a'));


but I got on the logs this error message:
Column not found: 1054 Unknown column 'periti.teams_ids' in 'where clause'

Any suggestions?

[Maximus]

Hi there,
for using the record\findOne function you need to set the filter (system filter or created by a report). There are a few ways on how to add this filter if it doesn't exist:
1. Via code https://docs.espocrm.com/development...select-manager
2. By utilizing Report https://docs.espocrm.com/administration/formula/#filter

[eymen-elkum]

Some corrections that may help:

1. teamsIds is array of id and not single id, so it is no expected to work for this:
   $id=record\findOne('Periti', 'createdAt', 'desc' ,'teamsIds=','5f5509aec68fcfb4a');
2. Teams are not directly linked to entities, but there is a middle table "entity_team" and this table has no entity definition.
   to search for the id of entity with specific team you must search in that middle table
   I made an sql statement to explain this:
   "SELECT entity_id FROM `entity_team` WHERE entity_type = 'Periti' AND team_id = '5f5509aec68fcfb4a'"
   this will return a list of IDs

May worth the time to try to define an entity for the EntityTeam, then you will be able to use it in the search using formula, BUT TRY ON A TEST CRM.

I think you need to define a new formula That do the job from here:

https://docs.espocrm.com/development/new-function-in-formula/