Another Date Range Script with popup

[ddreese]

I found this thread, but it didn't really seem to answer my questions:

http://www.fmforums.com/threads/showflat.php?Cat=&Board=UBB15&Number=59835&Forum=All_Forums&Words=Date%20Range&Match=Entire%20Phrase&Searchpage=0&Limit=25&Old=1week&Main=59364&Search=true#Post59835

I have a 'contact' file that summarized a lot of the information from many different files. I have a casenote layout in my contact file that has a portal with a reverse sort showing the most recent casenote made. I have a button up top that's for 'view/print range'. A popup comes up that prompts them for a start date and end date, both these are stored in the contact file itself.

The script on the contact side looks like:

- Set Field ["Casenote Date From"]

- Set Field ["Casenote Date To"]

- Show Custom Dialog [......]

- Go to Related Record [show, "Casenote"]

- Perform external script

Script in casenote:

- Goto Layout ["View/Print Range"]

- Enter Find Mode

- Set Field ["Date", "Contact::Casenote Date From" & ".." & "Contact::Casenote Date To"]

- Perform Find [Replace]

- Enter Browse Mode

I originally had the "Date" field in the casenote set as a Date type, but I couldn't put the "Contact::Casenote Date From" & ".." & "Contact::Casenote Date To" in it, because it wouldn't take the extra data. It's now a text field, and it doesn't find any results.

[LiveOak]

The Set Field step will not work for a date range find. What you need is a layout with the date field appearing on it and the step:

Insert Calculated Result["MyDateField", "DateToText(Constant::Casenote Date From) & "..." & DateToText(Constant::Case Note Date To)"]

Case Note Date From and Case Note Date To should be global date fields. The relationship used should be a Constant = 1 to Constant = 1 relationship and should only be used to pass global fields.

-bd

[Ugo DI LUCA]

Another method you could use if you often script/perform the same find is having your set of 2 date global fields in your Main file and create a calculation field on your related file

matchdatecalc using a constant relationship = Case(d_date<=::g_end and d_date>=::g_start;1;0).

Then trigger a script in your related file that will only find for matchdatecalc = 1.

Find mode

Insert calculated result (matchdatecalc,1)

Perform Find.

Of course, this method also works on the file itself, with no relationship at all.

[ddreese]

Thanks, it works now. I have an awful time grasping what exactly a global does and when I need to use one.

[ddreese]

Instead of creating a new thread, I thought I'd pop my next question in here. I'm having problems displaying the correct casenotes.

The date range search works fine. It's just it's not showing the records related to the current case. It will show all casenotes from all cases in that period of time.

I sorta know what the problem is. When I enter find mode, I lose the record set that's related to the current file, because it searches all records in find mode. I tried to mess around with all the 'restore find requests' and stuff like that, but I couldn't get a combination that works. Any help is appreciated )

[Ugo DI LUCA]

Hi Dreese,

So in your contact file, you are looking for any note for Mr DREESE that was created after g_startdate and before g_enddate.

There are several ways I think you could achieve this result.

A. Use the same portal "structure" with same relationship and sort it by

1.Matchdatecalc - descending

2.Notedate - descending.

This will lead to have all the notes sent within the date range for Mr DREESE appear in first in the portal, from last to first.

B. Use a container and a calculation to highlight each record with Matchdatecalc = 1 and use the same portal "structure".

In these 2 cases, you will draw a new portal next to the other one or in another layout (with a go to layout script).

C. Create a d_datestart and a d_dateend field in your related file (date fields, not global fields).

Change the matchdatecalc to Case(d_date<=d_datestart and d_date>=d_dateend;1;0).

Keep your g_startdate and g_enddate in the Main file and the constant relationship to pass the globals, and a script :

Goto related records (Customer:Customer_ID::Notes:Customer_ID) - (Show only).

External script

Go to layout X ###Make sure d_startdate and d_enddate are in this layout.

Set field (d_startdate, constant::g_startdate)

Replace (no dialog)

Set field (d_enddate, constant::g_enddate)

Replace (no dialog)

Back to Main

Go to Layout "Matchdate Layout".

This layout will hold a new portal with relationship Customer:constantnum (1):Notes:matchdatecalc.

Matchdatecalc is a stored result now, so you can only see all related notes for DREESE within the date range in the portal.

When you will leave the layout, trigger a script :

External script

Goto related record

Set field (d_startdate, "")

Replace (no dialog)

Set field (d_enddate, "")

Replace (no dialog)

Back to Main

Go to Layout "Your other layout".

As you understand, this last script is very, very, very important, and must not be forgotten as it guarantees the accuracy of your next search.