Fetch a certain link from a related entity by formula

[shalmaxb]

Hello,
is it possible to fetch a certain record from a n:1 relationship? I would like to display the last added record (link) to another field in the related entity. See screenshot.

[rabii]

you can use formula to do it based on some condition you can set up the link field something like record\findRelatedOne()

Though the tricky part if how to get the last one because you can still use orderBy and order but it will be applied to the whole list. you can try modifiedAt => desc below is some code played with in sandbox mode

$contactId = record\findRelatedOne('Account', id, 'contacts', 'modifiedAt', 'desc');

$o = record\fetch('Contact', $contactId);

if ($o) {
    $name = object\get($o, 'name');
}

output\printLine($name);​ 

[shalmaxb]

rabii, thank you. I will try to set a field, which would provide a condition, on which the record will be determined.

[rabii]

so you have two option to use formula on the main entity or use formula on the related entity (probably this would be better) to demonstrate let us use account opportunities we know that one account has many opportunities (supposedly we have also a link called opportunity 1:1 between the two entities) then i would use this code on the opportunity formula script:

if (entity\isAttributeChanged('accountId') && accountId != null){
    record\update('Account', accountId, 'opportunityId', id);
}​ 

I think something like this would work hence everytime an entity is related to the main entity it would update the link (i assume the link you have on the main is the for the same related entity).

[shalmaxb]

rabii, this all helped to my solution, though I did it a little bit different. It was a bit tricky actually, as I had to find first, which link, which entity etc. to set where in the formula.
I did it with the formula record/findRelatedOne and record/attribute.

First I created a new field to display the id of the found related record, which I then used to refer the field, that I needed to hand over to the parent entity. It now displays the value of the lastly createdAt record of the linked list items(as configured in order and orderBy, and as I showed in the screenshot).
There is only one little problem, which I will try to solve later: I always want to display the status of the chronolgical last record and so I cannot place a record before that last one, as this then will be the last createdAt in that moment, which means, the chronological order is not correct anymore.

[rabii]

Cool to hear you find a way to do it.

i didn't understand what you meant by ( I always want to display the status of the chronolgical last record and so I cannot place a record before that last one, as this then will be the last createdAt in that moment, which means, the chronological order is not correct anymore.)

[shalmaxb]

It is quite complicated in my app.

You see in the screenshot, that the list of "stations", where one piece of artwork may be temporarily located, is in a chronological order, the first record is the first location, then the work is sold and the next record is the second location and so on.
As it works now, the second record will be taken to hand over the status to the parent entity (order modiefiedAt, desc). As long as I create the records/locations in chronological order all is fine.
In artist`s catalogues, which are often used for scientific reasons, it may occur, that in a given chronological order it will happen, that later someone finds an additional time range, when the work was in another location formerly unknown. So it must be possible to insert that newly found time range and location.
But then this record would be the last one modyfiedAt, even if it had been placed, let`s say between the first and third location and two or three more following.
So I still need a solution, to keep the chronological last work to be the last modifiedAt record. Meanwhile I make in these kind of cases an edit in the chronological last record, by which the modifiedAt will be updated and make this record the most recent one.

[rabii]

i see maybe you can define a specific order on the relationship (if i understand correctly what you need) otherwise a workflow would help, especially if you add a datetime that capture some trigger like when modified + some other field / condition. check this out https://docs.espocrm.com/development...tionshippanels

[shalmaxb]

yes, that might be possible. I will see. I already have a mandatory field number (it is INT) for every record, so I could use this instead of modyfiedAt and set this in the parameters in orderBy.

[shalmaxb]

ok, solved. I created the number field for the linked record. Every record gets a number, which indicates the order. Then finding the last recordId with order=number, orderBy=desc, I get the last record and can reference other fields with formula record/attribute by the recordId.
Thanks again rabii

[rabii]

Glad you got it to work.

Thanks