Changes

bxp uses the MySQL engine to store data. The data can be stored as different data types e.g. dates, numbers, text. This article will give a brief description of some of the more commonly used data types in bxp and will explain how data types can be converted for the sake of reportingpurposes. The keystats module allows users to create highly customised reports, sometimes . Sometimes there is a requirement to sort , or compare data which has been entered into the form as if it's a number or date but it's stored and treated as text.

This wixi will explain how to mitigate against this occuringoccurring.

In bxp, any fields that are added to a form by users are added as text fields. They are the most flexible and can store multiple formats. A user may, for example, add in a new field to store whole numbers. This will be stored as a text field even if all the data matches the format of an int field. A user will therefore come across some very unusual behaviour if they attempt to order a result set by this column.

If a user has builds a database with an int column and decides to run a SQL statement to order the result by this column then , they would expect to the numbers to be ordered from smallest to largest:

However , if a user is creating a form in bxp and adds a field to a form to store the number than , this will be stored as text. If a user orders this field it will be ordered alphabetically. So any number starting with a one will appear before a number starting with a two because that's how it would be is handled alphabetically. The above list will be ordered like so:

This will could cause issues if a user is hoping to create creates a keystats report and wants to order it by this column. There However, there is a function that can be used to treat the data as a different data type.

In MySQL there is a function called Cast which is used to convert one column of data from one datat data type to another. A user may have a form in bxp with an id of 938. The field that contains the numbers is strCDA_938_field_0_6. The following SQL query will order the numbers alphabetically.

Another situation where the cast function can be useful is when a user needs to convert text to dates. A user may add a field to a form to hold dates , and will then need to a keystats report to display data depending on the date contained in this field. The data will be stored as text and will conform to the datetime layout.

If a user tries to create a keystat that compares the dates , then the result set will be empty.