Import custom module with relationship to accounts

Hello.

I created a custom module - "Customer Licenses" and established a relationship to Accounts.

Sugar correctly created the module and added the subpanel to Accounts. I can add "customer license" records. Everything looks and works well. The table is in the DB as is the relationship table (accounts_customer_licenses).

Now I want to import customer_license records from our legacy system into Sugar.  I go to "Import" and download the import template.  The correct customer_license columns/fields are there.

But there is no way to relate these to accounts. When I read the Import guide and the Importing Related Records guide, they both state we should fill in the appropriate account ID field (after getting the account ID).  We've done this with several other types of modules (contacts, cases, etc.).  But there is no Account ID field in the customer_license import template - so nothing to fill in and no way to relate these imported records to their associated account.

I suppose we could write routines to load the customer_licenses and then manually load records in the join table (accounts_customer_licenses) but we certainly don't want to go through all that trouble.

Are we missing something here?  As far as I can tell, we're trying to do the same thing with this import that we would be doing if we were trying to import Contacts and relate them to Accounts.  In that case, there is also a relationship table and it gets populated because there is an Account_ID column in the Contacts import template.

Thanks very much for your help.

Bob

  • The imports you are referring to, the ones that have the Account ID field in the import template, probably have Relate fields, not Relationships, defined with Accounts:

    https://support.sugarcrm.com/Knowledge_Base/Studio_and_Module_Builder/Introduction_to_Relationships_and_Relate_Fields/

    While a Relate field is essentially a Foreign Key in the table itself, the Relationship creates a table to be able to link the IDs from two different tables. I have always written scripts for imports with relationships, I don't think there is a way to use the built in Import for that.

    FrancescaS

  • AFAIK, there is no way. It's a (highly) frustrating limitation. You have to use Talend or another ETL for this, especially if you are on Cloud without direct access to the DB. 

    Same frustration with lists: you need to package them in PHP files... 

    Damien Pochon

    CRM & Digital consultant @ ITS4U Group

  • Hi Francesca.  Thanks for your reply.  I just checked and both Contacts and Cases have no relate field with Accounts. They do both have relationships with Accounts and the Account ID does show up in their import template.

    As I mentioned, there IS a relationship table between accounts and my custom module - based on the relationship I built between the two.  I guess we'll have to import both modules separately and then populate the relationship table. Very frustrating but it can be done.

    Thank you.

  • Thank you Damien. It's the same conclusion that I've come to. It will be annoying but apparently we're going to have to do it for multiple custom modules - so maybe we can at least re-use some of the work.

    Thanks again.


  • That is curious, perhaps there is something special about those import processes that was built just for those modules.

    I've never worked in the Sugar Cloud environment, always on-site so things are a little different for me.

    You could try to see if you can understand what it is that is different in the Import process for those standard modules (reverse engineer it in a local copy of your SgarCRM) and customize your custom module's Import to mirror those or see if you can leverage the Sugar APIs to do your import and link the beans programmatically.

    Best of luck!

    Francesca

  • Hi Bob,

    When you add a relationship using Studio, Sugar ALWAYS create the table regardless whether it is a one-to-many or many-to-many. To add the Account ID as a field in your custom module, you will need to export the package from Module Builder, unpack it, hand-craft the relationship, and zip it back up for installation. 

    You can use a custom package to install that relationship afterwards too - it is just a matter of crafting it correctly.

    Good luck,

    Romney

  • If you create a Relationship, that is true. But if you add a field of type Relate it won't create the table, it will add a field for the Foreign Key in the module's table, like all other fields.

  • I just created a new module "licenses" in modulebuilder, type basic with a many-to-one relationship to accounts.

    After installation I changed the labels of the related Accounts feld to "licenseowner" just to be able to identify it better.

    Then I created an import file, started the import and assigned the company name from the import file to the field with the label from the relationship "Licenseowner".

    After the import, all accounts were assigned to the licenses.

    So this mechanism should work in the current version. Perhaps you must check your labels.

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH

  • Wow. I had never thought about using the "name" field... I never saw it because I was always looking for the ID field! 

    While this is not entirely safe (you may have contacts with similar names for instance), it's great news still!

    Damien Pochon

    CRM & Digital consultant @ ITS4U Group

  • When you add a label for the ID to custom/Extension/modules<yourmodule>/Ext/Language/en_us.anyname.php you can select the ID during the import.

    I added one line to the above file and got the possibility to select the ID.

    <?php
    // WARNING: The contents of this file are auto-generated.
    $mod_strings['LBL_LIC_LICENSE_ACCOUNTS_FROM_ACCOUNTS_TITLE'] = 'Licenseowner1';
    $mod_strings['LBL_LIC_LICENSE_ACCOUNTS_FROM_LIC_LICENSE_TITLE'] = 'Licenseowner2';
    $mod_strings['LBL_LIC_LICENSE_ACCOUNTS_FROM_LIC_LICENSE_TITLE_ID'] = 'LicenseownerID';

    The name depends from your module names and the created relationship name. You can find its value in cache/modules/<yourmodule>/<yourmodule>vardefs.php

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH