Using the Delay fields

[Foxfire]

Hi Guys,

What I'm trying to accomplish is to create an invoice. I work with real estate software and the field I'm trying to calculate the invoice based off, is the tenants paid to date, which is already set up as a merge field. I'm also Australian so my dates are dd/mm/yyyy.

So for example I need a tenant with a paid to date of 31/07/2011 to display the following two dates.

Invoice #blah
You owe rent for one month being 01/08/2011 to 31/08/2011.

I've done a bit of internet searching (which I'm pretty bad at) and the only thing I could find that really helped was the big setup below.

I'm looking for two things. One if someone could explain what is happening below so I can understand what I'm typing in and also if there is an easier way to do that's been found/developed in the past couple of years as the field below is from 2009.

The second thing I need is how to calculate the second date on the invoice accurately. A delay date of 31 or 30 doesn't work accurately for every monthly period. The from date I have sorted as I used the below field with the delay of 1. The only way I can think of doing this one is with a massive 'if ... then' imbeded tree that checks what month the date is in and uses the appropiate delay then.

I also couldn't work out how copypaste the merge field itself, without it pulling through data . Although by typing it in I did start to understand it a lot more.

The initial lines are all about calculating the days, months and years based on the merge field, then adding them and subtracting them to get the right dd/mm/yy to display. I still don't understand the INT or the figures used however.

I got the following date calculation from the datecalcs.zip on http://www.gmayor.com/downloads.htm it was originally done by Paul Edstein (Macropod) and mad props to him for doing some really awesome stuff.

{quote "Invoice From "


{set Delay 1}
{set a{=int((14-{MERGEFIELD "tenant_Paid_To" \@ M}/12} }
{set b{={MERGEFIELD "tenant_Paid_To" \@ yyyy}+4800-a} }
{set c{={MERGEFIELD "tenant_Paid_To" \@ M}+12*a-3} }
{set d{MERGEFIELD "tenant_Paid_To" \@ d} }
{set jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay} }
{set e{=INT((4*(kd+32044)+3)/146907)} }
{set f{=jd+32044-INT(146097*e/4)} }
{set g{=INT((4*f+3)/1461} }
{set h{=f-INT(1461*g/4)} }
{set i{=INT((5*h+2)/153)} }
{set dd{=h-INT((153*i+2)/5)+1} }
{set mm{=i+3-12*INT(i/10)} }
{set yy{=100*e+g-4800+INT(i/10)} }
"{dd}-{mm}-{yy}"
}

[macropod]

Hi Foxfire,

The best way to do this sort of thing is to modify the data source, so that it calculates the correct dates from the outset. That way, there's nothing left for the mailmerge to work out.

If that's not practical, you can adapt the tutorial example titled 'Calculate a lease expiry date, using n months delay', by adding a new line, thus:
{SET StartDate {MERGEFIELD "Tenant_Paid_To"}}
after the '{SET Delay 12}' line (modified to'{SET Delay 1}') and changing all the 'DATE' references to 'StartDate'.

You should end up with a field code that looks like:
{QUOTE
{SET Delay 1}
{SET StartDate {MERGEFIELD "Tenant_Paid_To"}}
{SET m{=MOD(ABS({StartDate \@ M}+Delay+11),12)+1}}
{SET y{=INT({StartDate \@ yyyy}+(Delay+{StartDate \@ M}-1)/12)}}
{SET a{=INT((14-m)/12)}}
{SET b{=y+4800-a}}
{SET c{=m+12*a-3}}
{SET d{=IF(({StartDate \@ d}>28+((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)))*(m=2)=1,29+((MOD(yy,4)=0)+(MOD(y y,400)=0)-(MOD(yy,100)=0)),{StartDate \@ d})}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045-1}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
"{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}

Or you can copy/paste the above into your document and use the macro I developed at: http://www.gmayor.com/export_field.htm#TextToField to convert it to a working field code.

[Foxfire]

Yeah after I posted I thought about it for a bit and went back through the Datecalcs document and found the month delay field, which works a treat.

I still don't understand all your calculations but it seems to work fine, except for February.

Apparently with February, on the day delay field it needs a delay set of 3 instead of 1 and I just don't understand enough of the code you've put in there to adjust it.

[macropod]

Originally Posted by Foxfire

Apparently with February, on the day delay field it needs a delay set of 3 instead of 1 and I just don't understand enough of the code you've put in there to adjust it.

I don't understand: If the month ends in February, why would the month delay value be any different? As coded, the field allows for February to have either 28 or 29 days, depending on whether it's a leap year.

[Foxfire]

Thanks for the replies by the way, I really appreciate the help.

I have an SQL database I'm pulling the date fields from and here is what happens. Unfortunately I can't modify the date in the database at all, so I have to do it all on the letter.

Paid_to_date = 16/08/2011.

Invoice from <delay by 1 day> to <delay by 1 month>

The above merge fields pull through the following information.

Invoice from 17/08/2011 to 16/09/2011.

However without changing anything, if I create the letter from a file with a paid to date anywhere in February, it does the following:

Paid_to_date = 14/02/2011

Invoice from 12/02/2011 to 14/03/2011.

If I set the delay to 3, instead of 1. February then works fine. Maybe I typed something wrong. When I've a chance I'll double check it. But if the above field is correct, my letter should be correct.

[macropod]

Hi Foxfire,

I am unable to replicate that behaviour. Are you sure your SQL DB outputs the dates in the correct (ie dd/mm/yy) format?

What happens if you make a copy of the field and change:
{SET StartDate {MERGEFIELD "Tenant_Paid_To"}}
to:
{ASK StartDate "Tenant_Paid_To?"}
and update the field manually (via F9) and inputting various dates?