Payment Cycles

[Rob Reiser]

Each month, on the 21st, I have to pay all the sales tax I've collected since the 21st of the previous month, 'sales tax' being a field on each job record.

I have a running total of 'sales tax', but it includes ALL visible records. Without Finding or Omitting, is there any way I can set up a field(s), script, or calculation - or any combination - that helps me keep a running total on collected 'sales tax' that falls within one of 12 cycles (from 21st to 21st)?

I'm rather a neophyte when it comes to this stuff, but if someone could steer me in the right direction, I think I can do it. And I'm open to any suggestions other than that which I've outlined.

Thanks,

Rob

[Fitch]

One possibility: create two date fields, and set their storage to global. I'll call them StartDate and EndDate.

Now create a relationship, you can duplicate the existing table occurrence (TO). I'll call the new TO JobDateRange. The relationship will have two criteria:

StartDate <= Job Date

EndDate >= Job Date

Now make a calculated field:

Sum( JobDateRange::Sales Tax )

When you enter the dates you want to use, the calculation will give you the total for that date range.

You might also want to make a script that increments the two dates but I'll leave it at that for now.

[Rob Reiser]

Thanks Fitch. I'll give this a try as soon as I can. I'll probably have more questions. But, again, thanks for start.

[Rob Reiser]

Fitch,

I did what you suggested and got inconsistent results. At first the Calculated Range came back with a number... it was 180, I believe. Cool, I figured that was like an ordinal calendar (the 180th day of the year being June 29th, the End Date).

I kept changing the End Date, and the Calculated Range number would predictably change along with it. I changed the Start Date and the Calculated Range number stayed the same... still good, I think.

But that number was reflected on ALL my records (was that the Global Storage setting doing that?) Not so good, most jobs won't get paid on the same day.

And then, for some inexplicable reason (nothing I remember doing) it stopped working. I would change the End Date and the Calculated Range would no longer update.

?

So, I'll do it all again, in case I inadvertently screwed something up. Still, I don't know what to do with the ordinal number that is returned in the Calculated Range? My guess is that I'll have a 'Payment Cycle' field with some sort of Calculation:

If 'Calculated Range' is...

>= 1 and <= 20, the result is 'January'

>= 21 and <=51, the result is 'February'...

>= 52 and <=79, the result is 'March'...

>= 80 and <=110, 'Apr'...

>= 111 and <=140, 'May'...

>= 141 and <=171, 'Jun'

>= 172 and <=201, 'Jul'

>= 202 and <=232, 'Aug'

>= 233 and <=263, 'Sep'

>= 264 and <=293, 'Oct'

>= 294 and <=324, 'Nov'

>= 325 and <=354, 'Dec'

>= 355 and <=365, 'Jan'

I have no idea as to what the exact calculation syntax would be...

Anyway, your solution raises an interesting question: Is there no way to convert an End Date to a number (1 to 365) without having to rely on the Start Date?

I mean, as it stands, I have 3 dates, Job Date (the date I open the job), Start Date (the day I bill the job), and End Date (the day I get paid.)

I guess I really don't understand how it did the Calculated Range thing works. I can't figure out how having an arbitrary Start Date aids in the Calculation of the End Date ordinal number

>> You might also want to make a script that increments the two dates

What would this accomplish?

Again, thanks for your help.

Rob

[Fitch]

You're making this too complicated. See the attached file. Look at the relationship graph, look at the calculated total.

RelatedDateRange.zip

[Rob Reiser]

Okay, I see now. Every month I change the Start Date and End Date, and it shows the records that fall within that cycle. I didn't understand that from your first response.

Thanks,

Rob

[comment]

Every month I change the Start Date and End Date

You could automate this, too.

RelatedDateRange2.zip

[Rob Reiser]

Beautiful!... you anticipated my next question!

Thanks comment.

Edited August 22, 2010 by Guest

[Rob Reiser]

Hey Fitch and Comment (and anyone else up for the task). You guys were able to help me with this problem a couple of months ago. Maybe you can help again. I've recently learned that my tax payment cycles are NOT from the 21st to the 20th, but instead from the 1st to the end of the month (to be paid on the 20th of the following month.) I thought it would be easy to modify the calculations as follows:

Calculated Field 'cStartDate':

Date (

Month ( gAnyDate ) - 1 ; 1 ; Year ( gAnyDate )

)

Calculated Field 'cEndDate':

Date (

Month ( cStartDate ) ; 31 ; Year ( cStartDate )

)

gAnyDate being the day I'm getting ready to pay for the previous month's Taxes.

However, the above isn't right, as - obviously - not all months are 31 days and it ends up making the period of some tax cycles extend into the next month a day (or more in Feb).

What would be the right way to calculate this?

[comment]

Try:

cEndDate =

Date ( Month ( cStartDate + 1 ) ; 0 ; Year ( cStartDate ) )

or =

Date ( Month ( gAnyDate ) ; 0 ; Year ( gAnyDate ) )

or =

gAnyDate - Day ( gAnyDate )

Edited November 1, 2010 by Guest

[Rob Reiser]

Try:

cEndDate =

Date ( Month ( gAnyDate ) ; 0 ; Year ( gAnyDate ) )

Thanks comment, the above worked. What does that '0' do, just kick it back to the last day of the previous month?

[comment]

Yes.