AL Table Proxy Generator Tool – ARKSD Consultancy Private Limited

This is the Sixth post in the series. If you want to go to previous post click here.

From the series of steps this post is dedicated to Step-5:

As a fifth Step we will Create Integration Table(s) in Business Central for the Dataverse table created in Step-4

When table(s) are present in Microsoft Dataverse, but not in Business Central, this tool can be run to generate integration or proxy table(s) for the specified table(s).

The AL Table Proxy Generator tool you can find in AL Language extension by name altpgen.exe in the equivalent folder [C:\Users\ashwi\.vscode\extensions\ms-dynamics-smb.al-7.4.502459\bin\]

Run PowerShell ISE as Administrator

Change to folder containing altpgen.exe

Above command should be single line, I have break for clarity of parameters. Below is the sample command.

.\altpgen -project:"C:\Userdata\AL Project\DataVerseIntegration" -packagecachepath:"C:\Userdata\AL Project\DataVerseIntegration\packagecachepath" -serviceuri:"https://xxxxxxxxxxx.crm8.dynamics.com/" -entities:cr95d_prospects -baseid:50125 -tabletype:CDS

When you run the command, it will ask for authentication, provide and continue.

Once you Accept the Permissions request, you can see the output of the command.

Don’t worry for warnings, Your AL file will be generated in specified folder.

Below is the sample of generated file, it is suggested to generate this file using the tool, don’t try to create manually.

table 50126 "CDS cr95d_Prospects"
{
  ExternalName = 'cr95d_prospects';
  TableType = CDS;
  Description = '';

  fields
  {
    field(1;cr95d_ProspectsId;GUID)
    {
      ExternalName = 'cr95d_prospectsid';
      ExternalType = 'Uniqueidentifier';
      ExternalAccess = Insert;
      Description = 'Unique identifier for entity instances';
      Caption = 'Prospects';
    }
    field(2;CreatedOn;Datetime)
    {
      ExternalName = 'createdon';
      ExternalType = 'DateTime';
      ExternalAccess = Read;
      Description = 'Date and time when the record was created.';
      Caption = 'Created On';
    }
    field(4;ModifiedOn;Datetime)
    {
      ExternalName = 'modifiedon';
      ExternalType = 'DateTime';
      ExternalAccess = Read;
      Description = 'Date and time when the record was modified.';
      Caption = 'Modified On';
    }
    field(24;statecode;Option)
    {
      ExternalName = 'statecode';
      ExternalType = 'State';
      ExternalAccess = Modify;
      Description = 'Status of the Prospects';
      Caption = 'Status';
      InitValue = " ";
      OptionMembers = " ", Active, Inactive;
      OptionOrdinalValues = -1, 0, 1;
    }
    field(26;statuscode;Option)
    {
      ExternalName = 'statuscode';
      ExternalType = 'Status';
      Description = 'Reason for the status of the Prospects';
      Caption = 'Status Reason';
      InitValue = " ";
      OptionMembers = " ", Active, Inactive;
      OptionOrdinalValues = -1, 1, 2;
    }
    field(28;VersionNumber;BigInteger)
    {
      ExternalName = 'versionnumber';
      ExternalType = 'BigInt';
      ExternalAccess = Read;
      Description = 'Version Number';
      Caption = 'Version Number';
    }
    field(29;ImportSequenceNumber;Integer)
    {
      ExternalName = 'importsequencenumber';
      ExternalType = 'Integer';
      ExternalAccess = Insert;
      Description = 'Sequence number of the import that created this record.';
      Caption = 'Import Sequence Number';
    }
    field(30;OverriddenCreatedOn;Date)
    {
      ExternalName = 'overriddencreatedon';
      ExternalType = 'DateTime';
      ExternalAccess = Insert;
      Description = 'Date and time that the record was migrated.';
      Caption = 'Record Created On';
    }
    field(31;TimeZoneRuleVersionNumber;Integer)
    {
      ExternalName = 'timezoneruleversionnumber';
      ExternalType = 'Integer';
      Description = 'For internal use only.';
      Caption = 'Time Zone Rule Version Number';
    }
    field(32;UTCConversionTimeZoneCode;Integer)
    {
      ExternalName = 'utcconversiontimezonecode';
      ExternalType = 'Integer';
      Description = 'Time zone code that was in use when the record was created.';
      Caption = 'UTC Conversion Time Zone Code';
    }
    field(33;cr95d_ProspectName;Text[100])
    {
      ExternalName = 'cr95d_prospectname';
      ExternalType = 'String';
      Description = 'Required name field';
      Caption = 'Prospect Name';
    }
    field(34;cr95d_ContractAmount;Decimal)
    {
      ExternalName = 'cr95d_contractamount';
      ExternalType = 'Money';
      Description = '';
      Caption = 'Contract Amount';
    }
    field(37;ExchangeRate;Decimal)
    {
      ExternalName = 'exchangerate';
      ExternalType = 'Decimal';
      ExternalAccess = Read;
      Description = 'Exchange rate for the currency associated with the entity with respect to the base currency.';
      Caption = 'Exchange Rate';
    }
    field(38;cr95d_contractamount_Base;Decimal)
    {
      ExternalName = 'cr95d_contractamount_base';
      ExternalType = 'Money';
      ExternalAccess = Read;
      Description = 'Value of the Contract Amount in base currency.';
      Caption = 'Contract Amount (Base)';
    }
    field(39;cr95d_Probability;Integer)
    {
      ExternalName = 'cr95d_probability';
      ExternalType = 'Integer';
      Description = '';
      Caption = 'Probability';
    }
    field(40;cr95d_Stage;Option)
    {
      ExternalName = 'cr95d_stage';
      ExternalType = 'Picklist';
      Description = '';
      Caption = 'Stage';
      InitValue = Lead;
      OptionMembers = Lead, Opportunity, Won, Lost;
      OptionOrdinalValues = 256080000, 256080001, 256080002, 256080003;
    }
    field(42;cr95d_ForcastedRevenue;Decimal)
    {
      ExternalName = 'cr95d_forcastedrevenue';
      ExternalType = 'Money';
      Description = '';
      Caption = 'Forcasted Revenue';
    }
    field(43;cr95d_forcastedrevenue_Base;Decimal)
    {
      ExternalName = 'cr95d_forcastedrevenue_base';
      ExternalType = 'Money';
      ExternalAccess = Read;
      Description = 'Value of the Forcasted Revenue in base currency.';
      Caption = 'Forcasted Revenue (Base)';
    }
  }
  keys
  {
    key(PK;cr95d_ProspectsId)
    {
      Clustered = true;
    }
    key(Name;cr95d_ProspectName)
    {
    }
  }
  fieldgroups
  {
    fieldgroup(DropDown;cr95d_ProspectName)
    {
    }
  }
}

Create a Page for above table

page 50122 "CDS Prospect List"
{
    Caption = 'CDS Prospect List';
    PageType = List;
    SourceTable = "CDS cr95d_Prospects";
    Editable = false;
    ApplicationArea = All;
    UsageCategory = Lists;
    RefreshOnActivate = true;

    layout
    {
        area(Content)
        {
            repeater(Group)
            {
                field("No."; Rec.cr95d_ProspectsId)
                {
                    Caption = 'No.';
                    ApplicationArea = All;
                }
                field(Name; Rec.cr95d_ProspectName)
                {
                    Caption = 'Name';
                    ApplicationArea = All;
                }
                field(Stage; Rec.cr95d_Stage)
                {
                    Caption = 'Stage';
                    ApplicationArea = All;
                }
                field("Probability"; Rec.cr95d_Probability)
                {
                    Caption = 'Probability';
                    ApplicationArea = All;
                }
                field("Contract Amount"; Rec.cr95d_ContractAmount)
                {
                    Caption = 'Contract Amount';
                    ApplicationArea = All;
                }
                field("Contract Amount (Base)"; Rec.cr95d_contractamount_Base)
                {
                    Caption = 'Contract Amount (Base)';
                    ApplicationArea = All;
                }
                field("Forecast Revenue"; Rec.cr95d_ForcastedRevenue)
                {
                    Caption = 'Forecast Revenue';
                    ApplicationArea = All;
                }
                field("Forecast Revenue (Base)"; Rec.cr95d_forcastedrevenue_Base)
                {
                    Caption = 'Forecast Revenue (Base)';
                    ApplicationArea = All;
                }
                field("Exchange Rate"; Rec.ExchangeRate)
                {
                    Caption = 'Exchange Rate';
                    ApplicationArea = All;
                }
            }
        }
    }
}

You can read more about AL Table Proxy Generator Tool from Microsoft docs

AL Table Proxy Generator

Now you are good to proceed with Next Step.

You can jump to Next Step from here.

In this post I will discuss about how we can Synchronize data to Custom Tables using Dataverse.

This post is divided in series of small posts, one post for each involved step.

You follow each post link in sequence and at end we will achieve the final Goal set in starting of this post.

This post will walkthrough setting up an integration between a Custom table Prospects in Business Central and a Custom table Prospects in Microsoft Dataverse.

One thing important keep in mind while doing all the steps we need to use the Admin login you used to setup these environments.

Also, the Base Currency of Business Central & Dataverse Environment should be same else you will run into issue.

If the currencies of Business Central and Dataverse do not match, similar message will be displayed.

LCY Code XXX does not match ISO Currency Code XXX of the Dataverse base currency.

The Base Currency defined for Dataverse after setup is not possible to change however you can match it in Business Central by changing LCY Code in General Ledger Setup.

Let’s start with introduction to environment we are going to use, throughout the process and steps described below.

Introduction to Environment:

Before we start with the Steps Involved, let me introduce with the environment which I am going to use in this walkthrough.

Business Central Environment: