Exists and notExists Join in Microsoft Dynamics AX 2009 and Microsoft SQL Server

Some times while creating the query to have set the criteria that select this record from table A if  the reference of table A is available in table b or not.

This criteria can be easily achieved by using the exists and not exists join.

Below is the query that is showing the Exit join in X++;

smmBusRelTable smmBusRelTable;
smmBusRelFarmActivity smmBusRelFarmActivity;

Select count(recid) from smmBusRelTable
        exists join smmBusRelFarmActivity
    where smmBusRelTable.BRMCreditlineStatus == BRMCreditlineStatus::Preapproved
    &&
    smmBusRelFarmActivity.BusRelAccount == smmBusRelTable.BusRelAccount
        && (smmBusRelFarmActivity.CodeSecondary == "Dairy sharemilker"
        ||  smmBusRelFarmActivity.CodeSecondary == "Contract milker"
        ||  smmBusRelFarmActivity.CodeSecondary == "Personal use"
        ||  smmBusRelFarmActivity.CodeSecondary == "Retired"
        ||  smmBusRelFarmActivity.CodeSecondary == "Lifestyle"
        || (smmBusRelFarmActivity.CodePrimary == "Contractor" && smmBusRelFarmActivity.IsPrimary == NoYes::Yes));

    info(strfmt('%1', smmBusRelTable.RecId));

The above query is fetching the records from table smmBusRelTable but based on the criteria that their reference should be available in smmBusRelFarmActivity table and the range that we have applied on that table.

Same thing we can achieve for the "not exist" join like if i want to fetch the records from table A based on the criteria that its reference record should not be exist in the table B.

Below is the query for the not exists join.

smmBusRelTable smmBusRelTable;
smmBusRelFarmActivity smmBusRelFarmActivity;

Select count(recid) from smmBusRelTable

    where smmBusRelTable.BRMCreditlineStatus == BRMCreditlineStatus::Preapproved

       NotExists join smmBusRelFarmActivity

    where smmBusRelTable.BusRelAccount == smmBusRelFarmActivity.BusRelAccount

        && (smmBusRelFarmActivity.CodeSecondary == "Dairy sharemilker"

        ||  smmBusRelFarmActivity.CodeSecondary == "Contract milker"

        ||  smmBusRelFarmActivity.CodeSecondary == "Personal use"

        ||  smmBusRelFarmActivity.CodeSecondary == "Retired"

        ||  smmBusRelFarmActivity.CodeSecondary == "Lifestyle"

        || (smmBusRelFarmActivity.CodePrimary == "Contractor" && smmBusRelFarmActivity.IsPrimary == NoYes::Yes));

 info(strfmt('%1', smmBusRelTable.RecId));

note: for non exist join in the above AX query I am first applying the range on parent table i.e smmBusRelTable than doing the non exist join on the child table i.e smmBusRelFarmActivity. if we apply the range on the parent table after the join than it will not fetch the correct number of records.

Previously i was applying the range on the parent table after the join and it was not showing the same result as from sql server query and when i apply the range first on parent table and than join than it shows the same results as compare to the sql server query.

If we want to run the same query in Sql Server we can also do this.

Below is the example for using the exists and not exits joins in Microsoft Sql Server.

Not Exit Join:

select count(SMMBUSRELTABLE.RECID) from SMMBUSRELTABLE

where  Not Exists (select * from SMMBUSRELFARMACTIVITY

where SMMBUSRELFARMACTIVITY.BUSRELACCOUNT = SMMBUSRELTABLE.BUSRELACCOUNT

        and (SMMBUSRELFARMACTIVITY.CodeSecondary = 'Dairy sharemilker'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Contract milker'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Personal use'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Retired'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Lifestyle'

        or (SMMBUSRELFARMACTIVITY.CodePrimary = 'Contractor' and SMMBUSRELFARMACTIVITY.IsPrimary = '1'))

)

and

SMMBUSRELTABLE.BRMCreditlineStatus = 1

Exist Join:

select count(SMMBUSRELTABLE.RECID) from SMMBUSRELTABLE

where   Exists (select * from SMMBUSRELFARMACTIVITY

where SMMBUSRELFARMACTIVITY.BUSRELACCOUNT = SMMBUSRELTABLE.BUSRELACCOUNT

        and (SMMBUSRELFARMACTIVITY.CodeSecondary = 'Dairy sharemilker'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Contract milker'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Personal use'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Retired'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Lifestyle'

        or (SMMBUSRELFARMACTIVITY.CodePrimary = 'Contractor' and SMMBUSRELFARMACTIVITY.IsPrimary = '1'))

)

and

SMMBUSRELTABLE.CRTBRMCreditlineStatus = 1

This was all related to the exists and notexists joins in Microsoft Dynamics AX 2009 and Microsoft SQL Server.

Feel fry to comment or email me if you have any questions on this.

Thanks

Muhammad Zahid.

 

Sql Query Using Case (Decisions)

Like in programming we use if else check and switch cases to take decisions on run time based on the data

Same thing we can do during in Sql Query as well.

In most of the cases when we select the data from Sql Server by using Sql query we got the values 1,2,3 and so on for the columns containing data type bit.

To make this enum values usefull we have to convert enum values in to meaning full label.

Below is the example through which we can achieve this.

Select top 10

SMMBUSRELTABLE.CUSTACCOUNTNUM,

SMMBUSRELTABLE.BRMPriceLevel,

SMMBUSRELTABLE.CustGroup,

CASE SMMBUSRELTABLE.BRMAccountType

        WHEN '0' THEN 'Bushetts'

        WHEN '1' THEN 'Amnesty'

WHEN '2' THEN 'Business'

WHEN '3' THEN 'Family'

WHEN '4' THEN 'Internal'

WHEN '5' THEN 'Shareholder'

WHEN '6' THEN 'Staff'

WHEN '7' THEN 'Trade'

WHEN '8' THEN 'Standard(Corporate)'

WHEN '9' THEN 'Partnership'

WHEN '10' THEN 'Gulf'

WHEN '11' THEN 'Inter Island Card'

    END as BRMAccountType

from SMMBUSRELTABLE

Some time we have to check that for each specific row that we have fetch the column value is null or not and based on that we customize the value's in that column and show.

Below is the example to achieve this.

Select

CodePrimary,

CodeSecondary,

CASE 

        WHEN ADDRESStable.STREET IS NULL THEN 'NULL'

        ELSE 'Value Found'

    END as CustomerAddress 

from  ADDRESS as ADDRESStable


This was all related to the using of cases in Sql Query let me know if you have any issues on this.

Thanks
Muhammad Zahid.

X++ Find Legal Entities in Microsoft Dynamics AX.

Hi,

Some times while importing the cross company data in Microsoft Dynamics AX we need to check either the company define in excel column is exist in AX or not and some time we need to check either the company specified in AX is correct or not.

To check this thing below is the code snippet,

 xDataArea::exist(crtEDIInvoiceLine.Company);


Thanks
Muhammad Zahid.

Use of Printers On The Server is not allowed (Dynamics AX 2009)

We have created a new feature in Microsoft Dynamics AX 2009 that once the invoice is posted successfully than its copy will email to the user.

When we ran the whole process in batch job it was failing,

after checking the logs of the batch job we got the error message which was causing the issue.

The Error message was:

We have different AOS setup on that environment.After investigation we found out that the AOS server on which that batch job runs have some server configuration issue.

When we open the Server Configuration of that AOS Server we found out that the checkbox 'Allow Clients to connect to printers on this server' was un checked.

After checking that check box when we post the invoice again in batch mode it was successfully posted.

Further Details related to this issue can be seen on the below link.

http://mybhat.blogspot.co.nz/2011/05/running-batch-report-in-ax2009.html


Thanks
Muhammad Zahid.

Customize infolog in Microsoft Dynamics AX 2009

In Microsoft Dynamics AX 2009 you can customize the infolog to add some new buttons on it to open some other forms.

For example in my scenario I have to generate the word document and once the word document is generated successfully than I create a activity automatically and show the activity number on the infolog. once the user clicks on that activity number on the infolog than the show button is appears on the same infolog and when user clicks on that show button than it opens the activity form in edit mode so the user can change the values if they want.

Below is the screen shot of infolog containing activity number and show button.

Now when i click on that show button than it will open the activity form with the same activity number already filtered in it.

Below is the code that I have use to customize the infolog.

info(strfmt('Activity Created Successfully activity number is %1', smmActivities.ActivityNumber), "", Sysinfoaction_tablefield::newBuffer(smmActivities));

note:on smmActivities table the property formRef is set to form smmActivities thats why its opens the form smmActivities.

Below is link for more information on customizing infologs.

https://community.dynamics.com/ax/b/vishalsblogonmsdynamicsax/archive/2013/05/03/sysinfoaction-class-go-to-main-table-from-infolog-message-in-ax-2009-ax-2012


Thanks

Muhammad Zahid.

Send report in Email in Microsoft Dynamics AX 2009

In this I will share the steps of how we can send the report in email.

First of all for sending the email we all need to setup the email templates based on our requirements.

Below is the path for the email templates:

Basic -> Setup -> Email Templates

Each template is uniquely identified by the Email ID.

we can also create the setup form to allow the user to select the template based on his requirement in his feature.

For example for our daily tax invoice feature we have created a setup form for the user to select the email template.

 Now after creating and setting up the template below is the code to create the report and attach and send it in  an email.

public boolean send(PrintCopyOriginal _printCopyOriginal = PrintCopyOriginal::Copy)
{
    SysEmailId  emailId = crtParameters::find().DailyTaxInvoiceEmailId;
    //above code is use to get the email template from the setup table that is configured by the end user     //before

    str         tempFile;
    ;

    if (this.DailyTaxInvoiceEnabled())
    {

        tempFile = this.generatePDF(custInvoiceJour, _printCopyOriginal);
        above method generates the report in pdf in temporary location and send the address of that file           to be attached in the email
     
        SysEmailTable::sendMail(emailId,
                                'en-nz',
                                custTable.CRT_DailyTaxInvoiceEmail,
                                this.initMap(),
                                tempFile,
                                '',
                                true,
                                'admin',
                                true,
                                custInvoiceJour.RecId);
       //above method is use to send the email to the
       //First parameter is the email template id (The template which we will use to send      //the email)
       //second parameter is the language.
       //third parameter is the email address to whom we are sending the email
       //in fourth parameter we are just initializing the map based on the fields required in //the email            template for the subject and other things (will show this method below)
       //fifth parameter address of the report where report is saved after generation
       //eighth parameter is the user id from which we are sending the email (credentials are setup on            //the smtp server)
     
        new FileIOPermission(tempFile, 'rw').assert();
        WinAPIServer::deleteFile(tempFile);
        CodeAccessPermission::revertAssert();
        //above code is just deleting the report from the temporary location

        return true;
    }

    return false;
}

Below is the method for generating the pdf:

private str generatePDF(CustInvoiceJour _custInvoiceJour, PrintCopyOriginal _printCopyOriginal = PrintCopyOriginal::Copy)
{
    Args                args;
    ReportRun           rr;
    Report              rb;
    PrintJobSettings    pjs;

    str                 tempFile;
    str                 tempFolder;
    ;

    tempFolder = Global::strReplace(SysEmailParameters::find().AttachmentsPath,'//','/');
    //Above attachment path is define in the following navigation path of the setup of form                         //Administration -> setup -> E-mail Parameters
   
    tempFile = strfmt('%1\\TaxInvoice%2.pdf',tempFolder,custInvoiceJour.InvoiceId);

    args = new Args(reportstr(CRTFreeTextInvoice));
    args.record(_custInvoiceJour);

    args.parmEnum(_printCopyOriginal);

    // Set report run properties
    rr = new ReportRun(args,'');
    rr.suppressReportIsEmptyMessage(true);
    rr.query().interactive(false);

    // set report properties
    rb = rr.report();
    rb.interactive(false);

    // set print job settings
    pjs = rr.printJobSettings();
    pjs.fileName(tempFile);
    pjs.fitToPage(true);

    // break the report info pages using the height of the current printer's paper
    pjs.virtualPageHeight(-1);

    // force PDF printing
    pjs.format(PrintFormat::PDF);
    pjs.setTarget(PrintMedium::File);
    pjs.viewerType(ReportOutputUserType::PDF);

    // lock the print job settings so can't be changed
    // X++ code int the report may try to change the destination
    // to the screen for example but this does not make
    // sense when running a report here
    pjs.lockDestinationProperties(true);

    // Initialize the report
    rr.init();

    rr.run();

    return tempFile;
}

Below is the method for initiating the map for the email to insert the subject and other things that I have discussed above.

private Map initMap()
{
    Map     map = new Map(Types::String, Types::String);
    ;

    map.insert('subject', strfmt('Daily tax invoice %1- %2',                                                                           custInvoiceJour.InvoiceId,custInvoiceJour.DocumentDate));
    map.insert('phone', companyInfo::find().CRTFreePhone);
    map.insert('Customeraccount', strfmt('%1', custInvoiceJour.InvoiceAccount));
    map.insert('CustomerName', strfmt('%1',                                                                                                 CustTable::find(custInvoiceJour.InvoiceAccount).Name));
    map.insert('InvoiceNumber', strfmt('%1', custInvoiceJour.InvoiceId));

    return map;
}

This was all related to the generating the report and sending it in the email.

If you have any questions than feel free to post here.

Thanks
Muhammad Zahid.

SOLVED :"Illegal data conversion from original field (Dynamics AX 2009)"

After Restoring the data from Live Environment to the Test Environment When I synchronize the Data Dictionary after that I got the following error message.

Illegal data conversion from original field IPBCLIENTSESSION.CLIENTCOMPUTER to CRTFERTORDERTABLE.FertSupplier: Unable to convert data types to anything but character field type (0 to 4).

In Dynamics AX Environment I check the id of the table i.e CRTFERTORDERTABLE  and id of the field i.e FertSupplier

The id of table  CRTFERTORDERTABLE  is 30379

id of the field FertSupplier is 30002.

Than in Sql server I write the following query:

 SELECT TABLEID,FIELDID,NAME from SQLDICTIONARY
  where TABLEID = 30379 and  (FIELDID = 30002)

After running the query the result that I got is:

in the above image we can see that field CLIENTCOMPUTER contains the id 30002 while in AX Field ID 30002 belongs to FERTSUPPLIER

now I will update the SQLDICTIONARY table and set the CLIENTCOMPUTER ID to new id to remove the conflict.

below is the query to update the table

update SQLDICTIONARY set FIELDID = 300020
  where TABLEID = 30379 and FIELDID = 30002
 
Now after running this I will again synchronize the table in Dynamics AX and after the synchronization the error will not arrive.

Thanks

Muhammad Zahid.