Posted in AX 365

D365 bits and Pieces for daily use

Example to Get args() in Chain of command method

public void modifyWHSInventReserveAvailRange(boolean _useQueryRunQueryIfExists)
{
   next modifyWHSInventReserveAvailRange(_useQueryRunQueryIfExists); 
   Args args = this.args();

   if( args.record()
      && ( args.record().TableId == tableNum(SalesLine))           
   {
     //Logic here
    }
}

Example to get data source objects in Form Control event handler methods

 [FormControlEventHandler(formControlStr(WHSInventOnHandReserve, DXC_RemoveZeroQty), FormControlEventType::Clicked)]
public static void DXC_RemoveZeroQty_OnClicked(FormControl sender, FormControlEventArgs e)
{
   // Accessing DataSource
  sender.formRun().dataSource(formDataSourceStr(WHSInventOnHandReserve, WHSInventReserve)).research(true);
}

Example to get Form control in Form control event handler

 [Hookable(false), FormEventHandler(formStr(CustCollectionsPoolsListPage), FormEventType::Initialized)]
public static void CredManCustCollectionsPoolsListPage_OnInitialized(xFormRun _sender, FormEventArgs _e)
{
    if (CredManFeatureManagementVisibilityManager::isEnabled())
    {
      FormRun custCollectionsListPage = _sender as FormRun;

      FormButtonControl credManLinkedCustomerGroupButtonControl = custCollectionsListPage.design().controlName(formControlStr(CustCollectionsPoolsListPage, CredManLinkedCustomerGroup)) as FormButtonControl;
      if (credManLinkedCustomerGroupButtonControl)
      {
        credManLinkedCustomerGroupButtonControl.visible(true);
      }

      FormStringControl credManStringControl = custCollectionsListPage.design().controlName(formControlStr(CustCollectionsPoolsListPage, CredManCustTableCreditLimitTableView_CreditLimitId)) as FormStringControl;
       if (credManStringControl)
       {
          credManStringControl.visible(true);
        }

    FormRealControl credManBalanceRealControl = custCollectionsListPage.design().controlName(formControlStr(CustCollectionsPoolsListPage, CustTable_EffectiveCreditMax)) as FormRealControl;
         if (credManBalanceRealControl)
         {
            credManBalanceRealControl.visible(true);
         }
     }
}

Example to get args and form run objects in Post event handler methods

[PostHandlerFor(formStr(WHSInventOnHandReserve), formMethodStr(WHSInventOnHandReserve, init))]
public static void WHSInventOnHandReserve_Post_init(XppPrePostArgs args)
    {
        FormRun sender = Args.getThis();
        FormCheckBoxControl myCheckBox;

        if ((sender.args().record() && sender.args().record().TableId == tableNum(SalesLine))
            && SalesParameters::find().DXC_ShowDimReserrvation)
        {
            sender.control(sender.controlId(formControlStr(WHSInventOnHandReserve, DXC_RemoveZeroQty ))).Visible (true);
            myCheckBox =  sender.control(sender.controlId(formControlStr(WHSInventOnHandReserve, DXC_RemoveZeroQty )));
            myCheckBox.value(NoYes::Yes);
        }
    }

Posted in AX 365

Download file from SharePoint and Upload it in AX

Below is the code from my Recent requirement to get file from SharePoint folder and upload it as attachment in Documents,

To achieve this I’ve opened two new fields on CustParameters for Document type ID field and SharePoint URL, “location should be without HTTP or HTTPS in parameters

SharePoint location sample to defined on parameter field : Server.sharepoint.com/FolderLocation

Need to open/create a new file type for document and define complete SharePoint location so the attached file can be accessible, On normal file type attachment will error out, file type should be of type SharePoint

Note : In my example I’m looking for files on SharePoint location with folders defined as item numbers and file name matching Batch numbers.

  public static void downloadAndUploadFile(ItemId    _itemid, InventBatchId  _inventBatchId, salesId  _salesId, RecId _CustInvoiceJour)
    {

        Microsoft.Dynamics.AX.Framework.FileManagement.IDocumentStorageProvider storageProvider;
        Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider provider;
        Microsoft.Dynamics.AX.Framework.FileManagement.DocumentLocation documentLocation = new Microsoft.Dynamics.AX.Framework.FileManagement.DocumentLocation();
        Microsoft.Dynamics.AX.Framework.FileManagement.DocumentContents documentContents;
        str fileName;

        str             fileContetType;
        DocuRef         docuref;
        DocuTypeId      docuTypeId = CustParameters::find().SharepointDocuTypeId;
        DocuType        fileType = DocuType::find(docuTypeId);

        this.validateParameters();

        storageProvider = Docu::GetStorageProvider(fileType, true);

        storageProvider.ProviderId = DocuStorageProviderType::SharePoint;

        try
        {
            FileContents file;
            [file, filename]=   this.getFileStream(_inventBatchId,_itemid);

            if(!file)
            {
                info(strFmt('@Label:MissingFile', _itemid,_inventBatchId));
                return;
            }

            if (!this.checkDocuRef(SalesTable::find(_salesId), fileName))
                return;

            System.IO.Stream fileStream = file.Content;

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            fileStream.CopyTo(ms);
        
            fileContetType = System.Web.MimeMapping::GetMimeMapping(filename);
        
            if(ms && storageProvider)
            {
                docuref = DocumentManagement::attachFile(tableNum(SalesTable), SalesTable::find(_salesId).recid,curExt(), docuTypeId,ms,filename,fileContetType,filename);
                
                if(docuref && docuref.Restriction != DocuRestriction::External)
                {
                    ttsbegin;
                    docuref.selectForUpdate(true);
                    docuref.Restriction = DocuRestriction::External   ;
                    docuref.update();
                    ttscommit;
                }
            }
        }
        catch
        {
            throw Error("@ENG509");
        }
    }
  // Sample code for PDF and DOC file type
  public container getFileStream(InventBatchId  _inventBatchId, ItemId    _itemid)
    {
        #file
        #define.docx(".docx")
        #define.https("https://")
        #define.decodeUrl("/_api/Web/GetFileByServerRelativePath(decodedurl='/")

        ISharePointProxy proxy;
        System.Uri uri;
        System.Uri NavigateUri;
        FileResults fileResults;
        FileContents file;


        str host,site,folderPath ;
        str fileName    =  _inventBatchId + #pdf;

        [host, site, folderPath] = this.getConnectionDetails();
        folderPath               = folderPath +'/'+ _itemid;


        uri = new System.Uri(#https + host  + #decodeUrl + site + "/" + folderPath + "/" + fileName + "')");
        NavigateUri = new System.Uri(#https + host + "/" + site + "/" + folderPath + "/" + fileName);

        proxy = (proxy && proxy.Config.TargetHost == host && proxy.Config.Site == site) ?
                proxy :
                SharePointHelper::createProxy(host, site, xUserInfo::getExternalId());

        fileResults = SharePointHelper::GetFiles(proxy, FolderPath, '');
        file = SharePointHelper::GetFileContents(proxy, uri);

        if (!File)
        {
            fileName    =  _inventBatchId + #docx;
            uri = new System.Uri(#https + host  + #decodeUrl + site + "/" + folderPath + "/" + fileName + "')");
            NavigateUri = new System.Uri(#https + host + "/" + site + "/" + folderPath + "/" + fileName);

            proxy = (proxy && proxy.Config.TargetHost == host && proxy.Config.Site == site) ?
                proxy :
                SharePointHelper::createProxy(host, site, xUserInfo::getExternalId());

            fileResults = SharePointHelper::GetFiles(proxy, FolderPath, '');
            file = SharePointHelper::GetFileContents(proxy, uri);
        }

        return [file, fileName];
    }
  public container getConnectionDetails()
    {
        str connectionString, host, site, folders;

        connectionString = CustParameters::find().SharepointInvoicePath;

        host = subStr(connectionString,1,strfind(connectionString,'/', 1,  strlen(connectionString)));

        connectionString = strDel(ConnectionString,1,strLen(host));
        site = subStr(connectionString,1,strfind(connectionString,'/', 1,  strlen(connectionString)));

        connectionString  = strDel(ConnectionString,1,strlen(site));

        host = strDel(host, strLen(host),strLen(host)-1);
        site = strDel(site, strLen(site),strLen(site)-1);


        folders = connectionString;

        return [host, site, folders];
    }
 public static boolean checkDocuRef(SalesTable  _salesTable, str  _filename)
    {
        DocuRef docuref;

        select RecId from docuref
            where docuref.RefTableId    == tableNum(SalesTable)
                && docuref.RefRecId     == _salesTable.RecId
                && docuref.RefCompanyId == curExt()
                && docuref.Name         == _filename;

        if(docuref.RecId)
            return false;

        return true;

    }
  public void validateParameters()
    {
        if (!CustParameters::find().SharepointInvoicePath)
            throw Error ("@Label:SharepointPathMissing");
    }
 public InventBatchId getBatchId(InventTransId _inventTransId)
    {
        InventTrans         inventTrans;
        InventTransOrigin   inventTransOrigin;


        select firstonly InventDimId from inventTrans
            join inventTransOrigin
            where inventTransOrigin.recid           == inventTrans.inventTransOrigin
                && inventTransOrigin.InventTransId  == _inventTransId;

        return inventTrans.inventDim().inventBatchId;
    }
Posted in ax 2012

AX 2012, SSRS error

I got an Error while running the report in Production, The error is not specific to any particular report and there can be multiple possibilities for this error message

The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameterhttp://tempuri.org/:queryBuilderArgs. The InnerException message was ‘Element ‘http://tempuri.org/:queryBuilderArgs’ contains data from a type that maps to the name ‘http://schemas.datacontract.org/2004/07/XppClasses:SRSQueryBuilderArgs’. The deserializer has no knowledge of any type that maps to this name. Consider using a DataContractResolver or add the type corresponding to ‘SRSQueryBuilderArgs’ to the list of known types – for example, by using the KnownTypeAttribute attribute or by adding it to the list of known types passed to DataContractSerializer.’. Please see InnerException for more details


Possible Root causes:

  • SSRS service restarted without ending AOS client session
  • Power Failure on Sever
  • Zero available space/Memory on SSRS server.

Solutions:

Possible Solutions (1):
  • Turn off all AOS.
  • Restart SSRS service.
  • Start all AOS again.
Possible Solutions (2):
  • End client’s session,  AX> System Admin > Users> Online user> End session.
  • Stop AOS.
  • Open SQL, Select the record from SysClientSessions table [ take the backup of the records just to be on a safe side].
  • Delete all the records from SysClientSessions table.
  • Restart SSRS services
  • Start AOS
Possible Solution (3):
  • Deactivate and activate BIServices port from System administration > Setup > Services and Application Integration Framework > Inbound ports.


Posted in ax 2012

Table MAP object Becomes TABLE in AOT

Today i faced a strange issue which still is a mystery for me. Every thing was working fine and suddenly after restarting SQL server we start facing compile issues of MAP not found. I realized that MAP “CustVendTable” converts into a Table in AOT (NO Hotfix or recent update on the environment)

After spending hours to find the cause i finally able to fix it. Two main tables in model store database to look for this issue.

• Model element
• Model element data


You can get ElementHandle ID from Model element Table by filtering it with TABLEID and NAME. Filter Model element Data table to get the Suspected record.

Compare Properties Field data with old backup or other environment where its working fine and Update the Binary value from backup database in the table. Once update Successfully, Clear AUC files and XPPIL folder. Restart AOS server and you BOOM issue resolved.

Other useful Link for this Issue.

Cheers.

Posted in ax 2012

Get specific EP page URL

Recently I got a requirement to get the EP page URL which we can use in email or Power BI reports to call directly the specific page.

Below is an example of Sales order info page , Copy the code in your class or method and pass the Sales ID into to get the URL. You can use this code to get any specific EP page by changing the web control name.

Note: Run this method always on Server Side

    WebLink webLink;
    str     url;

    WorkflowEPUrlContext        urlContext;
    SalesTable salesTable = salesTable::find(_salesId);

    urlContext = WorkflowEPUrlContext::contruct();
    webLink = WebLink::construct(false,urlContext);
    urlContext.getHashComputer();
    if (urlContext.getInternalSiteURL())
    {
        webLink.menufunction(WebMenuFunction::createWebMenuFunction(webUrlItemStr(EPSalesTableInfo), WebMenuItemType::Url));
        webLink.tableid(salesTable.TableId);
        webLink.recordKey(record2DynaKey(salesTable));
        url = webLink.url(false);
    }

    return url;
Posted in AX 365

Read File’s from SFTP Server and Write data in AX365 (PART 2)

In Part 2 we are going to use the Dll’s to make an SFTP connection, read files from the folder and Write data in AX.

First We need to add the Dll’s in our New AX project and make sure you are adding it from your extension Bin folder.

My requirement was to import the files in batch but I’m sharing only the main logic here to keep the post short.

  public void readingFTPDirFiles()
    {
// I added my SFTP connection fileds in Cust parameters table 
        CustParameters                      parameter   =  CustParameters::find();        

        str                                 host     = parameter.Host;
        str                                 username = parameter.User;
        str                                 password = parameter.password;
        int                                 port     = parameter.Port;
        System.IO.Stream                    ioStream;
        System.IO.StreamReader              ioStreamReader;
        System.String                       strReadLine;
        container                           conFTPFilesDownload;
        ListIterator                        ftpFilesListIterator;
        str                                 sftpFile;
        System.IO.StreamReader              reader;
        System.String                       line;
        System.ObjectDisposedException                  error_ObjectDisposedException;
        System.InvalidOperationException                error_InvalidOperationException;
        System.Net.Sockets.SocketException              error_SocketException;
        Renci.SshNet.Common.SshConnectionException      error_SshConnectionException;
        Renci.SshNet.Common.SshAuthenticationException  error_SshAuthenticationException;
        Renci.SshNet.Common.ProxyException              error_ProxyException;
        System.ArgumentNullException                    error_ArgumentNullException;
        Renci.SshNet.Common.SftpPathNotFoundException   error_SftpPathNotFoundException;

        ClrObject                           list = new ClrObject("System.Collections.Generic.List`1[System.String]");

        str remoteDirectory                 = parameter.ImportPath;
        str archiveDirectory                = parameter.ArchivePath;
        str errorDirectory                  = parameter.ErrorPath;
        MySshNet.sftpConnection           sftp = new MySshNet.sftpConnection();
        
// Making an attempt to connect the SFTP server
        using (var sftpConnection = sftp.OpenSFTPConnection( host,port,username,  password))
        {
            try
            {
                int totalFiles = 0;
                sftpConnection.ChangeDirectory(remoteDirectory);
// Here we will get all the Files present in our folder in a List
                list  = (sftp.GetDirectories(sftpConnection,   remoteDirectory));
                ClrObject enumerator = list.getEnumerator();
                
                while (enumerator.movenext())
                {
                    totalFiles ++;
                    sftpFile = enumerator.get_Current();

                    if(sftpFile != ".." && sftpFile !=null && sftpFile != ".")
                    {
                        try
                        {
/* Download File method will provide the Stream object which we can use to read the content of the file.
 In ReadCSVFileAndCreateJournal method I'm validating my data and creating records in AX. 
If creation is Successful then Returning True otherwise false.
Return true will move the file to Archive folder and false will move the file to Error folder. 
These folder locations were defined in the parameters Mentioned in part 1 */
                           if(this. ReadCSVFileAndCreateJournal(sftp.DownloadFile( sftpConnection, remoteDirectory + '/'+sftpFile),sftpFile))
                            {
                                sftp.MoveFile(sftpConnection, remoteDirectory  + '/'+sftpFile,   archiveDirectory  + '/'+ this.getReturnFileName(sftpFile),false);
                            }
                            else
                            {
                                sftp.MoveFile(sftpConnection, remoteDirectory + '/'+sftpFile,   errorDirectory + '/' + this.getReturnFileName(sftpFile),false);
                            }
                        }
                        catch (error_ArgumentNullException)
                        {
                            throw error("path is null.");
                        }
                        catch (error_SshConnectionException)
                        {
                            throw error("Client is not connected.");
                        }
                        catch (error_SftpPathNotFoundException)
                        {
                            throw error("The specified path is invalid, or its directory was not found on the remote host.");
                        }
                        catch (error_ObjectDisposedException)
                        {
                            throw error("The method was called after the client was disposed.");
                        }
                    }
                }
            }
            catch (error_ObjectDisposedException)
            {
                throw error("The method was called after the client was disposed.. ");
            }
            catch (error_InvalidOperationException)
            {
                throw error("The client is already connected.");
            }
            catch (error_SocketException)
            {
                throw error("Socket connection to the SSH server or proxy server could not be established or an error occurred while resolving the hostname.");
            }
            catch (error_SshConnectionException)
            {
                throw error("SSH session could not be established.");
            }
            catch (error_SshAuthenticationException)
            {
                throw error("Authentication of SSH session failed.");
            }
            catch (error_ProxyException)
            {
                throw error("Failed to establish proxy connection.");
            }
            finally
            {
                if(sftpConnection.IsConnected)
                    sftpConnection.Disconnect();
            }
        }             
             
    }

Bonus Tips:

“MoveFolder” method will not move the file. if the file with same name Already Exist in the destination folder. Try to use Date Time Stamp in your destination file. Pasting the method below.

public str TimestampString()
    {
        int time;
        str s;

        time    = timenow();
        s       = date2Str(today(),
                            321,
                            DateDay::Digits2,
                            DateSeparator::None, // no separator
                            DateMonth::Digits2,
                            DateSeparator::None, // no separator
                            DateYear::Digits4);

        return  s + "_" +
                num2str0(time div 3600       ,2,0,0,0) + "_" +
                num2Str0(time mod 3600 div 60,2,0,0,0) + "_" +
                num2Str0(time mod 3600 mod 60,2,0,0,0);
    }

Now the Issue will be That you need to Split the file name as the extension is coming before the time Stamp. For example MyFile.csv1882020_10_29_30

Pasting the code below to get the correct file name

 public str getReturnFileName(str filePath)
    {
        str         actualFileName, fileExtension, actualFilePath;

        [actualFilePath,actualFileName, fileExtension] = fileNameSplit(filePath);

        return strfmt('%1_%2%3',actualFileName,this.TimestampString(),fileExtension);
    }
 public boolean ValidateLine(container   record, int lineNum, str fileName)
    {
        boolean ok = true;
        //To check if there is empty Line in between the records
        if(conPeek(record,1) == null)
            ok  = false;

        if(conPeek(record,2) == null)
            ok  =  false;

        if(conPeek(record,3) == null)
            ok  =  false;

        if(str2Date(conPeek(record,4),123) == dateNull())
            ok  =  false;

        if (!ok)
            Error(strFmt("@ECL:ErrorInRecord",LineNum,Filename));

        if( conPeek(record,1) != curExt())
        {
            ok =  false;
            error(strFmt("@ECL:WrongCompany",conPeek(record,1),fileName));
        }

        if ( !this.isNumeric(conPeek(record,3)))
        {
            ok =  false;
            error(strFmt("@ECL:WrongAmount",conPeek(record,3),LineNum,fileName));
        }

        return ok;
    }

Here is the Logic to read from CSV and creating journal. I’ve pasted the logic as i implemented according to the clients requirement you can modify accordingly.

  public  boolean ReadCSVFileAndCreateJournal(System.IO.Stream  stream, str fileName)
    {
        ledgerJournalCheckPost          JournalCheckPost;
        ledgerJournalName               JournalName ;
        ledgerJournalTrans              JournalTrans;
        ledgerJournalTable              JournalTable;
        boolean                         journalCreated = true;

        Common                          common;
        NumberSeq                       numberseq;
        CustTrans                       custTrans;
        InvoiceAccountsCustTable        invoiceAccount; //CustomTable

        CustParameters      custParameters = custParameters::find();

        try
        {
            Array                                           fileLines;
            FileUploadTemporaryStorageResult                fileUpload;
            AsciiStreamIo                                   file;
            container                                       record;
            LedgerJournalEngine                             ledgerJournalEngine;
            UnknownNoYes                                    triangulationResult;
            ExchRateSecondTxt                               exchRateSecondTxt;
           
            file = AsciiStreamIo::constructForRead(stream);
            if (file)
            {
                if (file.status())
                {
                    throw error("@SYS52680");
                }
                file.inFieldDelimiter(',');
                file.inRecordDelimiter('\r\n');
            
                ttsbegin;
                JournalName = ledgerJournalName::find(custParameters.JournalName);

                if(!JournalName .RecId)
                    throw error("@11250");

                JournalTable.clear();
                JournalTrans.clear();

                int  recordCount = 0;
                while (!file.status())
                {
                    record = file.read();
                    recordCount++;

                    if (conLen(record) && recordCount !=1)
                    {
                        // Need to validate all possible errors in the file and show it at the end
                        if(!this.ValidateLine(record,recordCount,fileName))
                            journalCreated =  false;

                        if(JournalName && !JournalTable)
                        {
                            JournalTable.JournalName = JournalName.JournalName;
                            JournalTable.initFromLedgerJournalName(JournalTable.JournalName);
                            JournalTable.CurrencyCode = Ledger::accountingCurrency(CompanyInfo::current());
                            JournalTable.insert();
                            
                            if(JournalTable)
                            {
                                JournalTable.selectForUpdate(true);
                                JournalTable.Name = fileName;
                                JournalTable.doUpdate();
                            }
                        }

                        if(JournalTable)
                        {
                            CustTable                       custTable = CustTable::find(conPeek(record,2));
                            InvoiceAccountsCustTable    activeInvoiceCustTable  = InvoiceAccountsCustTable::findActiveRecord(conPeek(record,2),str2Date(conPeek(record,4),123));
                            CustTable                       invoiceCustTable = CustTable::find(activeInvoiceCustTable.InvoiceAccount);
                            
                            if (custTable && activeInvoiceCustTable)
                            {
                                ledgerJournalEngine = new LedgerJournalEngine();
                                ledgerJournalEngine.ledgerJournalTable(JournalTable);
                                ledgerJournalEngine.initValue(JournalTrans);
                       
                                JournalTrans.JournalNum = JournalTable.JournalNum;
                                JournalTrans.DefaultDimension =  invoiceCustTable.DefaultDimension;
                                JournalTrans.AccountType = LedgerJournalACType::Cust;
                                ledgerJournalEngine.accountTypeModified(JournalTrans);
                                JournalTrans.LedgerDimension = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(activeInvoiceCustTable.InvoiceAccount, LedgerJournalACType::Cust);
                                ledgerJournalEngine.accountNumModified(  JournalTrans);
                                JournalTrans.AmountCurCredit =   conPeek(record,3);
                                JournalTrans.CurrencyCode =  Ledger::accountingCurrency(CompanyInfo::current());

                                //JournalTrans.ExchRate = this.exchangeRate(JournalTrans.CurrencyCode, str2Date(conPeek(record,4),123));
                                JournalTrans.TransDate = str2Date(conPeek(record,4),123);
                                JournalTrans.OffsetAccountType = JournalName.OffsetAccountType;
                                JournalTrans.OffsetLedgerDimension =  JournalName.OffsetLedgerDimension;
                                JournalTrans.OffsetDefaultDimension = invoiceCustTable.DefaultDimension;
                                JournalTrans.PaymMode = custParameters.DXC_MethodOfPayment;
                                JournalTrans.PostingProfile =    custParameters.DXC_PostingProfile;
                                JournalTrans.DelayTaxCalculation = NoYes::Yes;
                                JournalTrans.markAsApprovedByCurrentUser();

                                if (JournalTable.CurrencyCode && JournalTable.FixedExchRate)
                                {
                                    JournalTrans.ExchRate        = JournalTable.ExchRate;
                                    JournalTrans.ExchRateSecond  = JournalTable.ExchrateSecondary;
                                    JournalTrans.Triangulation   = JournalTable.euroTriangulation;
                                }
                                else
                                {
                                    [triangulationResult,
                                    JournalTrans.ExchRate,
                                    JournalTrans.ExchRateSecond,
                                    exchRateSecondTxt] = LedgerJournalEngine_Server::currencyModified(JournalTrans);
                                    JournalTrans.Triangulation = Currency::unknownNoYes2Noyes(triangulationResult);
                                }
                                JournalTrans.insert();
                            }
                            else
                            {
                                if(!custTable)
                                {
                                    journalCreated =  false;
                                    error(strFmt("@CL:JournalNotCreated",conPeek(record,2),fileName));
                                }

                                if(!activeInvoiceCustTable)
                                {
                                    journalCreated =  false;
                                    error(strFmt("@CL:NoActiceAccount",conPeek(record,2),fileName));
                                }
                            }
                        }
                    }
                }
            if(journalCreated == true)
                ttscommit;
            }
            else
                journalCreated =  false;

            if(journalCreated == false)
                throw error ("@CL:UpdateHasBeenCancelled");

        }
        catch
        {
            ttsabort;
            journalCreated = false;
        }
        finally
        {
            if(journalCreated)
                info(strFmt("@CL:ImportSucessfully",fileName,JournalTable.JournalNum));
            else
                checkFailed(strFmt("@CL:ErrorImportingFile",fileName));
        }
        return journalCreated;
    }
  public boolean validateFileExtension(str filepath)
    {
        str         actualFileName, fileExtension, actualFilePath;
        boolean     ok = true;

        [actualFilePath,actualFileName, fileExtension] = fileNameSplit(filePath);

        if(fileExtension != '.csv')
        {
            ok = false;
            Error(strFmt("@ECL:FileFormatError", actualFileName));
        }

        return ok;
    }

 public boolean isNumeric(str strToValidate)
    {
        return (strlen(strToValidate)) == strlen(strkeep(strToValidate,'1234567890.'));
    }
  public Amount exchangeRate(CurrencyCode currencyCode, TransDate transDate)
    {
        ExchangeRate     exchangeRate;
        ExchangeRateType ExchangeRateType;
        ExchangeRateCurrencyPair exchangeRateCurrencyPair;
        real             exchRate;

        CurrencyCode fromCurrency  = CurrencyCode;
        CurrencyCode toCurrency    = Ledger::find(Ledger::current()).AccountingCurrency ;

        select firstonly exchangeRateCurrencyPair
        where exchangeRateCurrencyPair.ExchangeRateType == Ledger::find(Ledger::current()).DefaultExchangeRateType
            &&  exchangeRateCurrencyPair.FromCurrencyCode == fromCurrency
            &&  exchangeRateCurrencyPair.ToCurrencyCode   == toCurrency;

        exchRate = exchangeRate::findByDate(exchangeRateCurrencyPair.RecId,transDate).ExchangeRate;

        return exchRate/100;
    }

Posted in AX 365

Read File’s from SFTP Server and Write data in AX365 (PART 1)

Recently I got a requirement to read files from SFTP directory and write in AX. There were many challenges I faced. I will try to cover all in this post.

In Part 1, I will Create a project with basic SFTP functionality which we will use in AX in Part 2

Basic Step is to add the Parameters to set the SFTP details.

Tips:

If we are using Port 22 then no need to use a prefix of SFTP in your Host because 21 is for FTP and 22 is for SFTP

Your File path should start from the directory root folder not from the SFTP server name.

To Connect to the SFTP Server. I used SSH.NET Dll.

Create a New C# Library project, Right Click on your project ,Click on Manage NUGET Packages and Install ssh.net In your project. It will add a reference of DLL into your project.

Add a reference of this dll in your class

using Renci.SshNet;

Now we need to write a method to make a connection,

public SftpClient OpenSFTPConnection(string host, int port, string username, string password)
{
    if (this.sftpClient == null)
    {
        this.sftpClient = new Renci.SshNet.SftpClient(host, port,      username, password);
    } 
   if (!this.sftpClient.IsConnected)
    {
        this.sftpClient.Connect();
    }

    return this.sftpClient;
}

This method will return all the files present in the directory

public List<string> GetDirectories(SftpClient _SftpClient, string path)
{
        return _SftpClient.ListDirectory(path)/*.Where(n => n.IsDirectory).ToList();
}

This method will return the stream which we can use to read the content of the files.

 public Stream DownloadFile(SftpClient _SftpClient, string sourcePath)
 {
        var memoryStream = new MemoryStream();

        _SftpClient.DownloadFile(sourcePath, memoryStream);

        memoryStream.Position = 0;

        return memoryStream;
}

I also got a requirement to move files to different folder after writing data in AX. Below method will do that magic.

 public void MoveFile(SftpClient _SftpClient, string sourcePath, string destinationPath, bool isPosix)
{
        _SftpClient.RenameFile(sourcePath, destinationPath, isPosix);
}

Final Code for our library project will look like this

using Renci.SshNet;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MySshNet
{
    public class sftpConnection
    {
        public SftpClient sftpClient;

        public SftpClient OpenSFTPConnection(string host, int port, string username, string password)
        {
            if (this.sftpClient == null)
            {
                this.sftpClient = new Renci.SshNet.SftpClient(host, port, username, password);
            }

            if (!this.sftpClient.IsConnected)
            {
                this.sftpClient.Connect();
            }

            return this.sftpClient;
        }

        public List<string> GetDirectories(SftpClient _SftpClient, string path)
        {
            return _SftpClient.ListDirectory(path)/*.Where(n => n.IsDirectory)*/.Select(n => n.Name).ToList();
        }

        public void MoveFile(SftpClient _SftpClient, string sourcePath, string destinationPath, bool isPosix)
        {
            _SftpClient.RenameFile(sourcePath, destinationPath, isPosix);
        }

        public Stream DownloadFile(SftpClient _SftpClient, string sourcePath)
        {
            var memoryStream = new MemoryStream();

            _SftpClient.DownloadFile(sourcePath, memoryStream);

            memoryStream.Position = 0;

            return memoryStream;
        }
    }
}

Now Just build your project then go to your projects bin\Debug folder and copy both Dll’s. paste the DLL’s to your AX extension Bin Folder so we can use it in our AX project.

Tips:

We need to add our Dll’s in our extension Bin folder so It can go through with check in to the next environments.

Now we are ready to Consume the Dll’s in Our AX project in Part 2..

Posted in ax 2012, AX 365

Passing and consuming C# List in AX 2012 – 365 (X++)

Recently I ran into an issue while trying to consume a dll in AX as the method was returning List,I tried to used VAR but the enumerator is not returning the correct values so i had to declare it as a CLRObject and Its worked like a magic.

C# Method

 public List<string> GetDirectories(SftpClient _SftpClient, string path)
        {
            return _SftpClient.ListDirectory(path).Select(n => n.Name).ToList();
        }

Code in AX

The most Importing part is the declaration of List, We need to declare List as CLRobject so we can use it in AX the same code works in both versions 2012 and 365.

ClrObject       list = new ClrObject("System.Collections.Generic.List`1[System.String]");
    ClrObject       enumerator;
    str             fileName;

    ...

    list = program.GetDirectories();

    enumerator = list.GetEnumerator();

    while (enumerator.MoveNext())
    {
       fileName = enumerator.get_Current();
       //Your logic here
    }
      

Hope it will safe your time 🙂

Posted in ax 2012

“Error in getting SID” [AX 2012]

This is a sample job to find the User which exists in Dynamics AX but deleted/Disabled from Active directory.

“Error in getting SID”

static void findUserNames(Args _args)
{
    UserInfo                userInfo;
    container               con;
       while select forUpdate userInfo
        where   userInfo.Enable &&
                userInfo.AccountType != UserAccountType::ADGroup
    {
        con = SysUserLicenseMiner::getUserRoles([userInfo.Id, userInfo.company]);
        if (conLen(con) == 0)
        {
            warning(strFmt("Userid: %1, username: %2", UserInfo.networkAlias, UserInfo.name));
        }
    }
}

 

Original Post Here