Help with better definition

[snekkis]

I have a lot of fields which I want a total of in a separate field (of course). I have made a very backward definition, I believe, which I would like to be better. But this is the best I can come up with.

The main fields are WeightlossDay, WeightlossDay Copy, WeightlossDay Copy2, WeightlossDay Copy3, ...

From the weightloss per day, the total weightloss should appear from an addition calculationfield that has added the subtraction calculationfields I have made for these mainfields.

SubtractDay = WeightlossDay - WeightlossDay Copy

SubtractDay Copy = WeightlossDayCopy - WeightlossDay Copy2

SubtractDay Copy2 = WeightlossDayCopy2 - WeightlossDay Copy3

Total Weightloss = SubtractDay + SubtractDay Copy + SubtractDay Copy2

The thing is with solution, is that the user is forced to put in a value in each Weightloss field, or else it will subtract with zero and you get a negative value in a SubtractDay field and the total weightloss field.

So, I'm looking for a better way of doing this, specially that the user can skip a day or two, or more, and the total field would be right.

Thanks,

Snekkis

[Lee Smith]

Wouldn't your calculation be correct by subtracting the day 1 from day 2?

i.e. SubtractDay = WeightlossDay Copy - WeightlossDay

SubtractDay Copy = WeightlossDayCopy 2- WeightlossDay Copy

SubtractDay Copy2 = WeightlossDayCopy3 - WeightlossDay Copy2

HTH

Lee

[snekkis]

I'm sorry, I did not bring the whole definition. I forgot that I had a If() formula to prevent to subtract with ziro, also.

But Lee, I tried yours but that gives negative value if we assume the user inputs decreased values for each day.

I have enclosed a file that explains what I have said before included the If() formula.

If you exclude one day you see what I mean.

Snekkis

[Ugo DI LUCA]

I got a "file corrupted" when I tried to download...

but I would "try" to use a selfjoin, then have a Calc of type

Case (Record_ID = Max(seljoin::Record_ID), Current Record, Current record - Next record)....

Sorry bad answer. More probably :

Case(yournextfield = 0, Currentfieldvalue, Currentfieldvalue-Nextfieldvalue)

I'll think of this...

[Ugo DI LUCA]

SubtractDay = WeightlossDay - WeightlossDay Copy

SubtractDay Copy = WeightlossDayCopy - WeightlossDay Copy2

SubtractDay Copy2 = WeightlossDayCopy2 - WeightlossDay Copy3

Total Weightloss = SubtractDay + SubtractDay Copy + SubtractDay Copy2

SubtractDay = Case(WeightlossDayCopy = 0, WeightlossDay, WeightlossDay - WeightlossDay Copy)

SubtractDayCopy = Case(WeightlossDayCopy2 = 0, WeightlossDayCopy, WeightlossDayCopy - WeightlossDay Copy2)

SubtractDayCopy2 = Case(WeightlossDayCopy3 = 0, WeightlossDayCopy2, WeightlossDayCopy2 - WeightlossDay Copy3)

Total Weightloss = SubtractDay + SubtractDay Copy + SubtractDay Copy2

[Vaughan]

A relational design would have been better. Each "day" appears in a portal and you can have as many or as few days as you want.

[snekkis]

Hi Vaughan,

I did not actually think of a portal. It doesn't fit so well with the overall design, and I don't quit understand how your function should execute either.

Ugo, your formula did not work in my FM6 Dev., and I am kind of confused that it should too. I only use my FM manual and it says that your Case() parameters does not have a result2 Case(test1, result1, test2). Actually, I don't know what I say is right, but I would like to try and see if your defenition works. That is what counts.

Anyway, if you managed to look on the file I had attached, maybe something can be done with the If () parameters instead. Look on this zip enclosed instead, or go to http://www.transpress.info/test/Weightlosstest.fp5.sit

Or, this is how it actually looks:

SubtractDay = If( not IsEmpty(WeightlossDay Copy), WeightlossDay - WeightlossDay Copy, 0)

SubtractDay Copy = If( not IsEmpty(WeightlossDay Copy2), WeightlossDay Copy - WeightlossDay Copy2, 0)

SubtractDay Copy2 = If( not IsEmpty(WeightlossDay Copy3), WeightlossDay Copy2 - WeightlossDay Copy3, 0)

SubtractDay Copy3 = If( not IsEmpty(WeightlossDay Copy4), WeightlossDay Copy3 - WeightlossDay Copy4, 0)

Snekkis

Weightlosstest.fp5.zip

[djgogi]

The calc for total:

WeightlossDay-

Case(

not IsEmpty(WeightlossDay Copy6), WeightlossDay Copy6,

not IsEmpty(WeightlossDay Copy5), WeightlossDay Copy5,

not IsEmpty(WeightlossDay Copy4), WeightlossDay Copy4,

not IsEmpty(WeightlossDay Copy3), WeightlossDay Copy3,

not IsEmpty(WeightlossDay Copy2), WeightlossDay Copy2,

not IsEmpty(WeightlossDay Copy), WeightlossDay Copy,

WeightlossDay

)

Dj

[snekkis]

Ahh!

Splendid, Dj!

[snekkis]

Ouch!

Please, help again!

The stupid Case() parameter will not take all my test,result parameters. I have up to 168 days. I get the dialog ...to many seperators... after about 40 lines. If delete the other 128, it works.

What will I do now? I feel like I'm just running round and around now.

[djgogi]

I would reconsider the Vaughan's advice, on make it relational.

Any way if you really don't want it then you'll need to split the calculation this way:

part1=

Case(

not IsEmpty(WeightlossDay Copy168), WeightlossDay Copy168,

not IsEmpty(WeightlossDay Copy167), WeightlossDay Copy167,

not IsEmpty(WeightlossDay Copy166), WeightlossDay Copy166,

.....

not IsEmpty(WeightlossDay Copy153), WeightlossDay Copy153,

not IsEmpty(WeightlossDay Copy152), WeightlossDay Copy152,

not IsEmpty(WeightlossDay Copy151), WeightlossDay Copy151,

WeightlossDay Copy150

)

part2=

Case(

not IsEmpty(WeightlossDay Copy149), WeightlossDay Copy149,

not IsEmpty(WeightlossDay Copy148), WeightlossDay Copy148,

not IsEmpty(WeightlossDay Copy147), WeightlossDay Copy149,

.....

not IsEmpty(WeightlossDay Copy123), WeightlossDay Copy123,

not IsEmpty(WeightlossDay Copy122), WeightlossDay Copy122,

not IsEmpty(WeightlossDay Copy121), WeightlossDay Copy121,

WeightlossDay Copy120

)

...

...

...

Partn=

Case(

not IsEmpty(WeightlossDay Copy29), WeightlossDay Copy29,

not IsEmpty(WeightlossDay Copy28), WeightlossDay Copy28,

not IsEmpty(WeightlossDay Copy27), WeightlossDay Copy27,

.....

not IsEmpty(WeightlossDay Copy4), WeightlossDay Copy4,

not IsEmpty(WeightlossDay Copy3), WeightlossDay Copy3,

not IsEmpty(WeightlossDay Copy2), WeightlossDay Copy2,

WeightlossDay

)

and finally

total=WeightlossDay-

Case(

not IsEmpty(Part1), Part1,

not IsEmpty(Part2), Part2,

...

...

not IsEmpty(Part(n-1), Part(n-1),

Partn

)

Dj

[Ugo DI LUCA]

Hi Snekkis,

I finally understood what you were doing. Of course, the Case calc I gave couldn't work.

Making it relational would be the better way for you.

[snekkis]

OK, all of you suggest the same thing now. No need to argue then, except I have no clue what a relational function will be like. The case() formula worked just fine, except its limitation.

I managed to nest all days in two part, and I wondered how the total would be like.

Dj, I have no clue what the last parameter was either, but if I could bug you once more, how would the total look like with my two parts?

WpartDay =

Case(

not IsEmpty(WeightlossDay Copy167), WeightlossDay Copy167,

...

not IsEmpty(WeightlossDay Copy81), WeightlossDay Copy81,

WeightlossDay Copy80

)

WpartDay Copy =

Case(

not IsEmpty(WeightlossDay Copy79), WeightlossDay Copy79,

...

not IsEmpty(WeightlossDay Copy), WeightlossDay Copy,

WeightlossDay

)

Total WeightlossDay =

WeightlossDay -

Case(

not IsEmpty(WpartDay), WpartDay,

not IsEmpty(WpartDay Copy), WpartDay Copy,

:

)

[djgogi]

It is simply the last chunk of data.

No need to test if it's empty, So in your case it should state:

Total WeightlossDay =

WeightlossDay -

Case(

not IsEmpty(WpartDay), WpartDay,

WpartDay Copy

)

Dj

[snekkis]

Perfect!

Thanks again, and to all of you.

Snekkis