Limit portals to date range

[Jason Goldsmith]

Is it possible to limit a portal to display fields from related records that were created after a particular date?

In other words, I have one file which creates a record for each patient visit. In a separate file, I have portals that relate back to the patient visit file. I would like to only show patient visit data in the portals after a particular date.

Thanks.

[chemparrot]

It doesn't look like anyone else has answered this, so I'll give it a try.

In your visits file, set up a "cutoff date" field to store your cutoff date. Then add a calculated field (call it "datekey") that gives "1" as a result if the visit date is after (or on/after, depending on your criteria) the cutoff date.

In your related file, set up a global field (you could call it "visitdatelink" or something like that) and set it to "1". Then, make your portal in the related file based on a relationship between visitdatelink and datekey. That should only show visits for which datekey = 1.

Anyone have something to add? I didn't test this first, and date calcs can sometimes get tricky in FM

Cheers,

Jennie

[LaRetta]

Welcome to the Forum Jennie!

It looks fine to me. I would clarify that the calculation in the Visits db needs to have index ON.

And you said, "In your related file, set up a global field." That's a bit misleading. What you meant was in the 'other' file (the Main db), of course. Related files are usually considered the right or child db. A global can't be in a related db because it can't be indexed. And your explanation did explain it. I just didn't want anyone to misinterpret that part of it - using a global as a key in a 'related' file.

Again welcome!

LaRetta

[CobaltSky]

Hello Jason,

Range-based relationships are certainly possible, but I'm afraid they don't work quite in the way that Jennie is hoping.

That is because the date that the calculations in the related file are referencing (in order to return a '1') must either be entered separately into a field in each record in the related file by a scripted 'loop' or 'replace' procedure or some similar method - which is very inefficient - or else must be placed in a global field. However if a global field is used, then the calculations which reference it cannot be stored and therefore cannot be indexed - and only indexed fields may be used as the target key field of a relationship.

Because of these issues you would be better placed to use a technique which is based on listing multiple values in a key field in your main file (a 'mulikey' field). There are several ways to go about setting that up. Some examples may be found at:

http://www.nightwing.com.au/FileMaker/demos5.html#d5

and

http://www.onegasoft.com/tools/smartranges/

Each of the techniques that are described/demonstrated at the above links uses a different principles and which approach is best for your current requirement depends on several factors such as:

1. How many records are in your visits file (or are likely to be in the future)

2. What span of dates are you likely to want displayed in your portal

3. What kind of interface you want to present users (to specify the 'from' date)

4. How comfortable you are with challenging scripts or calculations

However hopefully the above links will give you some ideas to work with. If you're interested, you'll also find a few sample files floating around on this forum that show still other alternative range techniques.

[allanc]

Note: this reply for CobaltSky

Hi CobaltSky,

I have downloaded the 'Formula Library' file from the site

http://www.onegasoft.com/tools/smartranges/. My objective is to summarize different fields in the portal based on date range (using gDateStart and gDateEnd). I was successful in creating a working portal that i wanted. But after continually working with the db, i encountered something weird. The portal was not working anymore. I don't have a clue what went wrong.

Have you had this similiar problem? I have attached the 2 db i am using for the portal. Any help will be appreciated.

Thanks.

sample.zip

[-Queue-]

It would appear that there are no records in Attendance that fall within the ranges calculated in the Payroll file. Check the match key field data in Attendance and you'll see that for yourself.

[Vaughan]

Queue -- yes, that's happened to me too. I usually have to pull the whole thing apart though before I realise the problem.

[-Queue-]

It's quite often those stupid mistakes or 'overlookings' that really screw things up for me for days at a time. And then the hand comes down firmly against my forehead when the inevitable DUH! moment arrives.

[allanc]

My understanding with the documentation that comes with the downloadable file is if i have generated in my left-side relationship matchkey containing numbers such as 0731509, 073151, 0731520, the number 073151 will cover the series 0731510...0731519 in the right-side. Of course, i may have had a stupid understanding.

Still confused with the portal relationship

[djgogi]

There are 2 errors:

First

in Attendance db the right key formula should be:

EmployeeID & " "& Right("000000" & LogDate, 7) & "

[ESpringer]

CobaltSky and others: I know the original post was a long time ago, but on Jason's original problem just for kicks: why not make clear that if all you want is RECENT records (rather than an arbitrary date range), and if the user doesn't need to be changing interval time "on the fly" then a calculation in the related file can do it by c_WithinSixMo = If(Today - VisitDate < 184,1,""). That can be indexed, no? Then, a relationship between a constant field of 1 in the master file and the c_WithinSixMo flag in the related file neatly pulls up visit records from within the past six months, and something similar could work to find everyone whose birthday makes them less than 21, etc. -- or for that matter everyone OVER 21 using a greater-than calculation -- without a complex range key. The stuff on date ranges is very intriguing! But since the original question was simpler than the subject line suggested, and someone newly browsing this thread might have a simple problem...

[Lee Smith]

Hi dj,

I made your recommended changes to the file submitted by allanc, but nothing appears in the portal unless the second date is change to a date within October. In other words, 10/31/2003 or less. Is that the way it is suppose to work?

Lee

[djgogi]

I don't recall the original Mikhail's formula.

I was simply saying that some important parts were missing and that prefixes (suffixes) should be separated with space from the proper range calculation.

The exact formula for right key (without prefixes) is

Right("000000" & Date, 7) & "

[Ugo DI LUCA]

Hey,

why not change your calc result to num instead of text....

Double Duh ?

[CobaltSky]

Ugo DI LUCA said:

why not change your calc result to num instead of text...

Fair question... to which the answer is that number fields do not support multi-line values, whereas range keys, by their nature, are multi-key fields which depend upon placing different values on successive lines.

[allanc]

Hi dj,

The space betweeen EmployeeID and calc did not matter actually but fixing the right-hand matchkey based on your recommended changes (as written below) produces the right result.

Right("000000" & LogDate, 7) & "

[Ugo DI LUCA]

Ray,

I know that.

Actually, i spent a couple of hours (hmm...honestly more than that) struggling with smart ranges and has it succesfully implemented

BUT curiously, by a "fair" attitude, I changed the result from text to number in the attachment on this thread.

And all came up as expected.

That's my DUH, which would better have been a DUH

[Ugo DI LUCA]

So I went back and entered a few range with the calcs as numbers.

Entering 10/25/2003 as as start date produces accurate results, but 09/25/2003 won't....

Still curious it works half way, would have expected it to always fail

[BobWeaver]

As I recall, a number field is limited to 255 characters. Your range result may have gone beyond that limit and got truncated.

[Vaughan]

A good reason for making TEXT key fields .