In this lesson you will learn how GuiXT tables and structures are passed as JSON to a .NET function and how the .NET result is loaded back as a JSON array into a GuiXT table. As a practical example we implement a route optimiser: delivery addresses with geo-coordinates are read from SAP customer master data and sorted into the optimal driving order via the REST API of OpenRouteService.

How the components interact:

  1. GuiXT reads customer data via a database select into an internal table variable.
  2. GuiXT passes the table as a JSON array together with the start point to the .NET function OptimizeDeliveries.
  3. .NET geocodes missing coordinates (Nominatim/OpenStreetMap), calls the ORS API and returns the sorted route as a JSON array.
  4. GuiXT automatically loads the JSON result into the target table routetable.

1. The Screen Layout

The GuiXT script defines input fields for the start point as well as two tables: customer for the loaded customer data and routetable for the optimised route. The pushbutton triggers the input script calc_route.txt on click.

GuiXT
InputField (15,32) "Country Code" (15,52) size=2  name="sCC"
InputField (14,32) "Postcode"     (14,52) size=10 name="sPC" -numerical
InputField (13,32) "City"         (13,52) size=30 name="sC"
InputField (12,32) "Street"       (12,52) size=30 name="sSt"
Box (11,31) (18,83) "Start Point"

Pushbutton (17,47) "Calculate Best Route" process="calc_route.txt"

Table (20,1) (40,150) name="customer" title="Customer Locations"
Column "Customer No." size=10 name="kunnr"
Column "Name"         size=20 name="name1"
Column "Name 2"       size=20 name="name2"
Column "Street"       size=20 name="street"
Column "City"         size=20 name="city"
Column "Post Code"    size=10 name="postcode"
Column "Country"      size=8  name="country"
Column "Latitude"     size=15 name="latitude"
Column "Longitude"    size=15 name="longitude"

Table (41,1) (61,90) name="routetable" title="Route"
Column "Stop"      size=20 name="stop"
Column "Street"    size=20 name="street"
Column "City"      size=20 name="city"
Column "Post Code" size=10 name="postcode"
Column "Country"   size=8  name="country"

The four InputField statements create fields for street, city, postcode and country code of the start point. Street is optional and handled separately in the script. The customer table has nine columns, including latitude and longitude, which come from a geoloc table. The routetable is populated by the .NET return value.

2. The GuiXT Script (calc_route.txt)

The input script is triggered by the pushbutton. It creates the table variables, performs the database access, validates the start point and calls the .NET function.

GuiXT
// create table variable with column names
CreateTable V[customer] _
    kunnr name1 name2 street city postcode country latitude longitude

// build SQL join expression
Set V[table] "knvp join kna1 on kna1~kunnr = knvp~kunnr"
Set V[table] "&V[table] join adrc on adrc~addrnumber = kna1~adrnr"
Set V[table] _
    "&V[table] left outer join geoloc on geoloc~addrnumber = adrc~addrnumber"

// comma-separated field list
Set V[fields] "knvp~kunnr"
Set V[fields] "&V[fields],kna1~name1"
Set V[fields] "&V[fields],kna1~name2"
Set V[fields] "&V[fields],adrc~street"
Set V[fields] "&V[fields],adrc~city1"
Set V[fields] "&V[fields],adrc~post_code1"
Set V[fields] "&V[fields],adrc~country"
Set V[fields] "&V[fields],geoloc~latitude"
Set V[fields] "&V[fields],geoloc~longitude"

// WHERE condition (without keyword WHERE)
Set V[condition] "knvp~pernr = '00000005'"
Set V[condition] "&V[condition] and knvp~vkorg = '1010'"
Set V[condition] "&V[condition] and knvp~vtweg = '10'"

Set V[orderby] "kna1~name1"

// execute database select
Call "/guixt/dbselect" _
    export.table="table" _
    export.fields="fields" _
    export.condition="condition" _
    export.orderby="orderby" _
    import.values="customer"

if not Q[ok]
    Message "&V[_exception]"
endif

// validate mandatory start point fields (V[name=] means "is empty")
if V[sC=] or V[sPC=] or V[sCC=]
    Message "No startpoint given"
    leave
else
    // Street is optional
    if not V[sSt=]
        Set V[Start] "&V[sSt], &V[sC], &V[sPC], &V[sCC]"
    else
        Set V[Start] "&V[sC], &V[sPC], &V[sCC]"
    endif
endif

// create output table from a structure template
CreateStructure V[struct] stop street city postcode country
CreateTable V[routetable] include=V[struct]

// call .NET: table is serialized as JSON, result fills routetable
CallVB routetable = DataVB.RouteOptimizer.OptimizeDeliveries _
    "&V[Start]<|>&V[customer]"

2.1 Creating a table with CreateTable

CreateTable V[customer] kunnr name1 ... creates an empty table variable with the specified columns. The column names must match the columns of the GuiXT screen table so that GuiXT automatically transfers the data into the display after the CallVB.

2.2 Database query with Call "/guixt/dbselect"

The command Call "/guixt/dbselect" executes a generic SQL select directly on the SAP database. The parameters are passed as text variables:

  • export.table: table expression including JOINs
  • export.fields: comma-separated field list
  • export.condition: WHERE condition without the keyword WHERE
  • export.orderby: sort field
  • import.values: name of the target table variable, without V[...], GuiXT resolves the name internally

After the call, V[customer] contains all result rows. Q[ok] indicates success; on error V[_exception] contains the message. The left outer join with GEOLOC ensures that customers without geo-coordinates are still returned, missing values are looked up via geocoding in the .NET code.

2.3 Validating the start point

The notation V[name=] is a GuiXT shorthand for "variable is empty". With if V[sC=] or V[sPC=] or V[sCC=] the mandatory fields are checked. Street is optional, the start point string is assembled with four or three parts depending on the input: "Hauptstr. 1, Frankfurt, 60311, DE" or "Frankfurt, 60311, DE". This distinction is taken into account in the .NET code when splitting at the comma.

2.4 Output structure with CreateStructure and CreateTable

CreateStructure V[struct] stop street city postcode country defines a named field definition. CreateTable V[routetable] include=V[struct] creates an empty table from it, a practical way to create a table based on a structure template without listing the fields a second time.

2.5 CallVB: passing tables to .NET and loading the result back

GuiXT
CallVB routetable = DataVB.RouteOptimizer.OptimizeDeliveries _
    "&V[Start]<|>&V[customer]"

How GuiXT serialises tables as JSON:
When an expression of the form &V[tablename] is used in a string parameter and the variable contains a table, GuiXT automatically serialises it as a JSON array. Each row becomes a JSON object, the column names become the JSON keys. Example for two rows in V[customer]:

[{"kunnr":"1000","name1":"Müller GmbH","street":"Hauptstr. 1","latitude":"50.1109","longitude":"8.6821"},
{"kunnr":"1001","name1":"Schulz AG","street":"Bahnhofstr. 5","latitude":"","longitude":""}]

In this example two values are combined in a single string parameter: the start point string and the JSON array of the customer table. As a separator the character sequence <|> is used, which appears neither in addresses nor in JSON. In the .NET code the string is split at the separator.

Return: JSON array automatically becomes a GuiXT table:
If the .NET function returns a JSON array string and a table variable stands on the left of the =, GuiXT fills the target table automatically: each object in the array becomes a row, the JSON keys are assigned to the columns with the same name. Missing keys remain empty, unknown ones are ignored. No further script code is needed.

3. The .NET Code

The class RouteOptimizer in namespace DataVB contains three methods: the public entry function OptimizeDeliveries, the geocoding GeocodeAddress and the route optimisation OptimizeRoute.

3.1 Entry point: receive and parse JSON

The method receives the combined string, splits it at the <|> separator and parses the JSON array of the customer table using System.Text.Json. For the start point the coordinate (0, 0) is initially entered as a placeholder.

using System.Collections.Generic;
using System.Globalization;
using System.Net.Http;
using System.Text;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;

namespace DataVB
{
    public class RouteOptimizer
    {
        private const string NominatimUrl =
                "https://nominatim.openstreetmap.org/search";
        private const string ORSUrl =
                "https://api.openrouteservice.org/optimization";
        private const string OrsApiKey = "YOUR_ORS_API_KEY_HERE";

        public string OptimizeDeliveries(string inputJson)
        {
            System.Net.ServicePointManager.SecurityProtocol =
                    System.Net.SecurityProtocolType.Tls12;

            // GuiXT concatenated "&V[Start]<|>&V[customer]" into one string.
            // Index 0 = start point string, index 1 = JSON array of the table.
            var sep = new string[] { "<|>" };
            string[] splitParts = inputJson.Split(
                    sep, StringSplitOptions.None);

            var addresses = new List<string>();
            var coordinates = new List<(double lat, double lon)>();

            // Add start point, coordinates will be resolved by geocoding
            addresses.Add(splitParts[0]);
            coordinates.Add((0, 0));

            // Iterate over JSON array of the customer table
            JsonDocument doc = JsonDocument.Parse(splitParts[1]);
            foreach (JsonElement el in doc.RootElement.EnumerateArray())
            {
                // .Replace because SAP may use comma as decimal separator
                double lat = double.Parse(
                        el.GetProperty("latitude").GetString()
                          .Replace(",", "."),
                        CultureInfo.InvariantCulture);
                double lon = double.Parse(
                        el.GetProperty("longitude").GetString()
                          .Replace(",", "."),
                        CultureInfo.InvariantCulture);
                string street = el.GetProperty("street").GetString();
                string city = el.GetProperty("city").GetString();
                string postcode = el.GetProperty("postcode").GetString();
                string country = el.GetProperty("country").GetString();
                addresses.Add(
                        $"{street}, {city}, {postcode}, {country}");
                coordinates.Add((lat, lon));
            }
        }
    }
}

When parsing the coordinates .Replace(",", ".") is applied. Depending on the SAP system settings GuiXT may serialise decimal numbers with a comma. CultureInfo.InvariantCulture then forces the interpretation of the dot as the decimal character.

3.2 Geocoding missing coordinates (Nominatim API)

Customers without geo-coordinates (Latitude = 0 and Longitude = 0) are geocoded via the Nominatim API of OpenStreetMap. The Thread.Sleep(1100) pause respects the rate limit (max. 1 request/second). If there is no match a fallback without street is tried. Since GuiXT executes .NET functions synchronously, the async method is called via a synchronous wrapper with GetAwaiter().GetResult().

        // Geocode all addresses that have no coordinates yet
        for (int i = 0; i < addresses.Count; i++)
        {
            if (coordinates[i].lat == 0 && coordinates[i].lon == 0)
                coordinates[i] = GeocodeAddressSync(addresses[i]);
            Thread.Sleep(1100);
        }

        private (double lat, double lon) GeocodeAddressSync(
                string address)
        {
            return GeocodeAddress(address).GetAwaiter().GetResult();
        }

        private async Task<(double lat, double lon)> GeocodeAddress(
                string address)
        {
            using (var client = new HttpClient())
            {
                client.DefaultRequestHeaders.Add(
                        "User-Agent", "SAPRoutingDemo/1.0");

                string q = Uri.EscapeDataString(address);
                string url =
                        $"{NominatimUrl}?q={q}&format=json&limit=1";
                string response = await client.GetStringAsync(url);

                // Fallback: search without street if no result found
                if (response == "[]")
                {
                    var parts = address.Split(',');
                    string fallback =
                        $"{parts[1].Trim()}, " +
                        $"{parts[2].Trim()}, " +
                        parts[3].Trim();
                    string fq = Uri.EscapeDataString(fallback);
                    url = $"{NominatimUrl}?q={fq}" +
                          "&format=json&limit=1";
                    response = await client.GetStringAsync(url);
                    Thread.Sleep(1100);
                }

                JsonDocument doc = JsonDocument.Parse(response);
                double lat = double.Parse(
                        doc.RootElement[0].GetProperty("lat")
                           .GetString(),
                        CultureInfo.InvariantCulture);
                double lon = double.Parse(
                        doc.RootElement[0].GetProperty("lon")
                           .GetString(),
                        CultureInfo.InvariantCulture);
                return (lat, lon);
            }
        }

3.3 Route optimisation via the OpenRouteService API

OptimizeRoute builds a JSON request for the ORS Optimization API. All customer addresses (from index 1) are passed as "jobs", the start point (index 0) as the vehicle start position. The API responds with an optimised order of job IDs in routes[0].steps. Entries of type "job" are customer stops, "start" and "end" are skipped.

Note: ORS expects coordinates in the format [lon, lat], longitude first. When converting double values to strings CultureInfo.InvariantCulture must be used so that the decimal point (not comma) is output.

        private List<int> OptimizeRouteSync(
                List<(double lat, double lon)> stops)
        {
            return OptimizeRoute(stops).GetAwaiter().GetResult();
        }

        private async Task<List<int>> OptimizeRoute(
                List<(double lat, double lon)> stops)
        {
            using (var client = new HttpClient())
            {
                client.DefaultRequestHeaders.Add(
                        "Authorization", "Bearer " + OrsApiKey);

                // Jobs array: each customer address
                //   becomes a job with its index ID
                // ORS coordinate format: [lon, lat] (longitude first!)
                var jobs = new StringBuilder();
                for (int i = 0; i < stops.Count; i++)
                {
                    string sLon = stops[i].lon.ToString(
                            CultureInfo.InvariantCulture);
                    string sLat = stops[i].lat.ToString(
                            CultureInfo.InvariantCulture);
                    if (i > 0) jobs.Append(",");
                    jobs.Append(
                        $"{{\"id\":{i}," +
                        $"\"location\":[{sLon},{sLat}]}}");
                }

                // Vehicle start position from stops[0]
                //   (= sales rep start point)
                string vLon = stops[0].lon.ToString(
                        CultureInfo.InvariantCulture);
                string vLat = stops[0].lat.ToString(
                        CultureInfo.InvariantCulture);
                string body =
                    "{\"jobs\":[" + jobs + "]," +
                    "\"vehicles\":[{" +
                    "\"id\":1," +
                    "\"profile\":\"driving-car\"," +
                    "\"start\":[" + vLon + "," + vLat + "]}]}";

                var content = new StringContent(
                        body, Encoding.UTF8, "application/json");
                var httpResp = await client.PostAsync(ORSUrl, content);
                string response =
                        await httpResp.Content.ReadAsStringAsync();

                // ORS returns the optimised order in routes[0].steps
                JsonDocument doc = JsonDocument.Parse(response);
                JsonElement steps = doc.RootElement
                        .GetProperty("routes")[0]
                        .GetProperty("steps");

                var optimizedOrder = new List<int>();
                foreach (JsonElement step in steps.EnumerateArray())
                {
                    if (step.GetProperty("type").GetString() == "job")
                        optimizedOrder.Add(
                                step.GetProperty("job").GetInt32());
                }

                return optimizedOrder;
            }
        }

3.4 Building the result and returning it as JSON to GuiXT

The optimised job order returned by ORS is assembled as a JSON array. The field names in the JSON (stop, street, city, postcode, country) must exactly match the column names of the GuiXT target table routetable, GuiXT then fills the table automatically. An address with street has four parts after splitting at the comma, one without street only three.

            var optimizedOrder = OptimizeRouteSync(coordinates);

            var result = new StringBuilder();
            result.Append("[");
            for (int i = 0; i < optimizedOrder.Count; i++)
            {
                if (i > 0) result.Append(",");
                var parts = addresses[optimizedOrder[i]].Split(',');
                string street, city, postcode, country;

                // 4 parts = with street, 3 parts = without street
                if (parts.Length == 4)
                {
                    street = parts[0].Trim();
                    city = parts[1].Trim();
                    postcode = parts[2].Trim();
                    country = parts[3].Trim();
                }
                else
                {
                    street = "";
                    city = parts[0].Trim();
                    postcode = parts[1].Trim();
                    country = parts[2].Trim();
                }

                // Field names must match the column names of routetable
                string obj =
                    $"{{\"stop\":{i + 1}," +
                    $"\"street\":\"{street}\"," +
                    $"\"city\":\"{city}\"," +
                    $"\"postcode\":\"{postcode}\"," +
                    $"\"country\":\"{country}\"}}";
                result.Append(obj);
            }
            result.Append("]");
            return result.ToString();

The returned JSON has for example the following format:

[{"stop":1,"street":"Hauptstr. 1","city":"Frankfurt", "postcode":"60311","country":"DE"},
{"stop":2,"street":"Bahnhofstr. 5","city":"Darmstadt", "postcode":"64283","country":"DE"}]

Key principle: The JSON key names must exactly match the column names of the GuiXT target table, including case. GuiXT creates a table row for each object in the array and assigns the values to the columns with the same name.

Download route optimiser scripts (ZIP)