Grouping records in tablebox

rjdio

Hi,
I have two tableboxes on my form and either the form or the tableboxes are bound to a table named G/L Entry. These tableboxes opens with the control of a checkbox.
TableBox1 has 10 fields and Tablebox2 has 3(date, no, username). What I want to do is when I press the button TableBox2.visible=true (and TableBox1.Visible=false) and I want to group records of TableBox2 for 2 fields(date and no). Visible property is ok but I cannot group records in TableBox2. There are lots of entries for TableBox2 with the same date and no. I want to show the same records with one row.!!

By the way the fields I want to group by is a key in related table.

I don't want to use temporary table in order the form run faster.

Can someone please help me???
The code goes something like this...;

reset;
...(some setfilter statements for the table)
IF chkbox = true then begin //open tablebox2
//GROUPING RECORDS MUST BE IN HERE
currform.tablebox2.visible(true);
currform.tablebox1.visible(false)
END ELSE BEGIN
currform.tablebox2.visible(false);
currform.tablebox1.visible(true)
END;

Thanks a lot...

rjdio

I realized something and changed SourceTableView of form property to key value.
Nothing has changed..!!!

kriki

I think the easiest way is to create an extra table with those fields in it and the primary key, so :
1:"Entry No."
3:"G/L Account No."
4:"Posting Date"
with primary key, the first field and as secondary key Fields 3 and 4.

These you can update in codeunit 12, Function "FinishCodeunit()", just after the command "GLEntry.INSERT;".
After this command, run a function (Eg. "InsertGLEntryGrouping()" that inserts a record in the new table if necessary.

Function InsertGLEntryGrouping()
recNewTable.RESET;
recNewTable.SETCURRENTKEY("G/L Account No.","Posting Date");
recNewTable.SETRANGE("G/L Account No.",GLEntry."G/L Account No.");
recNewTable.SETRANGE("Posting Date",GLEntry."Posting Date");
IF recNewTable.FIND('-') THEN
  EXIT;

recNewTable.transferfields(GLEntry);
recNewTable.INSERT(FALSE);

You don't have to worry about a unique primary key. The record "GLEntry" takes care of this, because this one is unique.

The negative effect is that posting will be slowed a little because you have to create extra records in a new table. To not slow it too much, try to limit as much the fields and the indexes in the new table.

rjdio

This is a solution but doesn't satisfy me.
Let me tell what i want to do.
I have a form based on GL Entry table and there are two tableboxed in it. One is GL Entry records and the other is grouped records. In the first tablebox there is no problem. 203k of rows are displayed.
There is a filter selection in the form and users can filter the records as they wish. There is a checkbox whether they want to see summary or detail. Detail is GL Entry table box and this is ok. But when user want summary i want to group those records of GL Entry with Document No and Posting Date. This must be so easy without using temporary table. I want to group records of filtered GL Entry table with Document No and Posting Date.

Marije_Brummel

You can change the code in the FindRecord and NextRecord triggers.

You can skip the records you don't want to see and use variables for the totals.

However I think a Temptable is faster and easier.

rjdio

I think the problem is I am thinking in SQL logic.
The first tablebox is
SELECT * FROM [Company Name$G_L Entry] WHERE user based filter.

I want the second one would be
SELECT "Document No", "Posting Date" FROM [Company Name$G_L Entry] WHERE user based filter GROUP BY "Document No", "Posting Date";

This must be the fastest way, cause if you use temporary table, you first read from database, then write to temp table with grouping, then read from temp table again. But in SQL logic you can read once with the filters and groups and display.

Am I wrong???

Marije_Brummel

You are right, but this is Navision. Don't fight it.!

Marije_Brummel

There are topics on this forum about linking a NAvision form/table to a SQL View.

But this requires a new table and a new form.