Drag and Drop Question You create the following Vendor table and Item table in Business Central. Vendor: Item: You require the following data set to assign vendors to items. You need to create a…

Question

Exhibits

Answer Area

Explanation

Business Central AL Query: Assigning Vendors to Items

The Goal

We need a query that returns only items that have an assigned vendor - meaning only records with a matching "Vendor No." in both tables. This is a standard join operation.

Why These 5 Blocks (Not 3 - Likely a Typo)

Note: The question states "three code blocks" but the correct arrangement shows five. This appears to be an error in the question - a working query requires all five elements shown.

Block-by-Block Breakdown

Block 1: DataItem("Vendor"; Vendor) This declares the outer (parent) DataItem. In AL query syntax, the first DataItem is the "driving" table. Vendor is placed first because you're conceptually starting from the vendor side and linking items to it. The format is DataItem(DataItemName; TableName).

Block 2: DataItemLink = "Vendor No." = Vendor."Vendor No."; This belongs inside the Item DataItem (Block 3) and defines how the child links to the parent. AL DataItemLink syntax is always:

DataItemLink = <ChildTableField> = <ParentDataItemName>.<ParentTableField>

The left side ("Vendor No.") is the field from the Item table. The right side (Vendor."Vendor No.") references the parent DataItem name (Vendor) and its field. This is the most common mistake - people swap left and right, or reference the wrong DataItem name.

Block 3: DataItem(Item; Item) This is the inner (child) DataItem, nested inside the Vendor DataItem. It must come after Vendor because it depends on Vendor to define its link. Its properties (DataItemLink and SqlJoinType) are declared inside it.

Block 4: DataItemLink = "Vendor No." = Item."Vendor No."; This is the alternative DataItemLink used when the order is reversed (Item as parent, Vendor as child). The note says "more than one order is correct" - this is why. If you flip Blocks 1 and 3, you use this DataItemLink instead of Block 2, because now Item is the parent DataItem name.

Block 5: SqlJoinType = InnerJoin; This is a property of the inner DataItem that specifies how rows are matched:

• InnerJoin - only rows with a match in both tables (correct - only valid vendor-item assignments)
• LeftOuterJoin - all vendors, even those with no items
• RightOuterJoin - all items, even those with no vendor
• CrossJoin - every vendor × every item (cartesian product)

InnerJoin is correct because the requirement is specifically to show assigned relationships, not unmatched records.

The Two Valid Orderings (Why the Note Exists)

Both return identical results because InnerJoin is symmetric.

Common Mistakes

1. Wrong DataItemLink direction - The left side is always the child table's field; the right side always names the parent DataItem, not the table.
2. Using LeftOuterJoin - This would include vendors with no item assignments, which is not what "assign vendors to items" requires.
3. Placing SqlJoinType on the outer DataItem - It belongs on the inner DataItem only; the outer has no join type.
4. Mismatching DataItemLink with table order - If Vendor is outer, use = Vendor."Vendor No.". If Item is outer, use = Item."Vendor No.". Mixing these up is the #1 exam mistake.

No community discussion yet for this question.