Discussions

Is there a way to change a field type based on data in another field?

My app tracks performance on service level agreements, and the metric can be either a percentage, a time in seconds or a number of incidents, depending on the contractual language of the SLA.  Currently it's a text value, but I am going to add some formulas to determine whether we failed the metric and to calculate penalty amounts and need to be able to toggle the type between "number" and "number - percentage" depending on the value in a metric data type drop down list.  Is there a way to do this?

You cannot change a field type by formula. One solution is to have it be a text field but calculate a text result which appears to be a number or a percent.

My Colleague is correct. field types are static unless changed in the field settings. You can only convert value types from other field using formulas: toText (Number) or ToNumber([a number stored in text field]).

solution I'm going with then is to change my [standard] field to a numeric and enter everything as a decimal (boolean are 1 and 0, 99.95% is 0.9995, etc.) and using mid(totext([standard])) to grab digits at certain places and insert the "." and "%" where appropriate. 

(I am of course using a case function for boolean, integer or percentage, just working on the percentage part now)

The problem I'm running into now is that the system isn't keeping leading/trailing zeroes, so percentages that are single digit or have no value after the inserted decimal point will not show up properly.  Is there a way to tell Quickbase that I need to keep all the leading/trailing zeroes on a field?

No way that I know of to format a number field to avoid trimming zeroes. You could write all that logic into your totext formula, to gauge the length of the numeral before formatting it.

You could also reverse the relationship and make the [standard] field text, which will retain your string exactly as entered, use the totext([standard]) formula for [display], and write the pass/fail calculations as tonumber([display]).

Ben,
I recommend that you explore using different fields and use form rules to use the proper field based on the SLA type. Then use formulas if needed to display your results and make data entry fields non-reportable.