Implement Picker, wich shows Accounts related to a parent record

Question

Hello all, 
 I would like to implement the following scenario: 
 There is module 1, which has an 1:n relationship with module A and also an 1:n with module B. Module A has a relationship to the accounts n:1, so several records from module A can be linked to one account. 
 Module 1 also has a 1:n relationship to module B. However, I would now like to implement a simple link in module B that represents a picker and lists only the accounts from module A that are also linked to the parent module 1. But there is a dropdown field in Module B where a value is a filter for selecting accounts. 
 I have attached a graphic that may explain a bit what we are trying to do. Do you have any experience on how we can implement such a "picker"? 
 As always, I welcome your feedback. 
 Kind regards. 
 Martin

Martin Neitzke · Answer

Hi Martin Neitzke 
 I believe that the project enum-api can do the job. 
 
 Cheers

Martin Neitzke · Answer

Hi Martin Neitzke 
 The project enum-api enables filtering complex data set with a different set of filters by implementing a custom endpoint. This endpoint will get arguments provided by the viewdefs. 
 That said, you just need to worry about which specific custom field type you need to load (enum-api, enum-parent-api, enum-mult-parent-api) in the viewdefs and how to implement the custom endpoint so it can filter the data accordingly. 
 
 According to your clear description I can suggest the following: 
 In the Module B you will implement on the account_id field the enum-parent-api custom field type and you are going to specify the Module 1 ID as the parent of the account_id field. 
 Your custom endpoint will load a SugarQuery which fetches all Accounts related to Module A which are under the Module 1 ID.

$query = new SugarQuery();
$query->from(BeanFactory::newBean('Module A'), ['alias' => 'a']);
$query->join('accounts', ['alias' => 'acc']);
$query->join('module1', ['alias' => 'm1']);
$query->select(['acc.id', 'acc.name']);
$query->where()->equals('m1.id', $args['m1_id']);

return $query->execute();
 
 This is it!