Sorting a Report on date

Alx
Alx Member Posts: 38
edited 2005-11-21 in NAV/Navision Classic Client
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

Comments

  • trag
    trag Member Posts: 23
    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
    Alx Member Posts: 38
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • Alx
    Alx Member Posts: 38
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • sharks
    sharks Member Posts: 22
    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
    Iqbal_Febriano Member Posts: 66
    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
    Be fast, be straight, be quiet

Categories