Category Archives: Configuration Packages

Extract Validation Input Titles and Messages from D365 Templates Using VBA

Posted on by
Reply

I’ve got a couple of D365/BC projects I’m involved with at the moment. In both cases the data is being loaded via D365 and I need to supply templates to the client.

I don’t know if you’ve ever been involved in loading data into D365 but if you’ve been used to BC’s Configuration Packages then you’re in for a nasty shock.

One of the key things you can do to keep yourself out of trouble is to make sure that the data the client supplies is of good quality. So you need to make sure that they know what sort of data goes in each field and how long each field is (and any other constraints, like Booleans or Option lists)

In Configuration Packages the relevant information is in a note on the header and even if you copy and transpose it to make it a bit more readable, you don’t lose the notes.

In D365 however the same information can be seen in the cells below the headers – the fields if you like. It’s stored in excel not as notes but as Validation Input Titles and Messages. So, if you transpose the Headers, you lose all that info that you need your customer to have.

This is annoying. I needed to find a way to extract that information and show it in a cell so I could send the template to a less techy person and they could easily see what it was I wanted from them.

 These days, my first port of call when messing around in excel is Power Query. But a quick Google left me without answers. So I turned back to an old friend: VBA.

I’ll confess to never having been much good with VBA. And to it having been several years since I needed it (thanks Power Query!) But, flaking rust everywhere, I managed to cobble something together and, after a few iterations, hit upon a little chunk of code that worked and didn’t hang every time it couldn’t find anything.

And here it is:

Sub looptest()

Dim c As Range

For Each c In Range(“D3:FZ3”)

       If c.Validation.InputTitle <> “” Then

       c.Value = c.Validation.InputTitle & ” ” & c.Validation.InputMessage  

       End If 

 Next c

End Sub

The result looks like this:

And then, because the descriptions are quite long, I’ve copied and pasted (transpose) it to show the headers as a vertical list with the data type descriptions and field lengths to their left.

I hope someone finds this useful. It was a bit of a headscratcher for me so putting in my blog means there’s one more place for the next person looking for a solution to this problem to find it.

Bulk Deletions in BC Using Configuration Packages

Posted on by
Reply

So you’ve got a whole bunch of items to delete. Perhaps your procurement department got really enthusiastic about a new range of products and listed them all and, then when sales looked at them they hated them.

You don’t want to sit there for hours deleting them one at a time so what can you do?

In the old days, you’d nip into the object designer and delete what you didn’t want. But in BC you can’t.

This cropped up today as a support case for a customer of mine. One of my helpdesk colleagues was wondering about a SQL script but I’d seen a session that José Miguel Azevedo had given at BC Bootcamp last year and I knew there had to be a better way.

First: A WARNING!!!

I was pretty pleased with this solution when I came across it, so I quickly wrote up this blog post and posted it. I’d spoken about it on Twitter so I posted a link there too.

Pretty quickly some folk who’ve been doing this a lot lot longer than me – folk I really respect – chipped in with some warnings about my solution and some truly horrifying stories.

Before you start on this please test in a Sandbox or test company.

The function I describe here, using configuration packages, deletes all records in the table, then commits, and only then reloads the records in the package.

I repeat…it DELETES all records in the table and only re-imports what you tell it to. Don’t do this when anyone else is on the system. You really don’t want to run into a conflict whilst doing this.

And final piece of advice: don’t muck about with anything else whilst doing the deletions. It might be tempting to edit some records too. Don’t do it. Keep it simple.

Configuration Packages

We’re all familiar with Config Packs, right? They’re just for loading data into BC during implementations, aren’t they?

Well, no. They’re not just for that. They can help you with bulk deletions too. Let’s take a look.

1         What to delete

In my Sandbox I created a dozen copy items of the good old ATHENS desk and I want to get rid of them.

First of all I need to check that there are no outstanding orders or item ledger entries because you must make sure you don’t delete an entity that has transactions against it. I’d usually do this with Power BI (list of items with an aggregated count of item ledger entry lines)

This is important because, whilst you usually cannot delete a record with open documents or ledger entries against it, using this function you can. It’s easy to screw up here and leave orphaned ledger entries which is messy and will beg questions when the auditor comes calling. 

2         Create the configuration package

I’ve created a really simple one here. It’s one table (27: Items) and I only need two fields: No. and Description.

3         Export the pack to Excel

Export the table to excel.

And then open it up.

Very, very carefully, delete the records you don’t want in BC anymore and save the spreadsheet.

4         Reimport the data

Reimport the data and, once it’s uploaded, check the “Delete Table Records Before Processing” checkbox. This will delete all the records in the table and only reimport what you’ve asked it to in the spreadsheet you’ve imported. Obviously this is a very useful but VERY dangerous feature. This is why you need to make your BULK_DELETE package clearly different and separate from all the others.

5         Apply the package

Once the package is imported you can see that there are fewer package records than Database records.

Click the “Apply Package” button in the ribbon and you then see that the deletions have occurred.

Go to Items and there they are: gone!

This can be done for any entity in BC: Customers, Vendors, Contacts; but the same rules apply. They have to have no ledger entries and mustn’t feature on any current document.

Remember…this is a very powerful feature that will permanently delete records. 

ALWAYS TEST IN A SANDBOX FIRST AND DON’T PERFORM THE DELETION WHEN ANYONE ELSE IS ON THE SYSTEM.

Deleting records using Edit in Excel

I wrote the above post whilst trying to solve a problem for a client who is on BC v14 (using the Nav desktop client – sighs sentimentally). In their case I’ll have to use the solution described. However in newer versions there is a safer method that doesn’t allow the user to delete records with ledger entries.

Again, I created a dozen duplicate records. This time using the PARIS Guest Chair

Click on the Edit in Excel button

This downloads an excel file to your desktop that is connected to BC. Highlight the lines you want to delete and delete them just like you would in any excel file. 

Now simply click “Publish”. This reloads the file back to BC. Your deletion of the lines passes an instruction back to BC to perform the deletions on those records. 

The following message pops up to let you know what you’re about to do (as opposed to configuration packages which give you a similar message after the event!)

Having had a play around with this I can see definite benefits as compared to my earlier solution. My favourite, though, is that it will not allow you to delete anything you shouldn’t. 

So if you’re lucky enough to be on a newer version (I think this is v16 onwards) this is a much safer option for bulk deletes.