Idea : Formula Sandbox as field type for admin

[item]

Hi,
i was in a big problem, i have a law entity (law change each year).
the main (root) entity is meeting .. keep in mind all attendee for understand (user/contact/lead and custom Person entity)

law have somes "major" fields who are same with other record, like :
- duration
- profession of User
- graduate of User
- date begin - date end
- ...

and others fields who change on every lay record, like :
- contact age
- contact insurance and type
- contact attendee count
- leads attendee count
- persons attendee count
- meeting duration
- meeting place
- meeting parent and superParent
- ..

i have a front-end button just left on each contact as attendee.. who get :
- id of the clicked attendee contact
- id of the meeting

on back-end i have some selectBuilder for get all "avaible law for this contact and this meeting".. but the problem was, i can't make selectBuilder for all law record !!

Then the idea was : Formula Sandbox
i have create a "Rule" fields in law entity and make this :

        "rule": {
            "type": "text",
            "rowsMin": 2,
            "cutHeight": 400,
            "displayRawText": false,
            "isCustom": true,
            "view": "views/fields/formula"
        },​ 

and then, in rule field i write a formula : if .... then ... else see print-screen for somes sample

and then in back end :

        $targetLink = LinkParent::create( $meeting->getEntityType() , $meeting->getId() );
        $variable = $this->getScriptVariable( $user, $contactInsurance, $contact ,$meeting );   // there i get all required variable from different entity​
        foreach($lawList as $law){

            $expression = $law->get('rule') ?? null;
            if (!$expression || !is_string($expression)) {
                continue;
            }

            // MAGIC PLACE
            $result = $this->formulaService->run($variable .$expression, $targetLink)->toStdClass()

            if($result->output == 'true') array_push($this->findedNomenclatureList ,$law->get('nomenCode'));​
       } 

    private function getScriptVariable( Entity $user, Entity $contactInsurance, Entity $contact , Entity $meeting) : ?string
    {
        $dofbirth = new DateTime( $contact->get('dofbirth'));
        $dateOfMeeting = new Datetime( $meeting->get('date') );
        $ddiff = $dateOfMeeting->diff($dofbirth);
        $visio = ( $meeting->get('visio')) ? 1 : 0;
        $userIntern = ( $user->get('qualified') == 'Intern') ? 1 : 0;
        $place = $meeting->get('place');
        $contactType = $contact->get('type');
        $contactIntern = ( $contact->get('intern') == true) ? 1 : 0;
        $pass = ( $contactInsurance->get('type') == 'Pass') ? 1 : 0;
        $interpreter = ( $meeting->get('interpreter') ) ? 1 : 0;

        $age = $ddiff->y;
        $this->contactAge = $age;

        $prescription = $this->getPrescription($contact, $meeting);
        $scriptVariable = '$age=' ."{$age};";
        $scriptVariable .= '$prescription=' ."{$prescription};";
        $scriptVariable .= '$pass=' ."{$pass};";
        $scriptVariable .= '$visio=' ."{$visio};";
        $scriptVariable .= '$userIntern=' ."{$userIntern};";
        $scriptVariable .= '$place=' ."'{$place}';";
        $scriptVariable .= '$contactType=' ."'{$contactType}';";
        $scriptVariable .= '$contactIntern=' ."{$contactIntern};";
        $scriptVariable .= '$interpreter=' ."{$interpreter};";

        //$this->log->error($scriptVariable);

        return $scriptVariable;
    }​ 

So, in futur.. i have no to change code... just adapt content of rule field.. maybe somethime, i need to add new variable

Hope this idea can be utile for anybody.

Of course, all is dependant of Formula .. and EspoCrm

[espcrm]

I run away when I see long code. But thank you!

I have to change our formula each year as well, as the 'fee' change. Last year I just copy the old year, put it in a comment, update the current fee and call it a day.

Obviously there is an issue if I create/archive older year file, but that is for the future to think about....

[item]

Since 2023-01, i have for 227 law records... 3743 fees ! all fees is dependant of all params in post 1. "Heureusement", i pump the official site of governement, get xml and parse it each month..and "update or create new fee"..

For your case, it's very simple (if i understand?) :
create a custom entity :
- Period with 3 fields dbegin/dend/index = sample : 2024-01-01 - 2024-12-31 - 1
- for entity where is the fee, create a field feeBase = sample 1euro

so each year :
create a new record in Periode :
2025-01-01 - 2025-12-31 - 1.2

and by formula : you juste need find the Periode , get the "index" value and set your fee to : feeBase * index.
so in this case : fee = 1 * 1.2 => 1.2 augmentation of 20%

[espcrm]

Ah smart, you create another entity. That mean we can API it somehow to do data update.

I think about doing something similar at the end of the year. Unfortunately I still can't do API. And I'm not sure if our Government have API/XML for these information.

I usually just look at their website and grab the new % and $ from there.