I learned something new today that comes with a word of warning.
Did you know that you can summarize a string with a SUM function? It is not possible without this little trick. I was working with a module that did just that. The way that the original developer must have accomplished this magical feat was to configure the SUMMARIZE function when the field was defined as a NUMBER, then ultimately reset the value back to a string. It works.
Here is the other thing that I learned that could affect you. If the incoming value is defined as a STRING and you simply cast it to a number you might receive a warning message like:
ConvError: Summarize (5): value: 12,345 stopped converting at a comma. It might be invalid.
ConvError: Select (6): value: 12,345 stopped converting at a comma. It might be invalid.
Question
I am getting a Conversion error when trying to summarize a number with a comma in it that also happens when converting in a select:
ConvError: Summarize (5): value: 12,345 stopped converting at a comma. It might be invalid.
ConvError: Select (6): value: 12,345 stopped converting at a comma. It might be invalid.
Why am I getting this error?
Answer
As explained by @MarqueeCrew in the discussion boards, this conversion error occurs because the number 12345 had a comma in it. The comma is considered a string value and if you have numeric values that might contain a comma, be careful. The sum of 10,500 and 12,750 returns as22.
Your best bet is to remove the commas in your string fields that you want to be numeric before attempting to convert to ensure you get the expected results. To do this, try using REGEX_Replace like so: REGEX_Replace([String Field],"\,","")
