Adding entire months

[Rafa]

Hello, everyone!

This is a simple question but the answer would help me a lot:

How can I add month by month? In a Loop script I need to add the next month to the next record in a set of 30 records. I tried Date(Month((invoice_date)+ 1), Day(invoice_date), Year(invoice_date)) but it didn't work. The year also wasn't affected.

I'll appreciate if you can give me a hand.

Thanks.

[Ugo DI LUCA]

Hi,

Don't know exactly what your script looks like, but try Insert Calculated result (your calc) .

[Rafa]

My script look like this:

Set Field ("counter", "0")

Loop

Exit Loop If ("g_counter = 30")

Set Field ("g_counter", "g_counter + 1")

New Record/Request

Set Field ("invoice_month", "?:?")

Here is where I wanto to increase the invoice_month date field month by month, starting from January 2003, and ending in June 2005 after 30 months.

I'm from Costa Rica. I hope you understand what I'm trying to say.

Thanks.

[BobWeaver]

You mean you want to add 1 month to the date, right? Your formula has an error in it. The problem is with the parentheses. Change it to:

Date(Month(invoice_date)+ 1, Day(invoice_date), Year(invoice_date))

[danjacoby]

The calc should be:

TextToDate(Day(invoice_date) & "/" &

If(Month(invoice_date) = 12,

"1/" & Year(invoice_date) + 1,

Month(invoice_date) + 1 & "/" & Year(invoice_date)))

[BobWeaver]

Dan, your calculation is more complex than necessary. The date function handles the rollover into a new year automatically. If the month part of the Date() function is more than 12, then it automatically adjusts the year and month to the required values.

In either method though, you need to watch out for a problem that might come up where, for example, the invoice date is January 31, and adding a month to this date has a couple of possibilities. You might want the result to be February 28, or you might want the result to be March 2 (invoice date plus 30 days).

Assuming that you want to the result to be no later than the last day of the following month, then you can use this calc:

Date(Month(InvoiceDate)+1, Min(Day(InvoiceDate),Day(Date(Month(InvoiceDate)+2,0,Year(InvoiceDate)))), Year(InvoiceDate))

[BobWeaver]

Here is a script that should work:

# You will need a global date field: gStartDate

Set field [gStartDate, InvoiceDate]

Set field [gCounter, 0]

Loop

  Exit Loop if [gCounter >=30]

  Set field [gCounter, gCounter+1]

  New Record/Request

  Set field [invoice date,Date(Month(gStartDate)+gCounter, Min(Day(gStartDate),

      Day(Date(Month(gStartDate)+gCounter+1,0,Year(gStartDate)))), Year(gStartDate)) ]

End Loop

[Rafa]

Thanks everyone for your help. How far I was of resolving my problem for myself! I'm not an expert in FileMaker to pay you back, but when you come to Costa Rica, I promise to get the best discounts for you in hotels, tour an transportation.