Concatenated Calc field, decimal problem...

[moonunit]

Today's driving-me-mad problem is...

I have a calc field with the calculation result as 'text'.

"Closing invoice period: " & FirstOfLastMonth & " to " & Rental ended & " ~ £ " & Round ( EndCalc ; 2 )

The field consists of text; a 'date' calculation field; a date field; and an external number calc (which is itself rounded).

All works fine when the result comes out as, say, £56.25, but if by some misfortune, the number is £56.20, it appears, concatenatedly as £56.2 ...

I've truncated, setprecision and rounded and done these in both the external calc and within this calc field, but nothing works...

Can some kind person put me right or put me out of my misery by telling me this isn't possible!

Thanks

Tim

[Kent Searight]

Before getting to the "how to" I have to ask why you want to calculate a price in a text field. Why not a number field? Also, you mention that rounding is taking place before the "final" rounding. This can be problematic, just to let you know.

OK, to solve your dilemna I'd suggest a formula such as:

Let ( [sign = Substitute ( Abs ( Number ) / Number ; "1" ; "" );

dollars = Int ( Round ( Abs ( Number ) * 100 ; 0 ) / 100) ;

cents = GetAsText (Right ("00" & Round (Abs ( Number ) * 100 ; 0 ) ;2 ) )] ;

sign & dollars & "." & cents)

Make the field an auto-enter calc, make sure "Do not replace existing...." is unchecked.

The other option, of course, is to just format the number field to show 2 to the right of the decimal.

Hope this helps!

[moonunit]

Thanks...

Of course, you're absolutely right to ask why I'd want to calculate a price in a text field and as a result of your question I'm sitting here asking myself the same question!

The simple reason is that I have a massive nested 'if' function and I haven't got far enough into the design to regret not having number values available...Besides which, it's a painful re-write...

Re your fix...

I didn't really 'get' it, but I deconstructed it and using the second and third lines I was able to make it do what I needed it to do...

Now I'm going to take a look at a re-write which keeps the calc as a number.

Once again, many thanks for your input...

Say hi to the Dandy Warhols for me!

Tim

[Kent Searight]

The simple reason is that I have a massive nested 'if' function and I haven't got far enough into the design to regret not having number values available...Besides which, it's a painful re-write...

Why would you need to rewrite your calc? Just go to Define Field dialog and make it a number field...unless I'm missing something?

I didn't really 'get' it, but I deconstructed it and using the second and third lines I was able to make it do what I needed it to do...

I should have commented in my calc because "sign" might be misinterpreted as currency sign - that's not what it is. The "sign", in this calc's context, refers to (+) or (-) sign. It's needed in the calc for rounding and displaying negative numbers properly.

Edited October 12, 2005 by Guest

[LaRetta]

Hi Kent,

I can't help getting in here ... I think Floor() might be perfect in this situation:

Let(

[

dollars = Floor ( EndCalc ) ;

cents = Right ( "00" & Round ( Mod ( EndCalc ; dollars ) * 100 ; 0 ) ; 2 )

]

;

dollars & "." & cents

)

I've used Sign() in other ways too. I'm adding your usage to my grab bag! But if you use Floor(), there would be no need for the Sign() variable line at all, right? I'm asking only to learn ... I adore calculations. This seems simpler if it won't break (and I tried but couldn't).

Thank you!

LaRetta

[LaRetta]

Of course, you're absolutely right to ask why I'd want to calculate a price in a text field

What am I missing here? You aren't calculating a price in a text field ... you are formating a number (displaying) in a text field - big difference. You have specified that EndCalc is your number calculation. You can't just set the field format to number on a text field; text will be your only formatting options. You must format the number within the calculation as you are doing.

Maybe I'm crazy tonight (more than normal), but it kept bugging me that 1) either I'm missing something here (in which case I want to be straightened out), 2) someone slipped me some whacky tobaccy or 3) I'm right. Which is it?

Oh. Why not just display the calculation pieces as merge fields (all placed within the same merge specification). In this way, you can format your EndCalc any way you wish. Maybe that's what was being suggested but it didn't sound like it. On that note, I'll go to bed.

LaRetta

[Kent Searight]

Floor()doesn't work if the input includes 3 numbers to the right of the decimal, and the calc doesn't handle negatives properly. Enter -9.99 for example and you get -10.99.

But, there is a quirk with my calc that I'd like to fix but I'm stumped for the moment. If the input value is > -.005 but < 0, the minus sign appears before the rounded value of 0, as -0.00. No big deal when it comes to using the value, but not very polished. :)

[Kent Searight]

Now I'm starting to wonder if I'm missing something :qwery: . I realize that it's not calcualting a price, and that it's formatting a number. Probably a poor choice of words on my part earlier. But, this formatting can still happen in a number field (see attached file) AND doesn't it make more sense to put numeric values in number fields than in text fields. I assume that something will be done mathematically with all these numbers eventually so why complicate matters by making them text values?

Well, thanks a lot, LaRetta!!! When a FM wiz like yourself starts questioning what seem(ed) to be solid reasoning, now I'm questioning myself. And you just go to sleep and leave me awake to ponder :)

NumberFormatting.zip

[LaRetta]

Hi Kent,

I'm up now.

Formatting a number is not the problem. Formatting text as number with trailing decimal zeros is the problem. Remember this part?

"Closing invoice period: " & FirstOfLastMonth & " to " & Rental ended & " ~ £ " & ...

Of course it makes more sense to put number in number fields (and Tim did that with his EndCalc). But as Tim has found, then taking any number and including it within a text-display calculation can break the number display. The only issue was filling out the zeros at the end - which you did wonderfully. There is absolutely no need to apply your formula to a number field.

Thanks for the input on Floor(). I've only used it with positive numbers and forgot the FM Help example on negative numbers. Floor() APPEARS to be inconsistent.

9.99 becomes 9

-9.99 becomes 10

*My* logic says it should become -9. But mathematically -9 is 'closer to zero' than -9.99 so changing it to 10 is logical after all. That could have come back to bite me and I appreciate the catch.

I'd just use merge fields for this if it were me since it is obvious it's for display. Adding each portion within the same merge field, then formatting each piece however Tim wishes. Click on merge with these portions in it, and you can format the dates, number, and text independently.

LaRetta

[moonunit]

Hiya guys,

[Does all help emanate from Oregon?]

I woke up thinking, hang on a minute, I'm not actually doing the calculation in a text field, not guilty your honour, despite my confession...Then I checked here and found out you'd all been busy while I was asleep.

Floor() seems to work for me - I don't think I'll ever have a negative value, and going to 6 decimal places seemed to produce the correctly rounded number every time.

However I am still struggling to understand what/why these variable fields are doing...

You're saying I need fields for "Dollars" & "Cents".

So I've created them but they're sitting there empty and the calc still works. Why is that?

What am I missing here? You aren't calculating a price in a text field ... you are formating a number (displaying) in a text field - big difference. You have specified that EndCalc is your number calculation. You can't just set the field format to number on a text field; text will be your only formatting options. You must format the number within the calculation as you are doing.

Maybe I'm crazy tonight (more than normal), but it kept bugging me that 1) either I'm missing something here (in which case I want to be straightened out), 2) someone slipped me some whacky tobaccy or 3) I'm right. Which is it?

As you worked out La Retta, I am displaying a number in a text field. I have formatted the number within the calculation - and it works fine until there's a zero on the second decimal place, at which point it displays the number to one decimal place.

All I'm trying to achieve is to produce a little text message that summarises what happens next, dependent on dates. The original calc number field is still available for use elsewhere.

I've now blended solutions like so...

"Short term invoice period: " & Rental begins & " to " & Rental ended & " ~ £ " & Int ( Round ( Abs ( FinalCalc ) * 100 ; 0 ) / 100) & "." & GetAsText (Right ("00" & Round (Abs ( FinalCalc ) * 100 ; 0 ) ;2))

...which works fine for my purposes.

I've had a little try at merging the data, but it doesn't appear as clean as a calc solution...

I must admit to being guilty of never having used a "let" function which might explain why I can't understand the earlier outlines...

Thanks for your help - further help understanding the why's and wherefores of Let() would be welcomed, but if you have to be elsewhere putting out other fires, so be it.

Tim

[Kent Searight]

[Does all help emanate from Oregon?]

Lots of rainy days leaves a lot of time for indoor pursuits

However I am still struggling to understand what/why these variable fields are doing...

You're saying I need fields for "Dollars" & "Cents".

No extra fields are necessary...dollars and cents are variables defined in the Let() function.

When LaRetta said:

Formatting a number is not the problem. Formatting text as number with trailing decimal zeros is the problem. Remember this part?

"Closing invoice period: " & FirstOfLastMonth & " to " & Rental ended & " ~ £ " & ..."

it reminded me of the original problem...something I'd lost focus on : Sorry if this caused confusion.

After thinking about it overnight I strongly believe that LaRetta is totally on the right track when she said to use a merge field and format the number as needed, as well as the text and the date. If I hadn't lost scope of the original post it would have made sense earlier.

I've attached an example of using a merge field on a layout and how formatting in that field is totally independent of the fields that actually hold the values. I've applied text, number, date and time formatting to that 1 merge field (maybe the word "field" is confusing here). Hope this helps.

MergeField.zip

[moonunit]

Dear Kent,

Many thanks for your continued advice (and downloads).

You and La Retta are probably both right that I should be using merge fields for this task, and retaining formatting control as a result - unfortunately I've already gone way down the calculated merge path and having recovered from the dropped decimal zero and extremely painful self-inflicted 'if'-itis, reverting to an alternative is not exactly near the top of my things to do list...

However, let me think out loud about this merge topic...

Unless I've forgotten how, merge fields have to be placed on the record, in place, yes? You can't make a merge from a calculation, except by merging the <>

My (merged) calc field is horribly complicated. There are five basic output statements, with different variables within each. For instance the first date field is drawn from two possibles; the second date field is filled from two different possibles; the total is calculated each time; and a conditional statement appears dependent on the two dates...

[And I've embedded all this in another 'if' statement so that the calc merge doesn't appear unless yet another condition is met!]

To my feeble mind, in order to pull off the same effect using merge fields, I'd have to be merging from calculated merges; or I'd be swapping my neat onscreen layout appearance for a whole heap of <>.

Am I wrong? Is it possible to display merge fields and not lose page-lines?

Tim

[Apologies if I haven't explained myself properly...]

[Kent Searight]

Why don't you post your calculation to this topic? It sound like it may be more complicated than is necessary. Then, if that's addressed, we can figure out how to display the info.

A couple of things I'm not sure you're aware of:

Merge fields don't do caculations, they can only hold the result of one.

A calc that concatenates the text is not needed...you just combine all in the merge field.

Formatting of the number is not necessary in the Number field that holds the value. The merge field formatting will address that.

-----

Take a look at the merge field example I attached to one of my earlier posts in this thread. Do you understand it? Let me know if you don't and I'll explain further. Trust me...understanding it is going to make life easier for you.

Also, I know you've spent a lot of time on your calculation and I know that starting over might seem that you've done all for naught. But if someone can show you a simpler approach and you learn something from it, then nothing is wasted. I'm not meaning to imply that there's anything wrong with your calc and maybe if you post it it'll turn out to be the best approach, but either way it never hurts to try. :

If you decide to post the calc for us to take a look at, please put comments in it where your objective might not be obvious to others.

(comments in calcs start with // and continue till a carriage return OR they start with /* and end with */, irrespective of carriage returns, either way works in FM7 and up)

[moonunit]

Hello Kent,

As I mentioned in my last post, I did download your Merge example. Yes I understood. Yes I've used merge a lot in the past - admittedly on one occasion on a wrong construct, I created a 25 page print document - in one single record (!), completely comprising merge fields; the merge fields being based on yes/no check boxes...

[On subsequent advice I re-wrote the document into multiple records, and now, as far as I recall, there's not a single merge remaining...]

With regard to my calc being too complex, I have no doubt that as it was created organically, it's unwieldy - I can already see elements that might benefit from a re-write; and I'm not averse to help, advice or criticism...

Are you suggesting that I post this in text form and you then work through it? It must be raining continually up there for you to volunteer for this!

Tim

[Kent Searight]

Hey Tim,

I'm not sure we're on the same wavelength, so let's go back to the start.

I have a calc field with the calculation result as 'text'.

"Closing invoice period: " & FirstOfLastMonth & " to " & Rental ended & " ~ £ " & Round ( EndCalc ; 2 )

The field consists of text; a 'date' calculation field; a date field; and an external number calc (which is itself rounded).

All works fine when the result comes out as, say, £56.25, but if by some misfortune, the number is £56.20, it appears, concatenatedly as £56.2 ...

Is this the calculation you refer to as horribly complicated? Is this where the "massive nested 'if' function" exists or is it within the Date Calc (which I assume is FirstOfLastMonth?) or the EndCalc? I've just been assuming that when you said:

My (merged) calc field is horribly complicated.

you were referring to the calc that contatenates "Closing invoice period: " & FirstOfLastMonth & " to " & Rental ended & " ~ £ " & Round ( EndCalc ; 2 ) . If this is the case, why do you even need the calculation? Does it do more than concatenate? Are there logical functions in it to determine what is displayed?

I guess that this is where I'm unsure of how to help at this point because I'm not sure what the question is (which could very well be my fault). That's why I suggested you post the calc you think has gotten out of hand.

Lastly,

Are you suggesting that I post this in text form and you then work through it? It must be raining continually up there for you to volunteer for this!

Sure, post it if you think it could use some improvement. I'll take a look at it, as might any one of the forum members.

Oh yeah, and it's raining in Portland today :

[moonunit]

Kent,

Thanks for your help: I have indeed cured the initial problem thanks to you. Everything works so I'm happy enough not to tinker with the calculations as of now.

See you in another part of the forum when I'm airing another little problem.

Tim

[Kent Searight]

Hey Tim,

My pleasure...I'm glad I could help.

Cheers,

Kent :goodluck: