Announcement

Collapse
No announcement yet.

Learning EspoCRM and Design

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    Click image for larger version  Name:	image.png Views:	0 Size:	5.9 KB ID:	91828
    Click image for larger version  Name:	image.png Views:	0 Size:	6.1 KB ID:	91829
    Click image for larger version  Name:	image.png Views:	0 Size:	6.0 KB ID:	91830
    Last edited by esforim; 05-04-2023, 06:24 AM.

    Comment


    • 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.

      Comment


      • 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.

        Comment


        • 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:

          Click image for larger version  Name:	image.png Views:	0 Size:	1.5 KB ID:	92603

          2) Now I create formula

          Click image for larger version  Name:	image.png Views:	0 Size:	5.5 KB ID:	92604
          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],"',")
          Click image for larger version  Name:	image.png Views:	0 Size:	7.2 KB ID:	92607

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

          Search for the code and add the above.
          Click image for larger version  Name:	image.png Views:	0 Size:	1.3 KB ID:	92605
          Make sure there is no , comma at the last city
          Click image for larger version  Name:	image.png Views:	0 Size:	1.5 KB ID:	92606

          Comment


          • 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:

            Click image for larger version

Name:	image.png
Views:	485
Size:	13.6 KB
ID:	92975

            Comment


            • 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)
              Click image for larger version

Name:	image.png
Views:	481
Size:	18.9 KB
ID:	92978

              And Ruler which I'm using to px measurement in the above post.
              Click image for larger version

Name:	image.png
Views:	465
Size:	14.5 KB
ID:	92979

              Here is some other feature this software have:

              Click image for larger version

Name:	image.png
Views:	457
Size:	28.5 KB
ID:	92980

              Comment


              • 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.

                Comment


                • esforim
                  esforim commented
                  Editing a comment
                  If you want to search for "Or" you can do the following:

                  'industry=' => ['Real Estate', 'Legal']

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

                Time to test it:

                Click image for larger version  Name:	image.png Views:	0 Size:	31.3 KB ID:	93439
                Last edited by esforim; 06-08-2023, 05:32 AM.

                Comment


                • 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.

                  Comment


                  • 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 //////////////////////////////​

                    Comment


                    • 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')
                      )​

                      Comment


                      • 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:
                        Click image for larger version

Name:	image.png
Views:	619
Size:	32.2 KB
ID:	93955

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

Name:	image.png
Views:	410
Size:	60.6 KB
ID:	93956

                        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 //////////////////////////////​

                        Comment


                        • esforim
                          esforim commented
                          Editing a comment
                          You can optimize it like so: https://forum.espocrm.com/forum/gene...3971#post93971

                        • item
                          item commented
                          Editing a comment
                          Hi chatEspCrm

                          can you explain me why is plural "notes" ?. i am just curious, all field is not plural out-of-box and when we create, i alway "singular?"

                          'notes', '[This record was auto-generate by (MY ROBOT NAME HERE). Data is prefilled from existing record, confirm and update any changes required.]'

                          i hope robot respond

                          it's a field ? varchar ?

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

                        Now I have to create some wallpaper

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

                        Comment


                        • esforim
                          esforim commented
                          Editing a comment
                          > Keep in mind that it doesn't work well with Dubas themes

                          YOU ARE WRONG! I'm using it and it good.

                          > Haha espcrm, new avatar .. are you chatEspCrm IA ? 🤣

                          Please don't tease her! I want to start trying out AI soon.

                        • Kharg
                          Kharg commented
                          Editing a comment
                          I had issue with portal login using dubas themes, the page would not load, I think it happens only with both themes installed.

                        • esforim
                          esforim commented
                          Editing a comment
                          If it portal you might be correct. I don't use portal so never know.

                          Maybe in the future I will unlock and start using it.

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

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

                        Comment


                        • yuri
                          yuri commented
                          Editing a comment
                          It's already there. The frontend is quite overhauled, some deprecation cleanups. A case for a major version number.

                        • esforim
                          esforim commented
                          Editing a comment
                          Seem to be backend related for the frontend? I can't tell any difference in the demo version. Maybe it still using 7.5.5 on demo.

                      • 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:
                        Click image for larger version

Name:	image.png
Views:	381
Size:	5.6 KB
ID:	95423

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

                        To fix, make sure to enable this correctly.
                        Click image for larger version

Name:	image.png
Views:	351
Size:	30.7 KB
ID:	95424

                        Now after I finish this, try again:
                        Click image for larger version

Name:	image.png
Views:	335
Size:	38.5 KB
ID:	95425

                        Comment

                        Working...
                        X