Skip to main content

Notifications

Announcements

No record found.

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..  

....

  • DWBIOC Profile Picture
    DWBIOC 5 on at
    RE: Is there Relation table for the Document Type?

    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.

  • Gearoid Profile Picture
    Gearoid on at
    RE: Is there Relation table for the Document Type?

    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
    RE: Is there Relation table for the Document Type?

    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 on at
    RE: Is there Relation table for the Document Type?

    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!

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

Quick Links

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Adis Hodzic – Community Spotlight

We are honored to recognize Adis Hodzic as our May 2025 Community…

Kudos to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

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

#1
YUN ZHU Profile Picture

YUN ZHU 616 Super User 2025 Season 1

#2
Mansi Soni Profile Picture

Mansi Soni 487

#3
Sagar Dangar, MCP Profile Picture

Sagar Dangar, MCP 391

Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans