calc field on some portal lines only

[GlennC]

Hello,

Is it possible to have a calc field the calculates only on defined lines of a portal. eg a 'sum' calc for only the top three records in the portal?

[CyborgSam]

One way you could do this is is to define the calc field as:

If( Status(CurrentPortalRow) <= 3,

Value_To_Sum,

0

)

Other folks have a more efficient way?

[GlennC]

Thanks CyborgSam.

[GlennC]

Actually, this does not seem to work. It gives the total of all records in the portal, not just the first 3 rows. Any further suggestions?

[GlennC]

Any further suggestions?

[BobWeaver]

Actually the function Status(CurrentPortalRow) won't work in this case. You need to use the Status(CurrentRecordNumber) function. Set up an unstored calculated field cFirst3Rows with the formula:

(Status(CurrentRecordNumber)<=3) * MyNumberField

where MyNumberField is the portal field you want to total. The field cFirst3Rows doesn't need to be in the portal.

Now create another calculation field cSum123 with the formula:

Sum(PortalRelation::cFirst3Rows)

This field will give you the total of the first 3 rows.

[GlennC]

Thanks very much Bob, I'll have a go at it.

[GlennC]

I can't make this work. Having record number <=3 gives me three records of the database, not three lines of the portal for each record. And the '*'as in '*MyNumber field' - is this multiplication? What does that have to do with it?

At the moment I want to find the max value of a field from the first (top) three lines of the portal in each record, and I'm going nowhere. Why is there not a simple means of addressing a particular line of a portal?

[BobWeaver]

Here is a sample file.

Sample.zip

[CyborgSam]

Glenn-> My apologies for screwing up using CurrentPortalRow, I brain farted...

Bob-> Thanks for jumping in and getting Glenn a solution after I wasted his time.

Glenn-> The reason your Status(CurrentRecordnumber) didn't return the number of the portal row is that this calculation has to be unstored so it will recalculate using the portal's sort order. In the calc's options, check "Do not store calculation results -- calculate only when needed" in the Storage Options dialog.

Sam-> Answer those spam ads to counter senility

[BobWeaver]

This is one of those things that shouldn't work but it does. As you noted, the calculation must be unstored.

Status(CurrentRecordNumber) (unstored) works in the context of a found set. When it is accessed via a relationship, the found set is all records that match the key field. Those matching records are then numbered accordingly.

[GlennC]

OK, got it up and running, thanks Bob and Sam.

Bob, appreciate you going to the trouble of the workout for me - thanks! As well as storing the calc I also had it in the wrong file, but now all is good. And the explanations mean not only does it work, but I also have some clue as to why - always a bonus. (I still don't get the use of '*', (multiplication??), but hey, mystery is fine if it's repeatable).

Sam - helping someone out is never wasting their time. Let us know how those spam ads work out!

[BobWeaver]

No problem doing the example file. I actually had to test it out before I posted the original method anyway, just to make sure it would work.

As for the multiplication, here's what's happening:

(Status(CurrentRecord)<=3) will produce a boolean result, either 0 or 1. If the current record number is 3 or less, the result is 1; otherwise it's 0. Now, when you multiply that by the number you want to total, you will get the number itself for records 1, 2, and 3; and zero for all the others. So, now when you take the sum of this field, you are really summing all records, but only the first 3 have nonzero values. So, you get a total for records 1, 2 and 3 only.

[GlennC]

Aha ... makes sense, and very crafty!

Thanks again.

[BobWeaver]

The following Case function would accomplish the same thing and is perhaps a bit easier to follow:

Case(Status(CurrentRecord)<=3,MyNumberField,0)

Because of execution speed concerns, I generally stay away from Case functions if there is a simple math operation that will do the same thing.