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:
GuiXT reads customer
data via a database select into an internal table variable.
GuiXT passes the table
as a JSON array together with the start point to the .NET
function OptimizeDeliveries.
.NET geocodes missing
coordinates (Nominatim/OpenStreetMap), calls the ORS API and returns
the sorted route as a JSON array.
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.
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, withoutV[...],
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
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]:
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.
Imports System.Globalization
Imports System.Net.Http
Imports System.Text
Imports System.Text.Json
Imports System.Threading
Imports System.Threading.Tasks
Namespace DataVB
Public Class RouteOptimizer
Private Const NominatimUrl As String =
"https://nominatim.openstreetmap.org/search"
Private Const ORSUrl As String =
"https://api.openrouteservice.org/optimization"
Private Const OrsApiKey As String = "YOUR_ORS_API_KEY_HERE"
Public Function OptimizeDeliveries(inputJson As String) As String
System.Net.ServicePointManager.SecurityProtocol =
System.Net.SecurityProtocolType.Tls12
' GuiXT concatenated "&V[Start]<|>&V[customer]" into one string.
' Part 0 = start point string, part 1 = JSON array of the table.
Dim sep As String() = {"<|>"}
Dim splitParts As String() = inputJson.Split(
sep, StringSplitOptions.None)
Dim addresses As New List(Of String)()
Dim coordinates As New List(Of (lat As Double, lon As Double))()
' 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
Dim doc As JsonDocument = JsonDocument.Parse(splitParts(1))
For Each el As JsonElement In doc.RootElement.EnumerateArray()
' .Replace because SAP may use comma as decimal separator
Dim lat As Double = Double.Parse(
el.GetProperty("latitude").GetString() _
.Replace(",", "."),
CultureInfo.InvariantCulture)
Dim lon As Double = Double.Parse(
el.GetProperty("longitude").GetString() _
.Replace(",", "."),
CultureInfo.InvariantCulture)
Dim street As String = el.GetProperty("street").GetString()
Dim city As String = el.GetProperty("city").GetString()
Dim postcode As String = _
el.GetProperty("postcode").GetString()
Dim country As String = _
el.GetProperty("country").GetString()
addresses.Add($"{street}, {city}, {postcode}, {country}")
coordinates.Add((lat, lon))
Next
End Function
End Class
End Namespace
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 i As Integer = 0 To addresses.Count - 1
If coordinates(i).lat = 0 AndAlso coordinates(i).lon = 0 Then
coordinates(i) = GeocodeAddressSync(addresses(i))
End If
Thread.Sleep(1100)
Next
Private Function GeocodeAddressSync(address As String) _
As (lat As Double, lon As Double)
Return GeocodeAddress(address).GetAwaiter().GetResult()
End Function
Private Async Function GeocodeAddress(address As String) _
As Task(Of (lat As Double, lon As Double))
Using client As New HttpClient()
client.DefaultRequestHeaders.Add(
"User-Agent", "SAPRoutingDemo/1.0")
Dim q As String = Uri.EscapeDataString(address)
Dim url As String =
$"{NominatimUrl}?q={q}&format=json&limit=1"
Dim response As String = Await client.GetStringAsync(url)
' Fallback: search without street if no result found
If response = "[]" Then
Dim parts As String() = address.Split(","c)
Dim p1 = parts(1).Trim()
Dim p2 = parts(2).Trim()
Dim p3 = parts(3).Trim()
Dim fallback As String = $"{p1}, {p2}, {p3}"
Dim fq As String = Uri.EscapeDataString(fallback)
url = $"{NominatimUrl}?q={fq}&format=json&limit=1"
response = Await client.GetStringAsync(url)
Thread.Sleep(1100)
End If
Dim doc As JsonDocument = JsonDocument.Parse(response)
Dim e1 = doc.RootElement.EnumerateArray().GetEnumerator()
e1.MoveNext()
Dim first As JsonElement = e1.Current
Dim lat As Double = Double.Parse(
first.GetProperty("lat").GetString(),
CultureInfo.InvariantCulture)
Dim lon As Double = Double.Parse(
first.GetProperty("lon").GetString(),
CultureInfo.InvariantCulture)
Return (lat, lon)
End Using
End Function
// 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 Function OptimizeRouteSync( _
stops As List(Of (lat As Double, lon As Double))) _
As List(Of Integer)
Return OptimizeRoute(stops).GetAwaiter().GetResult()
End Function
Private Async Function OptimizeRoute( _
stops As List(Of (lat As Double, lon As Double))) _
As Task(Of List(Of Integer))
Using client As 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!)
Dim jobs As New StringBuilder()
For i As Integer = 0 To stops.Count - 1
Dim sLon As String = stops(i).lon.ToString(
CultureInfo.InvariantCulture)
Dim sLat As String = stops(i).lat.ToString(
CultureInfo.InvariantCulture)
If i > 0 Then jobs.Append(",")
jobs.Append(
$"{{""id"":{i}," &
$"""location"":[{sLon},{sLat}]}}")
Next
' Vehicle start position from stops(0) (= sales rep start point)
Dim vLon As String = stops(0).lon.ToString(
CultureInfo.InvariantCulture)
Dim vLat As String = stops(0).lat.ToString(
CultureInfo.InvariantCulture)
Dim body As String =
"{""jobs"":[" & jobs.ToString() & "]," &
"""vehicles"":[{" &
"""id"":1," &
"""profile"":""driving-car""," &
$"""start"":[{vLon},{vLat}]" &
"}]}"
Dim content As New StringContent(
body, Encoding.UTF8, "application/json")
Dim httpResp As HttpResponseMessage =
Await client.PostAsync(ORSUrl, content)
Dim response As String =
Await httpResp.Content.ReadAsStringAsync()
' ORS returns the optimised order in routes[0].steps
Dim doc As JsonDocument = JsonDocument.Parse(response)
Dim re = doc.RootElement.GetProperty("routes") _
.EnumerateArray().GetEnumerator()
re.MoveNext()
Dim steps As JsonElement = re.Current.GetProperty("steps")
Dim optimizedOrder As New List(Of Integer)()
For Each step As JsonElement In steps.EnumerateArray()
If step.GetProperty("type").GetString() = "job" Then
optimizedOrder.Add(
step.GetProperty("job").GetInt32())
End If
Next
Return optimizedOrder
End Using
End Function
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.
Dim optimizedOrder As List(Of Integer) =
OptimizeRouteSync(coordinates)
Dim result As New StringBuilder()
result.Append("[")
For i As Integer = 0 To optimizedOrder.Count - 1
If i > 0 Then result.Append(",")
Dim parts As String() =
addresses(optimizedOrder(i)).Split(","c)
Dim street As String
Dim city As String
Dim postcode As String
Dim country As String
' 4 parts = with street, 3 parts = without street
If parts.Length = 4 Then
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()
End If
' Field names must match the column names of routetable
Dim obj As String =
$"{{""stop"":{i + 1}," &
$"""street"":""{street}""," &
$"""city"":""{city}""," &
$"""postcode"":""{postcode}""," &
$"""country"":""{country}""}}"
result.Append(obj)
Next
result.Append("]")
Return result.ToString()
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:
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.