How to use the Document Customizer function “Command Lines”

Introduction

Command lines (advanced value lookup) is a Document Customizer tool that can be used when a field that you wish to have on a certain document does not natively appear in the table that the document is generated from.

Process

The first step in the process of creating a command line is to find the necessary table ID(s) and field ID. For this process, the Page Inspection feature (keyboard shortcut: Alt+Shift+F1) can be a great help, as it shows the table used for the page that you are currently viewing in Business Central, the fields present in the table and their corresponding table ID’s.

The tables and fields that we must note are:

Table ID of the table that you wish to grab the field from.
The Unique Key Field(s) that you wish to use to connect the two tables, in the table that the document uses.
The corresponding Unique Key Field(s) in the table that you just noted.
The field ID of the field that you wish to add to the document

The general formula for the command line is (fill in the values with the corresponding letter above):
GetFieldValue(LookupRecord(This,A,'B','C','',''),D)
IMPORTANT: Do not use spaces in the formula, as this will result in an error when using the command line
If there are two fields used to make the link to the desired fields:
GetFieldValue(LookupRecord(This,A,'B1,B2','C1,C2','',''),D)
And if there are more than two fields you just extend the formula with the desired amount of links (n):
GetFieldValue(LookupRecord(This,A,'B1,…,BN','C1,…,CN','',''),D)

To explain this in greater detail we can use an example with the Posted Sales Shipment document. We want the Outstanding Quantity (if there is any remaining quantity to ship) to appear as a column on the Posted Sales Shipment document. For this, we will have to create a command line, since this field is not found in the Sales Shipment Line table.
Furthermore, since the Outstanding Quantity is present at a line level and can vary from line to line within the same Sales Order, we need two unique identifiers to get the correct Outstanding Quantity for the different lines. Specifically, we need an identifier that can connect the Posted Sales Shipment with the correct Sales Order and an identifier that can connect the different lines to the corresponding ones within the same Posted Sales Shipment and Sales Order.

We therefore need to use this formula:
GetFieldValue(LookupRecord(This,A,'B1,B2','C1,C2','',''),D)

Two fields that could allow us to do so are the Document Number field and the Line Number field. These fields will allow us to create a link between a line on a Posted Sales Shipment and a line on the Sales Order, as these are unique identifiers and can be found in both tables.

In the example described above the tables and fields that we should note are:

Example

Table ID: 37. This is the Sales Order table, in which the Outstanding Quantity can be found
Unique Key Fields IDs in the Sales Shipment Line table: 65 & 66. These fields are the Document number and Line number in this table.
Unique Key Fields IDs in the Sales Line table: 3 & 4. These fields are also the Document number and Line number in this table.
Desired field’s Field ID: 16. This is the field ID for the Outstanding Quantity field in the Sales Line table.

Based on this, we will get a command line that looks like this:
GetFieldValue(LookupRecord(This,37,'65,66','3,4','',''),16)

This can now be used to show the Outstanding Quantity field as a column on the Posted Sales Shipment. To do so, create a new column as you would usually do.
Table No. should be the same as for all other columns for the given document. Field should be “0”, as we do not want a value to be shown if there is no outstanding quantity. In relation to this the “Blank caption if no value” could be toggled on if there is no outstanding quantity on any lines. Do bear in mind that, technically the column will still be there, it will just be invisible. This means that the column will still take up the space that it would usually do, but nothing will be shown. This also means that you will not gain additional space for the other columns in this case.
Remember to give the line an appropriate Caption (create one if none of the preexisting are suitable)
Finish by filling in the command line in the Command Line field.
The Outstanding Quantity field will now be shown as a column on Posted Sales Shipment documents where the associated Sales Order has a value in the Outstanding Quantity field and will be hidden in cases where there is no outstanding quantity for any of the lines on the corresponding Sales Order.

For the simplest instance of a command line:
GetFieldValue(LookupRecord(This,A,'B','C','',''),D)
The “Using Table” and “Using Field” options can be used instead. However, there is a caveat to this. When using a command line we can chose “0” in the Field column, as we define the table and field that should be used in the command line itself. This cannot be done when using the “Using Table” and “Using Field”, as the field (the unique identifier) that connects the document table with the table where the desired field is present, must be stated in the Field column, as this is used to connect the two tables.
This means that if there are cases where the desired field is blank, the field on the document will instead be filled out with the value of the field used to connect the two tables. This can be undesirable as this will sometimes make no sense and might cause confusion. Therefore, the command line is often a more desirable option to use.

You might also wonder what this part of the formula does:
GetFieldValue(LookupRecord(This,A,'B','C','',''),D)
This part of the formula makes it possible to filter in the table that we are retrieving records from. To use this, this general formula can be used:
GetFieldValue(LookupRecord(This,A,’B’,’C’,'FieldE=1(%1)',’F’),D)
Here the E represents the field ID that we want to filter for, and F represents the value that we want to filter for.
Following the example used previously, we can choose to filter on the “Location Code” field (28) for the value “MAIN”, as such:
GetFieldValue(LookupRecord(This,37,'65,66','3,4','FieldE=28(%1)',’MAIN’),16)

Furthermore, you might also wonder what this part means:
GetFieldValue(LookupRecord(This,A,'B','C','',''),D)
These are two coding functions. They are used to get a field value from a record based on a Record ID and a field number (GetFieldValue) and look up a record in some table based on the Parent Record (LookupRecord). To refer to the Record ID of the current record in the command line you use “This”.

Lastly, it is also possible to make nested command lines if needed. This means that command lines can be created within other command lines as such:
GetFieldValue(LookupRecord(This,A,'B','C','',GetFieldValue(LookupRecord(This,AA,'BB','CC','',''),DD)),D)

Dansk version
Hvordan Document Customizer funktionen “Kommandolinjer” bruges

Introduktion
Kommandolinjer er et Document Customizer-værktøj, der kan bruges, når et felt, som du ønsker at have på et bestemt dokument, ikke findes direkte i den tabel, som dokumentet genereres fra.
Proces
Det første trin i processen med at oprette en kommandolinje er at finde de nødvendige tabel-ID’er og felt-ID. Til dette kan funktionen Sideinspektion (tastaturgenvej: Alt+Shift+F1) være en stor hjælp, da den viser tabellen, der bruges til den side, du aktuelt ser i Business Central, samt de felter, der findes i tabellen, og deres tilsvarende tabel-ID’er.
De tabeller og felter, vi skal notere, er:
A. Tabel-ID for den tabel, hvorfra du ønsker at hente feltet.
B. Det/de unikke nøglefelt(er), du vil bruge til at forbinde de to tabeller, i tabellen som dokumentet bruger.
C. De tilsvarende unikke nøglefelt(er) i den tabel, du netop har noteret.
D. Felt-ID for det felt, du ønsker at tilføje til dokumentet.
Den generelle formel for en kommandolinje er (udfyld værdierne med de tilsvarende bogstaver ovenfor):
GetFieldValue(LookupRecord(This,A,'B','C','',''),D)
VIGTIGT: Du må ikke bruge mellemrum i formlen, da dette vil føre til, at du får en fejl når du prøver at bruge kommandolinjen
Hvis der bruges to felter til at skabe forbindelsen til de ønskede felter:
GetFieldValue(LookupRecord(This,A,'B1,B2','C1,C2','',''),D)
Hvis der er flere end to felter, udvider du blot formlen med det ønskede antal links (n):
GetFieldValue(LookupRecord(This,A,'B1,…,BN','C1,…,CN','',''),D)
Eksempel
For at forklare dette mere detaljeret kan vi bruge et eksempel med dokumentet Bogført salgsleverance (Posted Sales Shipment).

Vi ønsker, at feltet Udestående antal (Outstanding Quantity) (hvis der stadig er en mængde tilbage at sende) skal vises som en kolonne på dokumentet. Til dette skal vi oprette en kommandolinje, da feltet ikke findes i tabellen for bogførte salgsleveranceer.
Da Udestående antaln findes på linjeniveau og kan variere fra linje til linje inden for samme salgsordre, har vi brug for to unikke identifikatorer for at finde den korrekte Udestående antal. Vi har specifikt brug for en identifikator, der kan forbinde den bogførte salgsleverance med den korrekte salgsordre, samt en identifikator, der kan forbinde de forskellige linjer mellem den bogførte salgsleverance og salgsordren.
Formlen bliver derfor:
GetFieldValue(LookupRecord(This,A,'B1,B2','C1,C2','',''),D)
To felter, der kan bruges til dette, er Dokumentnummer og Linjenummer. Disse felter findes i begge tabeller og fungerer som unikke identifikatorer.
I dette eksempel skal vi notere:
Den færdige formel ser således ud:
GetFieldValue(LookupRecord(This,37,'65,66','3,4','',''),16)
Dette kan nu bruges til at vise feltet Udestående antal som en kolonne på dokumentet Bogført salgsleverance.
Nu vil feltet Udestående antal blive vist i dokumentet, når der findes en værdi, og være skjult, når der ikke er nogen.
Yderligere information
For den simpleste udgave af en kommandolinje kan følgende formel bruges:
GetFieldValue(LookupRecord(This,A,'B','C','',''),D)
Man kan i stedet anvende mulighederne “Using Table” og “Using Field”. Der er dog en væsentlig ulempe ved dette. Når man bruger en command line, kan man vælge “0” i feltkolonnen, da vi i selve formlen definerer hvilken tabel og hvilket felt, der skal bruges. Dette kan ikke gøres, når man bruger “Using Table” og “Using Field”, da feltet (den unikke nøgle), der forbinder dokumenttabellen med tabellen, hvor det ønskede felt findes, skal angives i feltkolonnen, da det bruges til at forbinde de to tabeller.
Det betyder, at hvis det ønskede felt er tomt, vil feltet på dokumentet i stedet blive udfyldt med værdien fra det felt, der bruges til at forbinde tabellerne. Dette kan være uhensigtsmæssigt, da det i nogle tilfælde ikke giver mening og kan skabe forvirring. Derfor er det ofte mere ønskværdigt at bruge en command line.

Du undrer dig måske også over, hvad denne del af formlen gør:
GetFieldValue(LookupRecord(This,A,'B','C','',''),D)
Denne del af formlen gør det muligt at filtrere i den tabel, vi henter records fra. For at bruge dette kan følgende generelle formel anvendes:
GetFieldValue(LookupRecord(This,A,’B’,’C’,'FieldE=1(%1)',’F’),D)
Her repræsenterer E det felt-ID, vi ønsker at filtrere på, og F repræsenterer den værdi, vi vil filtrere efter. Som eksempel kan vi, med udgangspunkt i det tidligere eksempel, vælge at filtrere på feltet “Lokationskode” (28) for værdien “MAIN”, således:
GetFieldValue(LookupRecord(This,37,'65,66','3,4','FieldE=28(%1)',’MAIN’),16)

Du undrer dig måske også over, hvad denne del betyder:
GetFieldValue(LookupRecord(This,A,'B','C','',''),D)
Dette er to kodningsfunktioner. De bruges til at hente en feltværdi fra en record baseret på et record-ID og et felt-ID (GetFieldValue) og til at slå en post op i en tabel baseret på en parent-record (LookupRecord). For at henvise til den aktuelle record i en kommandolinje bruges ordet “This”.

Endelig er det også muligt at lave nestede kommandolinjer, hvis nødvendigt. Det betyder, at kommandolinjer kan indlejres i andre kommandolinjer, som for eksempel:
GetFieldValue(LookupRecord(This,A,'B','C','',GetFieldValue(LookupRecord(This,AA,'BB','CC','',''),DD)),D)

Further information

A. Tabel-ID: 37 (Salgsordre-tabellen, hvor Udestående antal findes)
B. Unikke nøglefelter i Bogført salgsleverance: 65 & 66 (dokumentnummer og linjenummer)
C. Tilsvarende nøglefelter i Salgsordre: 3 & 4 (dokumentnummer og linjenummer)
D. Felt-ID: 16 (Udestående antal i salgsordre)

Opret en ny kolonne som normalt.
Tabelnr. skal være det samme som for de øvrige kolonner på dokumentet.
Felt skal være 0, da vi ikke ønsker at vise en værdi, hvis der ikke findes en Udestående antal.
“Blank caption if no value” kan aktiveres, hvis der ingen Udestående antal er. Vær dog opmærksom på, at kolonnen stadig optager plads, men blot er usynlig.
Giv linjen en passende overskrift.
Indsæt formlen i feltet Command Line.