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 / Finance | Project Operations, Human Resources, ... / Return DueDate in Ledg...
Finance | Project Operations, Human Resources, ...
Answered

Return DueDate in LedgerJournalTrans in a view computed column

(0) ShareShare
ReportReport
Posted on by Community Member

I have a need to return the Due Date from the Vendor line in LedgerJournalTrans in a ViewComputedColumn.

I have tried using both a Date computed column, and a DateTime computed column, but it always comes up as an empty field when I'm looking at the view.  I'm trying to return the actual value that comes from a method I already have that looks up the Vendor line based on the JournalNum of the invoice.

My view method looks like this:

private static server str GetSQLForDueDate()
    {
        LedgerJournalTrans vendorRecord;

        LedgerJournalId journalNum = SysComputedColumn::returnField(tableStr(BTCAPMainAccrualNonPOReportView), identifierStr(LedgerJournalTrans), fieldStr(LedgerJournalTrans, JournalNum));

       // Here is where I get the Vendor LedgerJournalTrans record based on the journalNum.  This method is proven to work fine, it is used elsewhere in my product.
        vendorRecord = BTCAPUtilities::GetVendorRecord(journalNum);

        return SysComputedColumn::returnLiteral(vendorRecord.Due);
    }

What am I missing?  Am I returning the value incorrectly?

  • Community Member Profile Picture
    Community Member on at
    RE: Return DueDate in LedgerJournalTrans in a view computed column

    Actually, now that I think of it, if I can have a separate LedgerJournalTrans datasource in the view, I think I can just achieve what I need in the view itself without having to use a ComputedColumn at all.

  • Community Member Profile Picture
    Community Member on at
    RE: Return DueDate in LedgerJournalTrans in a view computed column

    Yes, two distinct LedgerJournalTrans records.  The view is only returning those lines that DO NOT have an account type of Vend.  I need to get that one line that DOES so I can accurately retrieve the due date.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 231,758 Most Valuable Professional on at
    RE: Return DueDate in LedgerJournalTrans in a view computed column

    I see that "The "T.JournalNum" field from the "ReturnField" statement above" is also taken from LedgerJournalTrans. Does it mean that you want to compare two distincts LedgerJournalTrans record, or do you misunderstand your requirement?

    If you need an additional LedgerJournalTrans data source, you can obtain in a sub-query.

    The conditions can be implemented with SysComputedColumn::equalExpression(). For the enum, use SysComputedColumn::returnLiteral(LedgerJournalACType::Vend).

  • Community Member Profile Picture
    Community Member on at
    RE: Return DueDate in LedgerJournalTrans in a view computed column

    Darn it.  You're right.  I copied that part of it from another method that I see now was abandoned because it didn't work.  I didn't think there was a way to grab a value from the database in a view computed column method and use that in X .

    I know the "X SQL" way to get the value I need, but  I'm not quite sure how to construct in a computed column method.

    I basically need to do this:

    select firstonly ledgerJournalTrans
    where ledgerJournalTrans.JournalNum == (The "T.JournalNum" field from the "ReturnField" statement above")
       && ledgerJournalTrans.AccountType == LedgerJournalACType::Vend; (Whatever that value is in SQL?)


    I assume that the value of "T.JournalNum" will be based on the current row being retrieved in the view at the time the computed column method is being executed?

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 231,758 Most Valuable Professional on at
    RE: Return DueDate in LedgerJournalTrans in a view computed column

    You made a typical mistake, discussed many times in this forum. What your method should return is T-SQL code obtaining the value, which isn't what you're doing.

    This is what will happen: At database synchronization, your method will be executed. It will take a string like "T1.JournalNum" and it'll try to find a vendor for that. It'll likely found nothing, therefore your T-SQL code will be a mere literal with an empty date. That's what you'll get for all records in the view.

    By the way, please use Insert > Insert Code (in the rich-formatting view) to paste source code.

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,884 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,758 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans