Tag: business central

Putting Queries to Use in Business Central

We’ve had query objects for a while now – since NAV 2013 (I think). In theory they sound great, link a bunch of tables together, aggregate some columns, get distinct values, left join, right join, cross join – executed as a single SQL query.

Why Don’t We Use Queries Much?

In practice I haven’t seen them used that much. There’s probably a variety of reasons for that:

  • We have limited control over the design and execution of the query at runtime. The design is pretty static making it difficult to create useful generic queries short of throwing all the fields from the relevant tables in – which feels heavy-handed
  • I find how the links between dataitems unintuitive
  • It isn’t easy to visualise the dataset that you are creating when writing the AL file
  • Habit – we all learnt FindFirst/Set, Repeat and Until when we first started playing with CAL development and are more comfortable working with loops than datasets. Let’s be honest, the RDLC report writing experience hasn’t done much to convert us to a set-based approach to our data

However, just because there is room for improvement doesn’t mean that we can’t find good uses for queries now. Queries are perfect for:

  • Using queries to select DISTINCT values in the dataset
  • Aggregates – min, max, sum, average, count – especially for scenarios that aren’t suited to flowfields
  • Date functions – convert date columns to day/month/year – which allows you to easily aggregate another column by different periods
  • Outer joins – it’s possible, but far more expensive, to create this kind of dataset by hand with temporary tables
  • Selecting the top X rows
  • Exposing as OData web services

It’s the last point in that list that I particularly want to talk about. We’ve been working on a solution lately where Business Central consumes its own OData web services.

What?! What kind of witchcraft is this? Why would you consume a query via a web service when you can call it directly with a query object? Hear me out…

Consuming Queries

I think you’ve got two main options for consuming queries via AL code.

Query Variable

You can define a variable of type query and specify the query that you want to run. This gives you some control over the query before you execute it – you can set filters on the query columns and set the top number of rows. Call Query.Open and Query.Read to execute the query and step through the result set.

The main downside is that you have to specify the query that you want to use at design-time. That might be fine for some specific requirement but is a problem if you are trying to create something generic.

Query Keyword

Alternatively we can use the Query keyword and execute a query by its ID. Choose whether you want the results in CSV (presumably this is popular among the same crowd that are responsible for an increase in cassette tape sales) or XML and save them either to disk or to a stream.

The benefit is that you can decide on the query that you want to call at runtime. Lovely. Unfortunately you have to sacrifice even the limited control that a query variable gave you in order to do so.

OData Queries/Pages

Accessing the query via OData moves us towards having the best of both worlds. Obviously there is significant extra overhead in this approach:

  • Adding the query to the web service table and publishing
  • Acquiring the correct URL to a service tier that is serving OData requests for your query
  • Creating the HTTP request with appropriate authentication
  • Parsing the JSON response to get at the data that you want

This is significantly more work than the other approaches – let’s not pretend otherwise. However, it does give you all the power of OData query parameters to control the query. While I’ve been talking about queries up til now almost all of this applies to pages exposed as OData services as well.

  • $filter: specify column name, operator and filter value that you want to apply to the query, join multiple filters together with and/or operators
  • $select: a comma-separated list of columns to return i.e. only return the columns that you are actually interested in
  • $orderBy: specify a column to order the results by – use in combination with $top to get the min/max value of a column in the dataset
  • $top: the number of rows to return
  • $skip: skip this many rows in the dataset before returning – useful if the dataset is too large for the service tier to return in a single call
  • $count: just return the count of the rows in the dataset – if you only want the count there is no need to parse the JSON response and count them yourself

Some More About Translating Business Central Apps

I’ve written before about using Azure Cognitive Services to translate the captions in the .xlf file that is generated when you compile your Business Central app. For us, the motivation is to make our apps available in as many countries as possible in AppSource.

Since then Søren Alexandersen has announced that it will not be necessary to provide all of a country’s official languages to make your app available in that country.

If you going to provide translations you might be interested in how to improve upon a the approach of the last post.

The Problem

The problem of course is that we are relying on machine translation to translate very short phrases or single words. A single word can mean different things and be translated in many different ways into other languages depending on the context. Context that the machine translation doesn’t have. That’s what makes language and etymology simultaneously fascinating and infuriating.

The problem is compounded by abbreviations and acronyms. You and I know that “Prod. Order” is short for “Production Order”. But “Prod” is itself an English word that has nothing to do with manufacturing.

We know that FA is likely short for “fixed asset” but if you don’t know that the context is an ERP system it could mean a whole range of things. How is Azure supposed to translate it?

What we need is some domain-specific knowledge.

The Solution

When we think about it we know that we’ve already got thousands of translations of captions into the languages that we want – if only we can get them into a useful format. We’ve got Docker images of Business Central localisations. They contain the base app for the location complete with source/target pairs for each caption.

If you can get hold of the xlf file it’s a relatively simple job to search for a trans-unit that has a source node matching the caption that you want to translate and find the corresponding translated target node.

As an example, I’ve created a container called ch from the image mcr.microsoft.com/businesscentral/sandbox:ch – the Swiss localisation of Business Central.

Find and expand the base application source. 

$ContainerName = 'ch'
$Script = {Expand-Archive "C:\Applications.*\Base Application.Source.zip" -DestinationPath 'C:\run\my\base'}

Invoke-ScriptInBCContainer -containerName $ContainerName -scriptblock $Script

This script will find the zip file containing the localised base application and extract it to the ‘C:\run\my\base’ folder. This will take a few minutes but when it is done you should see a Translations folder containing, in the case of Switzerland, four .xlf files.

The following script will load the fr-CH .xlf file into an Xml Document, search for a trans-unit node which has a child source node matching a given string and return the target i.e. the fr-CH translation.

$Language = 'fr'
$enUSCaption = 'Prod. Order Line No.'
[xml]$xlf = Get-Content (Get-ChildItem "C:\ProgramData\NavContainerHelper\Extensions\$ContainerName\my\base\Translations" -Filter "*$Language*").FullName

$NSMgr.AddNamespace('x',$xlf.DocumentElement.NamespaceURI)
$xlf.SelectSingleNode("/x:xliff/x:file/x:body/x:group/x:trans-unit[x:source='$enUSCaption']", $NSMgr).target

Which returns “N° ligne O.F.” – cool.

Some Obvious Points

I’m going to leave it there for this post, save for making a few obvious points.

  • This is hopelessly inefficient. Downloading the localised Docker image, creating the container, extracting the base app – all to get at the .xlf files. We’re going to want a smarter solution before using this approach in any volume and for more languages
  • Each .xlf file is 60+MB – that takes a while to load into memory – you’ll want to keep the variable in scope and reuse it for multiple searches rather than reloading the document
  • Not all of the US English captions you create in your app will exist in the base application – you’ll still want to send those off for translation.

Maybe we can start to address these points next time…

Satisfying Your Case-Sensitive Obsession with Regex

Obsession is probably a little strong, but I do like tidy code. You know – proper indentation, a sensible number of comments (which can be more than none but there shouldn’t be as much comment as code) and good names. Hungarian notation brings me out in a rash.

This extends to having keywords, variables and methods in the right case. While in CAL there was a lot of UPPERCASE, in AL there is far more lowercase. It took me a while to get used to but I prefer it now.

If you convert some CAL into AL then likely all the keywords are going to be in uppercase. The code will run fine, it just doesn’t look nice. In the below example my eye is drawn to the fact that some filters are being set, rather than what those filters are – on which records and fields.

You’ll notice that all the UPPERCASE words are highlighted in that example. That’s because they are all search results for my regular expression.

\b(?!NAV)(?!CACTMN)[A-Z]+\b
  • \b will match a word boundary – spaces, punctuation, start and end of lines – anything that denotes the start or end of a word
  • (?!) is a negative lookahead and does not find matches for the expression inside the brackets. This is useful for uppercase words that should be left uppercase like NAV or the AppSource suffix that you’ve added all over the place
    • Disclaimer: don’t ask me to explain lookaheads in any more detail than that – I don’t know. I’m not convinced that anyone actually properly knows how regex works 😉
  • [A-Z] matches uppercase characters between A and Z
  • + indicates that we’re looking for one or more of the previous group i.e. one or more uppercase letters

Altogether it translate to something like: match any word of one or more uppercase letters except where it contains “NAV” or “CACTMN” (the suffix we’re using in this app).

Once you’ve found the matches find and replace is your friend. I love how VS Code gives you a preview of all the replaces that it is going to do. Very useful before you replace “IF” with “if” and realise you’ve also replaced “MODIFY” with “MODifY”.

You Can Ditch Our Build Helper for Dynamics 365 Business Central

I’m a bit of a minimalist when it comes to tooling, so I’m always happy to ditch a tool because its functionality can be provided by something else I’m already using.

In a previous post I described how we use our Build Helper AL app to prep a test suite with the test codeunits and methods that you want to run. Either as part of a CI/CD pipeline or to run from VS Code.

Freddy K has updated the navcontainerhelper PowerShell module and improved the testing capabilities – see this post for full details.

The new extensionId parameter for the Run-TestsInBCContainer function removes the need to prepare the test suite before running the tests. Happily, that means we can dispense with downloading, publishing, installing, synchronising and calling the Build Helper app.

The next version of our own PowerShell module will read the app id from app.json and use the extensionId parameter to run the tests. Shout out to Freddy for making it easier than ever to run the tests from the shell 👍

Stop Writing Automated Tests and Get On With Some Real Code

To be fair, these weren’t the exact words that were used, but a view was expressed from the keynote stage at Directions last week along these lines. Frustration that developers now have to concern themselves with infrastructure, like Docker, and writing automated tests rather than “real” code.

I couldn’t resist a short post in response to this view.

If It Doesn’t Add Value, Stop Doing It!

First, no one is forcing you to write automated tests – apart from Microsoft, who want them with your AppSource app submission. Even then, I haven’t heard of Microsoft rejecting an app because it wasn’t accompanied by enough automated tests.

I’m an advocate of developers taking responsibility for their own practices. Don’t follow a best practice simply because someone else tells you it’s a best practice. You know your scenario, your team, your code and your customers better than anyone else. You are best placed to judge whether implementing a new practice is worth the cost of getting started with it.

AppSource aside, if you are complaining about the amount of time you have to spend on writing tests then you have no one to blame but yourself. Or maybe your boss. If you don’t see the value in writing automated tests then you probably should stop wasting your time writing them!

Automated Tests vs “Real” Code

Part of the frustration with tests seemed to be that they aren’t even “real” code. If by “real” code we are referring to the code that we deliver and sell to customers then no, tests aren’t real code.

But what are we trying to achieve? Surely working, maintainable code that adds value for our customers.

We might invest in lots of things in pursuit of that goal. Time spent manually testing, sufficient hardware to develop and test the code on, an internet connection to communicate with each other and the customer, office space to work in, training courses and materials, coffee. We’re not selling these things to the customer either but no one would question that they are necessary to achieve the goal of delivering working software. Especially the coffee.

Whether or not automated tests are “real” code is the wrong question. The important judgement is whether the time spent on writing them makes a big enough contribution to the quality of the product that you eventually ship.

I won’t make the case for automated testing here. That’s for a different post. Or a different book. Suffice to say, I do think it is worth the investment.

But We’ve Got a Backlog of Code Not Covered By Tests

One problem you might have is that you’ve got a backlog of legacy code that isn’t covered by any automated tests. Trying to write tests to cover it all will take ages. This frustration also seemed to be expressed by the speaker at Directions. It even got a round of applause from some of the Directions audience.

My response would be the same – you are best placed to make a pragmatic judgement. Of course it would be nice to have 100% code coverage of your tens of thousands of lines of legacy code – but if you’ll have to stop developing new features for six months to achieve it, is it worth it? Probably not.

Automated tests should give you confidence that the code works as expected. If you are already confident that your existing code works then there might be limited value in writing a suite of tests to prove it.

Try starting with tests to cover new features that you develop or bug fixes. With these cases you’ve got some code that you aren’t confident works as expected – or that you know doesn’t. Take the opportunity to document and prove the expected behaviour with some tests. Over time you’ll build a valuable suite of tests that you can run to demonstrate that each new release of your product works and that bugs haven’t been reintroduced.

With some practice you’ll find that you can use the library codeunits to create scenarios with little test code e.g. you can create a customer, item and sales order, post it and get the posted sales invoice in 2 lines of code.

Interested? More here…