Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

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

Recommended Posts

Posted

Hi,

The project I am working on involves date ranges. User may select monthly, quarterly, annual and fiscal date ranges. The report then finds parent records with related records in the range, looks at the related table and counts the number of records in the range.

That part is working fine, but I also need to set a field to Yes or No if the parent has or has not any related records within the previous period: monthly within the previous month, quarterly withing the previous quarter, annual within the previous annual and fiscal within the previous fiscal.

I can set the ending date for the previous range easy enough by subtracting 1 from the begin date of the current range, but I am having trouble finding a consistent pattern around which to set the beginning date for the previous range. Here is the formula I am using to Set the field now.

Case (

(People::gd_DateEnd - People::gd_DateBegin) = 365 ; People::gd_DateBegin - (People::gd_DateEnd - People::gd_DateBegin) ;

(People::gd_DateEnd - People::gd_DateBegin) = 30 ; People::gd_DateBegin - (People::gd_DateEnd - People::gd_DateBegin) ;

(People::gd_DateEnd - People::gd_DateBegin) = 29 ; People::gd_DateBegin - ((People::gd_DateEnd - People::gd_DateBegin) + 2) ;

(People::gd_DateEnd - People::gd_DateBegin) = 90 ; People::gd_DateBegin - ((People::gd_DateEnd - People::gd_DateBegin) + 2) ;

(People::gd_DateEnd - People::gd_DateBegin) = 91 ; People::gd_DateBegin - ((People::gd_DateEnd - People::gd_DateBegin) + 1) ;

)

Getting it right when it is annual or fiscal seems consistent enough, but I am having trouble getting consistent results with the month and quarterly ranges.

Would appreciate it if someone could give me a nudge in the right direction.

thanks much,

Graham

Posted

User may select monthly, quarterly, annual and fiscal date ranges.

Where is this selection, and why isn't it used to CALCULATE all the range boundaries (you don't need a script for this)?

Example:

First date of this quarter =

Date ( 3 * Div ( Month ( today ) - 1 ; 3 ) + 1 ; 1 ; Year ( today ) ) ;

First date of next quarter =

Date ( 3 * Div ( Month ( today ) - 1 ; 3 ) + 4 ; 1 ; Year ( today ) ) ;

First date of previous quarter =

Date ( 3 * Div ( Month ( today ) - 1 ; 3 ) - 2 ; 1 ; Year ( today ) )

Note: use Get (CurrentDate) or any other date as 'today'.

Posted (edited)

Hi, the user is selecting the range via two global date fields on a layout using the native FM pop up calendar. Are you suggesting I should then also select the previous range at the same time by calculation.

Thanks for the calculations.

G.

Also the user may not run the report during the current range, so I am figuring I have to let them choose the range they need.

Edited by Guest
Posted

I am afraid I don't quite follow. If users want a month, or a quarter, or a year, they should select the range (by name), and then ONE date (if at all - that is, if it's not set automatically to current month/quarter/year). The selected date can be anywhere within the desired range. The range boundaries are then calculated as in the example above. If users are allowed to select TWO dates, then shouldn't that be it?

Of course, you could have both: either users select the dates manually, or they choose a range and ask for the dates to be set for them. However you set it up, the previous range should be calculated at the same time, from the initial input - not from the results of the first calculation.

Posted

Ok, if it is June and they want to run a report for May, have them select a date in May and them click a Button that says Monthly and the calcs take place behind the scene. And so forth for the other ranges if I am following your logic. Thanks for the assistance.

G.

Posted (edited)

Yes, something like that. Once you know the selected range (from the script parameter, or a global field), you can run 4 Set Field[] steps, each along the lines of:

Case (

selectedRange = "Month" ; ... ;

selectedRange = "Quarter" ; ... ;

selectedRange = "Year" ; ... ;

...

)

Or, if they MUST choose a range (i.e. not allowed to set start and end dates arbitrarily), you can have those 4 calcs directly in the fields - no script required.

Edited by Guest

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