Multiply Currency field in PDF template (or in Calculated Formula if not in pdf)

Hello friends...
Any tips for this? 

I had this working as a test initially using a text field for the Value and multiply that by a text field that just contained one digit.  

Then, for "globalness" I made the Value field a currency field.  Then it wouldn't work.  I think it cannot multiply by a currency field?

______________________________

I want to do this in the PDF template:  example >  {$fields.name*$fields.description}

My actual fields are:  {$fields.s_serialnumber_r_rma_1.value_for_customs_c*$fields.numberofitems}

Value for Customs is actually a currency field with a formula to bring in the value from another currency field on our Product module.
(I have done edit and save on my records that I am testing with and the values are populated in the fields)

Number of items is a text field with mostly just a digit in there, mostly 1.  But I want to multiply the value by this for rare occasions when the number is more.

So, that didn't work when I started using the Currency Field so I thought I would bring the total value into a new field on the module required with a calculated formula...  maybe cleaner?  But using the PDF is rare (commercial export invoice) and we don't really need to have the fields calculated for every record - which is why my preference was for doing in inside the PDF - so that would only calculate it for the rare occasions we need it. 

Anyway, I then got stuck on how to write the multiply formula!  

multiply($numberofitems,related($s_serialnumber_r_rma_1,"value_for_customs_c"))

ERROR:   " multiply: All parameters must be of type 'number' "

Any tips to correct this?  thanks.

I would like to stick to the PDF option if poss.  So is there a way with Smarty modifiers to strip out the number from the currency, do the calculation, then display that result?  Maybe to add the users currency symbol or something?

Any advice welcome.  
Thanks in advance.  
Luke.

  • Any ideas anyone? :-(

    Is it not possible inside the PDF (manager/editor) to multiply a currency field?

    If I go the calculated formula route is my syntax OK... but needs something like sting to number?

    Thank you.

  • Hi ,

    Thanks for the info above, though I have to admit I am still stuck :-(

    This screenshot from my PDF:
     Note
     - 'Value' is bringing in a Currency Field, which I think is the issue?
     - I want to have it as a currency field in case it is used globally, not just UK.

    All I am trying to do is Value x Quantity = Total Value

    This is how am doing it:

    {assign var=val value=$fields.s_serialnumber_r_rma_1.value_for_customs_c}
    {assign var=totval value=$val*$fields.numberofitems}

    If I remove the variables it also fails to work:

    {$fields.s_serialnumber_r_rma_1.value_for_customs_c * $fields.numberofitems}

    Even adding the formatting it then just = o.oo

    Which is why I think the Currency Field is the cause.

    Plus, the same thing works fine elsewhere when not using a currency field:

    {assign var=kg value=$fields.s_serialnumber_r_rma_1.weight_c}  
    {assign var=totkg value=$fields.numberofitems*$kg}

    Commonly the weight is 7kg for example. If in a record I set number of items to 2 then my totkg variable returns 14.

    I think I may just need to do it with fields inside the module, and then bring that into the PDF...

    Would have just been nice for the PDF to do the work ;-)

  • Just to add to above - this will be needed only by the exception, rarely.. not the norm...  so having the PDF do the calculation would save some resource...  I assume.

  • Hi all,  just to add the reply from my case with sugar:

     This functionality is currently not available in the application.
     I have added your case to enhancement request #80518
     https://portal.sugarondemand.com/#supp_Bugs/80518

    ...goes on to add working with a partner..  But  I think easier is to do it with [formula] fields in sugar, then pull those into the PDF.
    The only reason I didn't want to do that is this is very rarely used, so doing it only when needed (in the PDF) seemed best, rather than for every record of which nearly all will never use it.

    Regards anyway,
    Luke.

  • btw, is SerialNumber to RMA relation is 1:1?




    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient

  • Hi Luke,

    jumping into discussion, I could suggest an option to remove a currency symbol and do the math in the PDF template itself:
    1: strip currency symbol from the field
    {assign var=val value=$fields.s_serialnumber_r_rma_1.value_for_customs_c|regex_replace:'/[^0-9,.]+/':''}
    2: mulitply variable above on 'Number of Items'
    {$val*$fields.numberofitems}

    It leaves you just with the number, without currency in the document, so I guess you would like to get the currency symbol back. I do not have a sophisticated solution here, but using {if $fields.currency_iso == 'USD'}${elseif...} should work:)


    hope this helps!

  • Thanks ,

    I used your |regex_replace:'/[^0-9,.]+/':'' and that stripped out all but the value enabling the calculation to work perfectly :-)   THANK YOU Pray

    So I now have one variable to just bring in the value.

    Another variable the same as that but with the regex replace for the calculation.

    To get the currency I wanted to avoid the if statement as we have a number of potential warehouses that could be used... So, I just added a warehouse currency symbol text field to that module, and now append that in front of the calculation.

    Thanks for you assistance with the regex.

    One question out of curiosity:  what would the regex look like to do the opposite and strip ourt everything except the currency symbol??   I tried all sorts with no luck so added the text field mentioned above.   But I was thinking I could have a another variable that stripped out all but the currency symbol.  There is also another currency field on our warehouse module which I tried to do this to...   but got stuck.

    Anyway - we have solution. Thank you again.

    ,
    I didn't want to have the field I mentioned also and you mentioned as it seemed to me to calculate something for every record where it will hardly ever be needed seems a waste of resource?   But thanks also for the additional info.

  • Sorry for not responding, I've been busy with other projects and have not checked the Community for a while. I am glad was able to give you a solution.

  • I'm glad it helpedRelaxed

    One question out of curiosity:  what would the regex look like to do the opposite and strip ourt everything except the currency symbol??

    I would go with smth like this (opposite to the expression above, where values except 0-9,. were removed):
    regex_replace:'/[0-9,.]+/':''