Use text and number in same field?

[WestTexasKen]

Hello,

I've created a database of rainfall records going back to 1867, and most entries are numeric. However, some are missing ("M" is value given) and some are "trace" ("TR" is value given).

I can set the data type as text, but lose the ability to sum columns and sorting is alphanumeric, not numeric, which gives wonky results in this case. If I set it as numeric, then I can sum columns but lose the real value of the entries Missing or Trace, which is NOT the same as zero.

How can I set this up so I can sum the rain totals but still show the true entries for Missing and Trace values in the result table cells? Is it possible? Is there a formula to use to make this happen? I've done it in Excel using SUMIFS formulas, but not having any luck in the database table universe.

Thanks,

Ken Grimm - Online Director for gosanangelo.com

[Bangalore]

Hello,

Does your table have a column with numbers in most cases and in some cases are there entries like "M" and "TR"? Are you trying to use Totals and Aggregates in your Datapage to sum the rainfall records? It is not possible to add number and text for the same field using standard features. You may be able to achieve this with some extra programming to check for fields with numerical values and sum them only.

Another suggestion is to have two fields in your table, one "Number" field for recording the rainfall and another "Text 255" field, as a dropdown field for "missing" or "traces" etc.

Please read this article for more help: http://howto.caspio.com/datapages/creat ... ation.html

Thanks

[NailDyanC]

Hi, just to update this post. Here is the new and updated link for totals and aggregations that you can use: https://howto.caspio.com/datapages/reports/advanced-reporting/totals-and-aggregations/