Custom Modules, relate fields issue / question

sapling · 12 December 2019 14:16

Hello All,
New to the forum and suite/sugar crm in general and I am currently having trouble figuring some stuff out.
Basically I have 2 custom modules a quick layout of the fields in the modules here:
[Module 1]
Customer Name, Product_Requested, Serial_number
[Module 2]
Name, Serial_Number, Status

I am trying to set it up so that I can select a serial number from Module 2 when filling out the Module 1 form to create.
Right now the only field I can select is the name field which of course displays the name instead of the serial number.
I found a few older posts but all of them were a bit out of date and the instructions didnt match up nor did that make 100% sense to me.

I have tried adding relationships etc… both ways my serial_number is a relate field in module 1 to module2
Any assistance here would be appreciated.

p.konetskiy · 13 December 2019 12:00

You cann’t take only field “serial_number” without field “name” using standart decision of SuiteCRM.

The Module1 should have relationship with the Module2. Fields (name and id) of Module1 will have data from fields “name” and “id” of Module2 and field “serial_number” of Module1 will have data from field “serial_number” of Module2. You can use this code in editviewdefs.php of Module1, for example:

                    'panels' => array (
                        ...
                        'default' => array (
                            ...
                            <number> => array (
                                <number> => array (
                                    'name' => <Field type relationship>,
                                    'displayParams' => array (
                                        'field_to_name_array'=> array(
                                            'name' => <Field type relationship>,
                                            'id' => <Field id type relationship>,
                                            'serial_number' => 'serial_number',
                                        ),
                                    )
                                ),
                            ...
                            ),
                        ...
                        ),
                    ...
                    ),

This way have one problem. If you change field “serial_number” of Module2 this information don’t copy to “serial_number” of Module1 automatic. You chould use “logic hook” for coping.

Another way. You can use field “name” of Module2 for information about serial number.

sapling · 16 December 2019 10:38

Thanks. One more question what goes in the <Field type relationship> and where do I find the value? I assume it must be related to something else?

p.konetskiy · 16 December 2019 17:27

Hi, @sapling! If you work with Studio please show the screenshot of relationship. See example. After that I can write code include yours field names.

sapling · 17 December 2019 11:37

Thanks!
So p_inventory is the module where serial numbers reside.
The module I want to pull them into is hw_request.

p.konetskiy · 17 December 2019 15:30

@sapling
Please open cache/modules/hw_request/hw_requestvardefs.php.
Find 3 fields where ‘relationship’ or ‘link’ = ‘eval_hw_request_inv_p_inventory’
And
<Field type relationship> = field which name ended ‘_name’
<Field id type relationship> = field which name ended ‘_ida’
It’s will be correct only if this relationship made in Studio.

sapling · 17 December 2019 15:56

I created the relationship in the custom module builder. Should I delete and re-create it with the studio module instead?

  'inv_p_inventory_id_c' =>
    array (
      'required' => false,
      'name' => 'inv_p_inventory_id_c',
      'vname' => 'LBL_SERIAL_NUMBER_INV_P_INVENTORY_ID',
      'type' => 'id',
      'massupdate' => 0,
      'no_default' => false,
      'comments' => '',
      'help' => '',
      'importable' => 'true',
      'duplicate_merge' => 'disabled',
      'duplicate_merge_dom_value' => 0,
      'audited' => false,
      'inline_edit' => true,
      'reportable' => false,
      'unified_search' => false,
      'merge_filter' => 'disabled',
      'len' => 36,
      'size' => '20',
    ),
    'serial_number' =>
    array (
      'required' => false,
      'source' => 'non-db',
      'name' => 'serial_number',
      'vname' => 'LBL_SERIAL_NUMBER',
      'type' => 'relate',
      'massupdate' => 0,
      'no_default' => false,
      'comments' => '',
      'help' => '',
      'importable' => 'true',
      'duplicate_merge' => 'disabled',
      'duplicate_merge_dom_value' => '0',
      'audited' => false,
      'inline_edit' => true,
      'reportable' => true,
      'unified_search' => false,
      'merge_filter' => 'disabled',
      'len' => '255',
      'size' => '20',
      'id_name' => 'inv_p_inventory_id_c',
      'ext2' => 'inv_p_inventory',
      'module' => 'inv_p_inventory',
      'rname' => 'name',
      'quicksearch' => 'enabled',
      'studio' => 'visible',
    ),
    'inv_p_inventory_eval_hw_request' =>
    array (
      'name' => 'inv_p_inventory_eval_hw_request',
      'type' => 'link',
      'relationship' => 'inv_p_inventory_eval_hw_request',
      'source' => 'non-db',
      'module' => 'inv_p_inventory',
      'bean_name' => 'inv_p_inventory',
      'side' => 'right',
      'vname' => 'LBL_INV_P_INVENTORY_EVAL_HW_REQUEST_FROM_INV_P_INVENTORY_TITLE',
    ),
  ),
  'relationships' =>
  array (
    'eval_hw_request_modified_user' =>
    array (
      'lhs_module' => 'Users',
      'lhs_table' => 'users',
      'lhs_key' => 'id',
      'rhs_module' => 'eval_hw_request',
      'rhs_table' => 'eval_hw_request',
      'rhs_key' => 'modified_user_id',
      'relationship_type' => 'one-to-many',
    ),
    'eval_hw_request_created_by' =>
    array (
      'lhs_module' => 'Users',
      'lhs_table' => 'users',
      'lhs_key' => 'id',
      'rhs_module' => 'eval_hw_request',
      'rhs_table' => 'eval_hw_request',
      'rhs_key' => 'created_by',
      'relationship_type' => 'one-to-many',
    ),
    'eval_hw_request_assigned_user' =>
    array (
      'lhs_module' => 'Users',
      'lhs_table' => 'users',
      'lhs_key' => 'id',
      'rhs_module' => 'eval_hw_request',
      'rhs_table' => 'eval_hw_request',
      'rhs_key' => 'assigned_user_id',
      'relationship_type' => 'one-to-many',
    ),
    'securitygroups_eval_hw_request' =>
    array (
      'lhs_module' => 'SecurityGroups',
      'lhs_table' => 'securitygroups',
      'lhs_key' => 'id',
      'rhs_module' => 'eval_hw_request',
      'rhs_table' => 'eval_hw_request',
      'rhs_key' => 'id',
      'relationship_type' => 'many-to-many',
      'join_table' => 'securitygroups_records',
      'join_key_lhs' => 'securitygroup_id',
      'join_key_rhs' => 'record_id',
      'relationship_role_column' => 'module',
      'relationship_role_column_value' => 'eval_hw_request',
    ),
  ),
  'optimistic_locking' => true,
  'unified_search' => true,
  'indices' =>
  array (
    'id' =>
    array (
      'name' => 'eval_hw_requestpk',
      'type' => 'primary',
      'fields' =>
      array (
        0 => 'id',
      ),
    ),
  ),

p.konetskiy · 17 December 2019 16:32

@sapling
No!
Please, show me fields of other module.

sapling · 17 December 2019 17:02

'serial_number' =>
    array (
      'required' => false,
      'name' => 'serial_number',
      'vname' => 'LBL_SERIAL_NUMBER',
      'type' => 'varchar',
      'massupdate' => 0,
      'no_default' => false,
      'comments' => '',
      'help' => '',
      'importable' => 'true',
      'duplicate_merge' => 'disabled',
      'duplicate_merge_dom_value' => '0',
      'audited' => false,
      'inline_edit' => true,
      'reportable' => true,
      'unified_search' => false,
      'merge_filter' => 'disabled',
      'len' => '255',
      'size' => '20',
    ),
    'inv_p_inventory_eval_hw_request' =>
    array (
      'name' => 'inv_p_inventory_eval_hw_request',
      'type' => 'link',
      'relationship' => 'inv_p_inventory_eval_hw_request',
      'source' => 'non-db',
      'module' => 'eval_hw_request',
      'bean_name' => 'eval_hw_request',
      'vname' => 'LBL_INV_P_INVENTORY_EVAL_HW_REQUEST_FROM_EVAL_HW_REQUEST_TITLE',
      'id_name' => 'inv_p_inventory_eval_hw_requesteval_hw_request_ida',
    ),
    'inv_p_inventory_eval_hw_request_name' =>
    array (
      'name' => 'inv_p_inventory_eval_hw_request_name',
      'type' => 'relate',
      'source' => 'non-db',
      'vname' => 'LBL_INV_P_INVENTORY_EVAL_HW_REQUEST_FROM_EVAL_HW_REQUEST_TITLE',
      'save' => true,
      'id_name' => 'inv_p_inventory_eval_hw_requesteval_hw_request_ida',
      'link' => 'inv_p_inventory_eval_hw_request',
      'table' => 'eval_hw_request',
      'module' => 'eval_hw_request',
      'rname' => 'name',
    ),
    'inv_p_inventory_eval_hw_requesteval_hw_request_ida' =>
    array (
      'name' => 'inv_p_inventory_eval_hw_requesteval_hw_request_ida',
      'type' => 'link',
      'relationship' => 'inv_p_inventory_eval_hw_request',
      'source' => 'non-db',
      'reportable' => false,
      'side' => 'right',
      'vname' => 'LBL_INV_P_INVENTORY_EVAL_HW_REQUEST_FROM_INV_P_INVENTORY_TITLE',
    ),
  ),
  'relationships' =>
  array (
    'inv_p_inventory_modified_user' =>
    array (
      'lhs_module' => 'Users',
      'lhs_table' => 'users',
      'lhs_key' => 'id',
      'rhs_module' => 'inv_p_inventory',
      'rhs_table' => 'inv_p_inventory',
      'rhs_key' => 'modified_user_id',
      'relationship_type' => 'one-to-many',
    ),
    'inv_p_inventory_created_by' =>
    array (
      'lhs_module' => 'Users',
      'lhs_table' => 'users',
      'lhs_key' => 'id',
      'rhs_module' => 'inv_p_inventory',
      'rhs_table' => 'inv_p_inventory',
      'rhs_key' => 'created_by',
      'relationship_type' => 'one-to-many',
    ),
    'inv_p_inventory_assigned_user' =>
    array (
      'lhs_module' => 'Users',
      'lhs_table' => 'users',
      'lhs_key' => 'id',
      'rhs_module' => 'inv_p_inventory',
      'rhs_table' => 'inv_p_inventory',
      'rhs_key' => 'assigned_user_id',
      'relationship_type' => 'one-to-many',
    ),
    'securitygroups_inv_p_inventory' =>
    array (
      'lhs_module' => 'SecurityGroups',
      'lhs_table' => 'securitygroups',
      'lhs_key' => 'id',
      'rhs_module' => 'inv_p_inventory',
      'rhs_table' => 'inv_p_inventory',
      'rhs_key' => 'id',
      'relationship_type' => 'many-to-many',
      'join_table' => 'securitygroups_records',
      'join_key_lhs' => 'securitygroup_id',
      'join_key_rhs' => 'record_id',
      'relationship_role_column' => 'module',
      'relationship_role_column_value' => 'inv_p_inventory',
    ),
  ),
  'optimistic_locking' => true,
  'unified_search' => true,
  'indices' =>
  array (
    'id' =>
    array (
      'name' => 'inv_p_inventorypk',
      'type' => 'primary',
      'fields' =>
      array (
        0 => 'id',
      ),
    ),
  ), "\

p.konetskiy · 17 December 2019 17:40

@sapling
You relationship work to another way. You can take serial_number from hw_request to p_inventory because relationship is hw_request -> one-to-many -> p_inventory. I recommend to use module builder, delete this relationship and add another relationship.

After that you can edit files: custom/modules/eval_hw_request/metadata/editviewdefs.php or/and custom/modules/eval_hw_request/metadata/quickcreatedefs.php
using the code:

...
                    'panels' => array (
                        ...
                        'default' => array (
                            ...
                            <number> => array (
                                <number> => array (
                                    'name' => 'eval_hw_request_inv_p_inventory_name',
                                    'displayParams' => array (
                                        'field_to_name_array'=> array(
                                            'name' => 'eval_hw_request_inv_p_inventory_name',
                                            'id' => 'eval_hw_requesteval_inv_p_inventory_p_inventory_ida',
                                            'serial_number' => 'serial_number',
                                        ),
                                    )
                                ),
                            ...
                            ),
                        ...
                        ),
                    ...
                    ),
...

sapling · 18 December 2019 10:42

So what should the relationship be then Many to One or Many to Many? And on each one should it be the same?

p.konetskiy · 18 December 2019 10:45

@sapling
Should be one-to-many but
You made:
hw_request -> one-to-many -> p_inventory
You do:
p_inventory -> one-to-many -> hw_request

sapling · 18 December 2019 11:02

Alright so that is all fixed up however after fixing the relationship and editing the viewdefs there is an issue.
The MYSQL cant find the field so its not displayed for me to select anything from.

Query Failed: SELECT inv_p_inventory_eval_hw_requesteval_hw_request_ida id FROM inv_p_inventory_eval_hw_request_c WHERE inv_p_inventory_eval_hw_request_c.inv_p_inventory_eval_hw_requestinv_p_inventory_idb = ‘7a111fc6-452a-3c7d-eab9-5defe2dd90d7’ AND inv_p_inventory_eval_hw_request_c.deleted=0: MySQL error 1054: Unknown column ‘inv_p_inventory_eval_hw_requesteval_hw_request_ida’ in ‘field list’

p.konetskiy · 18 December 2019 11:49

@sapling
You should remove old field from all forms of module “p_inventory”. You can do it in Module Builder or Studio.

sapling · 18 December 2019 12:13

Ah ok yeah that fixed that issue but, for whatever reason I cant see the field to add serial numbers its just the other default fields. I see it in the edit view in studio but not in the actual page.

p.konetskiy · 18 December 2019 12:30

@sapling
I don’t know. May be it help you:
Admin -> Repair -> Quick Repair and Rebuild
or
“Save & Deploy” for forms in Studio.