Fixed-width text files in data management (F&O)

Posted by on

Data management in Dynamics 365 for Finance and Operations supports several types of files for import and export. I usually see XML, Excel and CSV used, but sometimes we also need to deal with a text format with fixed-width columns. It means that there is no column separator as in CSV – we know that first ten characters contains an ID, next eight characters contains a quantity or something like that.

Data management supports this scenario too, which not everyone is aware of.

To try it, first set up a source format. Go to Data management workspace, click Configure data source tile and create a new format. Set File format = Fixed width and possibly other parameters as needed. I’ll use First row header = No and Row delimiter = {LF}. It doesn’t seem to work very well with {CR}{LF}.

Then create an export project and add an entity to be exported with our new source format. I used Customer groups entity (because it’s relatively simple and it doesn’t contain too many records).

Still in the project, notice Entity attribute button. It’s very important when talking about fixed-width files.

If you press it, you’ll see a list of columns that will be in the file. There is a sequence of columns, their length etc., and you can modify it as needed.

If you want to import the file, create an import project and add an entity with the fixed-width source format. It’ll complain about missing mapping, unless the columns have headers and data management is able to map them to fields.

In this case, we’ll configure it manually. As we already know, the Entity attribute button will take us into the setup of file columns. Here we can add the columns, their type, lenght and so on.

Then we can use these columns in source mapping in the usual way.

By the way, when I needed to deal with fixed-width text files in C# (namely Azure Functions), I used FixedWidthParserWriter, which did the job perfectly.

Animated GIF thumbnails from a video

Posted by on

This blog post isn’t about Dynamics AX / Dynamics 365, although it may occasionally be useful for some AX/D365 developers as well.

Let’s say I have a 30 minutes long movie that I want to share on a website and I want to give people an idea about the content of the video. Therefore I want to create a slideshow of screenshots taken from the movie at regular intervals. An animated GIF is an obvious choice.

I spent quite some time trying to figure out how to do it, because I had little idea. After some research, I came to a conclusion that a reasonable approach is extracting screenshots with FFmpeg and then combining them to a GIF file with ImageMagick.

Because installing those console applications and dealing with them directly would be quite cumbersome, I also looked for some wrapper libraries available via NuGet. I chose these:

Now let’s build a simple application to show the code. Create a console application for .NET Framework and add those two NuGet packages.

Add using statements for namespaces that we’ll need in a moment:

using System.IO;
using ImageMagick;
using MediaToolkit;
using MediaToolkit.Model;
using MediaToolkit.Options;

The structure of your program will be following:

class Program
{
    static void Main(string[] args)
    {
        new Program().Run();
    }
 
    void Run()
    {
        string videoFilePath = @"c:\temp\input.mp4";
 
        List<string> thumbnails = ExtractThumbnails(videoFilePath, 10);
 
        if (thumbnails?.Any() == true)
        {
            string gifFilePath = Path.Combine(
                Path.GetDirectoryName(videoFilePath),
                $"{Path.GetFileName(videoFilePath)}.gif");
 
            CombineThumbnails(gifFilePath, thumbnails);
 
            // Delete temporary files
            thumbnails.ForEach(file => File.Delete(file));
        }
    }
}

ExtractThumbnails() takes screenshots from the video as JPG files.

CombineThumbnails() then take these files and put them into an animated GIF.

Finally we delete JPG files, because they aren’t needed anymore.

Here is the implementation of ExtractThumbnails(). The key part is the call of engine.GetThumbnail() at the end.

List<string> ExtractThumbnails(string inputFilePath, int numOfPics)
{
    MediaFile inputFile = new MediaFile { Filename = inputFilePath };
    List<string> thumbnails = new List<string>(numOfPics);
 
    using (var engine = new Engine())
    {
        engine.GetMetadata(inputFile);
        if (inputFile.Metadata == null)
        {
            throw new InvalidOperationException("Invalid file");
        }
 
        int duration = (int)inputFile.Metadata.Duration.TotalSeconds;
        int picDistance = duration / (numOfPics + 1);
 
        for (int i = 1; i <= numOfPics; i++)
        {
            var options = new ConversionOptions { Seek = TimeSpan.FromSeconds(i * picDistance) };
 
            string outputFilePath = $"{inputFilePath}{i}.jpg";
 
            var outputFile = new MediaFile { Filename = outputFilePath };
            engine.GetThumbnail(inputFile, outputFile, options);
            thumbnails.Add(outputFilePath);
        }
    }
 
    return thumbnails;
}

The last missing piece is the method creating the animated GIF. It’s straightforward, thanks to Magick.NET:

void CombineThumbnails(string gifFilePath, List<string> thumbnails)
{
    using (var collection = new MagickImageCollection())
    {
        for (int i = 0; i < thumbnails.Count; i++)
        {
            collection.Add(thumbnails[i]);
            collection[i].AnimationDelay = 70;
            collection[i].Resize(800, 600);
        }
 
        collection.Write(gifFilePath);
    }
}

Entity metadata in a console application

Posted by on

There was a question in the Community forum about creating tables in an external databases that would match the schema of F&O data entities.

One of the possible approaches is generating SQL code for CREATE TABLE statement. The information about field names, types and such things would be taken from F&O metadata and we already have a nice API for this purpose.

Here is code for simple console application getting some of the necessary data:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.Dynamics.AX.Metadata.MetaModel;
using Microsoft.Dynamics.AX.Metadata.Storage;
using Microsoft.Dynamics.AX.Metadata.Storage.Runtime;
 
namespace DataEntityFields
{
    public class Demo
    {
        public static void Main(string[] args)
        {
            var environment = Microsoft.Dynamics.ApplicationPlatform.Environment.EnvironmentFactory.GetApplicationEnvironment();
            var runtimeConfiguration = new RuntimeProviderConfiguration(environment.Aos.PackageDirectory);
            var metadataProvider = new MetadataProviderFactory().CreateRuntimeProviderWithExtensions(runtimeConfiguration);
 
            AxDataEntityView entity = metadataProvider.DataEntityViews.Read("VendVendorV2Entity");
 
            foreach (AxDataEntityViewMappedField entityField in entity.Fields.Where(f => f is AxDataEntityViewMappedField))
            {
                var datasource = FindDataSource(entity.ViewMetadata.DataSources, entityField.DataSource);
                if (datasource == null)
                {
                    continue;
                }
 
                AxTable table = metadataProvider.Tables.Read(datasource.Table);
                AxTableField field = table.Fields[entityField.DataField];
                AxTableFieldString stringField = field as AxTableFieldString;
                if (stringField != null)
                {
                    AxEdtString edt = null;
                    if (stringField.ExtendedDataType != "")
                    {
                        edt = metadataProvider.Edts.Read(stringField.ExtendedDataType) as AxEdtString;
                    }
 
                    int stringSize = edt?.StringSize ?? stringField.StringSize;
                    Console.WriteLine($"{entityField.Name} (String {stringSize})");
                }
            }
 
            Console.ReadLine();
        }
 
        private static AxQuerySimpleDataSource FindDataSource(IEnumerable<AxQuerySimpleDatasource> dataSources, string dataSourceName)
        {
            foreach (AxQuerySimpleDataSource ds in dataSources)
            {
                if (ds.Name == dataSourceName)
                {
                    return ds;
                }
 
                return FindDataSource(ds.DataSources, dataSourceName);
            }
 
            return null;
        }
    }
}

Note that it requires references to a few assemblies:

  • Microsoft.Dynamics.AX.Metadata.dll
  • Microsoft.Dynamics.AX.Metadata.Core.dll
  • Microsoft.Dynamics.AX.Metadata.Storage.dll
  • Microsoft.Dynamics.ApplicationPlatform.Environment.dll

Simple metadata query in Powershell (D365FO)

Posted by on

If I need to extra metadata of a D365FO application, my preferred way is the new metadata API. But sometimes the requirement is so simple that it’s sufficient to query source XML files directly.

For example, this all what’s needed to get a list of fields from a table (together with their types) and store it in a CSV file. It’s written in Powershell:

[xml]$xml = Get-Content $inputXmlFilePath
$xml.AxTable.Fields.AxTableField | select Name, ExtendedDataType, EnumType | Export-CSV $outputCsvFilePath -nti

It’s great for trivial cases like this. If I needed more details about fields or I had to take into account table extensions, for example, then the metadata API would be a better choice.

Finding display methods in D365FO

Posted by on

Somebody asked in the Community forum for examples of display methods in views in the standard D365FO application. It’s not necessary to remember any concrete examples – finding all such methods is very simple. The right tool for this purpose is Metadata search.

Open Metadata search (Visual Studio > Dynamics 365 > Metadata search) and put the following query to the search box on the top:

type:view,method property:"IsDisplay=True"

The first part (type:view,method) says that we’re interested in methods in views. The latter part (property:”IsDisplay=True”) makes sure that only method with the given property value are returned.