web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Is there Relation tabl...
Small and medium business | Business Central, N...
Unanswered

Is there Relation table for the Document Type?

(0) ShareShare
ReportReport
Posted on by DWBIOC 5

I'm developing my own General Ledger Entries query for export purposes.  Document Type is a numeric value yet on the original standard report it displays as the corresponding text.  

IE .

1 = Payment, 2 = Invoice, ect.

Is there a relation table that I can join in the query which with display the text instead of the numeric value?

Also,

Would this be the same relation table for Vendor Ledger Entries or would there be a different one.

I could create a CASE statement to test the numeric value and assign the appropriate text but that's not a good practice and would hinder the flexibility of the export should a new type be used that was not in the original design of the query.

Kind Regards..  

....

I have the same question (0)
  • Gearoid Profile Picture
    Gearoid Microsoft Employee on at

    Hi,

    There is no relation table for the "Document Type" field. It is defined with the NAV/BC data type “Option”. The field is has a property called “OptionString” which contains the following for the G/L Entry table: ,Payment,Invoice,Credit Memo,Finance Charge Memo,Reminder,Refund

    That means 0=blank; 1=Payment, 2=Invoice… etc.

    Same deal for the Document Type field on the Vendor Ledger Entry table.

    See here for more details about the “Option” Data Type in BC/NAV: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/option/option-data-type

    Hope that helps!

  • DWBIOC Profile Picture
    DWBIOC 5 on at

    Thank you for your quick response.

    Is there a syntax (or function) to reference the Optionstring property for a query developed in an SSMS query window?  As a DBA that's my preferred working environment.   I've only had about an hour looking at and am not familiar with the Development Environment.  

    IF there is no SSMS equivalent could you I impose on you to provide the associated numeric values for the respective string?  Would they be the same OptionString value in the Vendor DocumentType be the same?

  • Gearoid Profile Picture
    Gearoid Microsoft Employee on at

    Unfortunately, you cannot see the OptionString property value for a field from SSMS because it is part of the NAV Application metadata. Therefore, you need to access these values via the NAV Application, e.g. via C/AL or AL code.

    The following are the OptionString property values for the Document Type field in the G/L Entry table (from a recent version):

    ,Payment,Invoice,Credit Memo,Finance Charge Memo,Reminder,Refund

    Therefore:

    0 = blank
    1 = Payment
    2 = Invoice
    3 = Credit Memo
    4 = Finance Charge Memo
    5 = Reminder
    6 = Refund

    The same values apply for the Document Type field in the Vendor Ledger Entry table.

    For further details you will need to install the NAV/BC Development Environment and look in the Object Designer for the relevant Table/fields and their associated properties.

  • DWBIOC Profile Picture
    DWBIOC 5 on at

    Thank you for your response.   I have been digging and discovered that myself.  Unfortunately to get past this issue I had to go the case statement route in my query using the codes you provided.  

    Thank you again Gerard.  Your input has been very helpful.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 1,687 Super User 2026 Season 1

#2
YUN ZHU Profile Picture

YUN ZHU 1,041 Super User 2026 Season 1

#3
Grigorios Mavrogeorgis Profile Picture

Grigorios Mavrogeorgis 974 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

Microsoft Training Manuals

Product updates

Dynamics 365 release plans