Formula

[Dukester0122]

I'm trying to create a formula to distribute the amount paid based to the start and end dates. Example, customer pays $12000 today (5/25) and we would start charging them subscription for 12 months starting 6/01.

Fields are:
subscription amt = $12000
start date = 06/01/2007
end date = 06/01/2008
invoice date = 05/25/2007

results:
should show 12 months of $1000/ea

 

[Turkbear]

Hi,
What determines when the subscription starts:
if ordered on
5/2/2007 would it start 5/2007 or 6/2007?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Dukester0122]

it would start on the next month

 

[synapsevampire]

Looks woefully inadequate for the task.

How will you know which months were paid?

You should have invoice date,amtpaid, paid date, etc.

Anyway, to cheat what you ask for, try something like:

whileprintingrecords;
numbervar months := datediff("m", {table.start}, {table.end});
numbervar x;
output:="";
for x := 1 to months do(
Output := "For period starting " & totext(dateadd("m",x-1,{table.start}) &"amount due: " & {table.amount}/months
);
Output

Should get ya close...

-k

 

[Dukester0122]

i'll definitely add that to the report but my issue is the date calculation. thanks

 

[synapsevampire]

Uhhh, what does my issue is date calculation mean in database and programming terms?

It appears that I provied what you asked for, perhaps you should try using tehnical terms and examples.

Do you mean that you do not currrently know the start date?

whileprintingrecords;
datevar start:= dateserial(year({table.invdate})month({table.invdate})+1,1);
datevar end:= dateserial(year({table.invdate})month({table.invdate})+13,1);
numbervar months := datediff("m", start, end);
numbervar x;
output:="";
for x := 1 to months do(
Output := "For period starting " & totext(dateadd("m",x-1,{table.start}) &"amount due: " & {table.amount}/months
);
Output

Might need some typos fixed/minor tweaking, but the theory is sound.

-k