filtering portals by past/future dates

[puterco]

I have a customers DB and a tasks DB which are related (each customer has a unique ID). I've created layouts for past tasks and future tasks. I have portals to "tasks DB" in both layouts and I want to filter the portal to show "*today" tasks in the other layout and "<today" in the other. Can I use "*" or "<" operators to filter portals?

What do I need to filter my portal as described above?

[Ocean West]

Use a multikey...

In your senerio, you have a record with a DATE field.

You would like to show a portal with all the dates for "Today" that is the easy one... A relation ship with gDate = Date should produce that when you fill in the Global Field gDate with some date.

But what about a range.... When you want to show from today forward... or today back to a certain date..

The very easiest way is with the ranges plugin. You create a range

of dates and set them in the gDate

Or you can use a looping script. In any event you will get a list of all dates between now and some date in the future... or some date in the past to today...

example...

730706

730707

730708

730709

730710

730711

730712

730713

730714

730715

This is the numerical equivelent of dates from 8/9 to 8/18

Put this data into a global text field and match it to a calculated text field in a relationship. You should see only the dates you want.

Now you could take it much further...

If you want to filter your list not only by date but say by some other status field or other data you can append information in the index

7307061801

730706 = the date

18 = the user id

2= status (0 for incomplete 1 for partial, 2 for complete,

then for the match field in the relationship you would create

a field that would be:

code:

---

cMatchField:  (Result Text) 
date&"000

[Ocean West]

Perhaps I want to see all complete users between

8/15/01 and 8/18/01 my global would look like this...

730712002

730713002

730714002

730715002

Or perhaps I want to see everything for user #18 between the same date range...

730712180

730713180

730714180

730715180

Or I want to see everything in that range...

730712000

730713000

730714000

730715000

The possibilites are endless...

[puterco]

Thanks for your reply. Since my range varies depending on the record (tasks) I really need a script to set the global fields content. The main DB has records of different clients with a portal to tasksDB where I store tasks relating to that client.

I want to see all the future tasks for a specific company (customerID) (> or = today). I can't input the date range manually since it could be years. What I need is a script that would define the globalfield on the mainDB. In addition to the customerID, I've set two fields (today) and (end date, which is a MAX calculated value from tasksDB "tasks_date" field) to filter the portal. Unfortunately my scripting skills are a bit limited when it comes to looping scripts. How do I create a script that would automatically calculate the range from (start date) to (end date) and set the global field to match the result.

Thanks a lot, You've already been a great help...

[Ocean West]

I would urge you to look at using a plugin for this task there are a few flavors out there. That are reasonably priced, David McKee offers a free version of Ranges Toolbox you can get it here.

It makes this task very easy. Do you have tasks that go beyond a decade? If not you will be fine. In most cases the only limitation is I believe 64k of data in a single field. If you are sparing on how many didigts you have in your index you may be able to span as many years as you need.

[Ocean West]

You would set your globals:

gClient = "The ID of the record your on"

gStart = Today

gEnd = Max(YourRelationship::TaskDate)

gStatus = "which ever stats you are looking for"

Your Match field would be:

(Result Text)

code:

---

MatchFieldA

External("rgTx-NumberRange", 

gClient & "|" & gStartDate & "|1|" & gEndDate & "|" & gStatus)

---