Jump to content

Need Help with adding Values

This topic is 7101 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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


Link to comment
Share on other sites

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... smile.gif 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.



Link to comment
Share on other sites

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


Link to comment
Share on other sites

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

Link to comment
Share on other sites

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... frown.gif 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.



Than I take the Int(,1) of that

Link to comment
Share on other sites

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


(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...

Link to comment
Share on other sites

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?


Link to comment
Share on other sites

This topic is 7101 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.