Script for portals

[Pablo Ramirez]

Hello,

I have a portal that shows only 10 records out of 100, after filtering the mother database.

In order to add,(or substract) values of a field in this portal, is it necessary a script or is there another way to do it?

In other words how do I add up the values from a field in a portal?

Thank you

Pablomac

[CobaltSky]

You can summarise portal contents by using an unstored calculation with an aggregate function (eg Sum( ), Min( ) Max( ), Count( ) etc).

The calculation should reside in the file ein which the portal is placed, and the function(s) should target portal fields via the relationship on which the portal is based.

So for example, if your portal is based on a relationship called MotherFile, and a field you want to add up is called TotalPerRecord, you could do so using a calculation with the formula:

Sum(MotherFile::TotalPerRecord)

Beyond this, aggregate functions can be incorporated into compound expressions to arrive (in a single step) at a more complex outcome. For Example:

(Sum(MotherFile::TotalPerRecord) - Sum(MotherFile::OutstandingBalance) * DailyForecast::ProfitMargin

..which can combine other values sourced from the curent file or via other relationshs.

[Pablo Ramirez]

Hello:

For some reason it does not work, it calculates the total of all the records, not just the ones that are in the portal, which is what I need.

Any other suggestions would be really appreciated.

Thank you

Pablomac

[harryk]

The outcome of Cobaltsky is correct, your ask for help needs a more specific explanation of how you set up the portal, the key used, how you filtered out your records in the portal etc.

Harryk

[Ugo DI LUCA]

Looking back to your previous post, you must understand that even if you only display 6 rows in your portal, whatever calculation you make is based on all values from the main file, related through the relationship used in this portal, not only the six values in it.

If you want to count only six values from the Main file, you need to "flag" the Main file to only show these 6 records. It's not that easy but we need to know what you are looking for. Is it still on the test files you posted some time ago ?

See my previous answer here.

[Pablo Ramirez]

Hello:

Yes, Unfortunately it is adding up all the records and not just the ones shown in the portal.

Any ideas, Please find the latest files attached.

Thank you

Pablo

[Pablo Ramirez]

I just read your post. Thanks in advance there is a lot of information, I am a bit confused right now. I'll try to read it slowly

Could you include an example of the 'flag" in my sample file?

Thank you again

Pablo

[CobaltSky]

Hello Pablo,

Unfortunately it is adding up all the records and not just the ones shown in the portal.

Well you say that.

However I downloaded your attachment and took a look at the portal layout and what I see is this:

The portal is showing three records. The 'yes' values for the three records are 22, 26 and 28 respectively. The no values for the three records are 8, 12 and 14.

Your Sum( ) calculations are corectly using formula along the lines suggested in my earlier post, and are showing the result 76 for 'yes' and 34 for 'no'.

...and since 22+26+28=76 and 8+12+14=34, I would say that they are most definitely adding up the values in the portal.

Just to be certain, I checked the master file and noted that the total of 'yes' values in the master file is 424 and the total of 'no' values is 172.

I'm therefore rather mystified by you saying that the calculations are adding up all the records and not just the ones shown in the portal?!

What do you mean?

[Ugo DI LUCA]

Pablo wants to have a sum on only the six latest values for any of his filter keys.

What he didn't understand is why whenever he had 6 records in a portal, his Sum was the result from the 10 records from the main, not only the 6 shown in his portal...I think he now gets it.

I suggested to have a "auto-flag" calcultation in the main file, that would return the Person Name in case the record belongs to the latest 6 values for this person. This result would therefore becomes the key for relationship to the Portal.

Here is what I would do, following your article (*****)(see Articles Pablo) for indexing the calc... That's why I reply to you.

If there is a simplier solution, please tell. If this happens to be the "unique" solution, do not be too confused Pablo as I could guide you to the result in your file...

Additional Fields necessary Mod Time, Serial Num, SerialPerName, nextSerialPerName, c_FlagLatest6, c_Lookupkey, t_FlagKey.

Additional Relationships : SefjoinOnNames, SelfjoinonLookupKey.

1. n_SerialPerName num field = autoentered serial N

[Pablo Ramirez]

Hello:

I included all the new fields and relationships and ... ... It does not work or better said partially works.

When the number of records that "belong" to a name (Mat, John, Pablo, Patrick) are 6 or less, the result in the total yes field is accurate. But when the number of records that "belong" to a name is more than 6 (for example Mat) it adds up all of them. The result for mat is 155 instead of 120.

Please see the attached updated files and thank you very much for being so helpful. I really appreciate your time and dedication to my questions.

Best Regards

Pablo Ramirez

[Ugo DI LUCA]

Hi Pablo,

Glad you had courage for such a work. Sorry you spent so much time.

I included all the new fields and relationships

That is what I said

I suggested to have a "auto-flag" calcultation in the main file

You actually included all calculations in the Portal Layout..., not in the Main (Master) file

But anyway there was one error in my suggestion, and one important precision (Sure Ray would have told you anyway) to be made :

c_Lookupkey = (ModTime + (SerialNum*0)

Please correct to c_Lookupkey = (ModTime*0) + SerialNum

and note that

SelfjoinonLookupKey will have at left c_Lookupkey and at right SerialNum

Now, if you want to use this method for your file (it actually works, I tested it), you need to understand that the lookup is not really "automatic". It does affect the current record only. So you will need a script from your Portal Layout to the Master, that would set the cursor into the c_Lookupkey and update all records related.

If you happen to use some plug-in, this script could be triggered through the value list into the "Portal Layout" file (if not, you could use a Pause script to make it work...)

I didn't posted the corrected file because I did make some change to your datas. But it may also help you try by yourself.

[Pablo Ramirez]

Hello

I was out for a while. Now I'm back to continue trying to solve this!!

I did all the additions and corrections but it did not work!!!

When you choose for example Mat, the sum of the

[Ugo DI LUCA]

Hi Pablo,

It appears that :

Mod Time is number field----> change to hours

Serial num is number. Set it to be autoentered serial number.

In order to see the result, you need to re-enter the records in the Main file or run a script to attach a serial for each record.

If not done, the serial per name and next serail per name cannot be calculated.

Let me know if you still don't get it.

[Pablo Ramirez]

I feel kind of embarrassed because I followed all the instructions but it still does not work. I reentered all the records as you told me and organized them as follows

Pablo 5 records Matt 6 records John 8 records

The application works fine for Pablo and Matt (<= 6 records) but does not work for John; it keeps adding up all his records and not just the ones in the portal.:

Please see the attached files with the latest corrections

Thank you again

Pablomac

[Ugo DI LUCA]

Don't feel embarassed as you are quite to the end of it; Your file was corrupted, but I looked back to the previous.

1.Mod Time = Modification Time

Set it to autoenter the Modification Time (in define fields auto enter dialog box)

2. nextserialpername is set as a multivalued field. Uncheck this box please.

3. serial per name is set to enter -1 when not found. Change to 1.

4. Index cLookup Key

That's it. Now , backup your file and reenter one at a time the 20 records of your Master file.

If it still doesn't work, move to Private section. But I'm quite sure you will see the sunshine tonight.

[Pablo Ramirez]

Here are the files with the corrections you suggested!!!

... doesn't work.

I don't know what to say!!!!!

Pablo

[Ugo DI LUCA]

Last and final touch.

Next serialperName = Max(SelfjoinOnNames::SerialperName) + 1

This way, your serial will go 1,2,3,4,5,6,7. If there is 7, it will not show 1...

[Pablo Ramirez]

Sorry to keep bothering you, but I did the corrections and... still adds up all the records instead of the 6 ones in the portal.

What am I doing wrong.. aaaaaaaaaarrrggggggggggghhhhhhhhhhhh!!!??

Pablomac

[Pablo Ramirez]

take a look a the file with all the corrections.

pablo has 8 records, the sum should be 6 but still shows 8.

Thanks for your patience

Pablo

[Ugo DI LUCA]

FlagLatest6 =TEST(Serialpername > MAX(SelfjoinOnNames::Serialpername)-6,Person,"Nothing").

You forgot the Max...

Do you happen to read the posts

Over.

Nothing

Nothing

Pablo

Pablo

Pablo

Pablo

Pablo

Pablo

Have fun with this.

[Dania]

Oh Brother! Even just reading this thread is making my eyes ache!!

[Pablo Ramirez]

Sorry for all of this, I apologize for the inconvenience but it does not work. I read all the posts and all the fields are exactly as you told me!!!

I get:

Nothing

pablo

pablo

pablo

pablo

pablo

pablo

Here are the files.

Your help is greatly appreciated and of course your patience

thanks

Pablomac

[Pablo Ramirez]

FlagLatest6 =TEST(Serialpername > MAX(SelfjoinOnNames::Serialpername)-6,Person,"Nothing").

??TEST?? or Case?

pablomac

[Ugo DI LUCA]

Sorry for TEST being Case (clipboard copy/paste from my French FM Version).

In your portal, use a new relationship to t-flagKey