sugar logic function--text clean

Hello there,

anybody please help with the text clean?

I want to replace several characters inside a number: " ", "-",".", etc. via Studio-->module--->fields---->edit formula(using logic function).

But it seems that I can only replace one character by one time...

The I come across with a question:

what if there're many other characters I want to remove, I just want to keep numbers, then how I can do that?

Any list of sugar logic function formula that I can refer to? I couldn't find one...

Thanks in advance for any help!

  • one more question please: how can I do strReplace(")","",$field,false) without error?

  • Hello

    What will you do with two fields in CRM - the original text and calculated value?
    To clear out 30K contacts phones (eliminate all the symbols but numbers) you may do the following:

    1. Goto Reports and create new Raws&Columns report for module Contacts
    2. Select fields ID and all the phones fields you like to clear out from unnecessary symbols
    like this:

    3. Save and Run the Report

    You should get the list of all 30K contacts with phones

    4.Select Export in the menu

    You should get a Report.csv file downloaded.

    5. Open the Report.csv file in Excel and use Text to Columns to split the text into ID and phones columns

    6. Remove unnecessary symbols with Replace

    7. When done with formatting phones in Excel, save file as csv

    8. Navigate Sugar, and perform Contacts import

    9. When Import is performed, you should have phones cleared from unnecessary symbols in Sugar

    After the data is cleared out, I'd suggest deploying a validation configuration, that my teammate Anastasia has configured for you with Logic Builder: 

    That might help to prevent cleanings in future

    I hope this helps
    If you have any questions or need help, please feel free to drop me a message

    Best Regards,
    Dmytro Chupylka
    We make work in Sugar CRM system faster, more convenient and efficient

  • Hi Dmytro,

    Thank you so much!

    Yes, this could be one way.

    Currently, I'm creating a new column via Studio and execute logic function to strReplace ",", "-", ".", ")" and space.

    My original demand is:

    using API filter (filter by phone number which has a fixed format: 0405XXXXXX)  and get the related Contacts.

    However, the phone number in the Contacts module has different format (it's very dirty: it contains , . - ? $ and even letters...)

    I don't want to create a new endpoint which could be slow for filtering.

    So I followed Andre's suggestion to create a new field.

    Now, I come across a new problem: the phone number in sugarCRM contains many 'dirty' letters...

    I want to find a way to clean it nicely instead of enumerating all possible characters using strReplace.

    Could you please help?

    Thank you very much!

  • It should be uneasy to put all the characters to replace
    abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ!"#$%&\'()*+,-./:;<=>?@[\\] ),
      into the Sugar logic formula to recalc on the fly
    And even if this is possible, this replaces undoubtfully will increase the cost of calculation in times, degrading system each time calculation is performed.

    In addition, the calculated field should be calculated at least once for all 30K - with mass recalculation.
    It also will recalculate each time the phone is edited.

    Therefore I have doubts if the calculations on the fly could work in case of clearing data is more complex than just removing the dash or space.

    Best Regards,
    Dmytro Chupylka
    We make work in Sugar CRM system faster, more convenient and efficient