Find date range for Invoices

[chr1shardcastle]

Hi,

I've looked around on the forums and there seems to be lots of info about on this but nothing that works for me so far.

I'm trying to create a simple script to make a vat calculation based on whether or not the invoice date falls into a certain range.

I have an 'Invoice date' field and then a field called 'VAT' which is referencing 'Sub Total'. The VAT calculation was this...

[color:blue]Sub total * .175

...which was simple enough. And I've since had this working (if the date is later than 31 Nov 2008 then calculate VAT at 15% otherwise, calculate at 17.5%)

[color:blue]If ( Invoice date > Date ( 11 ; 31 ; 2008 ) ; Sub total * .175 ; Sub total * .15 )

and then having looked around on here have tried setting ranges in various ways but not having any luck. e.g.

[color:blue]If ( Invoice date = Date ( 11 ; 30 ; 2008 & "..." & 12 ; 31 ; 2009 ) ; Sub total * .175 ; Sub total * .15 )

So all I'm trying to do really is say "if the invoice date falls between 30 Nov 08 and 31 Dec 09 then calculate VAT at 15%"

Any help greatly appreciated!

[gmmac]

I think you're in the wrong place to get help (this is tips & techniques--So your post may be moved). If or Case functions should work, just use:

IF (Invoice date ≥ 11/30/2008 AND Invoice Date ≤ 12/31/09 ; Sub total * .175 ; Sub total * .15 )

Edited December 3, 2008 by Guest
went to fast; didn't remove ) after 09

[chr1shardcastle]

Hi, thanks for this but I get an error saying "There are too few parameters in this function"??

[comment]

This was discussed quite recently (how surprising…) here:

http://fmforums.com/forum/showtopic.php?tid/199649/

[chr1shardcastle]

Yes, very surprising!

From what I can tell, that only gets as far as I have so far. It doesn't look within a range of dates. When VAT goes back to 17.5 (if it does) in 2010 then I want this calc to know that it should revert to 17.5%.

[comment]

I'm afraid you have missed the point. You don't want to store the tax rate DATA in a calculation formula.

Make yourself a table of VATrates with two fields: EffectiveDate and Rate. Define a relationship between Invoices and VATrates as:

Invoices::InvoiceDate = VATrates::EffectiveDate

Define a new VATrate field in Invoices as Number and set it to lookup from VATrates::Rate (if no exact match, copy next lower value).

Change your tax calculation to:

SubTotal * VATrate

This way, each invoice looks up and STORES the VAT rate in effect, according to its date. You do this once, then you just add a record in the VATrates table for every rate change.

[chr1shardcastle]

Thanks.

I'm most of the way with this (I'm quite new to FM) but I still don't quite follow how to control the value of 'Rate' in relation to the date?

Sorry, to clarify this. I'm pretty sure I've got this set up correctly. I've then added three Records to VATRates:

Effective date - 01/01/2001

Rate - .175

Effective date - 01/12/2008

Rate - .15

Effective date - 01/01/2010

Rate - .175

Is that the right idea??

Edited December 3, 2008 by Guest
Clarification

[chr1shardcastle]

It works! I hadn't realised you needed to re-insert the date info to get it to update.

Thanks very much for all your help.

[comment]

It seems about right. You could make the first record's effective date 1/1/0001, but as long as you don't have any invoices dated earlier than 2001, it doesn't really matter.

Note also that for existing invoices you need to perform a relookup in order to convert them to the new system (a backup is recommended).