grouping using other table?

julkifli33

hi all...
i want to create reports using sales shipment header
and then from this table i do query to pull another fields from customer
what i want to ask is...how to do grouping using this query fields?
thanks

mohana_cse06

i hope this post can help you
http://www.mibuso.com/forum/viewtopic.php?f=23&t=47629

julkifli33

that one using temporary table,is it?
hmmmm but how to do the grouping using temp table?
is there a way?

mohana_cse06

You can do it using code which i have given in above link

julkifli33

so in the grouping part will be ;

CurrReport.SHOWOUTPUT :=
  CurrReport.TOTALSCAUSEDBY = "Sales Shipment Header".FIELDNO(TempTable."Field");

is it like this?

mohana_cse06

I didnt try this..but it should work..

julkifli33

mohana_cse06 wrote:

I didnt try this..but it should work..

this code will modify and insert to the temp table

ILETemp.reset;
ILETemp.SETRANGE("Document No.","Document No.");
ILETemp.SETRANGE("Item No.","Item No.");
IF ILETemp.FINDFIRST THEN BEGIN
// your code to modify
ILETemp.MODIFY;
END ELSE BEGIN
// Your code to insert
ILETemp.INSERT;
END;

but how do we group it
i'm a little bit confuse

mohana_cse06

you have to set filters here based on your Grouping
this is just an exaample..

ILETemp.reset;
ILETemp.SETRANGE("Document No.","Document No.");
ILETemp.SETRANGE("Item No.","Item No.");
// add filter here

julkifli33

mohana_cse06 wrote:

you have to set filters here based on your Grouping
this is just an exaample..

ILETemp.reset;
ILETemp.SETRANGE("Document No.","Document No.");
ILETemp.SETRANGE("Item No.","Item No.");
// add filter here

i have 3 options
and then I set 3 conditions
here is the scenario
Sales Shipment Header table
- i add field (option type : 1st,2nd,3rd)

Customer table
- I add 3 fields Field : 1,2,3

when Sales Shipment Header = 1st, then i show field 1 from customer table
when Sales Shipment Header = 2nd, then i show field 2 from customer table
when Sales Shipment Header = 3rd, then i show field 3 from customer table

until here i'm able to do it
but how to group it...
let say i put it in temporary table, is it needed 3 temp table? or just 1?

mohana_cse06

I didnt get you exactly..you want to group on which field?

julkifli33

there will a condition
Sales Shipment Header (SSH), I add field named Trip (option type : 1st,2nd,3rd)
Customer (Cust), I add 3 fields : Trip 1,Trip 2,Trip 3

if in the SSH they choose 1st then I pull Trip 1 from customer table
if in the SSH they choose 2nd then I pull Trip 2 from customer table
if in the SSH they choose 3rd then I pull Trip 3 from customer table

in this case, i'm using variable...
it can....

the problem is.. i cannot group this variable

mohana_cse06

try like

SSHemp.reset;
SSHTemp.SETRANGE("Trip","Your variable");
IF SSHTemp.FINDFIRST THEN BEGIN
  // your code to modify
  SSHTemp.MODIFY;
END ELSE BEGIN
  // Your code to insert
  TRIP := Yourvariable;
  SSHTemp.INSERT;
END;

julkifli33

mohana_cse06 wrote:

try like

SSHemp.reset;
SSHTemp.SETRANGE("Trip","Your variable");
IF SSHTemp.FINDFIRST THEN BEGIN
  // your code to modify
  SSHTemp.MODIFY;
END ELSE BEGIN
  // Your code to insert
  TRIP := Yourvariable;
  SSHTemp.INSERT;
END;

ok... i already move it the value from variable to this temp table
it is correct in the details
but how to group it?

mohana_cse06

This code will group based on the Trip..

If it finds any Trip1 first time. it inserts into temp table..
second time if it finds, it vl update the existing line of temp table..

julkifli33

mohana_cse06 wrote:

This code will group based on the Trip..

If it finds any Trip1 first time. it inserts into temp table..
second time if it finds, it vl update the existing line of temp table..

but my report still the same
HC
HC
AT
BU
CU

BU
AT
HC
BU
CU


by the way... do i need to fill the data item with sales shipment header?
because it is not sorted

mohana_cse06

May be you are showing Sales Shipment Header not the temp table values in section..

take an integer dataitem and repeat it with temptable data

julkifli33

DataItem
- Sales Shipment Header
- Integer DataItemTableView --> SORTING(Number) WHERE(Number=CONST(1))

Integer - OnAfterGetRecord()
TempTable.RESET;
TempTable.SETRANGE("No.","Sales Shipment Header"."No.");
TempTable.SETRANGE("Shipping Agent Code",MyVariable);
IF TempTable.FIND('-') THEN
BEGIN
  TempTable."No." := "Sales Shipment Header"."No.";
  TempTable."My Field" := MyVariable;
  TempTable.MODIFY;
END
ELSE
BEGIN
  TempTable."No." := "Sales Shipment Header"."No.";
  TempTable."My Field" := MyVariable;
  TempTable.INSERT;
END;

in the section
I put it in the integer body
i pull from TempTable
but still not sorted

mohana_cse06

above code you need to write in Sales Shipment Header-OnAfterGetrecord and in Integer OnAftergetrecord you have to repeat the temp tabel from first record

TempTable.Reset;
IF TempTable.FINDSET THEN
  REPEAT
    //Do what ever you want
  UNTIL TempTable.Next = 0;

You want to show them on Preview or you want to do any thing else?

julkifli33

mohana_cse06 wrote:

above code you need to write in Sales Shipment Header-OnAfterGetrecord and in Integer OnAftergetrecord you have to repeat the temp tabel from first record

TempTable.Reset;
IF TempTable.FINDSET THEN
  REPEAT
    //Do what ever you want
  UNTIL TempTable.Next = 0;

You want to show them on Preview or you want to do any thing else?

i want to show them in preview
sorted.. so i can group them
so the code i put it in Sales Shipment Header and Integer?

mohana_cse06

Put your code in SSH-OnAfterGetRecord and
in Integer OnPreDataItem

SETRANGE(Number,1,TempTable.COUNT);

Remove Filter in integer dataitem

WHERE(Number=CONST(1))

Show Data in Integer Body Sections..

It should work now..check

julkifli33

still the same....
not sorted...