D365 Finance – Data entities – Import images (document handling)


To import images in D365 can be a bit tricky, if not painstaking. If you look at the previous post for Exporting Images, we had downloaded a package which contained an excel file and a folder with the images. It also included 2 xml files.

Importing data will pretty much be done the same way using a similar zip file which includes the 2 xml files as well PackageHeader.xml and Manifest.xml . These files specify the mapping between the excel and staging table and the entity and name of the excel file.
So to keep it simple, follow the Previous post and export the entity, and download the zip file.

Then you can run a powershell script which will update the filename, copy them across to a folder and rename them.

You may also want to follow this post from Munib Ahmand on importing attachments

Create your Excel file
Get the excel file and clear out the data from there. Then fill it up except for the columns FILETYPE, ORIGINALFILENAME
However, put the full path of the file into the column AttachmentFileName i.e. something like C:\myImages\MyProducts\T0001\TheProductImage1.jpg
Your excel file should now look something like this:
undefined

Run the script
Ok, so the time has come to run the script.
1. Create a new folder anywhere and copy the 2 xml files Manifest.xml and PackageHeader.xml in to the folder
2. Download the following powershell script
3. Open powershell and navigate to the folder where the above script was downloaded
4. Open the excel file that you have and find the following fields AttachmentFileName, FileType, OriginalFileName
In my case
FileType = 7
OriginalFileName = 11
AttachmentFileName = 13
Get the column number starting from 1 for these and close the excel file
5. Run the following command from powershell
PS C:\Temp\DM_Demo> .\FileUpload_ExcelResourceCreator.ps1 -ExcelFilePath “C:\Temp\DM_Demo\Import\Released product document attachments.xlsx" -TargetFolderPath C:\Temp\DM_Demo\Import\DMDemoImport -TargetEntity "Released product document attachments" -ColumnAttachmentFileName 11 -ColumnAttachmentFileType 7 -ColumnAttachmentFilePath 13
This will create a new excel file in the Target folder and update the values inside it . It will also copy the files mentioned in the excel file, rename them to the guid value assosicated in the excel file into the Resources/<entity name> folder
6. Navigate to the target folder (You have to go into the folder)
Select all the files and Zip them

Import the Images / attachments
1. In D365 navigate to Data management
2. Create a new Import Project
3. Select “Add File” and clieck “Upload and add”
Choose your zip file and click “Close”
Because your zip file contains the information about the entities it will add the entities to the project.
4. Click “Import” and your attachment / image will be against the record


What does the Script do?
The script takes the file name from the excel file, and replaces it with a GUID. The file is then copied to the target forlder under resources and renamed to the same GUID. The script also copies the file name and the type of the file (Jpg, png, txt, xlxs, etc) and places it in the excel file

D365 Finance – Data entities – Export images


With Data management in Dynamics 365 Finance (and maybe operations) you can export images the same way you export other data. The images are exported as files, however,, they are stored in a GUID name and you have to link this on.

  1. Create an Export type Data management project
  2. Add your entities that you want to export.I will use the Re;eased product attachments entity for this. Remember to check importable fields only at this stage. ( i will be using this template to import images later
    undefined
  3. Hit export and download the package
    undefined
  4. Open the zipped file and and you will see 3 files and 1 folder
    a. Manifest.xml
    b. PackageHeader.xml
    c. Release product document attachment.xlsx (This contains all the records with the file name in it.)
    d. Resource folder – this contains the files (inside the folder of the entity name) without a type. You will need to match the file name against the value in the excel file under the column “AttachmentFileName”. you will need to change the name of the file and type in order to open it.
    undefined

Updating SQL intellisense


So you added a new table / field via D365 Finops and synchronised the database.
To get the intellisense to pick those new objects you might be restarting SSMS ?

Instead you could just be refreshing the intellisense OR in other words Refresh intellisense’s local cache

  1. Create a new query window and link it to your database
  2. Click on Edit > Intellisense > Refresh local cache (Ctrl + Shift + R)

You will now have the new objects.

Enjoy !

[D365] Fin ops Visual Studio Addins


D365 & Visual studio gives you the ability to create your own Visual studio Add In for D365 development. The idea for me to create the add-in was to reduce the number of keystrokes and especially moving forward and backwards between the AOT and the code editor. There are quite a few “enhancements” i have added that certainly makes my development life a bit easier.

The Add-in however only works from the Object designer instead of the AOT nodes, which is most of the times a good thing especially when you are searching for code via the code editor.

I will be following up more on the add-ins in future posts.

Checkout the Add-in SSD365VSAddIn

 

[D365] Fin Ops Dev tools Add ins – location of VS extension folder


When creating Visual studio Tools addins for D365 Fin ops you would need to copy it into the extension folder. This folder is most probably different on every machine.

To get the folder Open Regedit and navigate to: HKCU:\SOFTWARE\Microsoft\VisualStudio\14.0\ExtensionManager\EnabledExtensions

This stores the folder under the property DynamicsRainer*

Store your addin over here.

I have a powershell script that copies all the dll’s into that folder

D365 FO EDMX / OData trimmer – Make your metadata smaller


Creating a .Net application with the OData metadata from your D365 application can generate a large file

A typical metadata file (edmx) retrieved from https://<d365url>/data/$metadata will return a file of almost 17MB. Turn that into a c# file using the T4 templates and the C# source file is almost 64MB.

After spending some time trying to reduce the EDMX file to only the entities I require (and a lot of nagging by my friend) I created a way to remove the excess entities from here. Result is a smaller EDMX file, and a more lightweight .Net application for you.

Reference this Trimmed EDMX file in your T4 template

The project and instructions can be found at my Github project EDMXTrimmer: https://github.com/shashisadasivan/EDMXTrimmer

 

[D365] Operations Financial Reporter – Cannot open


Financial reporter “Yet another post if this doesnt open up”
If you get a message from the click once installer saying : “Your security settings do not allow this application to be run on your computer”

Start with running this from IE. Chrome gives some other issues
1. Add the website to your trusted sites (via internet options > Security > trusted websites > Sites and add it over there)
2. Edit the registry editor (Start > regedit OR Start + r > regedit.exe)
3. Navigate to \HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT.NETFramework\Security\TrustManager\PromptingLevel
4. Change the value for TrustedSites from Disabled to Enabled
(if this is your own system and you work on multi[ple environments, then probably change the value of Internet as well from Disabled to Enabled)
5. Save regedit and restart IE.

Note: Edge / IE12 has better chances of running the clickonce than chrome

Source: https://msdn.microsoft.com/en-us/library/ee308453.aspx

[AX2012] Get Email for Customer / Vendor (with specific roles)


To get an email address for a Customer or vendor, you can use the following statement

static void DEL_SS_emailStmtjob(Args _args)
{
CustTable cust; //Replace with vendTable for Vendors
DirPartyLocation dirPartyLocation;
LogisticsElectronicAddress elecAddress;
LogisticsElectronicAddressRole elecAddressRole;
LogisticsLocationRole locRole;
select firstOnly cust
where cust.AccountNum == '‪‪‪Cust-001';
while select DirPartyLocation
where dirPartyLocation.party == cust.Party
{
while select elecAddress
where elecAddress.Location == dirPartyLocation.Location
&& elecAddress.Type == LogisticsElectronicAddressMethodType::Email
{
while select elecAddressRole
where elecAddressRole.ElectronicAddress == elecAddress.RecId
join locRole
where locRole.RecId == elecAddressRole.LocationRole
{
info(strFmt("%1 - %2", elecAddress.Locator, locRole.Name));
}
}
}
}

SystemSequences Update


If you ever get a error where the RecId being inserted into the table already exists then the SystemSequences table is to be blamed for this

In some of my previous blog posts, i update the system sequence table’s NextVal record, which determines the RecId to be given. However, this is tricky.

  1. Update in SQL
    • Stop the AOS
    • Update the NextVal recod in the SystemSequences Table
    • Start the AOS
  2. Write a job
static void SetNextRecId(Args _args)
{
    SystemSequences seq;
    ttsBegin;
    select firstonly forupdate crosscompany seq
        where seq.tabId == 123456; // use the table id here or tablenum()
    seq.skipTTSCheck(true);
    seq.skipDatabaseLog(true);
    seq.selectForUpdate(true);
    seq.nextVal = 5637123456 + 1; // enter the last recId for the table
    seq.update();
    ttsCommit;
}

You may need to run this job and restart the AX client only if the just the above doesn’t work

static void sab_recIdSequenceFix(Args _args)
{
    SystemSequence systemSequence = new systemSequence();
    Tableid tableId = 123456; // use the table id or tablenum() here

    systemSequence.suspendRecIds(tableId);
    systemSequence.suspendTransIds(tableId);
    systemSequence.flushValues(tableId);
    systemSequence.removeRecIdSuspension(tableId);
    systemSequence.removeTransIdSuspension(tableId);
}