Showing results for 
Search instead for 
Do you mean 

Group Edit Criteria using numbers versus text

Copper Super Contributor
Posts: 122
Country: United Kingdom

Group Edit Criteria using numbers versus text

We have a field that is defined as text but does contain numbers amongst other values
When setting up a group we want to use a criteria that says select all contacts where there is data in the group (we're ok with this bit!) AND the data is greater than 999 (eliminating all 3 digit codes in that field)
Is there a built in function or other workaround to query this field for numbers given its a text field?
Thanks in advance
Andy Kent
Angel Solutions (UK and NZ)
Nickel Contributor
Posts: 205
Country: United_Kingdom

Re: Group Edit Criteria using numbers versus text

There isn't a built in function to do this.


However, you could create a new field with a type of numeric and then copy into it from the character field using Edit -> Copy Field. 


This then gives you a numeric field where you can use the >999 


Then, going forward, just update the new numberic field. 

Trevor Lever

UK - ACT Certified Consultant
Platinum Elite Contributor
Posts: 6,757
Country: USA

Re: Group Edit Criteria using numbers versus text

The answer to your basic question is no, character fields always do a character sort, that is strictly from left to right. However, there is a trick you can use for numbers in a character field. The Postal Code/ZIP code field is a character field but in the USA with our numbers only ZIP code, you can do a range search on that field because all ZIP codes are at least five characters and we must use leading zeros. So in your case, if you use significant digits only such as 11, 12, 100, it would not sort correct and would return 100, 11, 12. However if you entered the number codes as 010, 012, 100, then they would sort in numeric order and would respond to queries in numeric order.
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer