formula Calculation Not Working

[dodom2]

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);

[Maximus]

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.

[dodom2]

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

[espcrm]

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?

[espcrm]

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));

[Maximus]

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.

[dodom2]

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!

[ciryaj]

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

[espcrm]

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

[espcrm]

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"

ifThen(meetinglocationtestStreet<=0, meetinglocationtestStreet=parentName)

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

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

[dodom2]

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

[Maximus]

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"

ifThen(meetinglocationtestStreet<=0, meetinglocationtestStreet=parentName)

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

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

ifThen(
    entity\isAttributeChanged('parentId') && parentType == 'Account' && !meetinglocationtestStreet,
    accountId = parentId;
    meetinglocationtestStreet = account.billingAddressStreet
)

[espcrm]

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

[espcrm]

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:

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.

ifThen(
entity\isAttributeChanged('parentId') && parentType == 'Account' && !meetingLocationStreet,
accountId = parentId;
meetingLocationStreet = account.billingAddressStreet;
meetingLocationCity = account.billingAddressCity;
meetingLocationState = account.billingAddressState;
meetingLocationPostalCode = account.billingAddressPostalCode;
meetingLocationCountry = account.billingAddressCountry;
)

[espcrm]

OK, I test the above code and the ; is perfect and it working. No need to create a then ifThen code.