Value could be currency or percentage.... Conditional formatting?

[MarionT]

I have a table where I'll be entering values - sometimes a percentage and sometimes currency.

Now of course, those are both numbers....

How do you handle this?
- have 2 columns, one for percentages and one for currency; this seams easier in terms of formatting the display. However, if I want to show the info in a grid, how do I do it in one column? Can the formatting (% or $) be conditional per row?

- have 1 column for the value, and one column which specifies the formatting (percentage or currency).. and then build a formula around it to add the % sign or $. How would I do this? Where?



I would really like the grid to show the values in 1 column, with either currency or percentage formatting...

[MarionT]

Re: Value could be currency or percentage.... Conditional formatting?

for that matter.... how do you deal with entering percentages altogether?


eg,
.1 = stored value
10% = display value

this is in an updateable detail view

and I'm hoping the user can enter: 10 or 10%

[kingcarol]

Re: Value could be currency or percentage.... Conditional formatting?

Some of my notes to myself about percents follow. (My db also stores 10% as .10 ) Maybe my notes will help:

Use this in the Display Format property:
alltrim(str((convert_type(<value>,"N")*100),250,2,"%"))

Use this in the Display Unformat.
convert_type(<value>,"N")/100

To keep the formatting after user enters a value,
Check Mark to use Client Side Formatting.
Select Custom as the Format type.
On the genie that opens, select Number, then put the following in the Parse Out field:
data = data.toFixed(2) + '%'

In Xbasic Validation Rules, enter something like this:
if e.data.JOB_MARGIN = "" then
validate_JOB_MARGIN .hasError = .t.
validate_JOB_MARGIN .errorText = "A Markup Percent is required."
else
if val(e.data.JOB_MARGIN) < 0 then
validate_JOB_MARGIN .hasError = .t.
validate_JOB_MARGIN .errorText = "The Markup Percent must be greater than or equal to zero."
end if
end if

ALSO! If field keeps producing an error that it is not a valid number when trying to save a new record, try putting something like this in the CanInsertRecord server side event (after the end With):
dim salesTaxRate as n
salesTaxRate = dataSubmitted.TAX_RATE
if salesTaxRate <= 0 then
dataSubmitted.TAX_RATE = 0
end if
'=========================================================================

IMPORTANT! If using these, then IF you do an insert query that will need the value, you will need to divide the value by 100 IF you will be using e.OldDataSubmitted or e._currentRowDataOld because they will use the DISPLAY value, and not the real value.
(See sample code at bottom of this entry). NOTE - This will NOT be the case if using e._currentRowDataNew (or old) because those use the real values. See Selwyn explanation:

when you do an ajax callback from an editable grid then the data and old data that is submitted are formatted values - because they can be simply read from the controls in the browser.

but if the grid is not editable, then there is no data that is actually submitted. the only thing that is submitted is the primary key of the row.
so, on the server, we do a query to find the record for the primary key and we create fake e._currentRow values by putting the data from the record that was found into the e._currenRow object. in this case the data in e is raw, not formatted.

[MarionT]

Re: Value could be currency or percentage.... Conditional formatting?

wow - this is fabulous -
thanks Carol -

[Julie]

Re: Value could be currency or percentage.... Conditional formatting?

Hey, Thanks Carol, very helpful!