GlennC Posted February 6, 2004 Share Posted February 6, 2004 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? Link to comment Share on other sites More sharing options...
CyborgSam Posted February 7, 2004 Share Posted February 7, 2004 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? Link to comment Share on other sites More sharing options...
GlennC Posted February 8, 2004 Author Share Posted February 8, 2004 Thanks CyborgSam. Link to comment Share on other sites More sharing options...
GlennC Posted February 12, 2004 Author Share Posted February 12, 2004 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? Link to comment Share on other sites More sharing options...
GlennC Posted February 14, 2004 Author Share Posted February 14, 2004 Any further suggestions? Link to comment Share on other sites More sharing options...
BobWeaver Posted February 15, 2004 Share Posted February 15, 2004 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 More sharing options...
GlennC Posted February 18, 2004 Author Share Posted February 18, 2004 Thanks very much Bob, I'll have a go at it. Link to comment Share on other sites More sharing options...
GlennC Posted March 11, 2004 Author Share Posted March 11, 2004 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 More sharing options...
BobWeaver Posted March 12, 2004 Share Posted March 12, 2004 Here is a sample file. Sample.zip Link to comment Share on other sites More sharing options...
CyborgSam Posted March 12, 2004 Share Posted March 12, 2004 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 Link to comment Share on other sites More sharing options...
BobWeaver Posted March 12, 2004 Share Posted March 12, 2004 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 More sharing options...
GlennC Posted March 12, 2004 Author Share Posted March 12, 2004 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! Link to comment Share on other sites More sharing options...
BobWeaver Posted March 12, 2004 Share Posted March 12, 2004 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 More sharing options...
GlennC Posted March 14, 2004 Author Share Posted March 14, 2004 Aha ... makes sense, and very crafty! Thanks again. Link to comment Share on other sites More sharing options...
BobWeaver Posted March 14, 2004 Share Posted March 14, 2004 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 More sharing options...
Recommended Posts
This topic is 7347 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 accountSign in
Already have an account? Sign in here.
Sign In Now