Is there Relation table for the Document Type?

(0) Share

Report

Posted on by DWBIOC

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)

All responses (4)

Answers (0)

Gearoid on at

Like (0)

Report

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!

Was this reply helpful? Yes No
DWBIOC 5 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Gearoid on at

Like (0)

Report

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.

Was this reply helpful? Yes No
DWBIOC 5 on at

Like (0)

Report

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.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Is there Relation table for the Document Type?

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

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

Featured topics

Product updates

Community site session details

Is there Relation table for the Document Type?

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

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

Featured topics

Product updates