Create Temporary table with dynamic fields?

KeeperRU · 2016-04-05

Which is the best method to create your own virtual structures like tables?

I create physical tables with fields like: CodeField1 (Text 250), Codefield2 (Text 250)....., DecimalField1....
This works great, but code hard to maintain in a long-term. There are always not enough fields or keys.. because I have much tasks with Buffers.

For example:

IF Item.FINDSET THEN
REPEAT
  IF NOT TempTable.GET(Item.Type, Item.Location) THEN BEGIN
    TempTable.CodeField1 := Item.Type; //KEY
    TempTable.CodeField2 := Item.Location; //KEY
    TempTable.INSERT
  END;
  
  TempTable.DecimalField1 += Item.GetLocationAmount();
  ...
  TempTable.DecimalField15 += Item.GetLocationQuantity();

  TempTable.MODIFY;	
UNTIL (Item.NEXT = 0);

Then:

TempTable.SETCURRENTKEY(DecimalField1);
IF TempTable.FINDSET THEN
REPEAT
  ExcelBuffer.AddCell(RowNo, ColNo, TempTable.DecimalField1);
  ..
  ExcelBuffer.AddCell(RowNo, ColNo+14, TempTable.DecimalField15);

  RowNo +=1;
UNTIL (TempTable.NEXT = 0);

Maybe XML, DotNet list, etc.? Need some link with best practices.
Method should be really fast, with sorting, filtering, etc.

parm · 2016-04-05

Hi,
I think the better solution is temporary tables.
With XML will be slowest and more difficult to sort.
With .Net structures sort and filter will need more code.

I use temporary tables with a little twist. My keys are Text(250).
In your example my key should be
TempTable.SETRANGE(TextKey1,
'##!' + Item.Type + '!##!' + Item.Location + '!##');
IF NOT TempTable.FINDFIRST THEN
TempTable.TextKey1 :=
'##!' + Item.Type + '!##!' + Item.Location + '!##';

If the order should be Location,Type I change the order when writting TextKey1.
To be a little more organized and readable I use a private function to get this key, like this:
LOCAL GetFilter<YourMeaningText> (Record Item) : Text(250)
MyFilter := '##!' ;
IF Item.Type = '' THEN
MyFilter += 'null'
ELSE
MyFilter += Item.Type;
MyFilter += '!##!';
....
EXIT(MyFilter);

TempTable.SETRANGE(TextKey1, GetFilter<YourMeaningText>(Item));
IF NOT TempTable.FINDFIRST THEN
TempTable.TextKey1 := , GetFilter<YourMeaningText>(Item);

Regards,

parm

parm · 2016-04-05

Hi,
I think the better solution is temporary tables.
With XML will be slowest and more difficult to sort.
With .Net structures sort and filter will need more code.

I use temporary tables with a little twist. My keys are Text(250).
In your example my key should be
TempTable.SETRANGE(TextKey1,
'##!' + Item.Type + '!##!' + Item.Location + '!##');
IF NOT TempTable.FINDFIRST THEN
TempTable.TextKey1 :=
'##!' + Item.Type + '!##!' + Item.Location + '!##';

If the order should be Location,Type I change the order when writting TextKey1.
To be a little more organized and readable I use a private function to get this key, like this:
LOCAL GetFilter<YourMeaningText> (Record Item) : Text(250)
MyFilter := '##!' ;
IF Item.Type = '' THEN
MyFilter += 'null'
ELSE
MyFilter += Item.Type;
MyFilter += '!##!';
....
EXIT(MyFilter);

TempTable.SETRANGE(TextKey1, GetFilter<YourMeaningText>(Item));
IF NOT TempTable.FINDFIRST THEN
TempTable.TextKey1 := , GetFilter<YourMeaningText>(Item);

Regards,

parm

KeeperRU · 2016-04-06

parm wrote: »

Hi, thank you.
This answer give me some ideas to increase transparency of my "Buffers" code.

Create Temporary table with dynamic fields?

Best Answer

Answers

Categories