Sales Invoice Line One Product Per Customer Per Line

erugalatha

Hi,

Is it possible (using a Dataport Export to .csv) to output one product per customer per line? At the moment if I loop through the Sales Invoice Line I can't find a way of grouping the Customers e.g if one customer buys a product at the start of the month and then the same product at the end of the month I want to total that as 2 of Product A for that Customer.

Any ideas?

Thanks

kriki

Yes, you have to create a temptable on launching the dataport with all records (grouped) that must be exported.
The Dataitem of your dataport, you must put on "Integer" and you loop this table so many times you have records in your temptable.

trigger "Integer - OnPreDataItem()":

FillTemptable();
tmpTempTable.RESET;
SETRANGE(Number,1,tmpTempTable.COUNT);

trigger "Integer - OnBeforeExportRecord()"

IF Number = 1 THEN
  tmpTempTable.FIND('-')
ELSE
  tmpTempTable.NEXT = 0;

In the dataport fields, you must use fields tmpTempTable."Field 1",tmpTempTable."Field 2",...
[/code]

erugalatha

Ok ... thanks for your help.

FillTempTable(); and tmpTempTable? where/how are they defined?

Is FillTempTable() a custom function?

kriki

erugalatha wrote:

Ok ... thanks for your help.

FillTempTable(); and tmpTempTable? where/how are they defined?

Is FillTempTable() a custom function?

-"FillTempTable()" is a custom function.
-"tmpTemptable" : global variable of type record of the same type as your real record but with property Temporary=TRUE. This means it will only be in memory, no other session, not even the other objects in your session will see it's records.

the code will be something like this:

recTable.RESET;
recTable.SETCURRENTKEY(...);
recTable.SETRANGE(....);
IF recTable.FIND('-') THEN
  REPEAT
    tmpTable.RESET;
    tmpTable.SETCURRENTKEY(...);
    tmpTable.SETRANGE("Field 1",recTable."Field 1");
      // a setrange for each field for which it must be unique. E.G. if you want 1 record per "Customer No."-"Item No.", you have to create a SETRANGE on "Customer No." and on "Item No."
    IF NOT tmpTable.FIND('-') THEN BEGIN
      tmpTable := recTable;
      tmpTable.INSERT(FALSE);
    END
    ELSE BEGIN
      tmpTable."field A" := tmpTable."field A" + recTable."field A";
      // this you have to write for each field for which you want a total (e.g. Quantity,Amount,...)
      tmpTable.MODIFY(FALSE);
    END;
  UNTIL recTable.NEXT = 0;

And that's it, after running this function, your temptable is ready to be exported.

kine

There is no possibility of Nesting dataitems on dataport. Dataport is able to read only blocks of same lines. The structure can be only:


Table 1 Record 1
Table 1 Record 2
Table 1 Record 3
Table 1 Record 4

Table 2 Record 1
Table 2 Record 2
Table 2 Record 3
Table 2 Record 4
...