Total Competition – Book Review

What’s a book about F1 have to do with Business Central? Nothing, absolutely nothing. Total Competition, written by Adam Parr and Ross Brawn, does contain a masterclass on leadership, at a Very Rare Level. I was fascinated by the lessons in it and how those apply to any company, in any industry. If you are in any leadership role, I would highly recommend this book and try to listen to what the authors are telling you.

What’s it about?

This book is written by two Formula One leaders. Adam Parr is a name that is known mostly in F1 circles, but Ross Brawn is an absolute Legend and is well known outside of motorsport for his accomplishments. For those that don’t know, he has like two dozen world championships under his belt, in various capacities, and he is responsible for some of the greatest successes in the history of Formula One.

Brawn gets top billing on the authorship, but the actual writing is done by Parr. He pulls together the topics and introduces them by drawing comparisons in ‘strategy’ between F1 and the concept of strategy in the military. He uses the mother of all books on strategy, ‘The Art of War’ by Sun Tsu. He starts various chapters off with a few paragraphs from the book and uses it as the basis of a series of interviews. These interviews are in the book in their ‘raw dialogue’ form, although I believe that they have been edited for the book. In the audiobook, Adam Parr narrates his own part and Brawn’s part is done by a voice actor. Unfortunate because I think Brawn has such a commanding presence, it would have been awesome to hear him do his own part.

The content of the book is a case study of Brawn’s history, a deep dive into the how and why Brawn did what he did when he did them. Since Parr was also a team principal at some point, he is able to add his experiences as well and juxtapose those to Brawn’s experiences. Interesting to say the least in cases where they had opposite interests.

There is, of course, a TON of F1 content that racing fans will want to read about. What struck me though was that what interested me most were the parts where they talked about leadership and how different types of leadership affects the performance of the team. As I was listening to these two go on about how ‘strategy’ and ‘tactics’ affect the direction of the team, I was just fascinated by how their thinking was just at a completely different level than I am used to.

The Art of Leadership

My biggest takeaway was that leadership is not a latent talent. It is not something intangible that someone does or doesn’t have. Leadership is a skill that is developed over time. It takes trial and error, keen observations, and a sense of purpose. You need to be able to change course when the thing that you are doing is not giving you the results that you are looking for. It’s a matter of being honest about what you are observing and take the steps that are necessary to correct the situation.

Then there is the concept of ‘Total Competition’. Everything…. Every… Thing… can have an effect on your performance. Even the smallest little detail can change the outcome. In F1 you can talk about polishing the bugs off the paint of the car to reduce drag, or a difference in where the measuring points are of the plank underneath the car and introducing a clever mechanism to affect that millimeter of surface area to meet the scrutineers’ requirements. In business this would be to have people polish their shoes and shave every day when attending an event, or enforcing time limits and having a meeting agenda in conference calls, holding developers to guidelines and naming conventions. We could go on and on about little things that we all take for granted in one way or another.

Brawn talks about how he hates micromanagement, how he hires experts for a reason. He gets the best people for the job, and then he facilitates their success to make sure they get the best out of their situation. Not because that’s how he inherently thinks that is the way things should be done, but because he himself was in a situation where he was micromanaged and found that to be the opposite of helpful. Let the experts do what they do best. Do not think that just because you are the boss, that you need to dictate what everybody does and how they do it. Of course he holds everybody accountable, and in meetings he will be very demanding of his people, but he does not get into the weeds of THEIR jobs. The most effective leaders are those that stay out of the way of their people.

Finally, the concept of being willing to do what it takes. These guys operate at the pinnacle of motorsports. You cannot fathom the type of pressure is on these guys to perform, and what is needed to be able to perform at that level. Brawn is one of a very small group of people that seemed to have figured out how to identify what is needed and how to build a team of people that are willing to do what it takes to get to that level. He didn’t just move an entire team, he built new teams everywhere he went.

Must Read

If you are a Formula One fan, this is a must read. The inside view of one of the most successful people in the history of motorsport is just fascinating from purely a racing point of view. If you are in a leadership position though, you owe it to yourself to read this case study in success at the highest level imaginable.

NAV/BC Version Numbers

Many others have posted this information before. I’m just putting this on my own blog because I have noticed a decrease in the number of ‘old’ blogs out there and this is getting harder to find. I’m just securing the information so that I know I’ll have it for as long as I keep my blog up.

When I started as a Navision developer in March of 2000, version 2.5 had just come out. Most of the clients that I worked for were on 2.01. The version that is current today (v21 for the 2022 Wave 2 release, and v22 is just a couple of months away) directly derives from that version back in early 2000. There are earlier versions than that, and you can search for the history yourself, but I have never encountered anything older than 2.01. I just wanted to have a handy list as a quick reference.

Here They Are

  • Navision in various incarnations (Financials, Attain, Microsoft Business Solutions) 1, 1.1, 1.2, 1.3, 2, 2.01, 2.5, 2.6 (also versions with manufacturing and another with Advanced Distribution, as well as specialty versions for the first NAS 2.65 a, b, c, d, and e), 3.00, 3.01, 3.10, 3.60, 3.70, 4.0, 4.0 SP1-SP2-SP3, 5.0
  • Dynamics NAV 2009 is version 6 – also had SP1 and then R2
  • Dynamics NAV 2013 is number 7 – also had R2
  • Then there were NAV 2015 (8), 2016 (9), 2017 (10), and 2018 (11)
  • Version 12 was the first BC version, AKA Business Central 2018. For a while there (at least through BC14) was a bit of a split personality with the splash screen saying “Microsoft Dynamics NAV Connected to Dynamics 365 Business Central”, a very catchy and easy-to-remember 24-syllable name
  • From then on there is a release every 6 months:
    • 13 – October 2018
    • 14 – April 2019 (this was the last version with C/SIDE)
    • 15 – BC 2019 wave 2
    • 16 – BC 2020 wave 1
    • 17 – BC 2020 wave 2
    • 18 – BC 2021 wave 1
    • 19 – BC 2021 wave 2
    • 20 – BC 2022 wave 1
    • 21 – BC 2022 wave 2
    • and so on and so forth

update 2023-02-14: added some missing versions

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.

Document Attachments

One of my clients had asked me to add Document Attachments to Bank Deposits. Thinking this was a quick and easy one I added the factbox, set the link, and went on with my day. When my client said they see all attachments for all records, I realized it is a little more involved. It took some time to figure out how it actually works, and this post explains the whole thing, including how to get document attachments through the posting process.

How Does it Really Work?

The reason why it’s not so simple is because the Document Attachment works with RecRef instead of a hardcoded table relationship. Take a look at the Document Attachment table (table number 1173 in the base app) for the field definitions. I’ll focus on single field PK records in this post, so tables where a single Code20 field has the unique identifier of the record. The more complex compound PK works the same, you just need to set more fields.

Standard BC has a limited number of tables that have document attachment capabilities. If you want to add another table, whether a custom table or another standard table, you will need to subscribe to some events to make that work. Let’s first look at how standard document attachments work.

Open Document Attachments

Let’s take a look at the Customer Card page in standard BC. The “Attachments” action has the following OnAction trigger code:

trigger OnAction()
var
    DocumentAttachmentDetails: Page "Document Attachment Details";
    RecRef: RecordRef;
begin
    RecRef.GetTable(Rec);
    DocumentAttachmentDetails.OpenForRecRef(RecRef);
    DocumentAttachmentDetails.RunModal();
end;

The “Document Attachment Details” page is where the magic happens. If you drill down into the ‘OpenForRecRef’ function, you will see that it takes a RecRef variable (which in this example is looking at the current Customer record) and sets filter on the table ID of the RecRef and the value of the PK field of the record itself. This is the function that defines all the tables in the standard BC app that have Document Attachments.

The important thing in this function is the call to OnAfterOpenForRecRef, which is an event publisher that we will subscribe to later. This event gives you the capability to set a filter to any table. Note that this is just a filter on a page. All that this does is make sure that you only see the document attachments for this particular record.

The Document Attachments Factbox

Another way to give the user access to the document attachments is the “Document Attachment Factbox” that you can see in the factboxes area. In the standard app on the Customer Card, the SubPageLink property links the “Table ID” field to a hardcoded value 18 (which is Customer table’s object id). If you want to create your own link, you should use the table name instead of its number. So we will be linking to the “Bank Deposit Header” table, so our constant value will be Database::”Bank Deposit Header”.

Take a look at the OnDrillDown trigger of the NumberOfRecords field in the factbox. It first sets up the RecRef, which is again hardcoded for the standard range of tables that have document attachments. Then it executes the same logic on the Document Attachment Details page as the action mentioned above.

Note the OnBeforeDrillDown function call in the ‘else’ leg of the ‘case’ statement. This is the event that you need to subscribe to in order to properly filter the document attachments for non-standard tables.

What is important to understand about this particular factbox is that records are not entered directly into the factbox. You have to click an action that adds the record, and as a result the “No.” field in the factbox is NOT populated by the page link.

Creating New Document Attachments

So far we’ve only looked at how to display the proper document attachments to the user. What’s left is how BC actually stores these records. The part that is tricky is not about getting the file itself, but how BC gets the value from the RecRef. The function that stores the values from the RecRef into the document attachment is called InitFieldsFromRecRef. You can see that this function again goes through all of the same hard coded standard BC tables that we’ve seen before, and it provides an event publisher called OnAfterInitFieldsFromRecRef that you can use for additional tables.

Document Attachments for Additional Tables

Alright, now let’s put it all together for a new table. I recently added this for Bank Deposits for a client of mine, so I’ll use the same table to illustrate. I’ll focus on the new ‘Bank Deposit Header’ and its Posted sibling. The new implementation of bank deposits can be found in the “_Exclude_Bank Deposits” app that is part of standard BC.

To get started, create a page extension for the Bank Deposit and the Posted Bank Deposit pages, and add the Document Attachment factbox. You can copy this from the Customer Card and change the links appropriately. Note that the records that you will see when you drill down into the details are filtered on the table but not by the PK value of the record that you are looking at. In other words, just adding this factbox will only give you a list of all the Document Attachments that are linked to ALL (Posted) Bank Deposits.

Finally, create a new codeunit, I called mine ‘DocAttachmentSubs’.

Filter the Details

First, we need to make sure that the “Document Attachment Details” page is filtered on the correct Bank Deposit number. For this we subscribe to the OnAfterOpenForRecRef event.

[EventSubscriber(ObjectType::Page, Page::"Document Attachment Details", 'OnAfterOpenForRecRef', '', true, true)]
local procedure DocAttDetailsPageOnAfterOpenForRecRef(var DocumentAttachment: Record "Document Attachment"; var RecRef: RecordRef)
var
    MyFieldRef: FieldRef;
    RecNo: Code[20];
begin
    if RecRef.Number in [Database::"Bank Deposit Header", Database::"Posted Bank Deposit Header"] then begin
        MyFieldRef := RecRef.Field(1); // field 1 is the "No." field in both tables
        RecNo := MyFieldRef.Value();
        DocumentAttachment.SetRange("No.", RecNo);
    end;
end;

Filter the Factbox

Next, we need to make sure that the details are filtered properly when the user clicks the DrillDown from the document attachment factbox. For this we subscribe to the OnBeforeDrillDown event in the factbox.

[EventSubscriber(ObjectType::Page, Page::"Document Attachment Factbox", 'OnBeforeDrillDown', '', true, true)]
local procedure DocAttFactboxOnBeforeDrillDown(DocumentAttachment: Record "Document Attachment"; var RecRef: RecordRef)
var
    BankDepositHeader: Record "Bank Deposit Header";
    PostedBankDepositHeader: Record "Posted Bank Deposit Header";
begin
    case DocumentAttachment."Table ID" of
        Database::"Bank Deposit Header":
            begin
                RecRef.Open(Database::"Bank Deposit Header");
                if BankDepositHeader.Get(DocumentAttachment."No.") then
                    RecRef.GetTable(BankDepositHeader);
            end;
        Database::"Posted Bank Deposit Header":
            begin
                RecRef.Open(Database::"Posted Bank Deposit Header");
                if PostedBankDepositHeader.Get(DocumentAttachment."No.") then
                    RecRef.GetTable(PostedBankDepositHeader);
            end;
    end;
end;

So now when the user clicks the drilldown, BC will set the RecRef to look at the (Posted) Bank Deposit, which is then sent into the details page, which then knows how to properly filter.

Set the Right Link

The last thing we need is to make sure that new document attachments have the (Posted) Bank Deposit number, by subscribing to the OnAfterInitFieldsFromRecRef event of the Document Attachment table itself.

[EventSubscriber(ObjectType::Table, Database::"Document Attachment", 'OnAfterInitFieldsFromRecRef', '', true, true)]
local procedure DocAttTableOnAfterInitFieldsFromRecRef(var DocumentAttachment: Record "Document Attachment"; var RecRef: RecordRef)
var
    MyFieldRef: FieldRef;
    RecNo: Code[20];
begin
    if RecRef.Number in [Database::"Bank Deposit Header", Database::"Posted Bank Deposit Header"] then begin
        MyFieldRef := RecRef.Field(1); // field 1 is the "No." field in both tables
        RecNo := MyFieldRef.Value();
        DocumentAttachment.Validate("No.", RecNo);
    end;
end;

You should now be able to create new document attachments for unposted and posted bank deposits. All that’s left is to get document attachments to flow through the posting process.

Posting

Document Attachments are not intrinsically difficult. In the end they are just records in the database. The records are identified by their Table ID and their PK values. For Bank Deposits the PK is a single Code20 field, and there are two versions of the table. All we need to do is write a little loopyloopy that reads the records for the unposted record, copy it into the posted record, and get rid of the old ones. Would be cool to just change the records, but since both the Table ID and the record identifier are all part of the PK, you can’t do a ‘Rename’ because then you’d have to sit there and click confirmations all day long.

For Bank Deposits, you can use the OnAfterBankDepositPost event in the “Bank Deposit-Post” codeunit.

[EventSubscriber(ObjectType::Codeunit, Codeunit::"Bank Deposit-Post", 'OnAfterBankDepositPost', '', true, true)]
local procedure BankDepositPostOnAfterBankDepositPost(BankDepositHeader: Record "Bank Deposit Header"; var PostedBankDepositHeader: Record "Posted Bank Deposit Header")
begin
    MoveAttachmentsToPostedDeposit(Database::"Bank Deposit Header", BankDepositHeader."No.",
                                    Database::"Posted Bank Deposit Header", PostedBankDepositHeader."No.");
end;

local procedure MoveAttachmentsToPostedDeposit(FromTableId: Integer; FromNo: Code[20]; ToTableId: Integer; ToNo: Code[20])
var
    FromDocumentAttachment: Record "Document Attachment";
    ToDocumentAttachment: Record "Document Attachment";
begin
    FromDocumentAttachment.SetRange("Table ID", FromTableId);
    FromDocumentAttachment.SetRange("No.", FromNo);

    if FromDocumentAttachment.FindSet() then begin
        repeat
            Clear(ToDocumentAttachment);
            ToDocumentAttachment.Init();
            ToDocumentAttachment.TransferFields(FromDocumentAttachment);
            ToDocumentAttachment.Validate("Table ID", ToTableId);
            ToDocumentAttachment.Validate("No.", ToNo);
            ToDocumentAttachment.Insert(true);
        until FromDocumentAttachment.Next() = 0;
        FromDocumentAttachment.DeleteAll();
    end;
end;

I have this in a separate function because I also had to make it work for the old Deposit implementation. You could totally combine this into a single event subscriber.

I’m thinking about creating a video about this topic. Let me know if this was useful in the comments and if you’d like to see the video.

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.

Obsolete But Not Gone

This post was born out of a bit of an embarrassing situation, in which I had to obsolete some fields from an AppSource app. Just a quick one to let you know what the official process is, and also that you don’t have to wait for the next major version of BC to fully obsolete a field.

The ‘Situation’

This app has table extensions for the Sales Line table and the posted sales line tables (invoice and credit memo). Not until their first live implementation did we find out that the field numbers in the credit memo line table were not aligned, so at that point BC will yell at you that field types are not compatible. Oops….

For those that don’t understand, let me explain. Field values in the Sales Line table are copied to their posted counterparts by a command called ‘TransferFields’. This command basically copies all the field values from the source table into the target table. Couple things to note: the fields must have the same field numbers, and they must have the same data types. In our case, we had fields 1 and 2 in the Sales Line table, and their corresponding fields in the posted credit memo line had field numbers 2 and 3. The posting process tried to put a number 2 value into field number 1, with the predictable result that there was a data type mismatch.

Why is this a problem? Can’t you just change the field number? Well no, because once an app is published on AppSource, you are not allowed to make any ‘breaking changes’, and renumbering a field is considered a breaking change. Doesn’t matter how I personally feel about that (I don’t agree that field numbers are breaking) and so we did not have any other choice but to use the obsoletion process.

As a side note – yes this exposed a serious issue in our process. Posting a credit memo had clearly not been tested, and matching field numbers is something that a BC developer with my experience should never have a problem with. To my client’s credit: no fingers were pointed, we addressed the issue, and we shared the cost of fixing it.

Obsoleting a Field

Alright, so the official process of obsoleting a field is described in the ‘deprecation guidelines‘. Skip the preprocessing piece if you have not seen that before, and focus on the steps in making code obsolete. There are plenty of blog posts that explain the process itself, so I will not go into any detail.

My main concern was how fast can we get this into AppSource. The process to make a field obsolete has two stages:

  • First, the ObsoleteState of the field is set to ‘Pending’.
    • This means that the field has been marked, but it can still be used in the app
    • The purpose of this stage is to flag the code to any party that has an extension of the code, so that they can take steps to address the change
    • All references to the field will be shown in the VSCode problems window with a warning. There is a reason and a tag property that is used to define which in BC version the change will become permanent
  • Second, the ObsoleteState of the field is set to ‘Removed’.
    • In this state, the field still exists but it cannot be used any longer
    • All references to the field will show up as errors in the problems list

There is a perfectly valid reason why there are two steps. My concern was how long it would take us to get the fields to be removed. The documentation does not address the required AppSource timeline, and I could not find any definite answers in Yammer. The only timeline reference that I could find in any Microsoft documentation was that code must be in ‘Pending’ state for one entire major version. This issue came up in early October, days after 2022 wave 2 was released. If this was true, we would have to wait until 2023 wave 1 (April next year!!) to get the fields obsoleted.

What We Did

Some partners assured me that there is no mandatory wait time for a whole major cycle. The intention of that timeline is not to limit partners but a practice that Microsoft uses. This is to make sure that the partners always have at least one full release cycle to address any compatibility issues due to obsolete code in the base app.

So, with that in mind we went to work. What saved us is that this particular app only had one implementation live, so all we had to do is make sure that they upgrade the app to the ‘Pending’ version as soon as humanly possible. Depending on the number of live implementations you have, this could take longer. I actually don’t even know if there is a dashboard where you can see the live versions that are in use.

I have to say that I was very impressed with how smooth the process of pushing a new version of an app into AppSource is now. After properly testing the change, we pushed the ‘Pending’ app to AppSource, and that was done and ready to publish within a day. The end user then upgraded the app, and we pushed the ‘Removed’ app to AppSource right away. We were able to address the issue within days, and the client lived happily ever after.

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 🙂

Reports to Excel

Just a quick post about the new reporting capability to use Excel for the layout. It’s been out for a while now (it came out in v20 or 2022 wave 1), but there was a tweet earlier this year to point out some FAQs and some samples to get you started on it. Personally I haven’t had the time to really get into it but I wanted to share this so it doesn’t get lost.

Kind of a Big Deal

I have a feeling that this new capability is being underrated, to me this is really kind of a big deal. We all know how difficult it is to use RDLC, and I’ve been able to keep report development at a minimum. Now that we have the Excel layout as a new target, I can’t help but feel like this is the main direction that reporting will go (that and PowerBI of course, but that’s a whole other story). I would not be surprised if Excel will be playing a much more important role now that we can use it as the target for report objects.

OK so here’s the tweet, it was posted back in March and I had bookmarked it at the time to look at later.

What I think is really cool about the way some people in the BC team interact with the community, is that they will keep track of what the community is sharing, and actually promote these posts in their own documentation. Granted, the GitHub repo is no official documentation, but they DO link to community blog posts in there.

Like I said I still haven’t taken the time to dig in, but I wanted to get this posted not to lose the link. This will surely be enough to get started and get your feet wet with Excel Layouts.

MVP Award Number 18

Just a quick post to celebrate that Microsoft gave me MVP Award number 18 today!!

At some level I was kind of expecting not to get an award this year. I didn’t attend any events, so I didn’t do any public speaking. My contributions are my daily participation in our Twitter community, and this here blog that you are reading right now.

Attending events is VERY expensive, and throughout my career this has always been paid by the company that I worked for. Now that I am back to freelancing, I no longer have the sponsoring to pay for travel and event tickets. Some people think that speakers get paid handsomely but I’m here to tell you that these days you are lucky if you get a discount on the ticket price. Besides the cost of travel and attendance, being at an event also means that you’re not working. As a freelancer that adds up real quick, so I decided not to attend events.

The main reason though why I was thinking that I may not get another award is that the latest new group of MVPs are incredibly prolific content creators. Not only do they produce an incredible amount of content, but they have also set the bar SUPER HIGH with the quality of their content.

Given the company that I am in with all of my fellow MVPs, it makes me feel very warm and fuzzy that those contributions are deemed worthy to give me another award. Thank you Microsoft for the recognition, and thanks to everyone in our community for making me feel like I belong.

Update July 26, 2022:

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.