Can not join Exact Online's Bill of Material Items and Items itself

Question

When I run the following query to retrieve item details on items in the Bill of Materials (BOM) of Exact Online, I get no item details:

select *
from   BillOfMaterialItemDetails bom 
join   ExactOnlineREST..items itm 
on     itm.ID = bom.item_id_attr

However, when I execute this, I do get item information:

select *
from   BillOfMaterialItemDetails bom 
join   ExactOnlineREST..items itm 
on     itm.code = bom.item_code_attr

Why is the join on the GUID in ID failing to find matches?

Answer 1

For some unknown reason, the Exact Online API have a different representation for a GUID in the REST and the XML API. You need to manually convert them yourself between '{GUID}' and 'GUID' as in:

select *
from   BillOfMaterialItemDetails bom 
join   ExactOnlineREST..items itm 
on     itm.ID = replace(replace(bom.item_ID_attr, '{', ''), '}', '')