How to Extract and generate a CSV File and Retrieve a List of Project Addresses in AX 2012 Using SQL and X++

In this post, I will share how to retrieve a list of project addresses in Microsoft Dynamics AX 2012 using both SQL queries and an X++ job. This guide includes both approaches to ensure flexibility depending on your requirements.

Background and Requirement

A client required a report listing all the addresses associated with projects in AX 2012. To fulfill this, I identified the necessary tables and their relationships to retrieve the relevant data. The key tables involved are:

  • ProjTable: Stores project-related data.
  • LogisticsLocation: Stores location details.
  • LogisticsPostalAddress: Stores postal address details.
  • LogisticsAddressCountryRegion: Stores country/region information.

After confirming the relationships between these tables, I wrote a SQL query to extract the required data and later implemented an X++ job to automate the extraction process into a CSV file.

SQL Query for Project Addresses

Here is the SQL query I used to fetch the project addresses:

SELECT 

    PROJTABLE.PROJID AS Project,

    PROJTABLE.NAME AS [Project Name],

    PROJTABLE.STATUS AS [Project Stage],

    LogisticsLocation.DESCRIPTION AS [DLV Name],

    LOGISTICSPOSTALADDRESS.ADDRESS,

    LOGISTICSPOSTALADDRESS.STREET,

    LOGISTICSPOSTALADDRESS.CITY AS [City],

    LOGISTICSPOSTALADDRESS.STATE AS [State],

    LOGISTICSPOSTALADDRESS.ZIPCODE AS [Zip],

    LOGISTICSPOSTALADDRESS.COUNTRYREGIONID AS [Country/Region],

    LOGISTICSPOSTALADDRESS.COUNTY AS [County]

FROM 

    PROJTABLE

LEFT JOIN 

    LogisticsLocation ON PROJTABLE.DELIVERYLOCATION = LogisticsLocation.RecId

LEFT JOIN 

    LogisticsPostalAddress ON LogisticsLocation.RecId = LogisticsPostalAddress.Location

LEFT JOIN 

    LogisticsAddressCountryRegion ON LOGISTICSPOSTALADDRESS.COUNTRYREGIONID = LogisticsAddressCountryRegion.COUNTRYREGIONID

WHERE 

    PROJTABLE.DATAAREAID = 'DAT' 

    AND PROJTABLE.STATUS <> 4

    AND GETDATE() BETWEEN LOGISTICSPOSTALADDRESS.ValidFrom AND LOGISTICSPOSTALADDRESS.ValidTo

ORDER BY 

    PROJTABLE.PROJID;


//////////////////////////////X++ Code///////////////////////////////////

X++ Job to Extract Project Addresses to CSV

To provide an automated solution, I created an X++ job that extracts the project addresses into a CSV file. Below is the code:

static void ProjectAddressExtractToCSV_Job(Args _args)

{

    ProjTable projTable;

    LogisticsLocation logisticsLocation;

    LogisticsPostalAddress logisticsPostalAddress;

    LogisticsAddressCountryRegion logisticsAddressCountryRegion;

    utcdatetime currentDateTime = DateTimeUtil::utcNow();

    SysDictEnum projStatusEnum = new SysDictEnum(enumnum(ProjStatus));

    str projStatusLabel;


    // File configuration

    #File

    CommaIO file;

    FileIOPermission fileIOPermission;

    str filePath = @"C:\Projects Address\ProjectsAddressDetailsJob.csv";

    int countRec = 0;


    // Define the valid time state for the query

    utcdatetime dateFrom = DateTimeUtil::minValue();

    utcdatetime dateTo = DateTimeUtil::maxValue();


    // File permission setup

    fileIOPermission = new FileIOPermission(filePath, 'W');

    fileIOPermission.assert();


    // Open the file for writing

    file = new CommaIO(filePath, 'W');

    file.inFieldDelimiter(',');

    file.write('Project', 'Project Name', 'DLV Name', 'Project Stage', 'Address', 'Street', 'City', 'State', 'Zip', 'County', 'Country/Region');


    // Query logic

    while select crossCompany validTimeState(dateFrom, dateTo) projTable

        where projTable.Status != ProjStatus::Completed

        join logisticsLocation

            where projTable.DeliveryLocation == logisticsLocation.RecId

        join logisticsPostalAddress

            where logisticsLocation.RecId == logisticsPostalAddress.Location

              && logisticsPostalAddress.ValidFrom <= currentDateTime

              && logisticsPostalAddress.ValidTo >= currentDateTime

        outer join logisticsAddressCountryRegion

            where logisticsPostalAddress.CountryRegionId == logisticsAddressCountryRegion.CountryRegionId

    {

        projStatusLabel = projStatusEnum.value2Label(projTable.Status);


        file.write(

            projTable.ProjId,

            projTable.Name,

            logisticsLocation.Description,

            projStatusLabel,

            logisticsPostalAddress.Address,

            logisticsPostalAddress.Street,

            logisticsPostalAddress.City,

            logisticsPostalAddress.State,

            logisticsPostalAddress.ZipCode,

            logisticsPostalAddress.County,

            logisticsAddressCountryRegion.CountryRegionId

        );

        countRec++;

    }

 file.finalize();

    CodeAccessPermission::revertAssert();

    info(strFmt("CSV file created at %1 with %2 records.", filePath, countRec));

}

Key Points

  1. SQL Query: Retrieves project address details directly from the database.
  2. X++ Job: Automates the extraction process and saves the data in a CSV file.
  3. Customizations: The code can be tailored based on specific requirements like data area or project status filters

Conclusion

This approach provides a flexible solution for extracting project address data in AX 2012. The SQL query is ideal for quick data retrieval, while the X++ job automates the process for regular use. I hope this guide helps others dealing with similar requirements.

Feel free to share your thoughts or ask questions in the comments!

Comments

Post a Comment

Popular posts from this blog

How to create C# Library and Use with in Dynamics 365 For Finance and Operation

Image
Some time we need to Integrate external business logic or third-party functionality into a Dynamics 365 project often involves creating a C# class library. In this library, you'll typically write methods that encapsulate the desired business logic or functionality. These methods can then be referenced and called within your Dynamics 365 project. Step-1 : open visual studio and create “ D365Project ”.For creating new   project in D365 Open visual    studio à File à New à Project à select Finance and operation à click Ok . After creating   the project visual studio will look like this. Step-2 : Secondly Add new Csharp Library project in the same Solution. I added “ CsharpProject ”. For adding right click on Solution à Add à New Project . Step-3 :  Now in the next window on the lift panel Select Visual C# à Class library  à Ok . Step-4 : After adding the above step now you can see in the Solution Explorer there is 2 projects . One of   the Csha...
Read more

Extracting Project Addresses to Excel in Dynamics AX 2012

 In this blog, I’ll demonstrate how to extract project address details into an Excel file using X++ in Dynamics AX 2012. While CSV is a great format for small-scale data export, it has limitations when dealing with large datasets, as it lacks the concept of multiple sheets. To overcome this, I transitioned to Excel, which supports multiple sheets and can handle larger volumes of data seamlessly. Why Excel Instead of CSV? In my previous implementation, I used a CSV format to extract project addresses. While it was simple and effective for smaller datasets, it became evident that: CSV files are single-page only : If the record count exceeds the limit, we cannot paginate to a new sheet. Excel offers better structure : With multiple sheets, we can organize and store large amounts of data efficiently. Usability : Excel files are more user-friendly, especially when filtering, sorting, or visualizing data. X++ Job for Exporting Project Addresses to Excel Below is the X++ code to extract p...
Read more