Media on temporary tables

Media on temporary tables

blog, Business Centralmmilince

I talked about “Tools and Techniques for Optimizing Business Central” at DOK Nordic in Odense in April and at DynamicsMinds in Portoroz last week. One topic I covered was about the benefits of using Media instead of BLOB. Also, I warned about the risk of unreferenced data when working with Media or MediaSet data type. During the DOK Nordic event, I got a question about Media on temp tables which led me to start testing.

Media and MediaSet

But before we get to the answer, let’s start from the beginning and say a few words about Media and MediaSets. Literally a few, because there are already some excellent blogs about using Media and MediaSet, so I won’t repeat that information here. Instead, if you want to learn more about how to start using Media, check out this blog, or this blog, or the Microsoft documentation. I’ll just say a bit about unreferenced data in those tables, or as Microsoft called it; Orphans.

Orphans

When we talk about orphans in Tenant Media and Tenant Media Set system tables, we talk about records in those tables that are not referenced to any record in the database. That means that for example, if we have an item with a Picture in a Tenant Media Set table, and we decide to delete this item, in some edge cases record from Tenant Media Set won’t be deleted. You can notice that this can cause big problems if this type of problem occurs often. The database could grow significantly, so it is important to be aware of this issue.

Because of that, Microsoft introduced the FindOrphans() procedure on Media and MediaSet objects. This procedure will return the list of GUIDs that represent Media/MediaSet that are not referenced anywhere. So a logical way to use this method would be something like this:

procedure DeleteOrphanedMedia()
var
    Media: Record "Tenant Media";
    Orphans: List of [Guid];
    OrphanId: Guid;
begin
    Orphans := Media.FindOrphans();

    foreach OrphanId in Orphans do begin
        if Media.Get(OrphanId) then
            Media.Delete();
    end;
end;

Data Administration – System app module

The good thing is that we don’t need to create a solution for removing orphans by ourselves for every new environment we maintain. The reason is that Microsoft some time ago introduced a Data Administration module within the System app for the cleanup of unreferenced Media.

It is a bit tricky to find how and where to use it. First, search for the Data Administration page; once it is opened and the Refresh action is selected, you will get something like this.

Now we can select the Delete Detached media action, which will open a Detached Media Cleanup page.

Finally, when we run the Load Detached Media action we will be able to see all the Orphaned Media. We see a few actions there, but personally, the most useful one is Schedule cleanup task which will delete all the Orphans. What will happen in the background: the system will try to run codeunit 1929 "Media Cleanup Runner" as a scheduled task, and if not possible, it will try to run it normally.

If we often expect to end up with a new Orphaned Media, then instead of going to the Detached Media Cleanup page manually every now and then, a better alternative is just to schedule a Job Queue Entry which will run above mentioned "Media Cleanup Runner" codeunit.

If you are interested in more details about the Data Administration module and how Microsoft implemented it, you can check it here.

Demo using a temp table

Let’s now try to answer the question from the introduction regarding the temporary table and having a Media field in it. The contradiction here is that temporary table data is held in memory, and Media data type is stored in System Tenant Media table. I hope you see a potential problem here. Or maybe you’re thinking: there is no problem at all, maybe an instance of temp Tenant Media will be created. But I need to disappoint you, that would be too good to be true. So let’s see the example below, where I created a simple table with a Media field in it and a simple page with action to import a media into this temp table. 

table 50100 "Demo Media"
{
    DataClassification = CustomerContent;
    TableType = Temporary;

    fields
    {
        field(1; "Entry No."; Integer)
        {
            DataClassification = CustomerContent;
            AutoIncrement = true;
        }
        field(2; Description; Text[100])
        {
            DataClassification = CustomerContent;
        }
        field(3; Image; Media)
        {
            DataClassification = CustomerContent;
        }
    }

    keys
    {
        key(Key1; "Entry No.")
        {
            Clustered = true;
        }
    }
}
page 50100 "Demo Media Card"
{
    PageType = Card;
    ApplicationArea = All;
    UsageCategory = Administration;
    SourceTable = "Demo Media";

    layout
    {
        area(Content)
        {
            group(GroupName)
            {
                field(Description; Rec.Description)
                {
                    ApplicationArea = All;
                }
            }
        }
    }

    actions
    {
        area(Processing)
        {
            action(ImportPicture)
            {
                Caption = 'Import';
                Image = Import;
                ToolTip = 'Import a picture file.';
                ApplicationArea = All;

                trigger OnAction()
                var
                    FileManagement: Codeunit "File Management";
                    TempBlob: Codeunit "Temp Blob";
                    InStr: InStream;
                    FileName: Text;
                begin
                    FileName := FileManagement.BLOBImport(TempBlob, '');
                    if FileName = '' then
                        exit;

                    TempBlob.CreateInStream(InStr);
                    Rec.Image.ImportStream(InStr, FileName);
                    Rec.Modify(true);
                end;
            }
        }
    }
}

This simple example will generate Orphan Media. If you don’t believe me, check the video below.

Base App combination

I quickly checked if there were combinations between temp tables and Media inside the Base App and I found 3 occurrences:

  • table 7499 "Item From Picture Buffer"
  • table 8902 "Email Account"
  • table 8913 "Email Attachments"

For example, let’s create a new item from a picture.

And now let’s cancel it, we simply changed our mind and don’t want to create it anymore.

What will happen next is that the picture of the item will stay in the system table and it will be orphaned. I know, I know, creating an item from a picture is really rare, and then clicking on the Cancel action is really edge case, but still, I just wanted to give an example here to be careful when using a Media data type on temp tables.

Conclusion

Using a Media instead of a BLOB has a lot of benefits, but we need to be careful when using Media, and we need to keep in mind that we can end up with unreferenced data in Tenant Media and Tenant Media Set system tables, which could grow really big. When we talk about Media on temp tables, even though that is not a very often case, we need to be extra careful there. A possible alternative is simply using a BLOB field on a temp table instead of Media or even better: have Data Cleanup procedures in place, so things with unreferenced Media won’t escalate.

Hope you enjoyed reading this blog! Until next time, cheers!