Calculate a field based on a date and another field

Hello Arnaud Grisey ,

Let’s break your question down into specific requirements 

1. Requirement 1: When the date in the reception_c field is before 01/10/2024, the field should display 'NA'.

2. Requirement 2: When the type_c field is set to '11', the field should display 'YES'.

3. Requirement 3: If neither of these conditions are met, you would like the field to remain editable so users can manually select an option from the dropdown. (Any of the three).

Addressing Requirement 3

Requirement 3 is a bit challenging because if you set the field to be calculated in Studio, it will become read-only for users.

This prevents them from manually selecting a value from the dropdown, which does not align with your needs.

If you want to retain manual editing, instead of using a calculated field in Studio, you could leverage a SetValue formula via the Dependency Actions framework.
The key difference here is that dependencies need to be uploaded to your Sugar instance as part of a Module Loadable Package (MLP).

Proposed Solution

Below is a SetValue dependency code that I tested, and it seems to meet all three requirements.
Additionally, you could add a SetReadOnly condition if you want to lock the field when either Requirement 1 or 2 is met.

Here’s the code I used for testing, mind that utilisation_c is a dropdown filed with the 3 options.
I added the code on the Accounts module, and it's not possible to add a field named type (it's a reserved name), that's why my field name is type_c_c. 

<?php
//path custom/Extension/modules/Accounts/Ext/Dependencies/utilisation_c_dependency.php
$dependencies['Accounts']['utilisation_c_dependency'] = array(
    'hooks' => array('edit', 'view'), // Trigger for both edit and detail view
    'trigger' => 'true', // Always evaluate
    'triggerFields' => array('reception_c', 'type_c_c'), // Fields to watch
    'onload' => true, // Apply on page load
    'actions' => array(
        array(
            'name' => 'SetValue',
            'params' => array(
                'target' => 'utilisation_c', // Target field
                'value' => 'ifElse(isBefore($reception_c, date("2024-10-01")),"NA",ifElse(equal($type_c_c, "11"),"YES",$utilisation_c))'
            ),
        ),
    ),
);
?>

This is a video of how it will look like:






Please have a look and let me know if this is a good starting point to address your requirement: 

Cheers, 

André 

Hello Arnaud Grisey ,

Let’s break your question down into specific requirements 

1. Requirement 1: When the date in the reception_c field is before 01/10/2024, the field should display 'NA'.

2. Requirement 2: When the type_c field is set to '11', the field should display 'YES'.

3. Requirement 3: If neither of these conditions are met, you would like the field to remain editable so users can manually select an option from the dropdown. (Any of the three).

Addressing Requirement 3

Requirement 3 is a bit challenging because if you set the field to be calculated in Studio, it will become read-only for users.

This prevents them from manually selecting a value from the dropdown, which does not align with your needs.

If you want to retain manual editing, instead of using a calculated field in Studio, you could leverage a SetValue formula via the Dependency Actions framework.
The key difference here is that dependencies need to be uploaded to your Sugar instance as part of a Module Loadable Package (MLP).

Proposed Solution

Below is a SetValue dependency code that I tested, and it seems to meet all three requirements.
Additionally, you could add a SetReadOnly condition if you want to lock the field when either Requirement 1 or 2 is met.

Here’s the code I used for testing, mind that utilisation_c is a dropdown filed with the 3 options.
I added the code on the Accounts module, and it's not possible to add a field named type (it's a reserved name), that's why my field name is type_c_c. 

<?php
//path custom/Extension/modules/Accounts/Ext/Dependencies/utilisation_c_dependency.php
$dependencies['Accounts']['utilisation_c_dependency'] = array(
    'hooks' => array('edit', 'view'), // Trigger for both edit and detail view
    'trigger' => 'true', // Always evaluate
    'triggerFields' => array('reception_c', 'type_c_c'), // Fields to watch
    'onload' => true, // Apply on page load
    'actions' => array(
        array(
            'name' => 'SetValue',
            'params' => array(
                'target' => 'utilisation_c', // Target field
                'value' => 'ifElse(isBefore($reception_c, date("2024-10-01")),"NA",ifElse(equal($type_c_c, "11"),"YES",$utilisation_c))'
            ),
        ),
    ),
);
?>

This is a video of how it will look like:






Please have a look and let me know if this is a good starting point to address your requirement: 

Cheers, 

André