Filtering records in a portal?

[Gray Fox]

Greetings,

I have several years worth of records in a database. I have a self join that allows me to browse and get summary data based on the "category" field.

Unfortunately the portal brings back several years worth of data.

Is it possible to set up a portal so that the records match the "category" *and* satisfy a time constraint ( say,for example, for the current year...). Each of the records contain a "date" field.

Thanks,

Gray Fox

[andygaunt]

Hi,

To set your portal up to do what you wish you will need the following.

1 x global field ( _gCategory )[text]

1 x global field ( _gYear ) [text]

1 x calculation field (_cCategoryPlusYear)

Calc is.

Category & ":" & Year(Date) & " " &

"ALL:" & Year(Date) & " " &

Category & ":ALL" & " " &

"ALL:ALL"

Result of text

1 x Calculation field (_cPortalFilter)

calc is.

_gCategory & ":" & _gYear.

Result of text.

So, now setup your relationship (CATEGORYPORTAL)from _CPortalFilter :: _cCategoryPlusYear

Now, add your portal and the two globals above it.

Now, set the category global to a category and the year global to a year and the portal will filter for you.

Change the year to ALL and you will get that category over all time.

Change the Category to ALL and the year to this year and you get all records this year.

Change Category to ALL and Year to ALL and you get all records.

You can obviously have a script setup in the portal on the category field. When clicked it could

Set Field (_gCategory, CATEGORYPORTAL::Category)

Exit Record

Add another script for a show all categories.

Set Field (_gCategory, "ALL")

Exit Record

This could then set the category to whatever you click on.

HTH

[Gray Fox]

Thanks Andy,

Does

Category & ":" & Year(Date) & " ? " &

"ALL:" & Year(Date) & " ? " &

Category & ":ALL" & " ? " &

"ALL:ALL"

represent a calculation that matches

the concatenation of the category and year of the date field ( plus

OR

"All" and Year of datefield

Or

category and "All"

Or

"All" : "All"

(the browser shows the above question mark as

a "paragraph" like symbol.

I guess that a month at a time query would involve a bit more of a calculation, but I get the basic idea.

I gotta go back and RTFM the manual again, I did not realize that one could associate a script with clicking on a field...

Thanks for the tip

Gray Fox

[andygaunt]

Hi,

The ? is a paragraph return symbol, located next to the operators in the calculation window.

If you want month as well, and you want this as well as year,then create a new global field _gmonth (text) then it is no problem.

Category & ":" & Month(Date) & ":" & Year(Date) & " " &

"ALL:" & Month(Date) & ":" Year(Date) & " " &

"ALL:" & "ALL" & ":" Year(Date) & " " &

Category & ":ALL" & Year(Date) & " " &

Category & ":ALL" & ":" & ALL" & " " &

"ALL:ALL:ALL"

Also change the second calc to _gCategory & ":" & _gMonth & ":" & _gYear

As to associating a script with a field. You can put a script on any object on the layout. Just select and then choose format > specify button.

HTH