asked on February 2, 2023
I have a numeric value that is saved in a text field and I want to use it in a calculated value.
I didn't found any string to number conversion and if I try to direct calculate using the text field it shows me an error: "The aggregation 'sum' in the 'Test' formula is not valid."
⋅ February 3, 2023
Hello, Rafael!
Thank you for reaching out to us.
Kindly note that it is possible to set the field type as number using mapping as follows:
report: {
dataSource: {
//other connection properties
mapping: {
"YourField": {
type: "number"
}
}
},
//other report properties
}
We have prepared a JSFiddle for reference: https://jsfiddle.net/flexmonster/ze3h2kyg/.
In the provided example, the 'PriceString' string field is converted to number using mapping and then added to the calculated value formula.
You can read more about mapping in our docs: https://www.flexmonster.com/doc/mapping/.
Hope you will find our answer helpful.
Feel free to contact us in case of any other questions.
Regards,
Solomiia
⋅ February 7, 2023
Hi Solomiia!
Thanks so much for your reply!
Is it possible for the end user, not having access to edit the js code used to load the data, to use a number stored in a text field in a calculated field?
Best regards,
Rafael
⋅ February 9, 2023
Hello, Rafael!
Thank you for your reply.
To understand better, we want to explain how the types are automatically resolved in Flexmonster. Upon receiving the data, Flexmonster checks the data types based on values in the first row. Speaking of numbers stored in text fields, Flexmonster will recognize the text as a number if the value matches one of these formats: "10", "-10", "+10", "10.1", "-10.1", "+10.1". At the same time, it is possible to set the data types explicitly via mapping: the data types set inside the MappingObject override the automatically defined types.
With Flexmonster's API, it is possible to implement a custom type resolution logic or the necessary controls to set types for fields manually. Could you please tell us which data source you are using and provide more information about the data structure? What is the number format used in the dataset? Are null values present? This information would help us to provide a better solution.
Looking forward to hearing from you.
Best Regards,
Maksym
⋅ February 21, 2023
Hello, Rafael!
Hope you are doing well.
We are wondering if you had time to read our reply. Our team is ready to provide further guidance, but we would like to have more information about the data structure.
Looking forward to hearing from you.
Best Regards,
Maksym
⋅ February 22, 2023
Hi Maksym
Thanks for the answer and sorry for the late reply.
I was looking for a solution for the client side for a field mapped as string, but I found it may not be the right way to solve my problem.
I couldn't make it work because the records have mixed values in this field. They could have full text values, only number values (integer, decimal) and also null values. I can't map it to only numbers and also cant filter records with only numbers.
As I don't have a way to check if the value in the field is a number, like an Excel formula:
if(isnumber("string_field"), sum("string_field"), 0)
I might have to find another solution, probably changing the way this data is saved in the datasource.
⋅ February 23, 2023
Hello, Rafael!
Thank you for sharing this information with us.
Please note that Flexmonster does not support working with multitype fields. Hence, changing the data structure to avoid multitype fields would be the best solution.
Feel free to contact us if any questions arise.
Best Regards,
Maksym
⋅ February 23, 2023
Thank you very much for all support.
Congratulations for flexmonster support team for your attention and helping!
⋅ February 27, 2023
Hello, Rafael!
Thank you for you reply.
Feel free to contact us if any other questions arise.
Best Regards,
Maksym