Need Help with adding Values

[NuttyMoose]

I hope this makes sense to everyone:

I have a lookup fuction that takes a entered Value and returns its related value from another table

Ex:Type in 1 and it returns 20

Table used for lookup example:

1 - 20

2 - 44

3 - 67

4 - 70

5 - 80

6 - 90

This works great... What I need is a calc that will take my typed in value lets say this time I type in 4 and it return back 70...but I really need it to add up the values of 1,2,3 & 4.

Is this a loop or is there another way... it has to be a calc that will change what it adds up depending on what is typed in....meaning if 200 is typed in it should add up all the values up to 200 including 200..

Can anyone help

Brahim

[QuinTech]

One of the new features of FM7... inequality relationships! What if you make a relationship from one table occurrence of lookupTable to another TO of lookupTable, from indexNum (1, 2, 3, 4, ... ) to indexNum, where the join is <=. Then you could make a calc field that sums the values produced in that relationship and this field could be your "totalSoFar" field.

I hope i'm not confusing, but i'm a little confused... Since i don't actually have a copy of 7 to work with, i may use improper terminology from time to time. But i think this should work.

HTH.

Jerry

[NuttyMoose]

so on my realtionship instead of using = i should use <= that that will return all the values added together including the number I am on?

[QuinTech]

Actually, what i would do is add a second occurrence of lookupTable and create the inequality relationship from lookupTable1 to lookupTable2. Your existing relationship should stay the same,

whateverTable::IndexNum = lookupTable1::IndexNum

If you change that you will mess up your lookups. The new relationship would be

lookupTable1::indexNum >= lookupTable2::indexNum

J

[NuttyMoose]

This <= symbol will add up my values up to and including my number...

Meaning

If I select 2 and it returns 44 with the = symbol

then if I select 2 and it returns 64 using the <= symbol

Correct

[QuinTech]

Yes, that is correct. Specifically, if the calc field "totalSoFar" is defined as

Sum ( lookupTable2::valueField )

then its value will be 64. There is no selection involved; the table looks like below:

indexNum valueField totalSoFar

1 20

[NuttyMoose]

I am a newbie at these lookups... what I have so far is:

A Reference Value -- Meaning this value is the starting point to a rather long equation. This is a calc value mostly never a full number (ie: 6.25)

I take the mod() of the reference value number and look up that number with table 2 (ie: 6) this return a value. But this value I really need Sum of 1,2,3,4,5,6. We can call this "TotalsoFar"

Then I take the Int (,1) of the reference value (ie:.25)...but I hold this number.

Then I need to get Mod+1 (ie: 7) and I look this number up, this time I only need this number not the sum of all. I take the value from 7 and multiply it by .25 and I get a value. We can call this Number "Total2"

The last step is I add up "TotalsoFar" and "Total2" and I get my answer.

All of my look up fuctions work...I am just not very good at writing calc. so I have so many fields... but the only thing I really do not understand is the Sum of the values from the Mod ().

I know how much of a pain it is to do this over the net... but If you could explain the <= fuction a liitle more...b/c when I have tried makeing my relationship <= not =. it only returns the first value of table 2... DOes not work at all for me.

Thanks

Brahim

Than I take the Int(,1) of that

[ESpringer]

It's not a <= *function* QuinTech is mentioning, but the relationship definition (as you say you've tried).

If you ask FM for "the" value in "the" related record when there's a one-many or many-many relation, FM will always return the first value acording to sort order or creation order. But if your calc is

Sum(ChartTableInclusive::field2)

(where ChartTableInclusive is the name of your second table occurrence of the values lookup table), then it should total the values of the appropriate field of all the related records...

[QuinTech]

Hi Brahim, i think you may not be understanding the relationships i am proposing... please have a look at the attached.

J

sample.zip

[NuttyMoose]

Ok...I have the attachment...I will study this...Boy this should make it alot easier...I will write back as soon as I learn this...

Thank you

Brahim

[NuttyMoose]

Ok...The totalsofar works well...I now understand it...Thank you... I have a related question...

When I return a looked up value meaning when I type in 5 and it returns 80 is that number really there? The reason I ask such a thing I am tring to use the looked up number in a formula...My formula is correct bit it keeps returning 0.00. So I tested the cell with a simple formula that cell the number is in +1 and still nothing?

Brahim

[QuinTech]

If the number displays in the field, it is really there. If you are trying to perform mathematical operations on it in another calc field, make sure both fields are of type/result number.

J

[NuttyMoose]

Thanks...that was it...Now I have finished my very tough calc (at least for me it was). Thank you

Brahim