Jump to content

Letter reminder portal


LA Girl

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

Recommended Posts

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!!

Link to comment
Share on other sites

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)?

Link to comment
Share on other sites

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 smile.gif

Link to comment
Share on other sites

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)B)

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

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6979 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.