How to export data using TextBuilder in Business central.

Today we will see how we can data from business central table using new data type Text Builder.

To check how this data type work build small program as below which pick up data from customer table and export the data in text file.

pageextension 50101 CustomerMasterExtension extends "Customer List"
{
    actions
    {
        addlast(General)
        {
            action(DataExport)
            {
                Caption='Data Export to Text';
                ApplicationArea=All;
                PromotedCategory=Process;
                Promoted=true;
                Image=ExportDatabase;
                trigger OnAction()
                var
                CustomerMaster :Record Customer;
                Tempblob:Codeunit "Temp Blob";
                TextFileBuilder :TextBuilder;
                FileName:Text;
                InStreamData:InStream;
                OutStreamData:OutStream;
                begin 
                    FileName:='CustomerData.Txt';
                    TextFileBuilder.AppendLine('Customer No'+','+'Customer Name'+','+'Balance');
                    If CustomerMaster.FindSet() then repeat
                        CustomerMaster.SetAutoCalcFields(CustomerMaster.Balance);
                        TextFileBuilder.AppendLine(CustomerMaster."No."+','+CustomerMaster.Name+','+Format(CustomerMaster.Balance));
                    until CustomerMaster.Next()=0;
                    Tempblob.CreateOutStream(OutStreamData);
                    OutStreamData.WriteText(TextFileBuilder.ToText());
                    Tempblob.CreateInStream(InStreamData);
                    DownloadFromStream(InStreamData,'','','',FileName);
                end;
            }
        }
    }
}

If you look at the above code it is taking required data from customer table and export the data in text file as below.

To get more insight of data type have a look at MS documentation from below link

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/textbuilder/textbuilder-data-type

Hope this will help you.

Stay tuned for more.

How to define multiple layouts for report in Business Central

With the release of business central V20 lot of new features are introduced and one of the features in you can define multiple layouts for reports while developing

In earlier days we are defining multiple layouts as below.

Now with the new release new rendering section is added while creating report in AL development

In the above rendering section we can define multiple layout as below

After compilations of report we can see the layout is created as below

Isn’t that cool features for layout

This features is available for Runtime 9.0

Hope this will help you..

Stay tuned for more.

How to change primary key in Business Central Table

Recently one of my customer asked me to add few more fields on primary key and I was looking at the customer like he asked something which is daunting task to be done with business central as everybody aware of that we can not change the primary key fields when table is already having data as while deploying the app it throws an error as below

Even I tried using Force sync also but it is still failing to deploy the app as this is breaking change for the app.

Now question is how to do this and overcome from such situation as your customer is behind you to do that.

To make it work we need to use ObsoleteState and ObsoleteReason that means we need to make your table obsolete and create a new table with new primary key and change the references in all those object where the object is referred.

To make the table obsolete mention the property as below.

Obsolete property value

ValueDescription
NoNot obsolete. This is the normal/default setting.
PendingWill become obsolete in a future version.
RemovedHas been made obsolete.

After making above table obsolete created new table with the new primary key.

Next point is how to transfer data in the new table .

To transfer data from obsolete table to new table used Installed code unit . Additionally you can use temporary table to update data from old table to new table.

If you would like to learn more about ObsoleteState Property then visit here.

Hope this will help you.

Stay tuned for more updates.

How to allow your app to debug in business central

With the release of Business Central Wave 2 2021 aka BCV19 new version of AL language is also published.

Do you remember this post where in I explain the use of showmycode property from app.json file. With this property we have only one option either we allowed to debug and download your app or vice versa.

Now with new version of AL language this property has been deprecated and new property has been introduced resourceExposurePolicy where in we get leverage to control your app from debug ,download the app file.

This property will be automatically added when new project is created as below.

If you can see in this property we have three options which can be used to control the app and your IP.

  1. AllowDebugging :- Allow to control the debugging of your app when it is published. The default setting is true.
  2. AllowDownloadingSource :- Allow to control the download of your app source code .The default setting is false.
  3. IncludeSourceInSymbolFile: Allow to control symbol to be included in the source code of package. The default setting is false.

Can I add resourceExposurePolicy and showmycode property together ?

With the introduction of this new property in app.json file we can not use showmycode property as it will show an error and warning as below.

Hopefully this will help us to control our IP and provide more security on our app.

Stay tuned for more.

How to modify table data in business central

This week one of the customer post few transaction and found that one of the field user enter some wrong information. In ideal situation user need to reverse the transaction and post correct transaction which is safest approach.

But do you remember those old days where we have access to the table and can modify the data but with business central this ability no more available . I just build one small extension to modify the data in the table where in we can modify the posted data .

Important: It is not recommended to change data in the tables directly as it will cause inconsistency in database and make database vulnerable.

Just check following video to check small amount of work I did.

Additionally I found blog from Volodymyr Dvernytskyi for Data Editor .Please have a look into it.

Let me know your feedback.

How to execute mathematical function in business central

Are you looking for for running some mathematical function in business central then may be this blog post is interesting for you.

In business central new system code unit is provided to execute some important mathematical function.

Build one simple page to show few expressions as below

One can determine following values from this codeunit.

MethodRemarks
PiReturns the value of pi.
EReturns the value of E.
Abs Returns the absolute value of a Decimal number.
AcosReturns the angle whose cosine is the specified number.
AsinReturns the angle whose sine is the specified number.
AtanReturns the angle whose tangent is the specified number.
PowReturns a specified number raised to the specified power.
SQRTReturns the square root of a specified number.
FloorReturns the largest integral value less than or equal to the specified decimal number.
ExpReturns e raised to the specified power.
CeilingReturns the smallest integral value that is greater than or equal to the specified decimal number.

Hopefully you find this helpful to you.

Stay tuned for more updates.

How to add keys in Base table of Business Central

With the release of Wave 1 2021 lot of new feature introduced . One of the new feature is that partners now can add keys in base table with customized field which was not possible earlier

Today we will see how we can do that

First Lets see what is happening is earlier version of business central. In earlier version we can only add the keys with the field added on table extension and we are not able to see the field available in base table for adding in keys

Look at the following example as I created one simple extension in V17 and trying to add key

Now when I try to add the keys in this table extension it will populate only those fields which are added in table extension

In New version of Business central i.e. Wave 1 2021 we can Add more keys with base table fields and use to improve the performance while retrieving or updating records

Now look at the following example for new version .Just created a simple table extension

Now when I try to add the keys in this table extension it will populate all the fields available in base table as below

So that means we can create keys with

  1. Fields from base table
  2. Fields from table extension

Can I create combination of fields from base table and fields from table extension ?

And the obvious answer is no and reasons are simple that both base table and table extension are two different tables in Sql server.

I hope you will find this information useful

So stay tuned for more updates.

Cheers.

How to add XML comments in AL Code :- Business Central

With the release of Business Central Wave 1 2020 XML comments is available in AL code.

If you would like to add documentation to your code then you can use XML documentation .The documentation comment must immediately before the object such as codeunit,table,functions etc.

The syntax for adding XML comments in your code is triple slashes /// followed by one of the supported XML tags. There is IntelliSense support for writing documentation comments. Most importantly providing a template documentation comment when writing the third slash in the triple slash.

These documentation comments are visible when hovering over source symbols.

Advantage :

  1. XML document comment will improve readability ,add useful information about the code implementation and help others take over your code .
  2. With XML comments it also enable intellisense in AL objects that you add in your code as a help to other developers who is going extend your code.

Let sees an example how we can add the xml comments.

Created one simple code unit where we can add documentation comments.

Please note that If you have the showMyCode setting in the app.json file set to false and download an app package; the app package will not contain any XML comments.

Hope this will help you.

How to Use SetLoadFields in Business Central for performance

With the release of Business Central Wave 1 2020 the partial records capability is available.

The partial records capability is allows for loading a subset of normal table fields when accessing the data source based on SQL.

To access the data source we use GET ,FIND NEXT. When we use these methods then runtime it load all fields and will hamper the performance while fetching the records.

Now from business central wave 2 2020 we can use ‘Partial records’ capability.

To accommodate partial records following methods available in business central. These methods are avaaible on RecordRef and Record datatype in AL. These methods are loaded in two groups i.e. Subsequent loads and Current load

MethodDescription
SetLoadFieldsSpecifies a set of fields to be initially loaded when the record is retrieved from its data source.
AddLoadFieldsAdds fields to the current set of fields to be initially loaded when the record is retrieved from its data source.
AreFieldsLoadedChecks whether the specified fields are all initially loaded.
LoadFieldsAccesses the table’s corresponding data source to load the specified fields.

Lets See few examples of these methods

Following example is for summation of Quantity fields from Item Ledger Entry .In this we would like to make calculation of only Quantity fields and not require the others fields to be loaded.

If you see SetLoadFields is call before data fetching operation starts. This will determine which fields needed for Find Set Call.

Advantage of this is that it gives you ability to load only those fields which are required to perform the operation which will make execution of code faster and give more performance. This feature is recommended to use while fetching the records and not for Insert,Modify or Delete operation.

Using the above method we added the required fields but what will happen to other fields which hasn’t been loaded .In such cases platform does an implicit GET and loads the missing fields, called as JIT Loading (Just in Time).

When JIT loading occurs, another access to the data source is required. These operations tend to be faster because they’re GET calls. GET calls can often be served by the server’s data cache or become clustered index seeks on the database.

Iterating over records in the database, enumerator is created based on selected fields and row is fetched when NEXT is called. but certain operations will not follow enumerator like if we passing a record by value and not by VAR .This operation actually creates new copy of the record and original records and copy of the record not share the filters ,selected field for loading. That means to access unloaded fields it need to trigger JIT load and but it will not update enumerator which means for future iterations also require JIT load.

To overcome such situations we can do following

  1. Pass the record reference using VAR.
  2. Call AddLoadFields on the original records before passing by value.
  3. Use SetLoadFields before calling FIND,GET,NEXT.

Hope this will help you to understand for partial records and stay tuned for more updates.

For more updates you can follow my blog on following channels.

Use of implicit ‘with’ will be removed in the future :-Business Central

Though above warning was informed by MS long time back but it has introduced in latest version of AL.

With the update of new version of AL extension this warning is populated in AL code.

Once you update above AL language version then you will find below warning

Now this warning will become error in near future that means we need to correct it anyhow . If your app contain more than 100 pages then it will be too much efforts to make it manually and tedious task.

To make it superfast work we have one extension available in marketplace which will help you to resolve it within seconds.

Once you install the extension then Press ‘F1’ and run following command

‘Fix Implicit with usages’

Just have look at the following file

Now run above command and relax as it will fix the warning in all pages.

Result of the file after running command

It will fix the all warning in all the files.

Hope this will help you

Stay tuned for more update