Jump to content

calc field on some portal lines only


GlennC
 Share

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

Recommended Posts

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.

Link to comment
Share on other sites

  • 3 weeks later...

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?

Link to comment
Share on other sites

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

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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! grin.gif

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6526 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
 Share

×
×
  • Create New...

Important Information

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