Sorting a Report on date

Alx

Good morning!

I have a report printing Purchase Invoice Lines, grouped by VAT%. What I want to do is to sort on Posting Date within each VAT perc. My problem is I don't have Posting Date in Purchase Invoice Lines, so I can't sort on this field directly. I think what I need is a "fake" DataItem above Purchase Invoice Lines, to loop through the dates (something like a DateLoop... :?: ).
Does anyone know how to do this?

Thanx in advance,

Alx

trag

Hi Alx,

how about adding the field 'Posting Date' to Purchase Invoice Lines and write a little extension to fill this field during the generation of a purchase invoice? Afterwards you are able to sort your report by using 'Posting Date'!

Greetings,
trag

Alx

Hi trag,

If possible I would like to avoid changing tables and messing with the posting routines. You are right, it's a solution to my problem but I beleive there MUST be another way....

Thanx for the suggestion!

Alx

Marije_Brummel

You can create a new table, poplulate this runtime and report on this.

If you make this table temporary it need not to be in your license.

Just make sure all the fields are in it, create a key and populate it runtime.

It may take a while to run, but you need not to change the datastructure of the navision tables.

Alx

Hoi Mark!

You mean create a temporary table like Purch. Inv. Line WITH an extra Posting Date field? If I understand correctly, this is like a View in SQL...
Only, how do I create a key on a temporary table?

Alx

Marije_Brummel

Yes, it can be a copy of the p-line table with the date field, but you can also leave out the fields you don't use.

If you make a copy you can use the TRANSFERFIELDS thingy.

The key you can add just like any other table via keys in object designer.

The only diff between a temp table and a normal table is the property in the variable declaration.

sharks

How about using Purchase Header in combination with Purchase Line. This way you can sort by Posting Date since it's in the header table and there is a clear link bewteen the two tables.
Another idea is to create a temp table which will have all the fields of the table you want to use and in addition posting date and in your report you will have two dataitems, first the real table and then the temp table. This way you run first the real table and create lines by transfering the fields and in addition you also make the calculations to populate the Posting date field. This way you have a complete temp table and you can have whatever keys you like, therefore any sorting...

Hope i have helped...

Iqbal_Febriano

I think Sharks's suggestion is the most simple way, just create a DataItem Purchase Invoice Header then you make the purchase invoice line dataitem nested to the header