Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Noreen Fujita-Sacco]

Hello, all.

I’m working with Power BI and NAV 2018. I need to report on GL accounts and by item number or item related codes. 

Table 5823 G/L Register No. was recommended in an article. However, I don’t see the relationship and once the invoice is posted, the GL Entry number disappears from that table. 

The other option I can think of to create a link is to create a custom field that combines the document number (invoice number) and posting date to obtain the GL account number. 

Is there a table I haven’t found that provides a link or another option?

Hoping you are all healthy and stay well.

#PowerBI #Technical????

——————————
Noreen Fujita-Sacco
Applications Coordinator
Coghlan’s/Gear Aid
Bellingham WA
360-392-2703
noreen.fujitasacco@gearaid.com
——————————

[Steven Chinsky]

Noreen,

Good Day. If I am following you correctly you need to report on the GL Account associated with each Item, when you sell it. Is this correct? For example, I see a Bicycle and the General Product Posting Group is Finished Good and this points to GL Account 40001 – Sales. Is this what you need?

Please expand.
Thanks,
Steve

——————————
Steven Chinsky
Senior Functional NAV/BC Consultant
Tigunia
Mansfield MA
——————————
——————————————-

[Noreen Fujita-Sacco]

Hello, Steve.

Yes, I need to associate not only the sale amount, but the cost of goods as well. Then I’ll also want to report the credit memos in the same way.

——————————
Noreen Fujita-Sacco
Applications Coordinator
Coghlan’s/Gear Aid
Bellingham WA
360-392-2703
noreen.fujitasacco@gearaid.com
——————————
——————————————-

[Steven Chinsky]

Noreen,

Then it would be best to pull the data (Sale Amount, Unit Cost, and Document Type (Invoice, CM)) from the Value Entry table. There you can see these values and related Document Type. 

Thank you,
Steve

——————————
Steven Chinsky
Senior Functional NAV/BC Consultant
Tigunia
Mansfield MA
NAVUG All-Star, Granite Award Recipient, DCMP
——————————
——————————————-

[Kyle Hardin]

I’m not sure I understand what you are trying to accomplish, but it sounds like you want to know which cost of sale account each sales invoice item line went to.

Value Entry is your starting point. That has Posted Sales Invoice No. (stored in Document Type and Document No. fields). General Business Posting Group and General Product Posting Group are pointers into General Posting Setup where you can fetch the COGS G/L account number. You’ll want to filter value entries on Sale (maybe Source Code or Item Ledger Entry Type) to exclude purchase receipts and inventory adjustments.

However – if your goal is some type of inventory cost to G/L reconciliation, that is far more difficult since NAV costing is complicated. Adjust Cost will come along and do things (or even add) value entries.

——————————
Kyle Hardin
NAV Developer
ArcherPoint Inc.
Duluth GA
——————————
——————————————-

[Noreen Fujita-Sacco]

Thank you, Kyle. That give me more food for thought. I’ve worked from Value Entries for COGS in the past. However, I haven’t used the GBPG or GPPG to tie to the GL number. I’ll try that.

——————————
Noreen Fujita-Sacco
Applications Coordinator
Coghlan’s/Gear Aid
Bellingham WA
360-392-2703
noreen.fujitasacco@gearaid.com
——————————
——————————————-