Calculating the rigth price

dabba23

I have a subscription table which holds information about how often customer has to pay for a subscription and how much.
F.x. can a customer have subscription that can be paid monthly, quarterly, every half year or once a year.
In the table there's a field called periodlength to represent these i.e.: 1, 3, 6, 12. And there's a field that holds the subscriptionprice per month

I need to create a report that requests users to enter in a startingdate and an endingdate.

In between these two dates I have to find out if a subscription has been made, i.e. if a user writes 010105..310205 and a subscription has a quarterly payment, then the price should not be shown (if the payment is made on 010405), but if a subscription has a monthly payment, then obviously the report needs to show the total price for the 2 months given.

So my problem is that I have absolutely no idea how to write this piece of code, as I'm really finding it hard coding with dates.
Anyone that can help me :shock: !

Thanks Ann

Marije_Brummel

Are you making this as new functionality for a customer?

We have a subscription/contract add on and the date calculation can get very complicated.

Basicly what we did is to make a period date table in which we store the payment dates, this table you can filter out and if you count the records you know how many payments can be made.

This table can be populated defining period codes with date formula's so customers can decide for themselves what kind of payment terms they have, they can also define 2 monthts payments/ 2 weeks, 1 week etc.

You can also make a date table temporary and then filter it out.

Succes with solving this issue!

dabba23

Well, the think is that the system has been made and is used by the customer. I did realise today, that there should have been kept a record of all payments, but that is just not the case.

But what you are saying is that I should still be able to calculate the price by making a temporary table?

Ann

Marije_Brummel

You can make a temporary date table, very simple and populate this with the payments. So for monthly payments this will be

1-1-05
1-2-05
1-3-05

When you filter this table

1-1-05..1-2-05 you can count 2 records, so 2 payments.

You can also use the date table from navision, but then you have a problem with half year payments.