How to export data with a date formula filter?

Recently, I got the following question from a partner: Using NAVBIS, is it possible to export data by applying a date formula as a filter?

In this case the customer wants to:

• Export all transactions created in the past week
• Run a recurring export.

Solution:

By adding a Timer Event to the pipeline you can export data with a certain interval. This is a default functionality provided in the system. The Timer Event is capable to register filters, however, it uses the standard Microsoft Dynamics NAV TableFilter datatype which does not support date formulas!

BIS for Microsoft Dynamics NAV 2009:

In Microsoft Dynamics NAV 2009 we had our own filtering mechanism which did support date formulas. There are several reasons for this:

• It is always good to use standard functionality as much as possible.
• For Microsoft Dynamics NAV 2009, the filters were stored in a table structure which makes it harder to handle, however, TableFilter stored as a field in a table makes it much easier to handle.
• Microsoft Dynamics NAV has a standard user interface for TableFilters. This accommodates option field validations, etc.

BIS for Microsoft Dynamics NAV 2013:

BIS for Microsoft Dynamics NAV 2013 comes with a Software Development Kit (SDK), which allows you to extend the product with additional stuff.

Following are some working instructions for this case:

1. We will use a Job Queue which specifies a data formula as a parameter:
   
2. 1. The Job Queue will execute a codeunit which has the Job Queue Entry table as the interface of the OnRun trigger. In this trigger we have to invoke the NAVBIS2013 pipeline by using the Code Event. The Code Event allows you to invoke a pipeline from a NAV object:

   OBJECT Codeunit 50000 NXXX Date Filter
   {
    OBJECT-PROPERTIES
    {
      Date=12-05-14;
      Time=09:44:33;
      Modified=Yes;
      Version List=;
    }
    PROPERTIES
    {
      TableNo=472;
      OnRun=VAR
        CustLedgEntry@1000 : Record 21;
        RegisterEvent@1001 : Codeunit 11069482;
        RecRef@1002 : RecordRef;     
    BEGIN
      // Open the record ref
      RecRef.OPEN(DATABASE::"Cust. Ledger Entry");
   
      // Apply the filter on the Posting Date field
      ParseFilterStringValue(
        RecRef,
        CustLedgEntry.FIELDNO("Posting Date"),
        "Parameter String");
     
      // Register the event for the filtered record set
      RegisterEvent.RegisterEvent(RecRef);
    END;
    }
    CODE 
    {
      PROCEDURE ParseFilterStringValue@1(RecRef@1002 : RecordRef;FieldNumber@1003 : Integer;FilterExpression@1000 : Text) : Text;
        VAR
          TableRef@1001 : Codeunit 11068685;
          FldRef@1004 : FieldRef;
        BEGIN
          // Parse the filter string and apply it on the specified 
          // field number
          FldRef := RecRef.FIELD(FieldNumber);
          TableRef.String2FieldFilter(FldRef,FilterExpression,TRUE);      END;
     BEGIN
     END.
    }
   }

   TIP: Page 17 of the SDK shows a complete walk-through for a code event.

3. The next step is to setup a Connectivity Studio connection with a Code Event.
   
4. After putting the status of the Job Queue Entry to Active the export will be triggered. The result is that a message will be created for each Cust. Ledger Entry.
   

NOTE: The Code Event creates an individual message for each record. If this is not sufficient you’ll have to create the messages yourself within the custom codeunit. Checkout the API reference of the SDK for more details.