How To Add Custom Fields to Many to Many Relationship

[earnolmartin]

How do I add 3 custom fields to a many to many relationship for a custom module (in its linking table essentially)?

I’ve created a samples module that has a many to many relationship to products. I need to be able to define a quantity, lot, and a chop boolean field that is part of each linked / created product relationship from the sample.

Thus, in the products area below, when a product is linked or created, I need those three additional fields to show up there as part of the relationship and be editable. Is that possible?

Is there a way to do this? Google’s AI has given me two different processes to follow, but it doesn’t seem right (doesn’t match the structure of suitecrm8 exactly).

Google’s AI says:

Adding additional fields to a many-to-many relationship in SuiteCRM requires direct modification of the relationship metadata and is not achievable through Studio or Module Builder. This process involves several steps:

• Define the new fields in the relationship metadata:
  • Locate the relevant metadata file for your many-to-many relationship. This file is typically found in custom/metadata/ and is named after your relationship (e.g., fp_events_contactsMetaData.php for a relationship between FP_events and Contacts).
  • Within this file, locate the $dictionary array for your relationship and add your custom field definitions to the fields array. For example, to add a date_cancelled field of type date:

Code

    $dictionary['fp_events_contacts']['fields']['date_cancelled'] = array(        'name' => 'date_cancelled',        'type' => 'date',        'vname' => 'LBL_DATE_CANCELLED', // Label for the field        'massupdate' => false,        'no_default' => false,        'comments' => '',        'help' => '',        'importable' => 'true',        'duplicate_merge' => 'disabled',        'duplicate_merge_dom_value' => '0',        'audited' => false,        'reportable' => true,        'unified_search' => false,        'merge_filter' => 'disabled',        'calculated' => false,        'size' => '20',    );

• Add the field to the subpanel layout:

  • Navigate to the subpanel definition file for the module where you want to display the field (e.g., custom/Extension/modules/FP_events/Ext/Layoutdefs/fp_events_contacts.php).
  • Add your new field to the columns array within the subpanel definition.

• Create a custom language string (LBL) for the field:

  • Create or modify a language file (e.g., custom/Extension/modules/FP_events/Ext/Language/en_us.lang.php) and add a label for your new field.

Code

    $mod_strings['LBL_DATE_CANCELLED'] = 'Date Cancelled';

• Perform a Quick Repair and Rebuild:
  • Go to Admin > Repair and execute “Quick Repair and Rebuild” to apply your changes to the database and clear the cache.

After these steps, the custom field will be part of the relationship table and visible in the corresponding subpanel. You can then populate and manage the data for this field.

And also says:

Adding custom fields to a many-to-many relationship in SuiteCRM 8.9 requires manual code modifications as it is not possible to do directly through Studio or Module Builder. You cannot manipulate the relationship tables using the standard admin interfaces.

The process involves modifying several specific metadata and vardef files to define the new field in the relationship’s junction table and then expose it in the user interface.

Steps for Implementation

The implementation process involves several steps to define the field in the database and make it visible in the UI:

1. Define the field in metadata: Add the custom field definition to the relationship metadata file (custom/metadata/<relationship_name>MetaData.php) within the $dictionary array. Then, run a Quick Repair & Rebuild to create the field in the database junction table.
2. Define the field in Vardefs: Create a new Vardefs file (custom/Extension/modules/<ModuleName>/Ext/Vardefs/) and define the custom field, including a ‘relate’ type field, a definition for the value type with source set to 'non-db', and a definition for the field’s ID.* Define the field in subpanel layoutdefs: Add the new field to the 'list_fields' array in the subpanel layoutdefs file (custom/modules/<ModuleName>/metadata/subpanels/).* Add the field label: Include the display label for the new field in a custom language file (custom/Extension/application/Ext/Language/en_us.<ModuleName>.php).* Perform a final Quick Repair & Rebuild: Run another Quick Repair & Rebuild from the Admin panel to make the new fields functional and visible in the subpanel.

Alternative Approach (Workaround)

As an alternative to manual coding, you can create an intermediate custom module (a junction module). This new module can have many-to-one relationships with the original modules, allowing you to easily add custom fields via Studio.

[BastianHammer]

Hello Eric,

the AI is generating a solution into the right direction.
If you’re a developer, you could do it the PHP way.

https://support.sugarcrm.com/documentation/sugar_developer/sugar_developer_guide_14.0/data_framework/relationships/custom_relationships/
And check the forums / google for something like “custom fields relationship table” - there are quite a few threads about it.

If you’re not a developer, you’d simply create your junction / mapping module in the module builder.
Then you have 2 relationships:

product 1:n sample
sample n:1 accounts (or quotes or similar)

I’m missing the 3rd module in your description.
Products and samples - what’s the 3rd one?
What does the business requirement look like?
You want to know which lead received which kind of samples and how many during an opportunity?

[earnolmartin]

Well, we started development of this functionality in our custom built application, but I was seeing if SuiteCRM could be used to handle it instead.

Essentially, we have an “order” that contains products that we give away for free as samples to potential new customers. These “orders” can contain multiple products which each have a different quantity, lot code, and whether or not it should be chopped or not. We want to track this. The many-to-many relationship between this “order” of samples and products is correct, but the linking table needs additional custom fields.

For example, here is the UI I was working on before trying SuiteCRM:

(can only put one image in a post, so removed this one)

We would like to add these fields to the linked product entries, kind of like so:

Ignore the other relationships for now… we will be taking them out. The only relationship is to the product, with each product ordered containing a different quantity, lot, and chop instructions.

Is this possible using SuiteCRM? I added the definition of the new fields to the correct suitecrm/public/legacy/custom/metadata file in the file system for the samples-products relationship, but I don’t know how to now get them to display like in the screenshot / be editable after a product is associated with our order.

Is what I’m asking possible, and is there a guide somewhere that explains this process? I’ve been looking, but the AI instructions are incorrect / incomplete as far as I can tell.

[BastianHammer]

My first idea would be to use the standard invoice feature here (or quote).
You can add a type to the invoice with “sample” and “real” or similar.

There, you’ve already got all the features that you’re looking for as well as your relationship:

1 Customer : n orders with m line items : 1 product

The key point here are the line items:
You can add your fields chop and lot to the line items.
They’ll show up in reports and PDF templates.
To make them show up in the UI, you’d actually need to add a bit of code to the line items in the CRM.

If you don’t want to use the invoices, you can just create a custom module called “Sample Orders” and another one “Sample Order Products”.

Sample Order has a relationship to the Accounts and/or Contacts.
Sample Order Products has a relationship to the Product.
Samples Orders has a 1:n relationship to the Sample Order Products.

Inside your Sample Order Products you’d add your qty, chop and lot fields.

So you’d basically be recreating the invoice feature there

Despite the UI with the line items inside the order, there is 0 code / touching PHP or JS files required.

[markbond]

@BastianHammer @earnolmartin

And just for completeness Suite8 Use a line items and group fields field types to achieve this. (Its also partially used in the new Marketing Mail functionality), you basically set up a repeating field (line items) with a group of fields (ie the individual fields you are after) and they are all stored in the one table (your Order) with display handled for you and yes a “linked product” can be one of those fields (this is effectively how the Invoice/Quote module now works).

Actually adding fields to a “link” table is how the Events functionality adds some things in and its “non-standard” and doesn’t work with a lot of things (reports etc) so I wouldn’t recommend that.

The alternative would be a “middle” object (say order line) where that order line has a link to the order and a link to the product with all of your extra fields in it.

Mark