Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Formating Positive Numbers


novata
 Share

Recommended Posts

I am working with a budget and would like to be able to use $0000.00 CR to show on a calculation field when we have profit, but apparently we can only chose diferent format and color for negative numbers.

Is there any "if condition" that I could use to achive my goal?

Or any other option would help.

Thanks,

Novata

Link to comment
Share on other sites

Hi Novata,

 

In filemaker you can use calculations to achieve any goal when displaying numbers. Assuming you already haev a number field displaying the value, all you need to do is to create a new calculation field with the following formula:

 

"$"& abs(numberfield) & " CR"

 

If you need to round your decimals, here is another one:

 

"$"& round(abs(numberfield), 2) & " CR"

 

You can use the color formatting options on your calculation if you need to show the text in blue; and that would be.....

 

TextColor ( "$"& round(abs(numberfield), 2) & " CR"; RGB ( 0 ; 0 ; 255 ) )

 

 

For all formulas, you must make sure that the result is specified as text on the calculation field.

 

Good Luck!

Link to comment
Share on other sites

Thanks Coolboy7163, it sounds promising but I do need to add all that only if the calculation results in a positive number. I believe that would have to have some sort of "if condition" but I do not recall how to do that.

 

I went around and created an additional field for the CR. I found out that with all that info on the formula, the number would not result in the format I need. Therefore I would need to use the number formatting function. I need with the dollar sign, decimal, coma and cents - $0,000.00 CR.

 

My solution:

Calculation field is set through the “number formatting” function to be blue and when negative to be black, but I DO NOT WANT the minus symbol. Is that possible to omit it?

Created an additional field with an if condition

If (Balance >0;"CR";"")

which is also formatted through the “number formatting” function to be blue

 

Problem: Is it possible to omit the minus (-) symbol of the negative number, without changing the color. I need to be able to set black in case is negative, since the positive it is set and needs to be blue

 

Let me know if you know any way to get that done.

 

Thanks again for your help!

 

Novata

Link to comment
Share on other sites

Hi Novata,

 

Just like I said on my fisrt answer, the best way to do that is through a calculation. Let's do this:

 

Write back and type a number the way it looks when is entered and right under it, type it again the way "you want" it to look.

 

I will give you a formula that should resolve your problem and the run around to use the actual values on other calculations.

 

Waiting for you....

Link to comment
Share on other sites

If you create a calculation (number) with: theNumber * -1

 

This reverses the sign. Then let field formatting take care of the rest for the calc (display) field. Set positive numbers color black (format your dollar sign, decimal and so on). Then format negative numbers as color blue and format as 1234 CR. Coolboy is right in that you can round or add other goodies to the number. But handling the majority through field-level format (rather than the calc) will probably save resources; which is always a good trade-off.

 

 

 

smiley-laughing

Link to comment
Share on other sites

Hello Coolboy,

Right now my calculation

(Total CoOps Budget +Total Credits Budget) - Total Expenses Budget

field is formatted through the number formating function to:

blue

notation currency $ sign,

fixed 2 decimals digits

i.e. $1,234.56

For negative it is:

black

with the minus symbol

i.e. -$1,234.56

 

I need the negative to be ONLY in black, WITHOUT ANY sign at all. And, - THERE IS NO OPTION to NO SIGN at ALL.

 

My goal is to have results greater than zero in BLUE and smaller than 0 in black. ANY symbol on the negative will only be confusing, since I already have an "if condition" on an additional field that adds "CR" right after the number when this is greater than zero.

 

I am sorry if I could not make myself understandable, so feel free to ask me any more questions you need.

 

Thanks again,

 

Novata

Link to comment
Share on other sites

Hi LaRetta,

So, you are saying that I should reverse everything. But where exactly I would put the * -1?

I would need you to be a little more specific since I have not used File Marker for so long and I am finally starting again...

Thanks in advance for your help.

 

Novata

Link to comment
Share on other sites

Try making your calculation =

- Total CoOps Budget - Total Credits Budget + Total Expenses Budget

 

Or just leave the calculation as it is (you might need it for further calculations or for exporting), and add another calculation field just for display =

- YourGrandTotal

Link to comment
Share on other sites

Novata,

 

I seriously think you are better off posting it the number the way you want it to look and someone here can write a little formula and walk you through a step by step process to get it done. Another alternativE would be to send you a sample file with the solution already implemented.

 

Actually, download the attached and tell us if this is it....

Link to comment
Share on other sites

Hi Novata,

 

Bottom line is ... you need another field because you can't control the formatting, ie, can't colorize numbers which have field-level format applied. You will either have to create a text calculation, include the colorizing and "CR", include decimal, comma and $ and so on within the new field's calculation OR simply create a new number calc and reverse the plus/minus (of the resulting number) then use field-level format to colorize the new calc field and use the new calc field for your displays.

 

Use Comment's calculation. My formula works for one field (reverses the sign); his works because he reverses the addition and subtraction on several fields but the results are the same ... your numbers will reverse. Then the new calc field will behave just as it should when you field-level format it! It is much more efficient (save evaluations) to control colorization and number format at the field level. Believe me, I know what dollars calcs (formatted with decimal, thousands separator and dollars) AND colorized looks like. So, create a calculation number with: Total CoOps Budget - Total Credits Budget + Total Expenses Budget. Format this calculation as I've explained.

 

If the fields contain (in order): 125.00, -225.50 and 122.40 then the TRUE result will be: 222.9. but will display as $222.90 CR. If the fields contains (in order): -125.00, 225.50 and -122.40 then the TRUE result will be: -222.9 and it will display as $222.90.

 

You can also forget the CR field (or adding it within the calc). FM will add it automatically on all numbers greater than 0 because FM will think it is a minus. Look at your minus options in that popup ... we are taking advantage of that last format option and taking advantage of 1) you can colorize a number field normally and you can choose the color for minus. It isn't needed within the calc.

 

LaRetta smiley-laughing

Link to comment
Share on other sites

Coolboy7163

I am sorry, I was trying to give all the details and probabily wasn't clear...

Now, my calculation field shows "-$1,234.56" (in black) for negative. I would like to show "$1,234.56" (in black). No need to ANY kind of differentiation from the positive number that is displayed in blue.

I would love to download your file but I can't. To much trouble going into PayPal...I tried yesterday all day!

Thanks for your prompt help!

Novata

Link to comment
Share on other sites

Coolboy7163,

I was finally able to download your sample, which looks great but the negative does not show the currrency ($). It appears in black with no negative sign, but without the currency.

Is there a way to add it?

Thanks,

Novata

Link to comment
Share on other sites

LaRetta,

I believe your solution is perfcet, but I have to confess that it is alittle bit confusing to me. I don't know how to do it, some of your codes I don't know where exactly I can place them. As I mentioned, I used to work with FileMaker long time ago, and I am just re-learning everyhting almost form scratch. If you could explain more deteiled I would love to try, because it makes a lot of sense.

Thanks in advance for taking the time to hlep me.

Novata

Link to comment
Share on other sites

I'm not sure how to make it any clearer. We are simply taking your original amount and REVERSING its sign then letting field format handle the rest. Forget everything you have tried prior; forget your CR calc; just duplicate this step by step. Name your calcs whatever you wish but to keep the example clear, I will name them as we go:

 

1) Create a calculation called cTotalAmount (result is number) with: Total CoOps Budget + Total Credits Budget - Total Expenses Budget. Drop those parentheses from your calc. When you are in the calculation box, double-click your fields to insert them into this calculation – do not type them in (just in case we have any spelling above wrong). This is your original calculation in which you were attempting to change the color, remove the minus and (conditionally) add the CR calc and it is your true, correct total. You want to preserve this calculation for other uses throughout your solution (such as other mathematical calcs).

 

2) Create another calculation called cDisplayTotal (result is number) with: - cTotalAmount. cDisplayTotal takes your cTotalAmount (which was your original calculation) and REVERSES it. If the original calc result is a plus, it makes it a minus; if the original calc result is a minus, it makes it a plus.

 

Now all we have to do is use field format (number) on this new cDisplayTotal calc! Place cDisplayTotal on your layout. Set the font color to black through Format > TextColor. Select the field, right-click and select Field Control > Behavior. Uncheck 'Allow entry in Browse.' Select this field again, right-click and select Number Format. Set it to format as decimal; fixed number 2 digits. Also specify Use Notation Currency leading/inside and specify $. Down in Separators section, select Use Thousands Separator. Over in the Negative section, select color blue and format as: 1234 CR at the bottom of the popup.

 

You see, Novata, you are wanting the exact opposite - you want to colorize the positive (blue) and end it with CR. And you want to remove the minus from negative numbers and have the color black. And you want decimals, thousands separator and $ on both positive and negative numbers. So we fool FM by reversing the sign on your original calc (in a new calc called cDisplayTotal) then apply field-level format to IT instead (and use IT for display). The results would look like:

 

If the original amount of cTotalAmount is $228.10 then the result in the NEW cDisplayTotal will be $228.10 CR. If the original amount of cTotalAmount is -228.10 then the result in your new calc will be $228.10.

 

I would much rather create a calculation to reverse the sign and then let field-level number formatting do its thing, than to attempt to add decimals, thousands separators, dollar signs, colors and CR within a conditional calculation. Number calcs can't handle colorizing & text AND field-level number formatting. By reversing the sign, you can have the best of both ... coloring, currency formatting and conditionally ending with CR.

 

LaRetta smiley-smile

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use