scripting reports

[Tmonk]

I hope I'm in the right forum. I need to specify criteria before a report is run. For instance, if I want to look at all registration paid in a particular year, rather then have all registrations from all years run. Is it possible to create a message box that will prompt a user to enter a date range for the year they wish to retrieve?

[MarkWilson]

You could use something like this example.

1. You would need two global fields (date). One for the start date "gDate_Start" and one for the end date "gDate_End".

2. You would need a new layout "Date_Range" (with the global fields).

3. You would need to add to your existing script or sub-script in...

Go to Layout [Date_Range]

Set Field [gDate_Start , ""]

Set Field [gDate_End , ""]

Go to Field [gDate_Start,Select/perform]

Enter Find Mode [pause]

Set Field [Date_Paid , gDate_Start]

Set Field [Date_Paid , Date_Paid & "..." & gDateEnd]

Perform Find []

Set Field [gDate_Start , ""]

Set Field [gDate_End , ""]

Go to Layout [original layout]

The script takes you to the new layout,

Clears the global fields,

Enters the gDate_Start field, and pauses for user input.

User can enter the date range.

Still in the Find Mode, the Date_Paid field is filled in with

gDate_Start...gDate_End ( 01/01/2003...02/02/2003 )

Performs the find,

Clears the global fields,

and finally returns to the original layout with your found set.

[CobaltSky]

Hi Mark,

I'm afraid that what you're suggesting won't work. The problems are not so much in the underlying logic but in the method of its application.

For example, if you try it, you'll find that using the Set Field [ ] command to clear a date field (global or otherwise) using a text null (ie "") does not result in an empty field, but instead produces a data validity error which appears in the field as a question mark.

Moreover, the seventh step of your script will also fail because the DatePaid field in find mode will only accept a valid date from the Set Field [ ] command - whereas the range criterion you are going for will not be presented to the field in a recognised date format. Consequently the Perform Find [ ] step which follows will also fail to return the desired result.

The first issue can be circumvented by forcing the null data type to correspond to the destination field type thus:

Set Field ["gDate_Start", "TextToDate("")"]

However the second issue cannot be solved for the Set Field command - it will always require valid data for the type of field it is targetting. A solution, however, would be to use the Insert Calculated Result [ ] script command instead, and then to force the date values to text before combining them with the range search operator, thus:

Insert Calculated Result ["Date_Paid", "DateToText(gDate_Start) & "..." & DateToText(gDate_End")

The 'Insert Calculated Result [ ]' command operates along the lines of a sophisticated 'paste' command and pays no heed to the target field data type (it instead applies data type characteristics according to the first typed element it encounters within the formula it contains). However like a paste command, it requires that the field it is targetting be present on the current layout. Therefore in order for the overall sequence to work, it would be necessary either to add the Date_Paid field to the 'Date_Range' layout you're proposing - or to have the script explicitly switch to a layout that contains that field before performing the Insert Calculated Result [ ] step.

A further issue is that global fields are not user-accessible in Find mode, so it would be necessary to pause the script in browse mode while the user enters dates, then subsequently enter find mode to transfer the criteria to the relevant target field for the search.

[Tmonk]

I am still unable to get this to work. Maybe I'm confusing some of the script steps that won't work with the ones you've suggested. Or more likely, I'm confused...

[CobaltSky]

Hi Tmonk.

To get the effect that Mark was proposing, you need a new layout called 'Date_Range' which has the 'gDate_Start' and 'gDate_Start' fields on it (perhaps with some instructions for the user about entering the dates they want to search between) plus a script along the lines of:

Go to Layout ["Date_Range"]

Set Field ["gDate_Start" , "TextToDate("")"]

Set Field ["gDate_End" , "TextToDate("")"]

Go to Field [select/perform, "gDate_Start"]

Pause/Resume Script [ ]

Enter Find Mode [ ]

Go to Layout ["(Layout with the Date_Paid field on it)"]

Insert Calculated Result ["Date_Paid" , "DateToText(gDate_Start) & "..." & DateToText(gDateEnd)"]

Perform Find [Replace Found Set]

Set Field ["gDate_Start" , "TextToDate("")"]

Set Field ["gDate_End" , "TextToDate("")"]

Go to Layout [original layout]

Check over what you've done and see if it matches up to the above.

[Tmonk]

This is getting me closer. Ihave used the first four lines of the script you've provided on a button that takes me to the "date range" layout. I can then enter the approriate date ranges and use another button to begin the remainder of the script. However, even though i specify date criteria "01/01/03", "12/31/03" for instance, the records that appear on my report layout show other years then those specified.

I'm using a layout(report) named "accounts receivable" where you specify "layout with the date_paid field on it". This report contains among other fileds a "payment date" field, which is the field i am performing the find on in the insert calc result step.

[CobaltSky]

Ok, here are a couple of things to look at first off:

1. Try entering four digit years into the global fields

2. Try performing the find manually (ie entering the text "01/01/2003...12/31/2003" directly into the field in Find mode) and see what you get.

3. Depending on the results of the foregoring suggestions, temporarily insert a Pause/Resume Script [ ] step immediately after the Insert Calculated Result [ ] step, so you can see exactly what is being inserted into the field.

It *could* be that you have a mismatch between file defaults and the current workstation settings for date handling - but if so, the above will give you some good diagnostic information.

[Tmonk]

Interesting. I have tried all of your checks. The script is passing the date range into the field properly but still returning all records, rather then just those in the date range. Also, when dates are entered manually, I get the same result! I have my payment field set up as a date field with strict 4 year validation. Does it matter that some of the files, including the Payment date field are in a related file? When I tried making a report in the Registration "Paid file" I seem to have no problems. However, when I try running the report from my Master file it just doesn't work.Could it be a relationship setup error?

[CobaltSky]

Hi,

First of all, make sure that the Perform Find [ ] script step does not have the 'restore' parameter enabled.

Then check whether the relationship between the files is correctly set up. In particular, to do what you are trying to do, both the key fields for the relationship must be of the same data type and they must *both* be stored and indexed.

[MarkWilson]

Thanks for the correction and detailed explanation how to.

I learned little more today.

[Tmonk]

All key fields are indexed and stored and same type(text,auto enter a serial number). My main file "player info" has a relationship with a file named "registration info". I would like to run this report from my main file because it has all the layouts there. What i can't understand is when I open the Registration file and create a report/ layout I can enter the date ranges and it works fine. I should note that when I manually enter the date ranges on my Main(player info) file I get the same results as if I run the script. I'm totally confused.

[CobaltSky]

Hmmn...

Bear in mind that when you are performing a search in the main file, what you are searching for is records *in the main file* that have related records that meet the date criteria you're entering.

That is quite a different thing from searching for the related records directly.

If what you want is a list of the related records themselves, you will either need a filtered portal in the main file (in which case you don't need a find at all) or a list layout showing as found set within the related file.

[Tmonk]

OK. Please bear with me as I try to underdstand. Even though I have a relationship setup between my main file and the registration file and the portal used on my main file layout accuratley displays related info; a perform find will not be able to see the related info from my related file? (sorry for the long sentence) Could you elaborate for me what a filtered portal and list layout are ? Thanks for your help.

[CobaltSky]

Hi,

A find will 'see' the related record info, but will only use it for the purposes of locating records within the current file (ie records which have related records which meet the criteria). Finds always work on the file in which they are performed, to isolate groups of records in that file.

A filtered portal is a portal placed in a file which uses an artificial relationship (to the same or another file) to display records that meet certain criteria. Usually such a portal is based on a relationship from a global field in the current file to a calculated field in the related file. The calculation in the remote file concatenates and/or appends (on separate lines) values from the records in that file to provide a context for filtering, then the value(s) in the global field determines which records will be displayed in the portal. In this respect, the outcome can be alike to that of a 'find', even though the records in question may be in another file.

A 'List Layout' is simply one that is designed to be displayed with the 'View as List' option from the View menu (in browse mode) enabled. Typically, layouts designed to be viewed in this way have a shallow body part with fields placed side-by-side so that a number of rows will appear on screen (or page), with the fields on adjacent records thus forming columns.