What should calculation result be?

[eddyb2]

Hi all,

I have 20 fields each of which are a calculation

The majority of the time the calculation result will be a number

At times it could be N/A

And at other times it could contain a symbol e.g. <0

The problem is on my layout I would like to format the fields so they are set to 1 decimal place, a comma seperator is used and negatives show in brackets

If I make the calculation result is...NUMBER

Then I can certainly set the decimal points, the seperator and the negative brackets no problem, but <0 is formatted as 0.0 and N/A shows as a blank field

If I make the calculation result text, the numbers come through, the N/A shows correctly and the <0 is also fine. But then I cannot format the field on the layout as a number, i.e. setting decimal places, using comma seperator, negatives in brackets

Does anyone know of a workaround here?

Many thanks

[Raybaudi]

Isn't the condition: <0 just saying that the number is a negative one ( so do not need brackets ) ?

[eddyb2]

Yes <0 would mean a negative. But some users actually enter the negative numbers, some users just put <0, some put N/A

It needs to be able to show all - there comes my issue with the fomratting of text, number, decimal, negative, 000 seperator all on the 1 field...

[Raybaudi]

I have 20 fields each of which are a calculation

... but they aren't really calculated field, are they ?

[eddyb2]

Yes they are calculated fields, from other fields that users are entering into.

[Raybaudi]

This is an idea...

TextAndNumbers.zip

[eddyb2]

many thanks, works absolutely perfect, but no idea what the calculation does.

I have been lookign at the FM help file to try and decipher but not getting close.

Please could you talk me throught the steps of

Let([

decimals = 2 ;

s = If ( Sign ( entry ) - 1 ; "[ ]" ) ;

e = Sign ( entry ) / 10 ^ ( decimals + 1 ) ;

n = Abs ( entry + e )

];

Case ( n ; Left ( s ; 2 ) & NumToJText ( Int ( n ) ; 1 ; 0 ) & Left ( Mod ( n ; 1 ) ; decimals + 1 ) & Right ( s ; 2 ) ; entry )

)

Very much appreciated!

Many thanks

[eddyb2]

Also, is there anyway I can format it so that the negative numbers are in red, as you normally can when formatting as number?

Thanks for all your help

[eddyb2]

Also, all seems to work great apart from rounding.

If I enter 12.39 the result will show as 12.4 which is correct

If I enter 12.38, the result will show as 12.3 which is not correct. Anyway to rectify this in the calculation?

Thanks

[comment]

If you always want 1 decimal place, you could use:

Let ( [

a = Abs ( Round ( Input ; 1 ) ) ;

t = NumToJText ( Int ( a ) ; 1 ; 0 ) & "." & 10 * Mod ( a ; 1 ) 

] ; 

Case ( 

Input ≠ Filter ( Input ; "-0123456789." ) ; Input ;

Input < 0 ;  TextColor ( "(" & t & ")" ; RGB ( 220 ; 0 ; 0 ) ) ;

t

)

)

---

Note that ultimately this is not a good solution. Mixing numbers with text in the same field precludes you from running any statistical analysis on the numbers. It would be best to split the entries into 2 fields.

Edited January 27, 2009 by Guest

[eddyb2]

Genius! Thank you both!

Just one last bit of advice is you don't mind.

Would you put this into the calculation fields or would you create seperate "display fields" with this caluclation referencing the first set of calculation fields?

[comment]

I am not sure I understand your question. We are missing the larger picture here: you said this is a result of a calculation, but it appears that users just enter a number or a non-number such as "<0" or "N/A". We also don't know what you intend to do with the results.

[eddyb2]

Apologies for not being clearer. Basically I am building a user customised report

Users have 15 drop down menus to decide on their columns in the report (they have previously entered data into about 50 fields [columns])

The resulting 15 calculated fields look at their selection fields and use the getfield function to set the data

So for example,

sf_1 (selection field 1) = Turnover

rf_1 (resulting field 1 find the turnover data using getfield) = £100m

sf_2 = Profit

rf_2 = N/A

So by using the selection fields they choose 15 columns which generates data in 15 calculated fields.

Now these fields need to be displayed on the report layout and thats where my question came in regarding formatiing. I never know if the result of each column is going to be a number, text symbols etc, it all depends what the user originally entered as their set of data and which columns they choose to show.

So my latest question is, with the calculation you kindly gave me, would I just add this into my rf_1 rf_2 calculation fields. Or, would i now create new fields e.g display_1 display_2 and use your calculation to reference the rf_1 rf_2 fields?

Either will work, just wondering what would be best practice?

Many thanks

[comment]

It's going to be problematic whichever way you choose, because 50 unstored calculations are going to be slow - and that's even before you got to the issue of sorting. It's not easy to rewrite Filemaker, and that's basically what you're doing.

I still don't understand one thing: is the Profit field a number? If so, how come it's allowed to have a value of "N/A"?

[eddyb2]

Because we dont know it is the profit field.

Users have 50 heading fields and 50 data fields.

So Head1 could be profit, could be Name, could be anything.

That means Data1 needs to allow any type of entry.

So the user builds up their 50 headings and 50 lots of data for those headings.

Then they select what they want to show ont he report from that lot of data.

So a value list is created based on their 50 headings.

They have 15 selection fields for the report so they choose 15 out of the 50 headings.

Again the result cannot be tied down to text or numbers because we do not know what they entered earlier nor do we know what they are going to select to show on the report.

So, I end up with 15 headings and 15 lots of data they want to show on the report.

The 15 data fields are calcs using the headings and the getfield function I get the data for what they slected.

Now, should I show those calc fields ont he report, incorporating your format calculation.

Or should I do a fresh lot of 15 fields, lets call them the 15 display fields. Put your formatting calc into these fields and reference the 15 data data fileds in this calc?

The reports are run off overnight so not a huge problem that I agree this may be slow because of the amounts of calcs and referencing I am doing.

As long as the users can select what headings they want to show, we should be good.

I think I may be better using new display fields.

Do appreciate all your input!

Thanks again

[comment]

I don't see why you should need two sets of calc fields, one to get the value and one to format it (if I follow you correctly).

I would suggest an entirely different approach to this. Note that it's still not perfect (it's difficult to control alignment, for example), but it's a whole lot easier than what you have now.

FormattedDynColumns.fp7.zip

[eddyb2]

Thanks again.

Interesting approach and certainly much more straight forward and a much cleaner solution to what I have right now.

But, as you say I may have trouble with alignments etc. To be fair, the method I have right now, although not the simplest, does actually produce the correct reports in a nice layout that the users are more than happy with once I have the formatting issue sorted.

I think I will stick with this for this version of the release and perhaps look at recreating a different method next time around.

Just going back to your formatting calculation, I am sure there is a way I can use a variable to join the 2 calculations together. So for example still have my calculation in there as it is which actually returns the data, but to return this data to a variable which your format calc then refers to, all in the 1 calc field.

So variable called RESULT$ would be my current calc...

If ( Position ( weeklybkselection 2::Column1; "0"; 1; 1) = 1; GetField ("Yr1Figures 2::Yr1C" &Middle(weeklybkselection 2::Column1;2;1));GetField ("Yr1Figures 2::Yr1C" &( Left (weeklybkselection 2::Column1; 2 ))))

Then your format calc in the previous posts would use the variable "RESULT$" rather than where you have "Input" as a field ref.

Would you be able to help me in uderstanding how to join the 2 calcualtions together with a variable?

Many thanks (again!)

[eddyb2]

Think I got it...

Let (result = If ( Position ( weeklybkselection 2::Column1; "0"; 1; 1) = 1; 

GetField ("Yr1Figures 2::Yr1C" &Middle(weeklybkselection 2::Column1;2;1));

GetField ("Yr1Figures 2::Yr1C" &( Left (weeklybkselection 2::Column1; 2 ))));

Let ( [a = Abs ( Round ( result ; 1 ) ) ; t = NumToJText ( Int ( a ) ; 1 ; 0 ) & "." & 10 * Mod ( a ; 1 ) ] ; 

Case ( result ≠ Filter ( result ; "-0123456789." ) ;

 result ;result < 0 ;  TextColor ( "(" & t & ")" ; RGB ( 220 ; 0 ; 0 ) ) ;t)))

Thanks for all your help

Ed

[librone]

...(it's difficult to control alignment, for example)...

Try this

Ann

FormattedDynColumns_mod.zip

[comment]

That's about right. I think you could make it simpler by:

Let ( [

result = << your calc which I do not understand >> ; 

a = Abs ( Round ( result ; 1 ) ) ;

t = NumToJText ( Int ( a ) ; 1 ; 0 ) & "." & 10 * Mod ( a ; 1 ) 

] ; 

Case ( 

result ≠ Filter ( result ; "-0123456789." ) ; result ;

result < 0 ;  TextColor ( "(" & t & ")" ; RGB ( 220 ; 0 ; 0 ) ) ;

t

)

)

[comment]

What exactly should we be looking at in your file?

[librone]

What exactly should we be looking at in your file?

In report layout the alignments of each repetitions of cReportValuesR field.

sorry for my English :

Ann

[comment]

I am still missing the point here. The idea is that result should be aligned to the left when the source data is coming from a text field, and to the right (or by decimal point) when the source is a number. I don't see that your file accomplishes that.

[librone]

I am still missing the point here. The idea is that result should be aligned to the left when the source data is coming from a text field, and to the right (or by decimal point) when the source is a number. I don't see that your file accomplishes that.

Ok, my file is not fully dynamic.

but you can put in 1th or 2nd or 3td rep only text and 4...5...rep only number.

via filter value list in gReportFieldsR

p.s. you can set dynamic alignments via script.

Daniele nick:Raybaudi posted a tip here in this forum.

Ann

[comment]

Daniele nick:Raybaudi posted a tip here in this forum.

That's exactly what I had in mind when I said it's difficult to control the alignment.

[librone]

That's exactly what I had in mind when I said it's difficult to control the alignment.

ah! in your mind... :

in my mind it's simple.

Ann

FormattedDynColumns_mod_2.zip

[comment]

Yeah, well... it may be simple, but it doesn't work well. On first try, the number field appears to be empty - because all the numbers have been forced to line #2. If I reduce the amount of spaces in your calc, I get this (attached). That's not a correct way to align numbers. Not to mention that this should be dynamic, and not depend on hard-coding of field names.

[librone]

Yeah, well... it may be simple, but it doesn't work well. On first try, the number field appears to be empty - because all the numbers have been forced to line #2. If I reduce the amount of spaces in your calc, I get this (attached). That's not a correct way to align numbers. Not to mention that this should be dynamic, and not depend on hard-coding of field names.

I know this.

try to reduce "dynamically" the amount of spaces.

case (number <10; ....;numer <100 ;...

this is the best we can do.

Ann

[comment]

That's not going to work well with a proportional font. And what will you do with numbers that can have a variable number of decimal digits?

I don't think "this is the best we can do". With a lot of work, it can be done perfectly. But I am not the one who's going to do it. :

[librone]

That's not going to work well with a proportional font. And what will you do with numbers that can have a variable number of decimal digits?

Ok print in courier :

Why decimal?

you must evaluate only integer part.

In format report layout decimal is 1 digit.

Ann

Edited January 27, 2009 by Guest

[comment]

Because I don't believe in solutions built for a single specific example.

[eddyb2]

comment, shortened version of the calc works perfectly.

Thanks again for all your help on this, very much appreicated.

I do have 1 other question you may be able to help me on with the report but will start a new thread for this I think

Thanks again

Ed

[eddyb2]

Spoke too soon!

Comment, are you able to help me again please on this formula. Everything works OK until I get a very small number in the field.

For example I have a record now where the actual figure is...

0.021661877

Once it is in the field containing your formatting calculation, it is displayed as...

2.16618773946371E-02

When actually all i would want is this to round to 1 decimal place so would show 0.0

Is there a fix in the calculation that would solve this?

Thanks

Ed

[comment]

Well, you could add "e" to the list of characters a number is allowed to have. But it's a patch and I didn't like this method of determining the data type to begin with. What if there is Text field with a value like "e1-e202"?

I'd suggest doing this properly and getting the type from an "authorized source":

Let ( [

fieldName = << do this part of your calc here >> ;

fieldType = MiddleWords ( FieldType ( Get (FileName ) ; fieldName ) ; 2 ; 1 ) ; 

result = GetField ( fieldName )

] ;

Case ( 

fieldType = "Number" ; 

Let ( [

a = Abs ( Round ( result ; 1 ) ) ; 

t = NumToJText ( Int ( a ) ; 1 ; 0 ) & "." & 10 * Mod ( a ; 1 ) 

] ;

Case ( result < 0 ;  TextColor ( "(" & t & ")" ; RGB ( 220 ; 0 ; 0 ) ) ; t )

) ;

result

)

)

[comment]

Just for fun, here's a rather simple way to simulate alignment by data type: text is aligned to left, date/time to right, and numbers by decimal point.

DynColumnsAlign.fp7.zip