Last Occurrence in Portal

[chikanyc]

Hi I'm trying to create a calculation field that will give me the LAST Occurrence of a portal.

I have two databases: (1)Products; (2) Invoices (and within, another Invoice Line Items Table). When I look at a Product, I want to have a field that gives me the last date it was bought.

I would like to do this with a calculation rather than setting up another portal table showing just one row on a descending sort. Does anyone have suggestions?

Thanks so much!

[Fenton]

Last ( portal relationship::date field )

Unstored calculation field.

[chikanyc]

Thanks so much! It worked.

[color:purple]Had another question...What if I wanted a calculation based on TWO FIELDS in the Portal?

e.g. Last Date based on Product and Type of Purchase (Business/Consumer). Type of Purchase is already its own field in the Invoice Database.

Thanks:)

[Fenton]

One way to approach this is to create calculation fields in the table where the data is.

_cDate_business = Case ( Type = "business"; Date )

_cDate_consumer = Case ( Type = "consumer"; Date )

Then target those:

Last ( relationship:_cDate_business )

Last ( relationship::_cDate_consumer )

An alternate method is to create constants in the table where you're looking FROM. These have a Text result, and can/should be Unstored,

_cBusiness_txt = "Business"

_cConsumer_txt = "Consumer"

Use those as the originating field of relationships, to the Type field in the table with the data.

Either method works. The relational method is better if you want other data from the relationship. It also does not add any extra data to the file, as the fields are unstored. But that's not a big consideration in this case, the only data added by the 1st method is a couple of dates (I suppose you could even make those dates unstored, but I wouldn't).

Edited March 25, 2008 by Guest

[chikanyc]

Thx again Fenton. However, I was having trouble with the relationship method that you had recommended (the 2nd method).

I created the two new fields in the Product table and created the constant relationship to the Type field in the Invoice table. After this, how do i set up the last date field?

[Fenton]

The same as the general one that was not based on Type, but using the filtered relationship. I don't know what you called the relationships, so I'll improvise:

Last ( prod_InvLines~cBusiness_Type::date field )

Last ( prod_InvLines~cCustomer_Type::date field )

You can just target the regular date field, because you've already filtered the relationship to only that type.

[chikanyc]

thank you. i will try it!