Question Status

Verified
Dandino asked a question on 15 May 2018 4:20 PM

Good evening,

Does anyone know if there is an item and inventory table easily accessible, im trying to access this into Excel through SQL.

Thanks

Reply
Verified Answer
Stefano Demiliani responded on 16 May 2018 1:58 AM

The Item table is direclty accessible via SQL.

Regarding Inventory,  this is calculated applying a SUM on the Item Ledger Entry table (quantity field). There's no a table for that, you need to apply a query if you want to calculate that via SQL directly (at least filtering for Item No. and Location Code).

Check field 68 (Inventory) on Item table in NAV.

Reply
Dandino responded on 16 May 2018 4:00 AM

I thought this was the case, I did import it to excel and pivot it but then I cant use the data for what I want it to because of that.

I'm very new to SQL, so its all trial and error, writing code and query is beyond me

Reply
Verified Answer
Stefano Demiliani responded on 16 May 2018 4:02 AM

Load in Excel also the Item Ledger Entry table and from here you can have the Inventory when pivoting.

Reply
Verified Answer
Amol Salvi responded on 17 May 2018 9:36 PM

Yes it is ..

Reply
Verified Answer
Stefano Demiliani responded on 16 May 2018 1:58 AM

The Item table is direclty accessible via SQL.

Regarding Inventory,  this is calculated applying a SUM on the Item Ledger Entry table (quantity field). There's no a table for that, you need to apply a query if you want to calculate that via SQL directly (at least filtering for Item No. and Location Code).

Check field 68 (Inventory) on Item table in NAV.

Reply
Verified Answer
Stefano Demiliani responded on 16 May 2018 4:02 AM

Load in Excel also the Item Ledger Entry table and from here you can have the Inventory when pivoting.

Reply
Verified Answer
Amol Salvi responded on 17 May 2018 9:36 PM

Yes it is ..

Reply