Announcement

**esforim** · 05-04-2023, 06:17 AM

Hi all,

I think I'm finally on the basic level of formula now. Here one for you to get an idea if you want to implement it yourself. From here I will create many more formula for other thing. What this formula do?

1) Create a related Meeting and Due Date
2) If the date change I want the date in the Meeting to be change too
3) I also want to add a Note so say that it was change. This along with Audit field allow admin to keep track
4) I assigned my Assigned User to a new user with the id, this user I call it "CRM System" this way I know it was create by my EspoCRM Formula rather than a manual edit from me or someone else.
5) I think it still a few bug need to get sort, Error 500 but generally it should work.
6) I only show 1 formula but it pretty much the same, just change as you need it to.
7) You will need at least 2 new field, or use existing one if you have them.

Code:

// Start of Finance meeting creation
$financeNAME = string\concatenate(ifThenElse(contractDate==financeDate, "CONTRACT IS UNCONDITIONAL - ", '\n', "Finance Due Date - "), caseName);

/// Finance Part
ifThen (financeDateMeetingID==null && type=='Property', financeDateMeetingID = record\create('Meeting', 'name', $financeNAME, 'dateStart', financeDate, 'parentType', entity\setAttribute('parentType', 'Case'), 'parentId', id, 'assignedUserId', 'id_of_the_assigned_user_go here'));
ifThen(entity\isAttributeChanged('financeDate'), record\update('Meeting', financeDateMeetingID, 'dateStart', financeDate, 'description', "Finance Date Has Change"));

// END of Finance meeting creation

How it look:

**esforim** · 05-04-2023, 07:14 AM

Hi all,

Another formula for the road. It purpose is to auto fill missing or unknown Postal Code!

It should work for all entity I think... one can only hope as I use it and test it. Anyway here it is:

1) Require: Real Estate extension or create one or own. It free so just use it!
2) For the field Type, I use a new Option call "City" so it only filter the city instead of searching for bunch of address.
3) All field name should be default.

Code:

ifThen(addressPostalCode==null, // if the Post Code is blank (null)
addressPostalCode= record\attribute('RealEstateProperty', // then write Post code for me by finding the "data" (attribute) from my RealEstateProperty entity name
record\findOne('RealEstateProperty', 'addressPostalCode', 'asc', 'type=', 'City', 'addressCity=', addressCity),  // Search for it by using findOne and it will return an ID, search it by find the type to be City only, and the City name is the same.
'addressPostalCode') //  write in data in my field postcode.

**esforim** · 05-05-2023, 07:32 AM

Hi all,

Do you use Sandbox Formula? Do you get an error?! After playing with it enough I found one something, here is learning below, hopefully the Helper is more clearer in the future... so I'm too ignorant.

Well one trick to using the formula is like this below, you can't put an outputprint at the start and end.

Let For example:

You want to run this code:

Code:

if (fromAddress=="abc@email.com"){
name
}

Then you want to see the output of it.

Code:

output\print(
if (fromAddress=="abc@email.com"){
name
}
)

You will get an error! You need to do it like this:

Code:

if (fromAddress=="abc@email.com"){
output\print(
name
)
}

And if you want variable, it outside as well.

Code:

$coolAttribute = string\concatenate(name, name) ;

if (fromAddress=="abc@email.com"){
output\print(
$coolAttribute
)
}

PS: I add paragraph so it easier to read/see in the code.

**esforim** · 05-18-2023, 07:39 AM

So, now with v7.4 feature where enum field filter down as you type it make sense to add all data!

For the Address's Autocomplete for City, I finally got around to add all the relevant city in my country. And my Firefox "froze" after I open the setting.

Naturally I found a online list listing all the City name, and I just need to add the JSON code to do the rest.

Anyway here the result and copy paste idea if anyone want to do the same.

1) Get a list of your country/state's City name, preferably in a single line format/CSV/Excel. Here how mine one look:

2) Now I create formula

Number 273 is starting number for me as I previously manually add 272 city already. Name is just city name. TextAll is the JSON code I need to add to the following file config.php. Here how I code it:

Code:

=CONCATENATE("    ",[@Number]," => '",[@Name],"',")

File location is here:
\app\data\config.php

Search for the code and add the above.

Make sure there is no , comma at the last city

**esforim** · 05-29-2023, 08:05 AM

Hi all,

If you read this you get another tip! Whether it useful or not I don't see feedback so I don't know.

Recently discover the Layout option for "Width" in px instead of %. Using width is much better for layout especially when it come to Mobile phone view. If you use % it tiny and get cut off. Whereas if you use px you can a much better view in my opinion.

I don't when this was add but I discover it by accident when I was playing with layout:

**esforim** · 05-29-2023, 08:07 AM

Also recently discover this Microsoft software: PowerToy: https://github.com/microsoft/PowerToys

One of the good feature I like and been using is this:

Text Extraction (OCR)

And Ruler which I'm using to px measurement in the above post.

Here is some other feature this software have:

**esforim** · 06-02-2023, 08:53 AM

Finally got around to follow one of the big Boss tutorial for Global Filter: https://www.youtube.com/watch?v=BYllS-6_xdE

Here is some of my result if anyone want to copy/paste or use as reference.

--- To Create Global Filter & Search. Edit the following file:

/custom/Espo/Custom/Classes/Select/CaseObj/PrimaryFilters/*.php
/custom/Espo/Custom/Resources/metadata/selectDefs/*.json
/custom/Espo/Custom/Resources/metadata/clientDefs/*.json

For the php file: AccountRealEstate.php

Code:

<?php

namespace Espo\Custom\Classes\Select\CaseObj\PrimaryFilters;

use Espo\Core\Select\Primary\Filter;
use Espo\ORM\Query\SelectBuilder;

class AccountRealEstate implements Filter
{
public function apply(SelectBuilder $queryBuilder): void
{
$queryBuilder->where([
'industry=' => ['Real Estate']
]);
}
}

For the selectDef Json: Account.json

Code:

{
"primaryFilterClassNameMap": {
"Legal": "Espo\\Custom\\Classes\\Select\\CaseObj\\PrimaryFilters\\AccountLegal",
"Real Estate": "Espo\\Custom\\Classes\\Select\\CaseObj\\PrimaryFilters\\AccountRealEstate"
}
}

for the clientDef json: Account.json

Code:

"kanbanViewMode": false,
"color": "#edc755",
"iconClass": "fas fa-building",
"filterList": [
{
"name":"Legal",
"style":"primary"
},
{
"name":"Real Estate",
"style": "success"
}
],

I probably create more filter now that I get the basic of it.

PS: I skipped his tutorial part for translation as I only use en_US, you might need to add it too maybe if you use other language.

**esforim** · 06-08-2023, 05:14 AM

CalDAV CardDAV is finally here: https://docs.dubas.pro/extensions/dav/carddav/

Time to test it:

**esforim** · 06-15-2023, 06:23 AM

Just a quick new formula, this one should work for default instant. Feel free to copy and paste.

What does it do? Whenever you create a Task from a Case entity, it will copy the Team information assigned in the Case over to the Task. This way, the team will have access to this Task. Previously I keep having to manual add it.

Furthermore I got smarter and add the ; at the start of the code comments, this way if you have other formula, it still work. But if this is the first Formula then be sure to remove it!

Code:

;/// CODE GUI 1002 - Auto-add Team Grouping when create Task from Case. Need Parent
ifThen(
parentType=='Case' && parentId!=null && teamsId==null,
teamsIds = record\attribute(parentType, parentId, 'teamsIds')
)
////////////////// CODE END

parentType need to be case, I want to restrict this to Case entity but you can do anything you want for other entity.
But I also want to make sure only to do this when the parent is NOT empty !=null
And that no other team is assigned to that: teamsId==null

Then I just link it by finding the ID using the record\attribute.

**esforim** · 06-15-2023, 03:52 PM

A quick formula for the road, hopefully you understand formula now and can understand what is being achieved here. Now with fancy comments.

Code:

;//////////////////////////////////////////////////////////////////////////
/// CODE START 1003 - Change Status to need more work after Manager/Management QA
ifThen(
statusQA=='Minor Defect' && status=='Completed' || statusQA=='Major Defect' && status=='Completed',
status='Require Update'; statusQA='Re-Check'
)
////////////////////////////// CODE END 1003 //////////////////////////////

**esforim** · 06-16-2023, 06:06 AM

Formula Helper! With v7.5 there is a new formula that can retrieve all data of an record. Very useful for formula creation and debugging, previously I need to go back and forth with field name, and checking current data.

Now you can just use something like this:

record\fetch(ENTITY_TYPE, ID)

Here an example in the Sandbox

Code:

output\printLine(
record\fetch('Contact', '123123-ID-number-here')
)

**esforim** · 06-16-2023, 08:24 AM

I accidentally delete the post so kinda lazy to re-write it now!

In summary:
It create a recurring task like system. It semi-auto create the next record for you.
Special thank to item for secret formula.

Nothing advanced but the code is hopefully readable and can easily understand what I'm trying to do.
Formula use many custom field so you need to edit for your usage.

Very little information is filtered out in this screenshot.

Result:

Code in screenshot format as copy/paste in here lose the "tab" spacing of formula so harder to read.

And finally the formula code:

Code:

;//////////////////////////////////////////////////////////////////////////
/// CODE START 5001 - Create a new schedule for the next week.
ifThen
(entity\attributeFetched('status')=='Schedule', // Check Attribute and if it equal to
ifThen
(status=='Paid' && datePaid!=null, // Only the next action if the following is true.
record\create
(
'Accounting', // Entity type
'status','Schedule', // Created Record Fieldname and Value
'name', name,
'casesIds', casesIds,
'description', string\concatenate
(
'[', 'Previous Payment record \nDate Paid: ', datePaid,
'\nPayment Amount: $', debit,
'](#/Accounting/view/', id, ')'
),
'date', datetime\addDays(date, 7), // Create a new field in the future so schedule can be varied. Example: Weekly will input "7"
'debit', debit,
'method', method,
'category', category,
'assetsAccountToIds', assetsAccountToIds,
'assetsIds', assetsIds,
'contactsIds', contactsIds,
'assignedUserId', assignedUserId,
'notes', '[This record was auto-generate by (MY ROBOT NAME HERE). Data is prefilled from existing record, confirm and update any changes required.]'
)
)
)
////////////////////////////// CODE END 5001 //////////////////////////////

**esforim** · 06-16-2023, 12:17 PM

Out of nowhere come more work! A free extension from Mr Kharg.

Now I have to create some wallpaper

GitHub - Kharg/custom-login: Custom Login Background Extension for EspoCRM

https://github.com/Kharg/custom-login

Custom Login Background Extension for EspoCRM. Contribute to Kharg/custom-login development by creating an account on GitHub.

**esforim** · 07-10-2023, 03:33 PM

Just saw Milestone is now v8... anyone want to guess (or want) the Major feature that will be added for v8?

Version 8.0.0 Milestone · espocrm/espocrm

https://github.com/espocrm/espocrm/milestone/167?closed=1

EspoCRM – Open Source CRM Application. Contribute to espocrm/espocrm development by creating an account on GitHub.

**esforim** · 07-15-2023, 04:43 AM

Finally got the DAV (extension) to work. I'll document some troubleshooting here for anyone that have same issue as me. Will post further as I go along. I think the latest version fix some of other error (e.g. DAV not found, error connection, etc). But this error is User/Admin fault, e.g you forgot to do something.

Anyway one of the error is this, I user Thunberbird to test it, it easy to test on PC than using Phone (DAVx5).

If you forgot to give "External Account" access, it will show up like this:

If you did it correct, it should show the username. For example.

To fix, make sure to enable this correctly.

Now after I finish this, try again:

Announcement

Learning EspoCRM and Design

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment