Temporary tables, created in server memory instead of the database, are widely used to store the interim results of complex calculations. Temp tables can be used as buffers for data presented to the user, when the dataset cannot be obtained directly from a table.
An example of such dataset can be a list of certificates issued or prolonged in the past year, that have not been revoked and not yet expired. While it is possible to construct a SQL query that will collect this data from a join of several tables, C/AL code for this task will be a little more intricate and requires a temporary storage for the records.
In this recipe, a temporary table is used to store a list of certificates collected in a C/AL function. The table will be used as a data source for a page to present the data to the user.
- Edit the codeunit 50010 Item Certificate Mgt. and declare a global function
CollectProlongedNotRevokedCertificates. To allow the function to be called from other objects, access its properties and set Local = No. - Declare a function's parameter
TempItemCertificate:Record "Item Certificate". - Set the checkmark in the Var field.
- In the local declarations inside the function, create the following variables:
Name
DataType
Subtype
ItemCertificateActionRecord
Item Certificate Action
ItemCertificateRecord
Item Certificate
- First of all, the function should make sure that there are no residual records left in the recordset:
TempItemCertificate.RESET; TempItemCertificate.DELETEALL; - Then it starts collecting data for the new recordset:
WITH ItemCertificateAction DO BEGIN SETRANGE("Action Date", CALCDATE('<-1Y>',WORKDATE),WORKDATE); SETFILTER("Expiration Date",'>%1',WORKDATE); IF FINDSET THEN REPEAT IF NOT TempItemCertificate.GET( ItemCertificateAction."Certificate No.") THEN IF NOT ItemCertificate.IsCertificateRevoked( "Certificate No.") THEN BEGIN ItemCertificate.GET( ItemCertificateAction."Certificate No."); TempItemCertificate := ItemCertificate; TempItemCertificate.INSERT; END; UNTIL NEXT = 0; END; - The
IsCertificateRevokedfunction, called in the code, should be declared in the table 50010Item Certificate. Create the function, change its return type toBoolean, and add a local variable:Name
DataType
Subtype
ItemCertificateActionRecord
Item Certificate Action
IsCertificateRevokedcalls the functionCertificateActionExiststo find out if there is aRevokedaction registered for the certificate:EXIT(CertificateActionExists( ItemCertificateAction."Action Type"::Revoked));- Open the page 50013 Certificates List in the page designer and click Page Actions in the View menu to access the list of page actions.
- Insert an action item in the RelatedInformation action container. Name it
ViewActive. - Access the action's C/AL code and write the following code in the
OnActiontrigger:ItemCertificateMgt.CollectProlongedNotRevokedCertificates( TempItemCertificate); PAGE.RUN(PAGE::"Certificates List",TempItemCertificate); - The
TempItemCertificatevariable should be declared in the list of local variables in theOnActiontrigger:Name
DataType
Subtype
TempItemCertificateRecord
Item Certificate
- Open the properties of the variable
TempItemCertificateand set Temporary to Yes. - Save the page, close the page editor, and run the page from the object designer.
- Run the page 50013 Certificates List and execute the action View Active.
The CollectProlongedNotRevokedCertificates function declared in codeunit Item Certificate Mgt., will analyze item certificates within the given period range and insert the resulting dataset into the record parameter TempItemCertificate.
The function's parameter is declared as a record variable, but the variable passed to it from the action trigger in Step 11 has the TEMPORARY attribute. This property does not change the way the table is accessed in the C/AL code, but data manipulation functions, such as FINDSET, INSERT, MODIFY, and so on, called on this variable won't issue SQL queries. Instead, records will be retrieved, inserted, and modified in a recordset stored in memory, as we will see in the code sample.
First thing the function does, is remove all filters from the parameter variable and delete all data it may contain. We simply call DELETEALL on the Item Certificate record, this statement won't affect records in the database.
After clearing the temporary buffer, we begin collecting data into it. For the sake of simplicity of the example, date range is limited to the period from work date - 1 year to the work date:
SETRANGE("Action Date",CALCDATE('<-1Y>',WORKDATE),WORKDATE);
This will filter out all certificates with no actions in the past year.
Next, we leave only certificates with an expiration date later than the application work date:
SETFILTER("Expiration Date",'>%1',WORKDATE);
Code will run a loop on all certificates' actions and rule out revoked certificates, inserting all active ones into the buffer.
Finally, from Step 9 to Step 13 we create a page action that will execute the function. First line in the action trigger calls the function CollectProlongedNotRevokedCertificates passing a temporary variable into which the records will be inserted as shown:
ItemCertificateMgt.CollectProlongedNotRevokedCertificates( TempItemCertificate);
In the next line, PAGE.RUN will open the page Certificates List with the temporary buffer as a source. The page will take the data to display from the buffer instead of the database:
PAGE.RUN(PAGE::"Certificates List",TempItemCertificate);
It is possible to build a page based on a temporary table. The SourceTableTemporary page property tells that the page won't select data from the database, but will read a buffer that must be filled on page initialization.
For details, see the Simpifying data access with queries recipe where we will fill the temporary page source using a query object.