Announcement

Collapse
No announcement yet.

formula Calculation Not Working

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

  • formula Calculation Not Working

    Hello All! Im trying to auto create a amazon url. For example I want https://amazon.com/dp/[[AMAZON ASIN]]. This would look something like https://www.amazon.com/dp/B07FQ24C67.

    However I can't seem to get it to concatenate.

    the field Im trying to store the value to is "url" and the ASIN is store in "vendorPartNo"


    I have tried the below with no luck .Can anyone get this to work somehow?

    url = string\concatenate('https://www.amazon.com/dp/',vendorPartNo,');

    url = string\concatenate('https://www.amazon.com/dp/',vendorPartNo,);

    url = string\concatenate('https://www.amazon.com/dp/',vendorPartNo);

  • #2
    Hi Dustin,
    url = string\concatenate('https://www.amazon.com/dp/',vendorPartNo); is correct. Provide a screenshot with all your formula. What type the 'vendorPartNo' field is? Investigate a log.

    Comment


    • #3
      Hey Maximus,

      Below is the requested information. Where I can view the log at?

      Screenshot of Formula: https://prnt.sc/r91hnc
      Field Type: https://prnt.sc/r91i2i



      Comment


      • #4
        Hi dodom2,

        Thank to your screenshot I just learn how you use this, greatly appreciate it.

        To return the favour I did some testing for you and it show that it work fine for me. I did the test on the demo.espocrm.com website. Here some screenshot for your reference:

        Maybe you can create another URL field and see if that work? Or maybe you typed in uri instead of url in the formula?


        Click image for larger version

Name:	Screenshot_2020-03-02 (29) Entity Manager.png
Views:	538
Size:	10.1 KB
ID:	56407
        Click image for larger version

Name:	Screenshot_2020-03-02 (29) Tracking 2.png
Views:	554
Size:	32.1 KB
ID:	56408
        Click image for larger version

Name:	Screenshot_2020-03-02 (29) Tracking 3.png
Views:	540
Size:	37.9 KB
ID:	56409
        Last edited by espcrm; 03-02-2020, 07:49 AM.

        Comment


        • espcrm
          espcrm commented
          Editing a comment
          UPDATE: Solution posted here: https://forum.espocrm.com/forum/gene...ng-concatenate

          Thanks to your idea, I will create a new field call "Search Engine", where I will concatenate a few common search field using the "%s" string formula.

          For example you can search DuckDuckGo using this type of URL, https://duckduckgo.com/?q=%s
          Where %s can be the Name (or any) field.

          Formula will be:

          searchEngine = string\concatenate(http://duckduckgo.com/?q=%s, name);
          searchEngine = string\concatenate(http://google.com/?q=%s, name)

          I also like how you use the term, "meta". I think I will use that something like, "metaContactFilename'
          ---

          Edit, just did a test and it will only use the last formula. Does anyone know how to create new line? \n doesn't work. For example:
          searchEngine = string\concatenate(http://duckduckgo.com/?q=%s, name);
          searchEngine = string\concatenate(http://searchengine2.com/?q=%s, name);

          Also tried
          searchEngine = string\concatenate(http://duckduckgo.com/?q=%s, name, \n, 'http://engine2.com', name');

          Secondly, does anyone know how to use "is blank" or "is empty", if it were Excel I would just use
          <>""

          I tried using which is what is use when it come to value (see: https://www.espocrm.com/documentatio...ation/formula/ )
          = 0

          But that doesn't work either.

          Here is a example code of what I'm planning to do, basically if this field = empty then do nothing, if there is any value then run the formula.

          ifThenElse(name = 0, '', searchEngine = string\concatenate('https://duckduckgo', name));
          Last edited by espcrm; 04-07-2020, 01:23 AM.

      • #5
        Hi Dustin,
        the log file should locate in the /data/logs directory. Before investigating it, reproduce the error again. The possible error description should appear at the end of the log file.
        Investigating your formula screenshot I noticed that:
        1. in the first line you are trying to calculate "costPrice*.30". I am not sure that it is correct definition and it will work.
        2. you missed the equal sign "=" at the 5-th line, right after 'metaTagKeywords'.
        These syntax errors may be a reason why the formula doesn't work.
        Please fix it and investigate the formula one more time for possible other syntax error. Try to test your formula one more time after fix.

        Comment


        • #6
          Thank you everyone for your help. The final formula was url = string\concatenate('https://www.amazon.com/dp/', vendorPartNo);

          It seems I wasnt putting a space between the comma and the vendorPartNo.

          Hope this helps someone in the future!

          Comment


          • #7
            Originally posted by dodom2 View Post
            Thank you everyone for your help. The final formula was url = string\concatenate('https://www.amazon.com/dp/', vendorPartNo);

            It seems I wasnt putting a space between the comma and the vendorPartNo.

            Hope this helps someone in the future!
            Hi did you figure out how to create new line? /n doesn't work

            Comment


            • #8
              Originally posted by ciryaj View Post

              Hi did you figure out how to create new line? /n doesn't work
              Still waiting for an answer, maybe it was a bad idea to write it as a comments: https://forum.espocrm.com/forum/gene...6412#post56412

              UPDATE: Just notice your solution here: https://forum.espocrm.com/forum/gene...ng-concatenate

              Comment


              • #9
                Hi all, trying to create this formula but I'm having no luck with it at this stage. I'm trying to use a "if = blank, then do this" type of formula.


                Temporary I'm using this formula as a workaround, I just type in "0" for it to activate. But I want it to be "Parent is selected then activate the formula, if Meeting is already filled with 'anything/something' then don't do anything"

                Code:
                ifThen(meetinglocationtestStreet<=0, meetinglocationtestStreet=parentName)
                In Excel it would be something like this.
                ifThen(meetinglocationtestStreet<=0, meetinglocationtestStreet=parentName)

                Code:
                =IF(meetinglocationtestStreet<>"","value if true", "value if false")
                How do I write the correct formula? I want to copy/paste the Account's address is selected, and if the Meeting Location field is blank.

                Comment


                • #10
                  You may be better off creating a new thread because I cant help here due to my skill. I'm sorry I couldnt be more help

                  Comment


                  • #11
                    Originally posted by espcrm View Post
                    Hi all, trying to create this formula but I'm having no luck with it at this stage. I'm trying to use a "if = blank, then do this" type of formula.


                    Temporary I'm using this formula as a workaround, I just type in "0" for it to activate. But I want it to be "Parent is selected then activate the formula, if Meeting is already filled with 'anything/something' then don't do anything"

                    Code:
                    ifThen(meetinglocationtestStreet<=0, meetinglocationtestStreet=parentName)
                    In Excel it would be something like this.
                    ifThen(meetinglocationtestStreet<=0, meetinglocationtestStreet=parentName)

                    Code:
                    =IF(meetinglocationtestStreet<>"","value if true", "value if false")
                    How do I write the correct formula? I want to copy/paste the Account's address is selected, and if the Meeting Location field is blank.
                    Hi espcrm,
                    Did you find a decision? If not, then I hope it will help you. As you may know, there is a relationship between Account and Meeting despite the possible parent field relation. So let's use it to achieve what you need. Use this formula:
                    Code:
                    ifThen(
                        entity\isAttributeChanged('parentId') && parentType == 'Account' && !meetinglocationtestStreet,
                        accountId = parentId;
                        meetinglocationtestStreet = account.billingAddressStreet
                    )

                    Comment


                    • #12
                      Maximus Thank you very much. Didn't really want to create another thread because it feel like spam and more difficulties for future people to find.

                      I will give that code a try. Also came across this thread here: https://forum.espocrm.com/forum/gene...rinting-to-pdf
                      Which I believe will also give similar result, but need time to explore your Formula and yuri Formula.

                      Thanks and Liked

                      Comment


                      • #13
                        Originally posted by Maximus View Post
                        Code:
                        
                        
                        Look like the code is working well (also I think it is case sensitive so whoever use the code above make sure you the field name is correct and case sensitive. I got a second question, how should I attach more field it in? Should I use a new ifThen code or is there a way to continue the script with ; or ,? For example below:

                        Code:
                        ifThen(
                        entity\isAttributeChanged('parentId') && parentType == 'Account' && !meetinglocationtestStreet,
                        accountId = parentId;
                        meetinglocationtestStreet = account.billingAddressStreet;
                        meetinglocationtestCity = account.billingAddressCity;
                        meetinglocationtestState = account.billingAddressState
                        )
                        Here is my final code if anyone want to just copy and paste. Make sure your "Address" field name is meetingLocation or update the field name as required.

                        Code:
                        ifThen(
                        entity\isAttributeChanged('parentId') && parentType == 'Account' && !meetingLocationStreet,
                        accountId = parentId;
                        meetingLocationStreet = account.billingAddressStreet;
                        meetingLocationCity = account.billingAddressCity;
                        meetingLocationState = account.billingAddressState;
                        meetingLocationPostalCode = account.billingAddressPostalCode;
                        meetingLocationCountry = account.billingAddressCountry;
                        )
                        Last edited by espcrm; 05-22-2020, 08:17 AM.

                        Comment


                        • espcrm
                          espcrm commented
                          Editing a comment
                          OK, I test the above code and the ; is perfect and it working. No need to create a then ifThen code.
                      Working...
                      X