Need to create a temporary table for a subform

TxHaz98 · 2010-04-13

I need to create a temporary table for a subform but keep getting the "You cannot make any changes in the database until a transaction has been started" error. Doesn't matter what trigger I put the code I seem to get this. My form is the sales lines for a quote and a subform of it needs to show a summary of on hand qty. in item ledger entries by "Item No.", "Variant Code", "Location Code", "Unit Cost". I've put the code that creates the entries in this new temporary table in the sales line table, form, a report but nothing seems to work because the call to the function has to occur from the form (tried OnAfterGetRecord and OnAfterGetCurrRecord). Any suggestions would be greatly appreciated.

Andy

Code on the form:

Form - OnAfterGetRecord()
ShowShortcutDimCode(ShortcutDimCode);

Form - OnAfterGetCurrRecord()
CurrForm.UPDATECONTROLS;
CurrForm.CAPTION("Document No.");

//DSW-ARH 4/13/10 ++++++++++
IF (Type = Type::Item) AND
   ("No." <> '') THEN
  CreateSummary;
//DSW-ARH 4/13/10 ----------

Form - OnBeforePutRecord()

Function in the sales line table:

CreateSummary()
TempILE.RESET;
TempILE.SETRANGE("Quote No.","Document No.");
TempILE.SETRANGE("Item No.","No.");
TempILE.DELETEALL;          <----------------Error occurs here

QtyTotal := 0;

ILE.RESET;
ILE.SETCURRENTKEY("Item No.","Variant Code","Location Code");
ILE.SETRANGE("Item No.","No.");
ILE.SETRANGE(Open,TRUE);
ILE.SETRANGE(Positive,TRUE);
IF ILE.FINDFIRST THEN REPEAT
  ILE.CALCFIELDS("Cost Amount (Actual)","Reserved Quantity");
  UnitCost := ILE."Cost Amount (Actual)" / ILE.Quantity;
  QtyAvailable := ILE."Remaining Quantity" - ILE."Reserved Quantity";
  QtyTotal := QtyTotal + (ILE."Remaining Quantity" - ILE."Reserved Quantity");
  IF NOT TempILE.GET("Document No.",ILE."Item No.",ILE."Variant Code",ILE."Location Code",UnitCost) THEN BEGIN
    TempILE.INIT;
    TempILE."Quote No." := "Document No.";
    TempILE."Item No." := ILE."Item No.";
    TempILE."Variant Code" := ILE."Variant Code";
    TempILE."Location Code" := ILE."Location Code";
    TempILE."Unit Cost" := UnitCost;
    TempILE."Qty. Available" := QtyAvailable;
    TempILE."Qty. Total" := QtyTotal;
    TempILE.Quantity := ILE.Quantity;
    TempILE."Remaining Quantity" := ILE."Remaining Quantity";
    TempILE.INSERT;
  END ELSE BEGIN
    TempILE."Qty. Available" := TempILE."Qty. Available" + QtyAvailable;
    TempILE."Qty. Total" := TempILE."Qty. Total" + QtyTotal;
    TempILE.Quantity := TempILE.Quantity + ILE.Quantity;
    TempILE."Remaining Quantity" := TempILE."Remaining Quantity" + ILE."Remaining Quantity";
    TempILE.MODIFY;
  END;
UNTIL ILE.NEXT = 0;

kine · 2010-04-13

1) Please, do not use the colors, the green is unreadable. Better to use the correct "code" tag and insert the code into code block. thanks... ;-)

2) The problem is in OnAfterGetRecord and OnAfterGetCurrRecord triggers - Because these triggers are called many times during displaying the data, you cannot modify any data from these triggers. You need to do it in another way. I think that in this way is better to refresh the data "on push" or update them in another way. Simple, you cannot use these triggers...

TxHaz98 · 2010-04-13

I have determined that I have to do it another way. I guess I should have stated does anyone have another way to suggest doing this.

David_Singleton · 2010-04-13

In any case you can't have a subform in a subform.

TxHaz98 · 2010-04-13

It's not a subform within a subform. The main form is a form looking at sales lines and the subform is the summary ledger entries dependent on the item in the main form.

garak · 2010-04-14

mhm are u sure that your TempILE is really temporary?
Also, please don't use findfirst in a loop. Use findset if u know that there is a match of ~ 500 recs or find('-') but not findfrist

Regards

TxHaz98 · 2010-04-14

It's not a real temporary table or at least it wasn't. I have gone a new direction but now have a new problem. Now I am calling the subform which has been changed to use the table as a temp table. I now get a new error, however, it's doing what I want it to. The error message seems to be getting ignored. Thanks for the suggestion on the findfirst, I will make that change shortly. Here are the new details:

I have created a form with many subforms related to the item in the main form. All the subforms work except for one. This one has a temp table behind it that is an accumulation of item ledger entries summed by "Item No.", "Variant code", "Location code" and "Unit Cost". The subform actually populates correctly but every time I change item numbers in my main form I get a message saying "Temp Item Ledger Entry cannot be modified in this form". It does actually update it so I can't figure out why I'm getting this message. I have given the main form, subform and custom table all permissions for the table. This message looks just like the message you would see if you went directly into the item ledger entry table and tried to modify it.

The way I am populating this subform is to call a function in the subform from the OnAfterGetRecord of the main form. The subform does have the property SourceTableTemporary set to yes.

Main form:

Form - OnAfterGetCurrRecord()

CurrForm.UPDATECONTROLS;
CurrForm.CAPTION("Document No.");

//DSW-ARH 4/13/10 ++++++++++
IF (Type = Type::Item) AND
   ("No." <> '') THEN
  CurrForm.Stock.FORM.CreateSummary("Document No.","No.");
//DSW-ARH 4/13/10 ----------

Function in subform:

CreateSummary(DocumentNo : Code[20];ItemNo : Code[20])
QtyTotal := 0;
UnitCost := 0;
QtyAvailable := 0;
DELETEALL;
ILE.RESET;
ILE.SETCURRENTKEY("Item No.","Variant Code","Location Code");
ILE.SETRANGE("Item No.",ItemNo);
ILE.SETRANGE(Open,TRUE);
ILE.SETRANGE(Positive,TRUE);
IF ILE.FINDFIRST THEN REPEAT
  ILE.CALCFIELDS("Cost Amount (Actual)","Reserved Quantity");
  UnitCost := ILE."Cost Amount (Actual)" / ILE.Quantity;
  QtyAvailable := ILE."Remaining Quantity" - ILE."Reserved Quantity";
  QtyTotal := QtyTotal + (ILE."Remaining Quantity" - ILE."Reserved Quantity");
  IF NOT GET(DocumentNo,ILE."Item No.",ILE."Variant Code",ILE."Location Code",UnitCost) THEN BEGIN
    INIT;
    "Quote No." := DocumentNo;
    "Item No." := ILE."Item No.";
    "Variant Code" := ILE."Variant Code";
    "Location Code" := ILE."Location Code";
    "Unit Cost" := UnitCost;
    "Qty. Available" := QtyAvailable;
    "Qty. Total" := QtyTotal;
    Quantity := ILE.Quantity;
    "Remaining Quantity" := ILE."Remaining Quantity";
    INSERT;
  END ELSE BEGIN
    "Qty. Available" += QtyAvailable;
    "Qty. Total" += QtyTotal;
    Quantity += ILE.Quantity;
    "Remaining Quantity" += ILE."Remaining Quantity";
    MODIFY;
  END;
UNTIL ILE.NEXT = 0;

Any help would be appreciated.

Andy

David_Singleton · 2010-04-14

TxHaz98 wrote:

It's not a real temporary table or at least it wasn't. ...

:shock: :shock: :shock: :shock: :shock: :shock: :shock: :shock: :shock: :shock:

So you were doing a DELETEALL on a filtered range of the Item Ledger Entry from a form.

If so then please please please stop now and get some help form a more senior developer in your company. This is extraordinarily dangerous, if you did this successfully on the live syste, then the whole database would be destroyed and virtually unusable.

garak · 2010-04-14

I think, it's not the real ILE Table because he do the following

IF NOT TempILE.GET("Document No.",ILE."Item No.",ILE."Variant Code",ILE."Location Code",UnitCost) THEN BEGIN

TxHaz98 · 2010-04-14

It is not the real item ledger entry table. It's a new table created in the 50,000 range.

David_Singleton · 2010-04-14

TxHaz98 wrote:

It is not the real item ledger entry table. It's a new table created in the 50,000 range.

Phew, your comments looked like it started as a Temp var based on ILE and then you made it not temp.

garak · 2010-04-14

do you need the data only in that time where you change an "Item no" on the Mainform :?: or ever like in some other forms/reports?

regards

TxHaz98 · 2010-04-15

only in that time the user is in the form.

garak · 2010-04-16

So, you are 1cm in front of the solution ;-)

garak · 2010-04-16

Little tip.

1. Your Table that u want to use do you have created --> OK
2. now you create a form based on this table where you want to see the datas --> OK
3. in this form u set the property "SourceTableTemporary" --> I belive that you don't do this
4. In this form, modifications are not allowed, so properties InsertAllowed, ModifyAllowed, DeleteAllowed = false --> I belive that you don't do this
5. U Create there a function to fill the form --> OK -> CreateSummary(Parameter)
6. Is CreateSummary() correct <---> mhm, no :-(

Example (simple)

CreateSummary(ItemNo : Code[20])
//U can also delete the temp rec here before but only needed if the mainform is open some times and in this time there a some new entries that must be shown in the subform

Rec.SETRANGE("Item No.",ItemNoParameter);
IF Rec.ISEMPTY THEN BEGIN
  ILE.RESET;
  ILE.SETCURRENTKEY("Item No.");
  ILE.SETRANGE("Item No.",ItemNoParameter);
  IF ILE.FIND('-') THEN BEGIN
    REPEAT
      Rec.INIT;
      Rec."Entry No." := ILE."Entry No.";
      Rec."Item No." := ILE."Item No.";
      Rec.Qty := ILE.Quantity;
      Rec.INSERT;
    UNTIL ILE.NEXT = 0;
  END;
END;
IF FINDFIRST THEN;
CurrForm.UPDATE(FALSE);

Now, u create a subformcontrol on your mainform, set there the property SubFormID to your new subform id, and give that subform a name like ILESubForm.
At last, go to Form - OnAfterGetCurrRecord() of the mainform and write this 2 little lines.

CurrForm.SubForm.FORM.FillTempRec("No.");

So, your are finished. test it, enjoy it <-- i have not test it, and the code here is based on my brain (no NAV here to test)
This little example works only for ITEMS :!: So, modify these codesnippes that it works also when a sales line is emty or the type is <> item.

Regards

Howdy, Stranger!

Quick Links

Categories

Need to create a temporary table for a subform

Comments