FM7V3 Script to Perform a Find on a date range

[tarheel]

Need help on FM7V3 script to Perform a Find on a date range.

I have two Global, Date fields "Report_Start_Date" and "Report_End_Date"

defined in my "Main Input" table. I tried this script but it puts the ">" and "<" in the fields instead of passing them on to the PerformFind.

Go To Layout [Main Input]

Enter Find Mode []

Set Field [ Main Input::Report_Start_Date ; ">" & Get ( ScriptParameter ) ]

Set Field [ Main Input::Report_End_Date ; "<" & Get ( ScriptParameter ) ]

PerformFind[]

Actually the operators are Greater_Than or Equal_To and Less_Than or Equal_To

Can anyone show me how to do this correctly?

Thanks

Bob...

[-Queue-]

You cannot perform a find on globals. You use the globals to perform a find on other fields. For example,

Set Error Capture [On]

Enter Find Mode []

Set Field [Table::dateField; Main Input::Report_Start_Date & "..." & Main Input::Report_End_Date]

Perform Find [ ]

[tarheel]

Yes, thats the ticket. Thanks very much for the quick and technically correct response. I'm learning, slow but sure.

Bob...

[tarheel]

The Set Find Mode step above works great for the date range and I can calculate a activity total for the date range for the found set but how can I also calculate the "Total" of all records for a "Year-to-Date" total?

Bob...

[-Queue-]

Create a calculation of Year(dateField). If you create a self-relationship based on this field, you can then create a calculation of Sum(relationship::numberField) to retrieve the total for all records within the same year. You can also use a relationship from a global to the Year calculation field in the same way. Changing the year in the global will then change the total in the Sum field appropriately.

[tarheel]

Yes, that did it. Thank you very much. I used the self-relationship method and it results in the correct total activity amount for the year. But how can I use this method to get total activity per year by account number? I put the calculation result in a layout in a Part Definition "sub-summary when sorted by Account#" but it displays the total for the year not the total by account#.

[tarheel]

Thanks to Queue I got the total amount for the year working but...

I tried everything I can think of to get this total activity per year per account# calculation but nothing I know works. Cam anyone help Me figure this out?

Bob...

[SlimJim]

Try adding in an account number match in the self-relationship based on the Year(Date) field giving a new relationship based on both account number and Year. The sum(AccYearRelationship::activity) should give the appropriate results in a subsummary sorted by account.

[tarheel]

Sounds like a great solution. I'm not sure how to construct the (AccYearRelationship) you mention. I believe my fields involved are account#, year to date (the year transaction date - self join), amount (activity). Could you help me figure out the code?

Thanks for the input

Bob...

[SlimJim]

Construct another copy of the self-join that you are using to get the total activity for the year. Now add in a second relationship account# = account# to this self join. So what this is doing is picking out activities with the same year and same account number. This is what I meant by AccYearRelationship. Do the Sum calculation using this relationship then the sum will be taken over the sets of records with the same year and same account number. A subsummary report sorted by account should show you a total for the year by account number.

[tarheel]

I am afraid I don't understand what you want me to do. I attached the file so maybe if its not to much trouble you can check it out and save yourself from trying to educate me and solve the problem at the same time.

Bob...

WVPD-Financial-June20V20.fp7.zip

[SlimJim]

I have added an extra table Main Input 3, set up the relation to Main Input, defined the field AccTotalYear as suggested by queue on this new related table and dropped the field into your Activity Report Synopsis, replacing the Summary field, it seems to work but then what you already had in there seemed to work.

WVPD-Financial-June20V20.zip

[tarheel]

Thanks SlimJim. Now I understand what you were trying to explain to me. I tested your solution but it doesn't exclude records from another year. Try entering a record with a date not 2005 and it will still include it in the year-to-date calculation. What I'm looking for is year-to-date for the current year such as 2005.

Bob...

[SlimJim]

I have had another look at this. Tell me if this is what you are doing. You put in a report start date and end date, which can be in different years, and extract all records within those dates. You want to compare the amounts in the account codes within that period with a year to date value. Is that always to be the current year (2005) or do you have another mechanism for deciding which year? I think this can be achieved by a slight varying of the relationship. Instead of having equal year have the year = to the report Year.

[tarheel]

Very close. the field "Activity to Date" is based on the Script "Activity Synopsis" selecting records that are within the "Report Start Date and Report End Date" period. The field "Expenditures to Date" need to include all records for the current year regardless of the "Report Start Date" - "Report End Date". It looks to me that I need the script to also select records for the year to date activity and somehow store those amounts per Account# and then find the records for the report start and end date.

Bob...

[tarheel]

Let me try to clear up the requirements.

We are using a Fiscal year calendar ie...

1st Quarter - July 1, 2005 - Sept 30, 2005

2nd Quarter - Oct 1, 2005 - Dec 31, 2005

3rd Quarter - Jan 1, 2006 - March 31, 2006

4th Quarter - April 1, 2006 - June 30, 2006

So date range entered usually would be of one of the defined quarters.

But the "Year to date" summary field needs to include record totals from the 1st Quarter through the Last Quarter. OK?

Bob...

[SlimJim]

OK I think I understand now. I have made some adjustments to the relationships which should resolve the situation. In addition I have added fields into your main input screen while I was testing (you will want to remove these when you go live) so that I could see what was happening and I have also added a script to reset the Fiscal Year start and end dates when you change year. Although this can be set up to do automatically from the point of view of reporting it is useful to have the control to change to a Fiscal Year that you are not in.

WVPD-Financial-June20V20.zip

[tarheel]

OK, thanks ever so much for your interest and help. I'll check out the changes and get back to you ASAP.

Thanks again...

Bob...

[tarheel]

Yes , that seems to have done the trick. Thanks again, great job.

Bob...

[tarheel]

Fiscal Year Reports

If today's date is: 6/23/2005 then the report dates would be:

All ... - ...

This Fiscal Quarter 04/01/2005 - 06/30/2005

This Fiscal Quarter-to-Date 04/01/2005 - 06/23/2005

This Fiscal Year 07/01/2004 - 06/30/2005

This Fiscal Year-to-Date 07/01/2004 - 03/23/2005

Last Fiscal Quarter 01/01/2005 - 03/31/2005

Last Fiscal Quarter-to-date 01/01/2005 - 03/23/2005

Last Fiscal Year 07/01/2003 - 06/30/2004

Last Fiscal Year-to-Date 07/01/2003 - 06/23/2004

Next Fiscal Quarter 07/01/2005 - 09/30/2005

Next Fiscal Year 07/01/2005 - 06/30/2006

Custom ... - ...

So now I need to figure out how create a pop-up menu with the report date selections above that when selected will populate the "Report Start Date" - "Report End Date" fields on the "Main Input" layout.

This will take awhile but will make the layout much more appealing to manager types.

Any hints would be appreciated.

Bob...