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.