Help needed with Formula Script in Workflow

[andreschiffer]

Hello fellow Espo-Users,

we are using EspoCRM for a while now and we are very happy with it, especially regarding its flexibility. We also bought some Extensions and would like to do some further development and customization fitting our needs.

I try to do as much as possible by myself and read a lot here, which is so helpful!

Unfortunately i need some help with Formula Script / Workflows, maybe one of you might help.

Here's my challenge:

Initial Situation:
I have two entities "Meeting" and "Auftraege / Jobs". In every "Auftrag" we need several appointments with our client to fulfill the job. We use the Outlook-Extension, which is able to sync the Calendar Events to Espo (Entity Type: Meeting). Every "Auftrag" has a unique ID (i.e. "ABC-123456"), which is created upon creation of the new Auftrag-Entity. When we create an calendar event in Outlook, the unique ID of the corresponding "Auftrag" ist put in the title of the calendar event (i.e. "Appointment with Mr. X, ABC-123456"). A calendar-event can also have multiple Auftrag-ID's in it's title. This event is synced via the Outlook-Plugin to Espo Meeting-Entity.

I would like to do the following:
Create a workflow which is triggered, whenever a calendar event (Meeting-Entity) is created or changed.
The workflow should check the title of the event if there is any Auftrag-ID in it. For every Auftrag-ID it should find the corresponding "Auftrag" by ID and link it to the Meeting-entity.
If a calendar event title (Meeting Entity) has an Auftrag-ID in it and it is linked to an "Auftrag" it should be unlinked, if the Auftrag-ID is removed from the meeting-title.

I already tested a little bit, but honestly lack knowledge of how to write formula-script on such a complex level.

So, if anyone of you could help me out, i would be very happy!

Thank you in advance,
kind regards from Germany,

André

[rabii]

So the calendar is a custom entity which is linked to the job entity correct?

Here you mentioned name of the calendar but in previous comment you mentioned title ?

If a calendar event name has an Auftrag-ID in it and it is linked to an "Auftrag" it should be unlinked, if the Auftrag-ID is removed from the event-title.

[andreschiffer]

Hey rabil,
sorry for the confusion. I edited the post for clarification.

The calendar-event in Espo ist entity-type "Meeting". I mean the title, not the name.

[andreschiffer]

here is a screenshot of the meeting-entity, i marked one of the "IDs" in the name (which i describes as "title"), that i would like to look up for.

[esforim]

From reading this it require outlook extension for me to understand it. Personally I dont have this extension and can not make any comments in getting it to work.

Here is my food for thoughts if you want to adapt it in some way. Most of my "work" revolve around the entity call Case. From there I do everything.

I create a hidden field calling it (example only), "DueDateID", and "SaleDateID", why it "hidden" cause my user will get confuse seeing these random digits! So only Admin see it.

Anyway, after that I create another two Date field: DueDate and SaleDate.

Then I would use the formula (writing this on remember).

ifThen(DueDate=!null, dueDateID=record\create('meeting', name, 'Due Date - Filenumber 123', dateStart, 'DueDate', dateEnd, 'DueDate') )

I do the same for SaleDate. Now it will "automatic" create these 2 date for me. THat using the formula way... you got Workflow so it is much easier for you.

The next question is, how do I get all these "Meeting" on another device or software? Unfortunately there is no good free way to sync these information at the moment. I'm currently using a Paid 3rd extension call DAV to do that.

[andreschiffer]

Thank you for your thoughts!

Actually you can forget about the detail with the Outlook-Extension. It only does the sync/push of Calendar-Events into Espo-CRM in "Meeting"-Entity for me.

What i would like to achieve in Formula Script in the context of a Workflow is:

----

-> Workflow-Trigger: After change / update of entity type "Meeting" do following:

if ($name of the meeting-entity, contains at least one string like "SIB-XXXXXX") {
for each found string (
find the job-entity which has this string in $name;
link the found job-entity to the meeting-entity;
);

----

The best case would be, that there is also another routine:
if an updated meeting-entity is linked to a job, which ID is no more found in the meeting-entity $name,
unlink the job-entity from the meeting-entity

I hope you all can understand, what i'd like to achieve.

Of course i already tried myself in Formula Script, but unfortunately failed on the array-part of the code. I also searched the forum, but could not find any helpful bits :-(

So, i'd be very happy, if you could help me somehow :-)

[rabii]

for each found string (
find the job-entity which has this string in $name;
link the found job-entity to the meeting-entity;
);

this part will not work, as the relationship between the job and the meeting is a parent to child. therefore a meeting could be assigned only to a one job. Meaning if a meeting has 3 unique ids for different jobs e.g ABV-123, XDF-786 and POP-890 then the system should link to the first found job as it can't link one meeting to multiple jobs based on your current set up.

I hope this is clear. I will drop you code to achieve your goal.

[andreschiffer]

I hope this is clear.

understood! thank you for the clarification. Actually it will make things easier here. I will take it into account and we'll avoid to have multiple ID's in one meeting.

[esforim]

Personally I don't do unlinked, and I think you can probably use unrelated formula to do that? I haven't personally done it so I can't give any tips.

How I do mine is through updating the record and creating them. For example, sometimes my due date get change, so I want the meeting to be updated as well. I also, add in a description, "Due Date changed" when this happen. It not fully working with information but it for now and I'm happy with it. Here is my formula for your reference.

$meetingName = string\concatenate("Sale Date - ", caseName, " [", matterID,"]"); // sale date is just a text, caseName is my custom field, and matterID is custom too.

/// Sale Create Meeting Part
    ifThen (saleDateMeetingID==null && type=='Conveyancing' && saleDate!=null,
       saleDateMeetingID =
            record\create('Meeting', 'name', $meetingName, 'dateStart', settlementDate, 'parentType',
            entity\setAttribute('parentType', 'Case'), 'parentId', id,
            'assignedUserId', 'idofthe-user-goeshere', 'teamsIds', list('idofthe-team-goeshere'),
            'usersIds', list('1','i-also-assign-my-id-so-i-can-see-everything')
            ));
    record\update('Meeting', saleDateMeetingID, 'dateEnd', datetime\addMinutes(record\attribute('Meeting', saleDateMeetingID, 'dateStart'), 60));
    ifThen(entity\isAttributeChanged('saleDate'),
    record\update('Meeting', saleDateMeetingID, 'dateStart', saleDate, 'description', string\concatenate('Sale Date Has Change \n Previously: ', entity\attributeFetched('saleDate'))));​

Good luck and be sure to share.

[andreschiffer]

Thank you all for the help and hints :-)
I finally managed to make it work!

Here is my code:

$name = name;

//check, if meeting is linked. if yes -> unlink
ifThen(cAuftraegeId != null,
            cAuftraegeId = null;
        );
    
//find the position, where the string begins
$needle = string\pos($name, "SIB-");    

// if string is found, link to relating "auftrag"
ifThen($needle != false,
    $az1 = string\substring($name, $needle, 11);
    //output\printLine($needle);
    //output\printLine($az1);
    $auftrag = record\findOne('Auftraege', 'name', 'desc', 'name=', $az1);
    cAuftraegeId = $auftrag;
    //output\printLine(cAuftraegeId)
)​

[esforim]

Thank you for sharing!