How To use temporary table data on a subform?

1. A little theory refresh

A record variable has an additional boolean property Temporary. If set TRUE, only table structure, triggers and functions will be available to your application - not the real data stored in database. It means, temporary table is empty at the beginning and all data operations made on its instance will be lost when application finished and have no cause on real data. All database operations take place only in system memory during the applications run. This is really very nice feature allowing us to play around with tables not having to worry about the real data. Such a table property declaration looks like this:

2. Form SourceTable property

The task becomes challenging first when we’d like to show temporary data on forms, not only use them hidden within our code. Yes, there is a new powerful property available starting with NAV 5.00 version which fully satisfies our needs. However, not every customer is about to make a decision to upgrade his system to this version in a near future. (anyway, wild usage of this new property could be a bit dangerous, especially when developing in a team and using DELETEALL commands :) So let's have a look at some tricky workaround steps allowing us to implement similar functionality not having to upgrade the system.

Navision form basic property SourceTable doesn't provide any temporary overload. It allows us to select a default table, nothing more. We need to remap standard form routines to disable form's default record processing. I'll explain the steps needed on a simple task:

3. Example task

We have following definition. Our customer would like to see some kind of customized customer details. Next to address fields a list of customer's sales documents should come in a single grid with possibility to open those documents directly. Following document types should be listed: quotes, orders, posted invoices, shipments and archived quotes and orders, too. During a kick-off meeting says our loved customer, just by the way of course, something like "...yeah, it would be great to see all customer's contacts on the list...". Clear, it means contacts MUST come on the list, too. :)

4. Solution

Good, we have a typical Master/Details situation to solve. The only difference is, our details come from several source tables but should look like they were from the same one. To achieve this we use a temporary table.

5. Details form (SubForm)

Let's start with the Details form (our future subform). First we select a best possible table structure to match our needs on most (ideally all) source tables. In our example the best one would be Sales Header (T36) of course. (since normal, posted and archived sales header tables have almost the same structure and our little contact issue should not be hard to solve)

As you can see, it's important to set some form properties properly. User should not be able to modify our tables so we restrict as much as possible to make our solution stable enough. Since we're about to misuse field "Document Type" a bit, we have to play around with some its properties on form. In fact we redefine the OptionString, the OptionCaptions and some code to match our needs.

Much more important are the global variables and trigger-level coding we have to do. We define our temporary instance of T36 (rRecTemp) and remap several low-level form triggers to this variable instead of using default Rec variable. We need to catch OnFindRecord, OnNextRecord and OnModifyRecord triggers to disable default form functionality.

Global Variables of the SubForm:

Name		DataType	SubType
rTempRec		Record	Sales Header	(property Temporary=Yes)
rCont		Record	Contact	
rSH		Record	Sales Header	
rSIH		Record	Sales Invoice Header	
rSSH		Record	Sales Shipment Header	
rSHA		Record	Sales Header Archive

Triggers of the SubForm:

Form - OnFindRecord(Which : Text[1024]) : Boolean
rRecTemp.COPY(Rec);
IF rRecTemp.FIND(Which) THEN BEGIN
  Rec := rRecTemp;
  EXIT(TRUE);
END ELSE
  EXIT(FALSE);

Form - OnNextRecord(Steps : Integer) : Integer
rRecTemp.COPY(Rec);
locResultSteps := rRecTemp.NEXT(Steps);
IF locResultSteps <> 0 THEN
  Rec := rRecTemp;
EXIT(locResultSteps);

Form - OnModifyRecord() : Boolean
ModifyRec;
EXIT(FALSE);

This part is very tricky. We let Navision call our own OnModify function and leave the OnModifyRecord trigger with FALSE result. This breaks the default OnModifyRecord processing. Our OnModify function replaces the default form Rec variable with our temporary instance rRecTemp:

ModifyRec()
rRecTemp := Rec;
rRecTemp.MODIFY;

Now we need to prepare some small function SetTempRecord for refreshing rRecTemp (SubForm) variable every time user navigates master form to another customer. This function will be called with reference (VAR) to the rRecTemp (MasterForm) variable with its contents updated during OnAfterGetCurrentRecord trigger execution of the master form. First we empty the subform temporary instance of rRecTemp and in the next step we fill it in with contents of VAR function parameter given by master form. Finally we have new contents for our subform listing ready to show.

SetTempRecord(VAR rRecIn : TEMPORARY Record "Sales Header")
rRecTemp.DELETEALL;
IF rRecIn.FINDSET(FALSE) THEN
  REPEAT
    rRecTemp.COPY(rRecIn);
    IF rRecTemp.INSERT THEN;
  UNTIL rRecIn.NEXT = 0;
CurrForm.UPDATE;

6. Header form (MasterForm)

Tasks to be solved on the Master form are pretty simple. However, some workarounds would be necessary, too. We need to update contents of our temporary instance of T36 each time user navigates to another customer and pass those contents to the SubForm using SetTempRecord function.

Global Variables of the MasterForm:

Name		DataType	SubType
rTempRec		Record	Sales Header	(property Temporary=Yes)
rCont		Record	Contact	
rContBusRel	Record	Contact Business Relation
rSH		Record	Sales Header	
rSIH		Record	Sales Invoice Header	
rSSH		Record	Sales Shipment Header	
rSHA		Record	Sales Header Archive

Form - OnAfterGetCurrRecord()
// don't forget to check 1000x that rRecTemp is REALY set Temporary
// before you start to test your solution :o)
rRecTemp.DELETEALL;
CLEARALL;

// Orders and Quotes
rSH.SETCURRENTKEY("Sell-to Customer No.");
rSH.SETRANGE("Sell-to Customer No.","No.");
rSH.SETRANGE("Document Type",rSH."Document Type"::Quote,rSH."Document Type"::Order);
IF rSH.FINDSET(FALSE) THEN REPEAT
  rRecTemp.INIT;
  rRecTemp."Document Type" := rSH."Document Type";
  rRecTemp."No." := rSH."No.";
  rRecTemp."Sell-to Customer No." := "No.";
  rRecTemp."Posting Date" := rSH."Posting Date";
  rRecTemp."Document Date" := rSH."Document Date";
  rRecTemp."Posting Description" := rSH."Posting Description";
  rRecTemp."No. Printed" := 36; // we'd like to show each source table on form
  IF NOT rRecTemp.INSERT(FALSE) THEN;
UNTIL rSH.NEXT=0;

// Posted Invoices
// "Document Type"::"Invoice" used since we do not show invoices from T36
// Sell-to/Bill-to logical background is not relevant for our example
// I just needed some standard key field  to play with :o)
rSIH.SETCURRENTKEY("Sell-to Customer No.");
rSIH.SETRANGE("Sell-to Customer No.","No.");
IF rSIH.FINDSET(FALSE) THEN REPEAT
  rRecTemp.INIT;
  rRecTemp."Document Type" := rRecTemp."Document Type"::Invoice;
  rRecTemp."No." := rSIH."No."; 
  rRecTemp."Sell-to Customer No." := "No.";
  rRecTemp."Posting Date" := rSIH."Posting Date";
  rRecTemp."Document Date" := rSIH."Document Date";
  rRecTemp."Posting Description" := rSIH."Posting Description";
  rRecTemp."No. Printed" := 112; // we'd like to show each source table on form
  IF NOT rRecTemp.INSERT(FALSE) THEN;
UNTIL rSIH.NEXT=0;

// Posted Shipments
// "Document Type"::"Credit Memo" used since we do not need them either)
rSSH.SETCURRENTKEY("Sell-to Customer No.");
rSSH.SETRANGE("Sell-to Customer No.","No.");
IF rSSH.FINDSET(FALSE) THEN REPEAT
  rRecTemp.INIT;
  rRecTemp."Document Type" := rRecTemp."Document Type"::"Credit Memo";
  rRecTemp."No." := rSSH."No.";
  rRecTemp."Sell-to Customer No." := "No.";
  rRecTemp."Posting Date" := rSSH."Posting Date";
  rRecTemp."Document Date" := rSSH."Document Date";
  rRecTemp."Posting Description" := rSSH."Posting Description";
  rRecTemp."No. Printed" := 110; // we'd like to show each source table on form
  IF NOT rRecTemp.INSERT(FALSE) THEN;
UNTIL rSSH.NEXT=0;

// Archived Sales Documents
// "Document Type"::"Blanket Order" and "Document Type"::"Return Order"
// used since we do not need them either)
// Yes, UR right - possible key violation due to multiple arch. doc.
// versions - not relevant for our example - we just show one :o)
rSHA.SETCURRENTKEY("Document Type","Sell-to Customer No.","No.",
                   "Doc. No. Occurrence","Version No.");
rSHA.SETRANGE("Sell-to Customer No.","No.");
IF rSHA.FINDSET(FALSE) THEN REPEAT
  rRecTemp.INIT;
  rRecTemp."Document Type" := rSHA."Document Type" + 4;
  rRecTemp."No." := rSHA."No.";
  rRecTemp."Sell-to Customer No." := "No.";
  rRecTemp."Posting Date" := rSHA."Posting Date";
  rRecTemp."Document Date" := rSHA."Date Archived";
  rRecTemp."Posting Description" := COPYSTR(rSHA."Posting Description" + 
                                 ' (v.'+FORMAT(rSHA."Version No.")+')',1,
                                 MAXSTRLEN(rSH."Posting Description"));
  rRecTemp."No. Printed" := 5107; // we'd like to show each source table on form
  IF NOT rRecTemp.INSERT(FALSE) THEN;
UNTIL rSHA.NEXT=0;

// Contacts
rContBusRel.SETCURRENTKEY("Link to Table","No.");
rContBusRel.SETRANGE("Link to Table",rContBusRel."Link to Table"::Customer);
rContBusRel.SETRANGE("No.","No.");
IF rContBusRel.FINDFIRST THEN BEGIN
  rCont.SETCURRENTKEY("Company No.");
  rCont.SETRANGE("Company No.",rContBusRel."Contact No.");
  IF rCont.FINDSET(FALSE) THEN REPEAT
    rRecTemp.INIT;
    rRecTemp."Document Type" := 6; // we just use something unique
    rRecTemp."No." := rCont."No.";
    rRecTemp."Sell-to Customer No." := "No.";
    rRecTemp."Posting Description" := COPYSTR(rCont.Name,1,
                                    MAXSTRLEN(rSH."Posting Description"));
    rRecTemp."Your Reference" := rCont."Phone No.";
    rRecTemp."No. Printed" := 5050; // we'd like to show each source table on form
  IF NOT rRecTemp.INSERT(FALSE) THEN;

  UNTIL rCont.NEXT=0;
END;

// Call our SubForm update function
CurrForm.frmDetails.FORM.SetTempRecord(rRecTemp);

As you can see we've misused several target fields for our needs. (i.e.: rRecTemp."No. Printed"). Remember, we're allowed to do so since we're not working with real data. We have to be pragmatic and efficient - we needed to store source table no. which is nothing more then an Integer value - so we looked for some Integer field in T36 we haven’t used yet. That's the beautiful thing on Navision temporary tables :)

7. Demonstration Form

8. Navision objects

There are 2 forms in the fob (MasterForm and DetailsForm as shown above). Fob was compiled with 4.00 version.

1. F99911: Header form (Master)
2. F99912: Details form (SubForm)

You'll found several additional features and functionalities implemented there, too. Enjoy it! :o)
The Geepz