Diffrent Type of Joins and Linked Type in AX 2009

Different type of Joins and Link Type

Car Table

CarId	ModelYear	CarBrand	Model	Mileage	ModelYear	CarId
CA101	2008	Mahindra	Scorpio	15	2008	CA101
CA102	2009	Suzuki	800	12	2009	CA102
CA103	2007	Hundai	i20	5	2007	CA103
CA104	2007	Toyato	Inova	3	2007	CA104
CA105	2009	BMW		5	2009	CA105
CA106	2010	Benz	AZ12	10	2010	CA106

 

Rental Table

RentalId	CustAccount	CarId	FromDate	ToDate	ToDate	FromDate
RE101	1104	CA101	10/12/2010	10/29/2010	10/29/2010	10/12/2010
RE102	1102	CA102	11/17/2010	12/14/2010	12/14/2010	11/17/2010
RE103	1203	CA103	11/24/2010	12/15/2010	12/15/2010	11/24/2010
RE104	1301	CA104	12/10/2010	1/5/2011	1/5/2011	12/10/2010
RE105	1304	CA101	1/3/2011	1/18/2011	1/18/2011	1/3/2011
RE108	1303
RE107	1202
RE106	2024	CA103	1/10/2011	1/29/2011	1/29/2011	1/10/2011

 

Inner Join

Select records from main table that have matching record form join table.

Main table record and join table records are joined together  and show as single record.

Code                                                                                                                                                    

static void JoinExamples(Args _args)                                                                                                      

{                                                                                                             

    CarTable carTable;                                                                                                      

    RentalTable rentalTable;                                                                                                          

    ;                                                                                                          

    while select carTable join rentalTable                                                                                                

        order by carTable.CarId                                                                                                        

        where carTable.CarId == rentalTable.CarId                                                                                                 

                                                                                                               

        {                                                                                                     

            info(strfmt("Car id is %1 renatla id is %2",carTable.CarId, rentalTable.RentalId));                                                                                                   

        }                                                                                                     

}

               

Output                                                                                

Outer Join                                                                          

                                                                               

Select all the records from main table and related records from join table                                                                                                                                            

If there is no match, the field from join table is empty                                                                   

Code

while select carTable outer  join rentalTable                                                                                                       

        order by carTable.CarId                                                                                                        

        where carTable.CarId == rentalTable.CarId 

Output

Exists Join                                                                                                                                                                                          

Selecting record from main table only if there is matching record in join table.                                   

It shoul be return only main table record, not a related table record.                                                                      

Code

while select carTable Exists  join rentalTable                                                                                                       

        order by carTable.CarId                                                                                                        

        where carTable.CarId == rentalTable.CarId 

Output

 

Not Exists Join                                                                                                                                                                 

Selecting record from main table only if there is not matching record in join table.                           

It is directly irrelevant to the Exists join.                

Output                

           

 

Link Type:  Delayed

A pause is inserted before linked child data sources are updated. This enables faster navigation in the parent data source because the records from child data sources are not updated immediately.

Form Output

Form Design in Data Source Level:

 

Link Type: Active

The child data source is updated immediately when a new record in the parent data source is selected. Continuous updates consume lots of resources.

Link Type: Passive

Linked child data sources are not updated automatically. Updates of the child data source must be programmed on the active method of the master data source.

Calculate the week Days ,week Ends between two date intervals In Ax 2009

How to Calculate the total number of weeks,weekends or count of  Sundays in a given date interval.. 

static void calcDays(Args _args)
{
    FromDate    fromDate    = today();
    ToDate      toDate      = mkdate(31,01,2011);
    WeekDays    weekDay = WeekDays::Sunday; // the day we search after
    int         days;
    int         cntSpecDay;
    int         leadIn;
    int         leadOut;
    ;
    days = fromDate - toDate; // days difference
    //Calculate the No.Of weeks in given Date Intervals
    leadIn = any2int(fromDate - toDate) /7;
    cntSpecDay = days / 7; // how many sundays in complete weeks
    if (dayOfWk(fromDate) <= weekDay)
    {
        cntSpecDay++; // the day we search is in lead in week
    }
    if (dayOfWk(toDate) >= weekDay)
    {
        cntSpecDay++; // the day we search is in lead out week
    }
info(strfmt("%1 is %2 times embedded between %3 and %4", weekDay,cntSpecDay, fromDate, toDate));
  print days;
  print leadIn;
  pause;
}

Out put:
======
786== Days
112== Weeks
Sunday is 113 times embedded between 3/27/2013 and 1/31/2011

EDT Relations in 2012

EDT Relations migration tool in 2012:

 

As you all know we cannot create relations under EDT in AX 2012, all relations will be created in Table level. The EDT relations created in AX 2009 will be still active but Microsoft recommends to move all EDT relations to Table level.  

The EDT relation migration tool helps you move relations from EDT nodes to table nodes. The EDT relation migration tool is found on the client menu, under Tools > Code upgrade > EDT relation migration tool.

Kindly refer the link for more details - http://msdn.microsoft.com/en-us/library/gg989788.aspx

 

Hope it helps..............

How to send Emails to users in batch processing by using X++ code in Ax2009

Create the class with below methods

public class OnhandQty extends RunBaseBatch

{

     SysMailer   mailer;

     boolean check;

     SysEmailParameters parameters;

    #define.CurrentVersion(1)

    #localmacro.CurrentList

    //onhandQty

   // methodVariable2

    #endmacro

}

public static void main(Args args)

{

    OnhandQty onhandQty;

    ;

    onhandQty = new OnhandQty();

    onhandQty.run();

}

void run()

{

    this.WriteCSV();

    if(check == true)

    {

        this.EmailCheck();

    }

}

// This method is used to bring the data and Save it into CSV file

void WriteCSV()

{

    Commaio file;

    container line,line1;

    FileIOPermission   permission;

    InventTable inventTable;

    MinimumQty  minimumQty;

   // #define.filename("C:\\Report\\On-Hand.csv")

    #File

    ;

    check = false;

    permission = new FileIOPermission(#filename,#io_write);

    permission.assert();

    file = new Commaio(#filename , #io_write);

    file = new Commaio(#filename , 'W');

if( !file || file.status() != IO_Status::Ok)

{

throw error("File Cannot be opened");

}

line1 = ["Item Id","Description","On Hand","Min-ReOrder Qty"];

file.writeExp(line1);

while select minimumQty

{

if(minimumQty.MinQty  > minimumQty.Onhand && minimumQty.MinQty !=0)

{

line = [minimumQty.ItemID,InventTable::find(minimumQty.ItemID).ItemName

,minimumQty.Onhand,minimumQty.MinQty];

file.writeExp(line);

check = true;

}

}

}

// By using this method we can send this CSV file to users in the List..

void EmailCheck()

{

//Set                     permissionSet2 = new Set(Types::Class);

InteropPermission permission = new InteropPermission(InteropKind::ComInterop);

;

CodeAccessPermission::revertAssert();

// permissionSet2.add(new InteropPermission(InteropKind::ClrInterop));

// permission = new InteropPermission(InteropKind::ComInterop);

// permissionSet2.add(new FileIOPermission(_attachmentFilename, 'r'));

permission.assert();

mailer = new SysMailer();

parameters = SysEmailParameters::find();

if (parameters.SMTPRelayServerName)

{

mailer.SMTPRelayServer(parameters.SMTPRelayServerName,

parameters.SMTPPortNumber,

parameters.SMTPUserName,

SysEmailParameters::password(),

parameters.NTLM);

}

else

{

mailer.SMTPRelayServer(parameters.SMTPServerIPAddress,

parameters.SMTPPortNumber,

parameters.SMTPUserName,

SysEmailParameters::password(),

parameters.NTLM);

}

mailer.fromAddress('ABC@XYZ.com,');

mailer.tos().appendAddress('xyx@abc.com’);

mailer.tos().appendAddress('abc@abc.com);

mailer.htmlBody('Find the attachment and update the on hand quantiy. <Br>\n NOTE:This is a System                            Generated Email. Please do not Reply.');

mailer.subject('Inventory On Hand Status');

//mailer.attachments().add("C:\\Report\\On-Hand.csv");

CodeAccessPermission::revertAssert();

}

// Save the values

public container pack()

{

    return [#CurrentVersion];

}

// Retrieve those values

public boolean unpack(container packedClass)

{

Version version = RunBase::getVersion(packedClass);

switch (version)

{

case #CurrentVersion:

[version] = packedClass;

break;

default:

return false;

}

return true;

}

You Know friends here one more issue is there, in this process we need to assign the email ids for the users manually, but that is not the correct process for technical peoples.

Avoid those process, create one form like parameter form there users can add /remove the email id. System will automatically take the ids from that form and emails every day.

After completing above process we need to configure the batch in Batch server.

Go to ->Administration Module->set Up->Batch Group

Here we need to select the batch  server

Over view tab

Batch Server Tab

Administration -> set Up -> Server configuration->

Here we need to select the server for sending mails to users..

Next Basic Module-> Common Forms-> Batch Job List

Create the new job with description and go to View Task->

Select the class what we wrote for sending Emails to users , choose the class and save it .

Go to Functions ->change status->  change the status to waiting

Final Step is Go to Recurrences -> Here we can give the timings based on the time give below.

Thanks
DAX..