Save Report Filters

Going back into the stone age, report objects have always remembered the filter values for the next time that you run the report. What I never noticed was a capability to actually save filter values (yes I now realize that it’s been in there for years now). It’s similar to saving a view of a list page, but for reports. It works a little hinky but let me try and explain.

What I Am Talking About

You may have noticed it in some (but not all) reports, processing-only or actual printed ones. Right at the top of the request page, you see a dropdown box with the words “Last used options and filters”. Click the dropdown arrow and that should show you an option to “Select from full list”.

This opens the “Select – Report Settings” page. The page actually has nothing to do with any settings, but it does show you a list of saved values for the options and filters of the report object. In my mind I am calling it a ‘view’.

I just created a bogus empty report just to show you the options, so ignore the content of the report itself. The interesting part of this page is what you can do. When you click the ellipses, you get a number of options.

  • Delete – obviously to delete the currently selected option
  • New – creates a new record, where you can give the saved value a label and you can define whether this view is for all users or not. Assigning the view to a specific user is done on the list page itself
  • Copy – creates a new record with the same values as the one that’s called “Last used options and filters”
  • Edit – This only works on custom views, and it runs the report’s request page where you can enter the options and filter values for the current view

You can assign the view to a specific user or share it with all users. If this capability is enabled for a report, you should be able to pick from the list of views right from the request page.

How To Enable This

You need to do two things to enable this capability.

  • First, you set the ‘SaveValues’ property on the report’s requestpage. If the report doesn’t have a requestpage, you can add one with just the property and no controls
  • Second, you need to run the report. When you first deploy the report with SaveValues turned on, it does not yet have a view saved. Run the report with any filter/option value and it should create this record for you
requestpage
{
    SaveValues = true;
}

A Little Hinky

To me it feels a little unfinished, like it was rushed into the system. To begin, it’s inconsistent in labeling. On the requestpage it is called “Use default values from” which is incorrect. This is about ‘saved’ values, not ‘default’ values. Then when you open the full list, it is labeled “Select – Report Settings” which again doesn’t feel right. I don’t consider filter values a ‘setting’. Then when you click ‘New’ it opens a screen that is captioned “Edit – Pick Report”… I mean come on… Finally, having to run the report in order to even get the dropdown is not very user friendly. When I first tested this I thought I had done something wrong because the dropdown would not show.

Another big drawback is that the SaveValues property is not available in Report Extensions. You’ll have to copy the standard object in order to provide the capability.

Regardless of its hinkiness and shortcomings, the feature itself is great, especially when you have periodic activities to run for different sets of filters. it’s really nice to have the ability to preconfigure sets of option/filter values. It has the potential to increase productivity and eliminate typos in entering filters. In my opinion, this feature should be enabled by default.

Formatting Booleans

As you know, boolean fields are presented as a checkbox in BC. The issue with that is that checkboxes themselves have no formatting properties. There are a couple of different ways to change the appearance of boolean fields, read on if you want to know what they are.

One of the apps I work on has a custom journal, in which we use the StyleExpr to color fields based on a number of checks on the record. Over time, we’ve added this functionality to a bunch of fields. You have to scroll to the right to check all the fields, so we added a field called ‘Has Alerts’ to tell the user that something is up with this record. My client didn’t like the standard checkbox and asked if we can show the field as ‘Yes’ or ‘No’.

I asked Twitter for help and got a few responses:

Use the OnDrillDown Trigger

Some of the old quirks of NAV made their way into BC, and one of them is that the simple act of adding a trigger can modify the behavior of page controls.

Image
Using the OnDrillDown trigger

As you can see, the code in the trigger is absolutely meaningless. All we are doing is setting a dummy variable, and the mere presence of this trigger causes the boolean to be presented as a text. The StyleExpr property is tied to a variable that is set in the OnAfterGetCurrentRecord trigger of the page itself. I don’t like this solution because the trigger has no meaning. The field values become clickable, but because the code is bogus, the user will want to click something that does nothing. Of course there could be situations where you actually want to do something in the drilldown, in which case this is perfectly acceptable.

Format the Field

The winning suggestion was to include the Format command in the SourceExpr of the control. So elegant, and makes sense right. The page recognizes that the source is a text, and presents the Yes/No variation of the boolean.

Image
Formatting the SourceExpr

I like this because the field value is not clickable, just a text value. Just a quick tip, nothing earth shattering, but something that I know I’ll want to use some time in the future.

PowerShell Duration

This is a really quick tip just for myself to save the script where I can easily get to it, this time a quick way to output the duration of a PowerShell script. When a script takes longer than expected, in my mind I am waiting HOURS for it to complete but it is probably just a minute or two. I’ve run into this before, where I spend WAY too much time trying to locate a good, easy way to output the duration of a script. Without further ado, here’s the PowerShell code:

$startTime = Get-Date

<insert your script here>

$myTimeSpan = New-TimeSpan -Start $startTime -End (Get-Date)

Write-Output ("Execution time was {0} minutes and {1} seconds." -f $myTimeSpan.Minutes, $myTimeSpan.Seconds)

The key elements here being

  • I was looking for a ‘Time’ function and didn’t realize that in PowerShell you actually need to use the ‘Date’ object to access the time. Get-Date returns a DateTime stamp
  • Coming from BC, I was looking for something called ‘duration’, so it took quite a bit of time to find out about New-TimeSpan. This creates a ‘TimeSpan’ object that has its own members for easy concatenation of a user-friendly message. I’m only using minutes and seconds here

Hopefully next time I need this I will remember to search my own blog 🙂

Repair Companion Tables

One of my clients has been seeing these weird data issues when migrating databases to the cloud. The most important purpose of this post is to point out a handy data tool that is buried deep in the application, read on to learn what I am talking about.

The problems that my client is experiencing mostly have to do with various ways in which they get data from OnPrem to the cloud. One way is to use the cloud migration tools, another is configuration packages. In addition to using different tools, it feels like there may be some issues with the BC platform’s capability to properly manage data in table extensions. I’ll tell you why I think that.

Missing Data

The first indication was a problem where invoices were migrated into the cloud. The migration process seems to have completed, and the posted invoice list shows a list of invoices. The weird part is that when you try to open an invoice, it shows an empty invoice page. Click ‘View Table’ from the page inspector, and you get nothing, a list of zero records even though the posted invoice list shows the records. Go to the admin center to look at the capacity, it tells us there are like 1154 invoices but when you drill down into the number you get another empty list.

This feels very familiar to me, very much like when a lot of companies tried to migrate straight into SQL Server and we would see null field values. As we all know, BC can’t handle null values. Instead of getting an error saying ‘there are null values!! I don’t know what to do!!’ you get weird behavior like empty lists for tables that you know have plenty of records.

Solving The Problem

With the explosion of moving functionality into separate apps, I had a feeling that the problem had something to do with table extensions (which as you know have added fields in what is called ‘Companion Tables’). I had posted the question on Twitter and there were suggestions to uninstall and re-install apps. This worked sometimes but not all the time.

The original Tweet asking for help on this issue

What it feels like to me is that either there are null values in records, or maybe records are missing in companion tables altogether. We don’t have access to Azure SQL so there is no way for me to actually prove that there is an issue with table extensions. SOMETHING is wrong here though, and for the longest time the only way that I thought we could fix it was to uninstall/re-install apps until the issue was fixed. The reason why this works is that each time you install an app, it will update the schema and make sure that data integrity remains intact. In other words, it will make sure that all records in the main tables have corresponding records in all companion tables.

And then I became aware of a VERY handy little tool. This tool is not available when you are working in local containers, but when you are in a cloud tenant, you will have access to it. I’m talking about the “Repair Companion Table Records” process in the “Cloud Migration Management” page.

The tool will go through all tables that have table extensions, and make sure that each record in extended tables has a corresponding record in each companion table. I still can’t prove my theory but I do know that running this process fixed the problem for my client.

How to Populate a Test Suite

You’ve spent a bunch of time developing test codeunits, and you’ve figured out how to manually pull those into a Test Suite in the Business Central test toolkit. In this post, I will show you how you can automatically populate the test suite, which is especially useful for automatically testing your app in a build pipeline.

What Are We Talking About?

To keep the sample as simple as possible, I started with a Hello World app that I created with the “AL: Go!” command, plus a test app that has a dependency on it. In this test app, I have two test codeunits that don’t do anything. They are completely useless, just meant to show you how to get them into the test tool.

Those test codeunits are deployed into a BC container that has the test toolkit installed. This test toolkit (search for the ‘AL Test Tool’ in Alt+Q) is a UI that allows you to manually run these tests. Just like a standard BC journal page, when you first open the tool, it will create an empty record called ‘DEFAULT’ into which you must then get the test codeunits. Click on the “Get Test Codeunits” action, and only select the two useless codeunits. You should now see the codeunits and their functions in the Test Tool.

It’s kind of a drag to have to manually import the test codeunits into a Test Suite every time you modify something. To make it much easier on you, you can actually write code to do it for you. Put that code into an Install codeunit, and you never have to worry about manually creating a test suite again.

Show Me The Code!!

First we need an Install codeunit with an OnInstallAppPerCompany trigger, which is executed when the app is installed, both during an initial installation and also when performing an update or re-installation. You could probably create a separate “Initialize Test Suite” codeunit so you can run this logic in other places as well, but we are going to just write the code in our trigger directly.

The code below speaks mostly for itself. I like completely recreating the whole suite, but you can of course modify to your requirements. The important part of this example is a codeunit that Microsoft has given to us for this purpose called “Test Suite Mgt.”. This codeunit gives you several functions that you can use to make this possible.

codeunit 50202 InstallDnStr
{
    Subtype = Install;

    trigger OnInstallAppPerCompany()
    var
        TestSuite: Record "AL Test Suite";
        TestMethodLine: Record "Test Method Line";
        MyObject: Record AllObjWithCaption;
        TestSuiteMgt: Codeunit "Test Suite Mgt.";
        TestSuiteName: Code[10];
    begin
        TestSuiteName := 'SOME-NAME';

        // First, create a new Test Suite
        if TestSuite.Get(TestSuiteName) then begin
            TestSuiteMgt.DeleteAllMethods(TestSuite);
        end else begin
            TestSuiteMgt.CreateTestSuite(TestSuiteName);
            TestSuite.Get(TestSuiteName);
        end;

        // Second, pull in the test codeunits
        MyObject.SetRange("Object Type", MyObject."Object Type"::Codeunit);
        MyObject.SetFilter("Object ID", '50200..50249');
        MyObject.SetRange("Object Subtype", 'Test');
        if MyObject.FindSet() then begin
            repeat
                TestSuiteMgt.GetTestMethods(TestSuite, MyObject);
            until MyObject.Next() = 0;
        end;

        // Third, run the tests. This is of course an optional step
        TestMethodLine.SetRange("Test Suite", TestSuiteName);
        TestSuiteMgt.RunSelectedTests(TestMethodLine);
    end;
}

When you deploy your test app, it will now create a new Test Suite called ‘SOME-NAME’, it will pull your test codeunits with their test functions into the Test Suite, and it will execute all tests as part of the installation.

This code is very useful when you are developing the test code, because you won’t ever have to pull in any test codeunits into your test suite manually. Not only that, it will prove very useful when you start using pipelines, and you will be able to have precise control over which codeunits run at what point.

Dependencies

Here are the dependencies that I’m using :

  "dependencies": [
    {
      "id": "23de40a6-dfe8-4f80-80db-d70f83ce8caf",
      "name": "Test Runner",
      "publisher": "Microsoft",
      "version": "18.0.0.0"
    },
    {
      "id":  "5d86850b-0d76-4eca-bd7b-951ad998e997",
      "name":  "Tests-TestLibraries",
      "publisher":  "Microsoft",
      "version": "18.0.0.0"
    }
  ]

Credits

This post has been in my drafts for a while now, based on a question I posted to my Twitter, click on the Twee below to see the replies. The code in this post was copied almost verbatim from Krzysztof’s repo, he has a link in one of the replies. I had worked out my own example based on his code but I lost that when I had to clean up my VM’s.

Disable Autoplay in Edge

In this post I’ll show you how to disable autoplay in the new Edge browser. Normally I would just link to the post that helped me, but ironically that one is riddled with autoplaying videos. For that reason I decided to just copy the information in here and spare you the crap popping up by linking to the post.

Step One

For the first step, enter “edge://flags/” into the address bar, find the setting for ‘Show block option in autoplay settings’, and set this to ‘Enabled’. You’ll get a message that you have to restart Edge for the change to take effect. Don’t worry about any open tabs, it will reload all of them.

Step Two

When the browser reloads, enter “edge://settings/content/mediaAutoplay” into the address bar, and set that to ‘Block’.

Done

You’re all set. I tested it in two of the worst offenders (CNN and YouTube) and nothing autoplays for me. I wonder how long it will take before they figure out how to bypass these settings. The annoying part is that you have to go to internal settings. This should really be a simple setting.

PowerShell in Task Scheduler

One of the programs on my computer creates a shortcut on my desktop every time I restart my computer. As people who know me can attest, I am a little compulsive about certain things, and having anything on my computer’s desktop is one of them. The program in question does not have an option to disable this thing, so someone suggested to me to use Task Scheduler to remove the shortcut. Since I’ve been using PowerShell a lot, I thought I’d try that.

The PowerShell Script

First, you need a script to execute the task at hand. In my case I needed to remove the shortcut from my desktop. After searching for a while, I found it in the Users folder. Create a new PowerShell file in the ISE and write this script:

$MyFile = "C:\Users\<UserName>\Desktop\<TheShortCutYouMustDelete>.lnk"

if (Test-Path $MyFile) 
    {
        Remove-Item $MyFile -Force
    } 

Obviously, you replace <UserName> with your user name, and the name for the shortcut. I’m sure you can even replace the folder with an environment variable. Save this script as a .ps1 file and put it in a folder, like your documents folder. I called mine RemoveShortcut.ps1 and put it in the folder “C:\Users\DENSTER\Documents\”.

For me, this was a need of the moment, because this program was stubbornly creating this shortcut. You can use PowerShell for a million different things, so anything you can do with PowerShell you can put in the Task Scheduler.

Update July 24, 2020 – Based on a conversation with @steveendow on Twitter the other day, automatically updating NavContainerHelper would be a good example. I had already written most of this post (started it back in June) and was about to add the update as a second example before publishing it when Steve created his version of this same post himself. I actually gave him the update script that he used, and I suggested using the Task Scheduler, so I don’t feel too bad for this redundant post, and it saves me from having to test the NavContainerHelper update script in the scheduler :).

Task Scheduler

Next, you need to create a task in the Windows Task Scheduler. Hit the Start button and type ‘task scheduler’, and the app will come up. When the Task Scheduler opens, you’ll see the “Task Scheduler Library”. To make it easier on yourself, create a new folder by right clicking on the Library node and then clicking “New Folder”. Enter a name and hit Enter. This creates a folder where you can save your own tasks. Trust me, it is VERY easy to lose tasks in the standard folders. Having your own folder is going to save you a ton of time.

My new folder “theDenSter”

To create a new task, right click your new folder and select ‘Create Task’. Give it a name and then select the SYSTEM account under security options. You can leave it under your own account, but then you will see PowerShell pop up and run as you are using the computer. Selecting the SYSTEM account makes it so that it happens in the background.

You need a trigger to execute the task. I chose to run it at log on, and I set the task to repeat every 5 minutes for 30 minutes. The reason I did that is because the program that created the shortcut sometimes took a while to create the shortcut, and running my script just once did not always remove it right away, so I had to repeat it a few times. Take a look at all the available triggers and select the one that makes sense to you. Make sure that the trigger is enabled.

Now the meat of the task: the action. We are going to run the PowerShell script, so select ‘Start a program’ and enter ‘powershell’ in the “Program/script” box. The file name goes into the “Add arguments” box:

-File "C:\Users\DENSTER\Documents\RemoveShortcut.ps1"

If you want to be able to manually execute the task, you have to check the box for “Allow task to be run on demand” on the Settings tab. This way you can run the task at any time right from the Task Scheduler.

That’s it. Hope this is useful for you. I had not thought of using the Task Scheduler, but it feels like I could automate a bunch of things. It’s nice to have the ability, let me know if you use the Task Scheduler, and what you use it for.

Span Some (but not all) Monitors

If you use multiple monitors, and you would love to span your RDP session or your Hyper-V session across some, but not all, monitors, then this is the post for you. Say you have three monitors, I will tell explain how you can span your RDP/VM across two of those monitors, while still using the software that runs on the host on the remaining monitor.

Why I Wanted to Know This

My desk has my laptop on a stand on the left side, plus two external monitors to the right of the laptop. The first external monitor is my primary work screen, so it is positioned right in the middle of my desk. I think of the middle screen as “screen 1” and the one on the right as “screen 2”. My laptop screen is just ‘my laptop screen’ :).

My screen setup. Note that the laptop screen shows a third of a fantastic looking multi-screen spacescape, mostly hidden by my 2 screen spanning RDP session into my VM on the two external monitors ‘screen 1’ and ‘screen 2’

For my daily development work, I use Hyper-V virtual machines, and I used to full screen it either on screen 1 or 2, depending on what I was doing. Doing development I’d have my VM full on screen 1, and other supporting programs on the host on screen 2. Writing documentation, I’d have the VM full on screen 2 and Word on screen 1, and then I’d use Snagit for taking screenshots.

When I started including documentation (in markdown files) into my source code, it became difficult to work on documentation, because I would want to take screenshots of the app, and then incorporate it into the markdown file. I would have to switch between VSCode and the app inside the VM, switch to the host to activate Snagit, and then switch back to the VM to process the screenshot in the documentation. Very tedious situation with lots of clicks. I did try to use ‘all monitors’ in the Hyper-V connector, but then it would be difficult to take proper screenshots because I only had Snagit installed on the host. I’d have to minimize the VM, start the Snagit screenshot with delay, re-activate the VM and hope that it would be back up in time for me to take the screenshot.

So, then I thought it would be nice if I could span my VM across just 2 of my 3 screens. I could have VSCode on screen 1, the app on screen 2 (both inside the VM), and Snagit on the host on the laptop. Kudos go to Martin, one of the IT leaders at one of my clients, for teaching me how to do this for RDP. Although the Hyper-V connection uses RDP technology, there does not seem to be a way to do the same thing for Hyper-V – it’s either only 1 monitor, or all monitors. “No problem”, Martin said, “you can use RDP to connect to your VM”, and he showed me how to make that work as well. Let me share this useful nugget with you.

RDP Into your VM

First you need to make sure that your VM is set up to accept remote connections. This section explains how to do that for Windows Server 2019, which is what I use in my VMs.

Inside the VM:

  • Click Start and open Settings
  • Go to the “Remote Desktop” page, and turn on Enable Remote Desktop
  • While you are in this settings screen, note the name of your VM. You’ll use this to create an rdp file for each of your VMs

Back on the host – open Hyper-V Manager and select your VM. At the bottom, you will see a screen part with three tabs. In the Networking tab, it shows the IP address of your VM. Be aware that this IP address can change without any clear indication when or why. Hyper-V Manager will go long times using the same IP address when restarting the same VM, and all of a sudden it will change it.

Now open Remote Desktop Connection, and expand the options. I used to use the VM’s IP address as the name, but because Hyper-V changes the IP pretty much every time that the host computer reboots, I was constantly updating the rdp file. Then I discovered that you can also enter the VM’s computer name into the name box. Now click on ‘Save As’ and save the rdp file with the VM name in a folder that is convenient for you; mine are in ‘My Documents’.

Very cool, congratulations, you are now using RDP to connect to your VM.

X of Y Monitors in RDP

The rdp file is nothing more than a text file with key-value pairs. We are going to edit this file using notepad, and add some screen properties. Feel free to use any other text editor. You can even open it with VSCode if you want.

You need the following settings in the rdp file:

  • screen mode id:i:2 – Determines whether the session is opened in full screen, and the value 2 stands for ‘full screen’. I tried leaving this out and for fun tried to use the laptop monitor and screen 1 at the same time, and that did not work well for me. It seems that this only works when the screens have the same resolution capabilities.
  • span monitors:i:1 – I think this is a boolean parameter – 1 means on
  • use multimon:i:1 – Same here, I think this is a boolean so 1 for on
  • selectedmonitors:s:1,2 – The monitor index is specific to the hardware. To get the numbers, run “mstsc.exe /l” from a command prompt. On one computer, this was 1 and 2 for me. My monitors are connected through a USB-C dock, and when I connected a new computer to the same dock, this no longer worked for me. Same monitors, same dock, different computer gave me screen indexes 3 and 4.
The ‘MyVM.rdp’ file in Visual Studio Code, showing the relevant screen properties

If you’re interested, here is where you can read more about rdp properties.

Now, instead of opening the VM through the Hyper-V Connection Manager, just double click the rdp file and it will connect. You should now see the desktop across the monitors that you defined in your rdp file. If you take a closer look at the image above, you can see that it shows an RDP session spanning my ‘screen 1’ and ‘screen 2’ monitors, with my laptop showing the host.

One other thing that is very convenient is that RDP remembers all the connections you make. If you pin RDP to the taskbar, you can right-click it and select which connection you want to use. I have rdp files for each of my VM’s. All I have to do is start the VM, select the right one in RDP, and it connects on two of my three screens.

Great Improvement

For me this was a really big improvement for my workflow. I used to have to maximize, minimize, switch between VM and host, and it was just distracting to me. Now I can have Outlook, Spotify, and Snagit all visible to my left, and have the real estate of two full monitors to work on whatever I want to run inside the VM, all at the same. The most I need to do is click on my host desktop to make my Snagit keyboard shortcut work.

Personally I do most of my development work in my local VMs, but this would also work for regular RDP sessions. This gives you total control over the screens that you want to use.

Hope this helps you, let me know in the comments or send me a message on Twitter.