While I was flying home from Directions, I built a small experiment in AL where I used graph theory to navigate tables in BC. Check out the madness in this video:
In this video, Erik takes us on a journey through an interesting plane project: implementing Dijkstra’s shortest-path algorithm in AL to navigate the relationships between Business Central tables. The goal is to automatically determine the path data must travel — for example, from a Customer table to a Customer Ledger Entry — so that field transfers in the Simple Object Designer can be mapped out programmatically rather than manually, one step at a time.
The Problem: Field Transfers Between Tables
In the Simple Object Designer, there’s a concept called a field transfer. When you enter a customer on a sales header, the customer’s address is copied to the address fields on the sales header. The field value “transfers” from one table to another. These transfers cascade through multiple tables — from sales headers to lines, from journal lines to ledger entries, and so on.
Erik spent considerable time mapping out the events that move data between tables and building a system around that. But a question arose: if a user creates a field on a Customer and wants that field to eventually appear on Customer Ledger Entries, what is the path? Which intermediate tables need the field, and what events facilitate the transfer?
Currently in the Simple Object Designer, you can figure this out one step at a time. But since we’re really dealing with a graph — tables are vertices, connections between tables are edges — and we have a starting point and an ending point, we’re squarely in the territory of shortest-path algorithms.
Visualizing the Graph with Mermaid.js
Erik uses Mermaid.js to visualize the table relationships as a graph. The code generates two sets of output: one describing the vertices (tables) and another describing the edges (connections between tables). Mermaid then renders the graph automatically.
The resulting diagram shows pathways like:
- Customer → Sales Header
- Sales Header → Item Journal Line
- Sales Header → Sales Invoice
- Ship-to Address → Sales Header → Item Journal Line → Item Ledger Entry
This visual representation makes it clear why a graph algorithm is the right tool for the job.
Why Dijkstra’s Algorithm?
Dijkstra’s algorithm is one of the oldest and most classic shortest-path algorithms. It’s guaranteed to give the correct answer, and given the relatively small amount of data involved (the number of BC tables and their connections), there’s no need for more complex or approximate algorithms that trade accuracy for reduced memory or compute requirements.
The concept is the same as Google Maps: you click on Copenhagen and want to go to Berlin, and the algorithm finds the shortest route through the graph of roads and connections.
Building the Matrix
The core data structure is a matrix represented as a nested dictionary:
// Dictionary<Integer, Dictionary<Integer, Integer>>
// Outer key: source table number
// Inner key: destination table number
// Inner value: distance/weight between the two tables
This is a dictionary of integer (table number) mapped to another dictionary containing all the connections from that table to other tables along with the distance. Erik notes that this nested dictionary approach is a neat use of AL’s dictionary and list types.
The matrix is built by iterating through the transfer connection table:
- For each connection record (source table → destination table), check if the source table already exists in the matrix. If not, add it with an empty edges dictionary.
- Do the same for the destination table.
- Retrieve the edges dictionary for the source table (whether it was just created or already existed).
- Check if a connection to the destination table already exists in the edges. If not, add it.
- Update the matrix entry for the source table and move on to the next record.
Implementing the Algorithm in AL
The FindRoute function ties everything together. It calls a ShortestDistance function (the Dijkstra implementation), passing in the start table, end table, the matrix, and a route (output parameter):
// Pseudocode structure of FindRoute
procedure FindRoute(StartTable: Integer; EndTable: Integer)
var
Matrix: Dictionary of [Integer, Dictionary of [Integer, Integer]];
Route: List of [Integer];
Distance: Integer;
begin
// Build the matrix from the transfer connection table
BuildMatrix(Matrix);
// Run Dijkstra's algorithm
Distance := ShortestDistance(StartTable, EndTable, Matrix, Route);
// Build output: for each table number in Route,
// find the table name and build the result string
foreach TableNo in Route do begin
// Look up table name and append to result
end;
Message('Distance is %1', Distance);
end;
Iterating Over Dictionary Keys
One challenge Erik highlights is that you can’t directly iterate over a dictionary’s entries in AL the way you might with a table. The solution is to extract the keys as a list:
// Get all keys from the matrix dictionary as a list
Keys := Matrix.Keys();
// Now we can iterate using a for loop
for J := 1 to Keys.Count() do begin
CurrentNode := Keys.Get(J);
// Process this node...
end;
This pattern — creating a list from dictionary keys to enable sequential iteration — is essential for implementing the algorithm’s nested loops.
The Core Dijkstra Logic
The algorithm follows the classic Dijkstra approach:
- Initialize distances: Set the distance to every node to
MaxInt(a number high enough that it will never be a real distance). - Set start distance to zero: The distance from the starting node to itself is zero.
- Iterate through all nodes: For each node, examine all of its connections. If going through the current node provides a shorter path to a connected node than previously known, update the shortest distance.
- Build the path: As shortest distances are discovered, track which node led to each shortest path. This builds up the route.
- Return results: Extract the distance to the end node and reconstruct the route by following the path records backward.
In Erik’s test, finding the route from Ship-to Address to Item Ledger Entry returned a distance of 3 (with each connection weighted as 1): Ship-to Address → Sales Header → Item Journal Line → Item Ledger Entry. Counting the edges: one, two, three — the algorithm works correctly.
Limitations and Next Steps
Erik notes an important caveat: the shortest route through the data model is not necessarily the correct path for data to travel. In practice, data may need to flow through specific intermediate tables for business logic reasons, even if a shorter path exists in the graph.
The real challenge is finding all possible paths between two tables, not just the shortest one. If a user says “I want this field on a Customer and I need it on the Customer Ledger Entry,” the system needs to identify every path the data could potentially travel in order to populate all the intermediate tables correctly.
Erik mentions that he has already started working on this more comprehensive path-finding approach, but that’s a topic for another video.
Conclusion
This project is a great example of applying classic computer science concepts to Business Central development. By modeling BC tables as a graph and implementing Dijkstra’s algorithm in AL, Erik created a tool that can automatically determine how data flows between tables — turning what was a manual, step-by-step process into an automated pathfinding operation. The implementation leverages AL’s dictionary and list data types effectively, and while the shortest path isn’t always the business-correct path, it’s a strong foundation for more sophisticated routing logic in the Simple Object Designer.
