Calculation in data base

[digilux]

Hey there,

i'am a complete newbe and yust one question. I use FMP as a date base where some data from my client's is stored. The date comes from an excell file and contains over 3000 records. The data is imported in a made layout. What i need to do is make an calculation wth some fields. For instance field index revenue is field (revenue 2007*10) + (field revenue 2006*5) + (field visits 2007*100)+ (field visits 2006 *50)

If i make this calculation, it only calculates once. It the calculation needs te be updated with every record.

Sorry for the very beginner question, but this would be the only one and any help is very much uprecieaded.

Thankx! :

Ronald

[digilux]

For the good order,

i ad an example file date base with 5 records where imade the calculation in the file index. The number which comes out is 842688 abd is very false. In the excel filein colum AE you'll see three example calculations which how it should be.

Thanx for any help

Ronald

fmp.zip

Edited November 4, 2007 by Guest

[comment]

1. Remove all quotation marks from your calculation;

2. Fix the coefficients for the 'bezoeken 200X' fields - they are in opposite order compared to the Excel formula.

I don't know how you're going to use this, but you should consider moving the year-specific data into a separate related table - otherwise you may have problems with the current structure when new data arrives.

[digilux]

Thanx for the help, the file works now

don't know how you're going to use this, but you should consider moving the year-specific data into a separate related table - otherwise you may have problems with the current structure when new data arrives.

I like to update trough the year, let say every two weeks i want te do a new import with new thsi year (2007) numbers. Would this be a problem?

Thanx,

Ronald

[comment]

I don't know enough about your solution to answer that. If you are always importing the data, and interested only in the data from the last 3 years, then I suppose it should work alright (except for the field labels, of course).

[digilux]

Thanx!!

sorry one last question, the last thing i need to do is place a text in a three specific fields, i can't find the formula for this. It is absolutly simple. It would be something like this:

Field familie: if value field index is >200 then place texst familie, if value is <200 then place no texst

How can i make this formula?

THANX!!!

Ronald

[comment]

Try something like:

Case ( index > 200 ; "familie" )

[digilux]

works!!!

Thanx, how thus this one work with between numbers like between 100 and 200?

Thanx

Ronald

[comment]

Sorry, I don't understand.

[digilux]

everthing below 100 is a C customer wich should get label kennissen in filed kennissen. This works the same way as familie , every one above 200 should get label famiklie in filed familie.

Everyone wich index 100-200 should get label kennissen in filed kennissen

something like this

Case ( index< 200 and >100; "kenissen")

but this thus not work

Thanx!!!!

Edited November 4, 2007 by Guest
change of numbers

[comment]

I think the syntax you are looking for is:

Case ( 100 < index and index < 200 ; "kenissen")

But I wonder why you don't make a single field for the label.

Also, your conditions don't make much sense: You're saying that everyone below 100 is 'kennissen', and that everyone between 100 and 200 is also 'kennissen' - to me that would mean simply that anyone below 200 is 'kennissen'.

Note also that between 'below' and 'above' there is 'equal' - you need to be very specific about that, and use ≤ or ≥ where appropriate.

[digilux]

works!

sorry mistype of coarse everthing between 100 and 200 are vrienden or B level client, everyone below 100 are kennissen or C level client and evruthing above 200 are vrienden or top clients A status.

Ik works, and yes one time i will get in into one label.

Thanx for the help!!

Cheers,

Ronald

[comment]

So why not simply:

Case (

index < 100 ; "C level" ;

index < 200 ; "B level" ;

"A level" )

Read the help on the Case() function to understand why it works this way.

[digilux]

Yes, i could do that. It works. The only thing is thath i like to give differnt collors to the c level text and b and a level. This is very easy for me to do when there are different fields.

I'am happy with your formula, now only the collors......

Thanx

Ronald

[comment]

Try:

Case (

index < 100 ; TextColor ( "C level" ; RGB ( 0 ; 180 ; 0 ) ) ;

index < 200 ; TextColor ( "B level" ; RGB ( 0 ; 0 ; 180 ) ) ;

TextColor ( "A level" ; RGB ( 180 ; 0 ; 0 ) )

)

[digilux]

Works!!

Thanx!

[digilux]

If anyone can help me wit the two following quistion's

Above used file contains over 3000 records. I want to make calculation's from al thes records. Example, revenu field 2005 record 1 till last makes total revenu 2005.

Quistion 1 how can i make an calculation wich uses one specific field in all the records

Quistion 2 how can i count alle the records

Quistion 3 how can i count a specic value in a field, specific values used are: familie, vrienden, kennissen

Any help would greatly be apriciated

THANX,

Ronald