Code highlighting

Thursday, January 17, 2008

Creating and Posting Inventory ProfitLoss journals in DAX using .NET Business Connector

Yesterday, I was helping a friend from Canada create a small solution that would create and post ProfitLoss journals in Microsoft Dynamics AX 4.0 using .NET Business Connector.

In the end, we created 2 solutions - one was entirely written in X++, and only simple class static method calls were made from C#. The other was completely written in C#, using various classes, available with .NET business connector.

I uploaded the solution to the following link, in case anyone would be interested to download it and play around with it or use in their projects.
Download ProfitLossPostingAppl

Also, here is the source code - it is a small console application, and I tried to add as many comments as possible, so that even complete X++ beginners would be able to easily read the code.

Notice that in the solution, there is a reference to the business connector dll.
You can find the Microsoft.Dynamics.BusinessConnectorNet.dll in the Client\Bin folder of your Dynamics AX installation. This dll is what provides you with access to the Dynamics AX application and the set of classes to use AX tables and classes.

using System;
using Microsoft.Dynamics.BusinessConnectorNet;

namespace ProfitLossPostingAppl
{
class AxProfitLossPostingEngine
{
static void Main(string[] args)
{
Axapta ax = new Axapta();
// company name, language, object server, configuration
// this uses Windows Authentication
ax.Logon("cmul", null, "localhost", null);

try
{
// Start a transaction
ax.TTSBegin();

// AxaptaRecord is a class that allows to work with Tables in AX
AxaptaRecord header = ax.CreateAxaptaRecord("InventJournalTable");
AxaptaRecord journalName = ax.CreateAxaptaRecord("InventJournalName");
AxaptaRecord line = ax.CreateAxaptaRecord("InventJournalTrans");
AxaptaRecord inventTable = ax.CreateAxaptaRecord("InventTable");
AxaptaRecord warehouse = ax.CreateAxaptaRecord("InventDim");

// You can call static table methods using the following syntax
journalName = ax.CallStaticRecordMethod("InventJournalName", "find", "IPL") as AxaptaRecord;

// There is a set of predefined methods on the AxaptaRecord class, like the clear(), initValue, DML operations, etc.
header.Clear();
header.InitValue();
// You can call table object methods as well, not only static
header.Call("initFromInventJournalName", journalName);
header.Insert();

line.Clear();
line.InitValue();
line.Call("initFromInventJournalTable", header);
// You cannot use table fields directly as in X++. Instead you have set/get methods
line.set_Field("itemId", "B-R14");

// Instead of using static table methods (like find) you can execute a direct SQL statement and receive the result in the AxaptaRecord object
inventTable.ExecuteStmt("select * from %1 where %1.ItemId == 'B-R14'");
// If you receive more that one record you can iterate through them using Next (as in AX)
line.Call("initFromInventTable", inventTable);

line.set_Field("Qty", 160.0);

warehouse.Clear();
warehouse.set_Field("InventLocationId", "MW");

warehouse = ax.CallStaticRecordMethod("InventDim", "findOrCreate", warehouse) as AxaptaRecord;

line.set_Field("InventDimId", warehouse.get_Field("inventDimId"));
line.Insert();

// Notice AxaptaRecord is passed by reference here
ax.CallStaticRecordMethod("InventJournalTable", "initTotal", header);
header.Update();

ax.TTSCommit();

// You can call static class methods the same way you call table static methods, but using a different method on Axapta class
// So in case you wrote the posting in X++, you would be able to call it, passing the JournalId as the argument
// int numOfLinesPosted = (int)ax.CallStaticClassMethod("DEV_ProfitLossEngine", "postProfitLossJournal", header.get_Field("JournalId"));

// Or, you can use the AxaptaObject class to accomplish the same from C#
// You can initialize a new class using the Axapta class method
// AxaptaObject journalCheckPost = ax.CreateAxaptaObject("InventJournalCheckPost");

// Or using a static method, if that suites your needs better
// Notice here that an object of type AxaptaRecord is passed into a method that expects InventJournalTable as the argument
AxaptaObject journalCheckPost = ax.CallStaticClassMethod("InventJournalCheckPost", "newPostJournal", header) as AxaptaObject;
// You can object methods the same way you would on a table
journalCheckPost.Call("parmShowInfoResult", false);
journalCheckPost.Call("parmThrowCheckFailed", true);
journalCheckPost.Call("parmTransferErrors", false);

journalCheckPost.Call("run");
int numOfLinesPosted = (int)journalCheckPost.Call("numOfPostedLines");

Console.WriteLine(String.Format("{0} line(s) have been successfully posted", numOfLinesPosted));
Console.WriteLine("JournalId is " + header.get_Field("JournalId"));
Console.WriteLine("Press any key to continue ...");

ax.Logoff();
}
catch (Exception ex)
{
ax.TTSAbort();
ax.Logoff();

Console.WriteLine(ex.Message);
}

Console.ReadKey();
}
}
}



P.S. Of course, it would probably be a better idea to use classed InventJournalTableData, InventJournalTransData, etc.
But for the simplisity of the example, everything is done directly with tables.

30 comments:

  1. Hi Vanya,

    Just wanted to say Happy Birthday because I believe that it;s your birthday today:)!

    I tried to send the same using LinkIn, I hope one of them will know how to make its way to you.

    Thanks for all info that you are posting about Dynamics,

    Mihaela

    ReplyDelete
  2. Vanya,

    First of all, thanks for posting this, it is a big help for people learning this stuff. Question; whenI try to run part of the code I get the exception about can't update because the table has not selected forupdate. Any idea what I am doing wrong? Any help would be appreciated.

    Thanks,

    Mark

    ReplyDelete
  3. Hi, Mark.

    Obviously, you are trying to update a record in Dynamics AX. But in order to do that, you need to "select it for update" (lock from other users, if optimistic concurrency is not used).

    This is done by providing a "forupdate" keyword in the select statement.

    select forupdate * from %1 where %1.Field == value

    If you are using a find method to get the record, the method usually has a 2nd boolean parameter, specifying if the record should be selected for update.

    Also, an update need to take place inside a transaction, so call ttsbegin and ttscommit before and after the code, respectively.

    Hope this helps. Also, make sure to write contact details when you ask something. Otherwise, I cannot reach you with an answer

    ReplyDelete
  4. Vanya, thanks for the help. It worked. I added true after the variable using the find method.

    Did you get the contact information this time?

    Mark

    ReplyDelete
  5. Not really, no.
    I see your profile, but you don't have any contact info there.

    Anyway, it's not that important.

    The only problem is that I am not sure if the person will actually read the reply when I answer :)

    ReplyDelete
  6. Ivan :

    how can we use joins with AxaptaRecord .

    ReplyDelete
  7. Sorry, did not see your question until today. Anyway, I see you got your answer on the Community Page by now.
    So I will just post it here as well, just in case anyone else has the question:


    Microsoft.Dynamics.BusinessConnectorNet.Axapta AXConn = new Axapta();
    AXConn.Logon("", "", "", "");

    AxaptaRecord CustTable = AXConn.CreateAxaptaRecord("CustTable");
    AxaptaRecord CustTrans = AXConn.CreateAxaptaRecord("CustTrans");
    AxaptaObject Query = AXConn.CreateAxaptaObject("Query", "CustTable");
    AxaptaObject queryRun = AXConn.CreateAxaptaObject("QueryRun", Query);

    while ((bool)queryRun.Call("next"))
    {
    CustTable = (AxaptaRecord)queryRun.Call("get",
    CustTable.get_Field("tableid"));
    CustTrans = (AxaptaRecord)queryRun.Call("get",
    CustTrans.get_Field("tableid"));
    Console.WriteLine(CustTable.get_Field("AccountNum"));
    Console.WriteLine(CustTrans.get_Field("AmountMST"));
    }

    ReplyDelete
  8. Hi Vanya,

    Thanks for the great example, it is really helping me out (I'm just starting to use the business connector).

    However, at the end you wrote about using InventJournalTableData instead of creating records directly. can you give me a simple example for that as well? Porblem for me is that I need to pass a JournalNameMap as parameter for the method "InitFromJournalName", but I do not understand how that should be created.

    thanks,

    remco

    ReplyDelete
  9. Hi Ivan, I'm begin to work with AX-2009. My question is, how is the performance of Document print manager when you have many criterias, I say 30 about, may be more.

    ReplyDelete
  10. Hi Kashperuk Ivan,

    Thanks for sharing knowledge. Your Blog helps to address right issue and leads to correct solution. I need some guidence on showing inventory onhand based on default warehouse for customer on EP. I have created method on custtable to use inventsum class - findsum method. Hope this is right approach, is any other option? It works fine from AX client but when try to call from EP it gives error "Error executing code: xRecord object does not have method 'getQtyOnHand'."

    Can you throw some light on it?

    Thanks

    ReplyDelete
  11. Well, the error message you are getting is because you are obviously trying to call an object method on a record buffer, but this particular buffer does not contain a method like that.

    ax.CallTableStaticMethod("InventSum", "findSum", ....)
    should do the right trick. Just use get_field methods to return the values from it after this.
    Also, check out the latest post I had - maybe you will find some more ideas there.

    If you are still having problems after that, feel free to e-mail me your code (or post it here), and we'll try to figure out the problem with it

    ReplyDelete
  12. Thanks Ivan.
    Let me know on which email i can send code.

    ReplyDelete
  13. ivan .dot. kashperuk .@. gmail .dot. com

    (replace .dot. and .@. respectively)

    ReplyDelete
  14. I am trying something like this but with project expenses. I had a lot of trouble getting it to work and now it seems to be working but is very slow. here is my code, any ideas?

    ReplyDelete
  15. Hi, Adam. Well, 2 things I noticed (not sure that will help much though):

    56. journalTableData = JournalTableData::newTable(ledgerJournalTable);
    57.
    58. ledgerJournalStatic = journalTableData.journalStatic();

    The above code can be moved out of the while loop, I think.


    90. projTrans.doinsert();
    91. projTrans.write();

    Remove the last line. It does an update() right after you did an insert(). Unless, of course, that's what you wanted to do...
    Also, the transaction scope is a little strange there..

    ReplyDelete
  16. Thanks for the great info this has been very helpful

    ReplyDelete
  17. Hi! Ivan!
    I want to ask: Can I execute query on the two or more tables with AxaptaRecordWrapper?
    Something like:
    {"SELECT DISTINCT TRANSACTIONLOG.CREATEDDATETIME, INVENTTRANS.QTY, INVENTTRANS.CUSTVENDAC " +
    " FROM INVENTTRANS, TRANSACTIONLOG WHERE INVENTTRANS.ITEMID == '" + _ItemID + "' && INVENTTRANS.INVENTDIMID IN ('" + InventDimensions + "') " +
    " && ((TRANSACTIONLOG.TXT Like '%'+INVENTTRANS.PACKINGSLIPID ....}

    ReplyDelete
  18. Hello Victor,

    Yes, you can execute a query on more than one table, but not with AxaptaRecord.
    There is a method for this on the Axapta class.
    It's name is executeStatement() or something like that, and you pass in record buffers in there as arguments.

    ReplyDelete
  19. Hello, I am newbie in x++ programming, I've created new table in AX called ManData and imported data into it. Now I need to find records in this table depending on bookingNum i.e. mandata = Mandata::Find(bookingnum). I tried to create find method with no luck:
    static ManifestData find(BookingNum bookingNum, boolean update = false
    )
    {
    ManifestData manifestData;
    ;

    manifestdata.selectForUpdate(false);

    if (bookingnum)
    {
    select firstonly manifestData
    index hint BookingNumIdx
    where manifesdata.bookingnum == bookingnum;
    }

    return manifestdata;
    }
    I've got error on first line "syntax error". What I am doing wrong?

    ReplyDelete
  20. Hi Anonymous.

    Well, a couple of questions that can lead you in the right direction.

    1. Is the table called ManifestData or ManData?
    2. What column is the syntax error on?
    3. Is there an ExtendedDataType called BookingNum?
    4. Is it possible that you misspelled some word? For example, if you have a character in your (non-english) language, that resembles a "c", but in reality has a different letter code.

    ReplyDelete
  21. Thank you for your reply Vanya, there was an error on first line because I didn't have ExtendedDataType for bookingnum, just changed first line to:
    static ManifestData find(int bookingNum, boolean update = false). And it started working.
    Many thanks for the tips...

    ReplyDelete
  22. Hi Vanya,

    I'm really new on Dynamics AX, I'm working on a project that gets invoices on a system and insert it on AX.

    What I do is: 1. Create the order, 2. Create invoice.

    I'm using .net business connector, but is there any steps first this, then this, and later this, to do this? I'm lost, and documentation on AX sucks.

    Really appreciate if you can help me.

    Estuardo

    ReplyDelete
  23. Well, I am sorry to say it's not really a fixed process, so there are too many things to consider in order to write this kind of step-by-step instructions. But your case is probably very specific, thus, can easily be broken down and automated step by step. Just sit down with your consultant and figure out what needs to happen

    ReplyDelete
  24. Hello Vanya kashperuk,

    Your AX tutorials have helped me a lot. I have got lots of solutions posted by you on websites. I am really thankful to you.

    Currently Iam working on AX 2012 and facing issues on Workflows. I have checked in many forums, i did not find any solution.

    I request you to provide me your email ID, so that I can email you the details.

    My email ID is amith.kamath2006@gmail.com

    Please help!

    Regards,
    Amith

    ReplyDelete
  25. Hello,

    In your code to post the journal, where are you passing in the Journal ID? How will axapta know which journal to post?

    ReplyDelete
  26. Hi Jaffer,

    The class used for posting inventory journals, InventJournalCheckPost, has a couple static constructors, that take different arguments for easier initialization. I've used the below constructor, which takes the InventJournalTable record as a parameter. This record has the JournalID on it.

    AxaptaObject journalCheckPost = ax.CallStaticClassMethod("InventJournalCheckPost", "newPostJournal", header) as AxaptaObject;

    ReplyDelete
  27. Hello Vanya,

    Excellent tutorial. It is great to see some good examples using .NET business connector.

    I'm currently using business connector to do some importing of data into Dynamics Ax for some simple routines. I'm trying to do something more difficult and that is to use the business connector to mark open transactions in the CustTransOpen table. Do you have any idea how I could do this? I'm thinking I should use the CustVendOpenTransManager class and updateTransMarked method but don't know how to call it from the business connector. Any help you can provide would be much appreciated. My email is: Jasonn38@hotmail.com.

    Thanks,

    Jason

    ReplyDelete
  28. Are you having trouble using AxaptaObject for calling the above-mentioned class, Jason?

    ReplyDelete
  29. yes, I'm still having troubles trying to invoke the CustVend class method. What I'm trying to do is mark payments from the Open Transaction Editing (Accounts receiveable -> Customer Details -> Functions -> open transaction editing ) form through the business connector by passing in a recid of the record to mark. I'd like to call the same logic (insert records into spectrans, etc.) as if you were doing it from the form through the business connector.

    I'm not an Ax developer so I'm having hard time finding out how to call the right classes/methods to be able to do this.

    Any help you could provide would be much appreciated.

    Regards,

    Jason

    ReplyDelete
  30. Hi thanks for sharing codes

    ReplyDelete

Please don't forget to leave your contact details if you expect a reply from me. Thank you