Letter reminder portal

[LA Girl]

Ok guys and gals, this should be fun. I have File A which contains job records, one job per record. File B has late letters that I have sent for jobs that haven't been paid. I need a portal in File A that reminds me to send a second follow-up letter, after 30 days have elapsed and the silly people still haven't paid. I delete the letter from file B when they have paid so there aren't any letters there that don't need to be. I'll create a third file "C" for the second letter sent, but that's another story. Right now I just need a portal that shows me all the job letters that are 30 days old so I know to send a follow-up letter. I have a Menu layout in the job file "A" that I will put the "reminder portal" on but I need it to be the same on each record... I don't know if that makes sense, but I tried using the same relationship that I already have between the job and letter files and the portal shows the letters but changes when I switch between job records. I didn't set it up right, I know. Please help!!

[RalphL]

Assuming you have a date field in file B, Make a calculated date field in A = Status(CurrentDate) - 30. Make relationship in A to B where the calcualted date = the date. Make your portal using this relationship.

[comment]

I think we'd want to capture letters that are 30 days old - or older.

How about a script that replaces a field value (in with a calculated result:

Status(CurrentDate) - Date >= 30

In A, a calculation field = 1.

[LA Girl]

Hmmm, I'm not getting it. I have a field in B that calculates 30 days from the letter creation date. This works great. I get a "due date" that tells me when that letter comes due. Now, how do I link that to file A and create a portal that will only show me the letters with the "due date" being today's date or before today (and not the ones due tomorrow and thereafter)?

[LA Girl]

Oh, and not all the records in file A contain a direct match to a record in file B (because not all the jobs have a late letter). So, I created a new layout in file A to display the portal that contains all the letters dues, not just the one that would pertain to that record. So, no matter which record in A that I'm on, if I click to the portal layout, it will show all the letters due and show the same ones no matter which record I'm on in A. Hope that makes sense. If there's another way, please let me know

[comment]

You can't link to any of the two date fields directly for this, because you need a relationship based on inequality (less than or equal to today). This can be done in v.7, BTW.

So you need another field in B that will translate the inequality into a Boolean result: 1 if true, 0 if false. Let's call this field Due.

You can make Due a calculation field =

DueDate >= Status(CurrentDate)

Or, you can bypass the interim DueDate field, and arrive at the same result by:

Status(CurrentDate) - CreationDate >= 30

So now we have all the records where this is true marked by 1 in the Due field.

in A, we will have a field that is always 1, let's call it ShowDue. ShowDue can be a calculation field = 1, or a global number field with a value of 1.

The relationship ShowDue = Due will show records for which Due is true.

---

Addendum (sorry, I completely forgot the main issue)

The relationship ShowDue = Due as descibed above will not work, because Due has to be an unstored calculation. So we need to make Due a number field, populated by a script.

Since you have created a special layout for this purpose, you can make this entirely transparent to the user by attaching everything to the same button:

Freeze Window

Perform Script[External:"B"]

Go to Layout ["View Due"]

Exit Record/Request

The external script in B:

Go to Layout [sOME LAYOUT WITH DUE FIELD]

Replace [No dialog, "Due", "Status(CurrentDate) - Date >= 30"]

[Ender]

Calcs that use Status functions, like Status(CurrentDate) will not work as match fields for a relationship. In FM5/6, there is a cool technique for building this type of range relationship, called Smart Ranges. You can check it out here:

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

Although the technique is pretty tricky, you can try to adapt the examples to your solution (see the examples on the 1st edition page.)

[comment]

Can't you wait until I am finished editing?

IMHO. Smart Ranges is a bit of an overkill for this.

[Ender]

Can't you wait until I am finished editing?

Hey, you gotta be quick around here.

IMHO. Smart Ranges is a bit of an overkill for this.

From the immortal words of Steven Blackwell: "When hunting ducks, go where the ducks are."

[LA Girl]

what? So, which one should I use?

[Ender]

For a small number of related records (under 1000,) the scripted replace may be fine, but for larger data sets, this can be too slow.

The Smart Ranges technique does not require scripts at all, so relationships based on Smart Ranges are very fast opening up. But figuring out the calcs can be daunting.

[Fenton]

If you were using 7 this would be easy. But since you're not, you may be able to use an old free plug-in, by FileMaker's David McKee, Ranges. It is easier to figure out than the Smart Ranges calculations. It hasn't been updated for yonks; but for Windows 2000 it should be fine.

http://www.geocities.com/SiliconValley/Network/9327/plPLUGs.html

[LA Girl]

My file will definately have way less than 1000... probalby less than 200. I downloaded the plug-in but all that happened when i clicked on it is FM opened like I was creating a new document. So, I tried to do the first suggestion about the Due field and the script. I don't get the script. I don't want any buttons, just the portal to show the right records. I understand that the relationship can't have a field with a Status calculation but why the script?

[comment]

The script makes the Status calculation and stores the results in the Due field, so that you CAN link to it.

If you don't want a button, you can run the script when opening the file. If it were me, I'd make a button that says "Show Due" and it would take me to the correct layout AND run the script at the same time.