Inactive Role feature for contact account relationship

[alasdaircr]

Hello,

I want to be able to track people as they change jobs from one Account to another. In this case I don't want to remove the record of their previous role, just set it as inactive.

I've started work on the feature: https://github.com/alasdaircr/espocr...ae8ca12938a20e

It's very simple, just adding another column to accountContact table. if !active show a strikethrough on the contact's detail page.

I now wish to have only contacts with an 'active' role to show up in searches by default. This means adding a where clause to all searches for accountContact.acccount_id = <accid>.

I'm trying to see if there's a nice way of doing this or it'll just need to be a hack.

Is this something that Espo might want to take on in the future? If so I could tidy up the patch with your suggestions.

An issue I'm having is that the accountRole and accountId that is returned for contacts that haveMany accounts is the primary one, not the actual relevant accountRole.

e.g. John is CEO at Light Corp (primary account), and Janitor at Dark Corp. when I lists contacts of Dark Corp I see title: CEO for John, NOT Janitor.

Do you know how this could be fixed?

[yuri]

Hi

I'm really not sure. I checked stable version: it displays appropriate titles in Contacts panel. Did it work correctly before your customization?

[alasdaircr]

I just checked, and the title is correct in the Contacts panel, just not in the main Contacts list view if I add a filter of Account = <foo>

this is manageable though, but something is still wrong with my code as 'active' refers to the primary role, not the current relevant one, even in the contacts panel.

How does the selectManager deal with accountId differently in the panel compared to the main list view?

[alasdaircr]

Ok I see it's accountRole relation specified in Contact's listForAccount layout.

I wonder then if I need to filter inactive roles out twice in two different ways. Because only primary roles which are inactive are being filtered out, not another roles.

[alasdaircr]

This looks a bit weird:

            LEFT JOIN `account_contact` AS accountContact
            ON accountContact.contact_id = contact.id AND accountContact.account_id = contact.account_id AND accountContact.deleted = 0
         JOIN `account_contact` ON contact.id = account_contact.contact_id AND account_contact.account_id = '28415769' AND account_contact.deleted = '0' WHERE (contact.id IN (SELECT contact_id FROM account_contact WHERE deleted = 0 AND active = '1')) AND contact.deleted = '0' ORDER BY contact.first_name ASC LIMIT 0, 5; 

[alasdaircr]

The

 WHERE (contact.id IN (SELEET contact_id FROM account_contact WHERE deleted = 0)) 

shouldn't be needed should it, given the join above it

[alasdaircr]

So I'm having a bit of trouble with this and the syntax of all the joins, relationships etc is starting to confuse me again.

Have pushed the latest version to github and giving up for the weekend.

[yuri]

I didn't have time to sort out. I think it would be easier to change primary account (stored as account_id field) when the current one gets inactive. Or remove it if no more accounts are available.

[alasdaircr]

Hello , I already change primary when the current primary is made inactive.

When contacts are listed in a panel for an account's detail view, which field is used to show the correct role for that account?

[yuri]

application/Espo/Services/Account.php

    protected $linkSelectParams = array(
        'contacts' => array(
            'additionalColumns' => array(
                'role' => 'accountRole'
            )
        )
    ); 

Here is the magic place. It passes this params to ORM. I don't remember how it is implemented inside.

[alasdaircr]

I would still really like to get this working, even if it involves hacks.

I've updated my code here: What isn't working is that if a Contact has links to more than one Account, and has as 'active' role in at least one of them, they show up in all select operations, even when the AccountId is specified and that specific role is inactive.

It's because of this WHERE clause.

        accountActive: {
            type: bool,
            notStorable: true,
            select: accountContact.active,
            orderBy: accountContact.active {direction},
            where: {
                "=": "contact.id IN (SELECT contact_id FROM account_contact WHERE deleted = 0 AND active = {value})"
            }
        }, 

This translates to 'the contact is active in at least one role'. Which is not what I'm looking for. What I need to do is know when an AccountId is part of a query, and then modify the where clause to be something like

"=": "contact.id IN (SELET contact_id FROM account_contact WHERE account_id={account_id} deleted =0 AND active = {value}) 

But only one {value} is passed in to the query generator.

Like I say massive hacks would be acceptable. It just has to look and work properley.

Do you have any ideas yuri ?

[yuri]

In 4.0 it will be possible to add custom code to SelectManager class that generates whereClause for a specific field type. I think this will be helpful in your case.

[alasdaircr]

It could be, in the meantime I hacked it, and it works really well!

Perhaps it's something you guys would want to add? We track clients and their contacts, and quite oftern contacts move between different companies. It's good to keep a track of what is current and what is historic.

[yuri]

How you designed it in UX. Can you show screenshot of UI where user can set role to inactive.

[alasdaircr]

This is how it looks right now