Referencing middle tables from formula script

[dnaoverride]

Can I access middle tables from formula script to fetch some data.
I have tried:

$category_id = record\findOne('contactContactsCategories', null, null, 'contact_id=', $contact_id);

But no value is returned.

My middle table definition is:

[yuri]

Should be possible with:

$id = record\findOne('ContactContactsCategory', null, null, 'contactId=', $contactId);

$categoryId = record\findOne('ContactContactsCategory', $id, 'categoryId');​

[rabii]

you can do it like below:

record\relationColumn('Contact', $contactId, 'contactscategories', $contactCategoryId, 'role') 

In this example you will retrieve the role column from the middle table.

[dnaoverride]

Thanks guys made it work somehow, kind of 😂

Here's the created formula:

/*
* For Reports purpose
* Fill name of contact category and number of meetings attributes
*/

if (entity\isAttributeChanged('contactsCategoriesIds')) {
    // Get id of the current contact
    $contactId = id;
    
    // Find id from middle table row which contains $contactId and not deleted  
    $id = record\findOne('ContactContactsCategory', null, null, 'contactId=', $contactId, 'deleted=', 0);
    
    // find in middle table categoryId attribute for given $id
    $categoryId = record\attribute('ContactContactsCategory', $id, 'contactsCategoryId');
    
    // Setting contact attributes
    numberofvisits = record\attribute('ContactsCategory', $categoryId, 'requiredNumberOfVisits');
    contactCategoryName = record\attribute('ContactsCategory', $categoryId, 'name');
}​

I have contact categories which contains category names (A, B, C...) and required number of meetings for a contact.
Problem I'm facing now, is when I change contact category, the required number of visits change but somehow it reflects previous categories required number of visits.
For instance if previous category was B and 6 visits, when i change to A category which has 8 visits, numberofvisits will be 6, and if i change after, to B it will became 8....
Can some of you guys see a bug in my code and reasoning?

Thanks...

[dnaoverride]

Found solution myself did a stupid thing, I consulted previous contact category and i simply should do following:

/*
* For Reports purpose
* Fill name of contact category and number of meetings attributes
*/

if (entity\isAttributeChanged('contactsCategoriesIds')) {
    // Get id of the selected contact category
    $categoryId = array\at(contactsCategoriesIds, 0);
    
    // Setting attributes
    numberofvisits = record\attribute('ContactsCategory', $categoryId, 'requiredNumberOfVisits');
    contactCategoryName = record\attribute('ContactsCategory', $categoryId, 'name');
}​

When you overworked can't think straight 😂