Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Suggested answer

How to link DOCUVALUE table to related business metadata

Posted on by 5

I am trying to pull a report of all the documents referenced in AX, and I'm having a heck of a time figuring out the AX database structure. Ideally I want to pull a list of documents and the Journal / Batch # each is associated with.
In our AX environment, all documents are stored on a share (i.e. they're not actually stored as BLOBs in the AX database).

It looks like the DOCUVALUE table is the principal table that references the documents, having the ORIGINALFILENAME and other columns that seem to "point" to the files on the AX share. But DOCUVALUE doesn't contain any useful business metadata.

After a bit of exploring, it looks like like the DOCUREF table relates to DOCUVALUE (DOCUVALUE.RECID = DOCUREF.VALUERECID) which helps a little - gives you the Company #, but that's about it.

After a bit more exploring, it looked like it would be possible to join across to LEDGERJOURNALTABLE as shown below:

select ljt.journalnum, filename + '.' + filetype filename, ljt.name journal_name,
dr.refcompanyid, convert(varchar(10), ljt.posteddatetime,111) posted_date,
ljt.createdby, convert(numeric, ljt.journaltotalcredit) journalamount
from LEDGERJOURNALTABLE ljt, DOCUREF dr, DOCUVALUE dv
where dv.RECID = dr.VALUERECID and dr.refrecid = ljt.recid
order by 1,2

This looked promising, so I pulled out a data listing and asked one of our key business users to review the results. She indicated that it was accurate to some extent, but there were other areas where the document referenced just didn't have any relation to the JournalNum in the listing.

So - I'm at a bit of a dead end - I've spent further time generating SQL statements to harvest data using specific RECID values, trying other joins, but each time I just disappear down a rabbit hole.

Any ideas? Any help gratefully received!!

  • Suggested answer
    Simon Weekley Profile Picture
    Simon Weekley 5 on at
    RE: How to link DOCUVALUE table to related business metadata

    Thanks Nikolaos - that's great!!

    I couldn't find a function called tableNum, but I did some poking around and found the table id as follows:

    select NAME , TABLEID

     from SQLDICTIONARY

    where FIELDID = 0

    and name like 'LEDG%JOU%TAB%'

    Put that in to the WHERE clause (and dr.RefTableId = 211) - bingo!

    Looks like much better data!

    Thanks again

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: How to link DOCUVALUE table to related business metadata

    You need to limit the DocuRef results by RefTableId as well (dr.RefTableId == tableNum(LedgerJournalTable)). Now you're only using RefRecId as criteria but it returns documents that are related to ANY table where some record has the same RecId than your journal.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,459 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,783 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans