Jump to content

Filtering records in a portal?

Gray Fox

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

Recommended Posts

  • Newbies


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.


Gray Fox

Link to comment
Share on other sites


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" & " " &


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.


Link to comment
Share on other sites

  • Newbies

Thanks Andy,


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

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

Category & ":ALL" & " ? " &


represent a calculation that matches

the concatenation of the category and year of the date field ( plus smile.gif


"All" and Year of datefield


category and "All"


"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

Link to comment
Share on other sites


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" & " " &


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.


Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

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