Getting the name of months from the posting dates.

Freaky

Hi Guys,
I have a table called the cust.ledger entry table and another called the customer table. The cust.ledger entry table is the second dataitem and is indented.A field called date filter from the customer table is linked to the posting date on the cust.ledger entry table.Now, when I filter by posting date and run my report all posting dates for that an customer is seen. But I want the name of all months to display from the first posting date to the last of that customer. I tried using the date table but to no avail. Can anyone please help me.

Thanks in advance.

DenSter

FORMAT("Posting Date",0,'<Month Text>')

Go to the C/SIDE Reference Guide (which is under the Help menu), and search for 'format'. Down the list you'll see one entry for 'format', which explains a number of default formats that you can use for various types of data types. The entry for 'FORMAT(String)' will give you a couple of examples as well.

Savatage

MonthName := FORMAT("Cust. Ledger Entry"."Posting Date",0,'<Month Text>');

EDIT: Too slow this time didn't press submit

Freaky

Sorry Guys I think my question did not portray what I meant.
Let say I filter by posting date from 01/01/09..24/04/09
Now I want it to display all months between this two dates that is,
Example

Posting dates
01/01/09
23/02/09
24/04/09

Months to get
January 2009
February 2009
March 2009
April 2009

Can you help please.
Thanks in advance

DaveT

Hi,

You can code for this or use the virtual table Date and Period Type = Month

Freaky

Please can you assist me because tried coding it and using the virtual date table but it does not give me what i want.

DenSter

DenSter wrote:

Go to the C/SIDE Reference Guide (which is under the Help menu), and search for 'format'. Down the list you'll see one entry for 'format', which explains a number of default formats that you can use for various types of data types. The entry for 'FORMAT(String)' will give you a couple of examples as well.

Have you even checked this??? Go to that article and scroll down, you will find exactly what you are looking for.

FORMAT("Posting Date",0,'<Month Text> <Year4>')

Savatage

Freaky wrote:

Posting dates
01/01/09
23/02/09
24/04/09

Months to get
January 2009
February 2009
March 2009
April 2009

Actually he wants the month to show if there is a posting or not see March..

DaveT wrote:

You can code for this or use the virtual table Date and Period Type = Month

\:D/
If you have Form 351 you can see it in action

Freaky

You got it Savatage I want all months to show within the posting date range whether there is a posting or not. Anyway I will give it a try.

Thanks

DenSter

Alright, so you don't want to just display the month names of the posting date, you want to take the date filter, and list all of the months in that filter. I don't see how you want to display all of that, but for that I would use the Date virtual table, and then the FORMAT command to get the right string out.

This is the kind of thing that a senior should do with you by the way, you should not have to get this type of information from a forum.

Create a new variable MyDate, type Record, Subtype Date. This is a record variable, based on the Date virtual table. By the way, one way to figure out how virtual tables work is to create a list form that is based on them. That way you can see what columns are available, what data types they are, which sort order you can use, things lilke that.

So you set a filter on the Period Type field where that equals 'Month'. Now you only have Month type periods in your filter. One thing to remember is that Month type periods always start on the first, so you have to make sure that the start date in your date filter is on the first of the month, to get the first month in the date filter.

Create two variables of the type Date, call them StartDate and EndDate. Set these values like this:

StartDate := GETRANGEMIN(DateFilter);
StartDate := CALCDATE('-CM',StartDate); // to get the first of the month

EndDate := GETRANGEMAX(DateFilter); // no need for the first or last of the month here

So now you have the start and end dates in variables, much easier to deal with. Use these values to set a filter on the "Period Start" field of the date record variable. Now you have all Month type Date records between the first of the month of the start of the date filter, and the end of the date filter. It's time to start looping through them.

IF MyDate.FINDSET(FALSE,FALSE) THEN BEGIN
  REPEAT
    SomeText := FORMAT(MyDate."Period Start",0,'<Month Text> <Year4>');
  UNTIL MyDate.NEXT = 0;
END;

Now how to display this in a report is another story. You'd probably have to create a new dataitem based on the date virtual table, and set the dataitem filters somewhere.

kapamarou

If your report is like this:

Customer
-Customer Ledger Entry

you could change it to:
Customer
-Date
--Customer Ledger Entry

Filter the Date dataitem to show the months.
Filter manually the customer ledger entries for the month start/end dates and the customer no and play around with the PrintOnlyIfDetail property.

The additionally you can have a flag in the options form in the manner of Show Details and in the section that prints all the ledger entries add a ShowOutput(showDetails)...

DenSter

Or:
Customer
-Date
-Customer Ledger Entry

And list the months at the top of the report. It really depends on what they want to do, anything we say here about that before we know how they want it is pure speculation.