CreateSalesLine form in Dynamics AX(just like SalesCreateOrder form)

This post is about creating a New CreateSalesLine form which is just going to work like SalesCreateOrder form except that this out of box form creates SO and the new one create SalesLine.

Let's start with the reasons why we may need this form - In out of box SalesTable form
1.  whenever user clicks on the new button from toolbar all the user filters and sort expressions are lost and the active line item that user wants to refer will loose the focus, this is all because the datasource query re-executes. I tried to persist the user filters using my earlier post technique, but it didn't work for me in the case of new button.
2. since it is about referring an existing lineItem we can always use out of box "Copy from lines" function, but the issue with this one is - it doesn't allow user to change the ItemId of the line ( my user wants to use the values from existing lineItem for a new lineItem with different ItemId). i tried to solve this one by making ItemId field editable in the Salestable form, but it was causing chain of other errors - let me know if you have any luck with this.
So i came up with this approach where i provide a new button named "New LineItem" in the salesLine section of SalesTable form(Fig 1), on click i will pop-up the new form that i call CreateSalesLine (Fig 2)

Fig 1                                                                                                        Fig 2:

SalesLine section in SalesTable form (Parent)

New form - CreateSalesLine (Child)

this form will create a new line item record and pre-populates it with values from the active lineItem record in Parent form(SalesTable). User can change the item number if needed and once OK button is clicked the newly created lineItem record is commited to DB , child form closes and the SalesLine grid in the parent form refreshes with the focus on newly Created LineItem.

Here are the things i did to achieve this :

1. To simplify i have created this simple data flow diagram

2. within the new button's click event , i added :

   CreateSalesLineClass obj;
    ;   
    obj = new CreateSalesLineClass();
    obj.init(salesTable, salesTableForm, inventDim, salesLine);  // passing current records info

    obj.create();  // we will discuss this method in subsequent steps

     SalesTable_ds.research(true);

     SalesLine_ds.research(true);

     SalesLine_ds.findRecord(obj.newSalesLineRec());   // to focus the newly created record

     super();

3. CreateSalesLineClass:  

 a)  variables declared at class level scope:

SalesTable salesTable;

  SalesTableForm salesTableForm;

  InventDim  inventDim;

  SalesLine copyFromSalesLine;

  Int64 newLineRecId;

  SalesLine newSalesLineRec;

b) init method:

void init(SalesTable _currRec, SalesTableForm _salesTableForm, InventDim _inventDim, SalesLine _copyFromSalesLine)

{

  ;

  salesTable = _currRec;

  salesTableForm = _salesTableForm;

  inventDim = _inventDim;

  copyFromSalesLine = _copyFromSalesLine;

}

c) Create method:

boolean create()

{

   Args args = new Args();

   FormRun CreateSalesLine;

   SalesLine salesLine;

   boolean result = false;

   SalesTable localSalesTable;

   ;

   args.name(formStr(CreateSalesLine) );

   args.caller(this);

   CreateSalesLine = classFactory.formRunClass(args);

   CreateSalesLine.init();

   CreateSalesLine.run();

   if(!CreateSalesLine.closed())

        CreateSalesLine.wait();

   if(CreateSalesLine.closedOk())

         result = true;

   else

          result = false;

   return result;  // indicating if the new record is created or not

}

4. CreateSalesLine form:

a) Add SalesLine datasource , add simple design tab control with some salesLine fields (Fig 2) and add Command buttons - OK and Cancel

b) CreateSalesLine form class declaration:

   CreateSalesLineClass  CreateSalesLineClass;
   SalesTable salesTable;
   SalesTableForm salesTableForm;
   InventDim copyFromInventDim , inventDim;
   SalesLine copyFromSalesLine, newlyCreatedSalesLine ;


c) init method:

public void init()

{

    super();

     if (!element.args().caller())  // throw error if called directly

        throw error("@SYS22539");

    CreateSalesLineClass = element.args().caller();

    salesTable = CreateSalesLineClass.getSalesTable();

    salesTableForm = CreateSalesLineClass.getSalesTableForm();

    copyFromInventDim = CreateSalesLineClass.getInventDim();

    copyFromSalesLine = CreateSalesLineClass.getCopyFromSalesLine();

}

d) run method:

public void run()

{

    super();

    SalesLine_ds.create(); 

    CreateSalesLineClass.newSalesLineRec(SalesLine);   // pass the newly created salesline ref to caller

}

e) SalesLine_DS -> Create method:

public void create(boolean _append = false)

{

    super(_append);

    newlyCreatedSalesLine.data(SalesLine); // back up the newly created record

    newlyCreatedInventDim.data(inventDim); // back up the newly created record

   // we can use both the back-up buffers later if User changes the ItemId

    // set the basic Line info

    SalesLine.SalesId = salesTable.SalesId;

    SalesLine.ItemId = copyFromSalesLine.ItemId;

    inventDim.InventColorId = copyFromInventdim.InventColorId;

    inventDim.InventLocationId = copyFromInventDim.InventLocationId;

    inventDim = InventDim::findOrCreate(inventDim);    // create new inventDim rec

    SalesLine.InventDimId = inventDim.inventDimId;

    SalesLine.CustAccount = salestable.CustAccount;

    SalesLine.CurrencyCode = salesTable.CurrencyCode;

    SalesLine.CustGroup  = salesTable.CustGroup;

    SalesLine.SalesType  = salesTable.SalesType;

    // copy data from active salesLine record in parent form, i just copied the data that my client needs

    SalesLine.ReceiptDateRequested  = copyFromSalesLine.ReceiptDateRequested;

    SalesLine.ShippingDateRequested = copyFromSalesLine.ShippingDateRequested;

    salesLine.SalesQty           =  copyFromSalesLine.SalesQty;

    salesLine.SalesUnit          = copyFromSalesLine.SalesUnit;

    salesLine.QtyOrdered         = copyFromSalesLine.QtyOrdered;

    salesLine.SalesPrice         = copyFromSalesLine.SalesPrice;

    salesLine.PriceUnit          = copyFromSalesLine.PriceUnit;

    salesLine.LinePercent        = copyFromSalesLine.LinePercent;

    salesLine.LineDisc           = copyFromSalesLine.LineDisc;

    salesLine.MultiLnDisc        = copyFromSalesLine.MultiLnDisc;

    salesLine.MultiLnPercent     = copyFromSalesLine.MultiLnPercent;

    salesLine.SalesMarkup        = copyFromSalesLine.SalesMarkup;

    salesLine.LineAmount         = copyFromSalesLine.LineAmount;

    // you can copy more data as per your need , refer class SalesLineType.initFromSalesLine() - used by out of box copy line functionality

}

f) Override form's salesLine datasource's Write method:

public void write()

{

   ; 

     if (!element.closedOk())

          return;

     SalesLine.createLine(NoYes::Yes, NoYes::No, NoYes::No, NoYes::No);

     super();

}

so far whatever i coded is good as long as User doesn't want to change the ItemId in the new form, my user like the ability to change the ItemId so i coded the following...

g) override the modified method for ItemId field of SalesLine datasource:

public void modified()

{

    ItemId itemId;

    ;

    super();

    //info(strFmt('Old ItemId - %1',  copyFromSalesLine.ItemId ));

    //info(strFmt('New ItemId - %1',  SalesLine.ItemId ));

    itemId = SalesLine.ItemId;

    SalesLine.data(newlyCreatedSalesLine);  // restore from the back-up buffer

    inventDim.data(newlyCreatedInventDim);

    SalesLine.ItemId = itemId;

    SalesLine_ds.refresh();  // refresh form data from the cache

    info(strFmt('Old ItemId - %1',  copyFromSalesLine.ItemId ));

    inventDim = InventDim::findOrCreate(inventDim);//inventDim.data(InventDim::find(salesLine.InventDimId));

    salesLine.modifyItemDim(inventDim, fieldnum(InventDim,InventLocationId),  InventTable::find(salesLine.ItemId).DimGroupId);

    InventMovement::bufferSetTransQtyUnit(salesLine);

    salesLine.setDeliveryDateControlType(InventDim::findOrCreate(inventDim).InventDimId);

    SalesLine::modifySalesQty(salesLine,inventDim);

    SalesLine_ds.refresh();

    //info( strFmt('AfterChange Invent Color and Location: %1 and %2', inventDim.InventColorId, inventDim.InventLocationId) );

    // copying back

    SalesLine.SalesId = salesTable.SalesId;

    SalesLine.CustAccount = salestable.CustAccount;

    SalesLine.CurrencyCode = salesTable.CurrencyCode;

    SalesLine.CustGroup  = salesTable.CustGroup;

    SalesLine.SalesType  = salesTable.SalesType;

    SalesLine.ReceiptDateRequested  = copyFromSalesLine.ReceiptDateRequested;

    SalesLine.ShippingDateRequested = copyFromSalesLine.ShippingDateRequested;

    salesLine.SalesQty           =  copyFromSalesLine.SalesQty;

    salesLine.SalesUnit          = copyFromSalesLine.SalesUnit;

    salesLine.QtyOrdered         = copyFromSalesLine.QtyOrdered;

    SalesLine_ds.refresh();

}

Comments, questions are always welcome, happy DAXing :)

Programatically Add Webpart to all Pages only once

Programatically add a webpart to all the pages in a Site collection - let's start with the reason why you might need to do this. During my portal upgrade from MOSS 2007 to SP 2010 i had an issue where all the pages in a particular site collection was showing an empty space on the left side (Figure 1).
Simple fix for this one is add a content editor webpart on the page with the following javascript:

Figure 1

 <script type="text/javascript">
document.getElementById("s4-leftpanel").style.display = 'none';
document.getElementById("MSO_ContentTable").style.marginLeft = "10px";
</script>

This solves the issue for current page, but i have the same issue for more than 100 pages. To automate this i have exported my content editor webpart(with javascript fix)  and uploaded to SiteCollection's WebParts gallery and named it "Hide Quik Launch.dwp"(it is really hiding the Out-of-box quick launch html element).
Here is the code to programatically deploy this webpart to all the pages in my sitecollection, in short it looks for all the aspx pages in each site and adds this webpart to the page only if it doesn't have it.






public class Program
    {
        static void Main(string[] args)
        {
            SPSite sc = null;
            SPWeb pc = null;
            string SPSiteUrl = ConfigurationManager.AppSettings["SPSiteUrl"];
            try
            {
                using (sc = new SPSite(SPSiteUrl))  //("http://sansp2010dev:8010/sites/information"))
                {
                    using (pc = sc.OpenWeb())
                    {              
                        AddWebPartToCurrWeb(pc, pc);   //top site
                        foreach (SPWeb currWeb in pc.Webs)
                        {
                            try
                            {     AddWebPartToCurrWeb(currWeb, pc);    // subsites                        }
                            finally
                            {
                                if (currWeb != null)
                                    currWeb.Dispose();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {                Console.WriteLine(ex.Message);            }


            Console.WriteLine("Done!");
            Console.ReadKey();
        }


 private static void AddWebPartToCurrWeb(SPWeb currWeb, SPWeb pc)
        {
            Console.WriteLine("In " + currWeb.Title + ":");
            SPList currList = currWeb.Lists["SitePages"];
            if (currList != null)
            {
                foreach (SPListItem currItem in currList.Items)
                {
                    if (currItem.Name.EndsWith("aspx"))
                    {
                        XmlReader xmlReader = GetWebPartXML(pc, "Hide Quick Launch.dwp");
                        bool currPageAlreadyHasWebpart = PageContainsHideQuickLaunch(currWeb, "/SitePages/" + currItem.Name);


                        if (xmlReader != null && !currPageAlreadyHasWebpart)
                        {
                            SPFile currPage = currWeb.GetFile(currWeb.Url + "/SitePages/" + currItem.Name);
                            if (currPage.Item == null)
                                continue;


                            using (SPLimitedWebPartManager webPartMgr = currPage.GetLimitedWebPartManager(PersonalizationScope.Shared))
                            {
                                string errorMessage;
                                System.Web.UI.WebControls.WebParts.WebPart ceWP = webPartMgr.ImportWebPart(xmlReader, out errorMessage);
                                Console.WriteLine(currPage.Name + " - Error if any: " + errorMessage);
                                webPartMgr.AddWebPart(ceWP, "LeftColumn", 1);
                                Console.WriteLine("Added Hide Quick Launch webpart to " + currPage.Name);
                            }
                        }
                    }
                }
            }
        }


private static bool PageContainsHideQuickLaunch(SPWeb currWeb, string currPageName)
        {
            bool found = false;
            SPFile currPage = currWeb.GetFile(currWeb.Url + currPageName);
            if (currPage.Item == null)
                return found;


            using (SPLimitedWebPartManager webPartMgr = currPage.GetLimitedWebPartManager(PersonalizationScope.Shared))
            {
                foreach (System.Web.UI.WebControls.WebParts.WebPart wp in webPartMgr.WebParts)
                {
                    if (wp.Title.Contains("Hide Quick Launch"))
                    {
                        found = true;
                        break;
                    }
                }
            }


            return found;
        }


private static XmlReader GetWebPartXML(SPWeb pc, string webPartName)
        {
            XmlReader xmlReader = null;
            try
            {
                SPQuery query = new SPQuery();
                query.Query = string.Format("<Where><Eq><FieldRef Name='FileLeafRef'/><Value Type='File'>{0}</Value></Eq></Where> ", webPartName);
                SPList webPartGallery = pc.GetCatalog(SPListTemplateType.WebPartCatalog);
                SPListItemCollection webParts = webPartGallery.GetItems(query);
                if (webParts != null && webParts.Count != 0)
                {
                    Stream xmlStream = webParts[0].File.OpenBinaryStream();
                    StreamReader reader = new StreamReader(xmlStream);
                    StringReader strReader = new StringReader(reader.ReadToEnd());
                    xmlReader = XmlReader.Create(strReader);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return xmlReader;
        }


Happy Coding - San
Reference:  zabistop.blogspot.com

Extract CRM data from the backend into Excel

General way of exporting CRM data into excel is by out-of-box export functionality that CRM provides, it works very well but you will see the performance hit on CRM Application server because everything has to go through CRM web services. Alternate way for this one is to get the data directly from SQL Server through the data tab in MS Office Excel. “From Microsoft Query” option is powerful, it supports SQL queries to tables, views and much more.

Let’s go thru this in simple steps:                     

1. select Data tab –> From Other Sources –> From Microsoft Query.                                                             
2. On Choose Data source window, select <New Data Source> , click OK.                                                  
3. On Create New Data source window give some name : MyQuery1,  select driver as “SQL Server”, click connect.                                                                                                                                                  
4. On SQL server login window give Server name, leave the default options and click OK.                              
5. you would come back to “Create New Data Source” window and here leave option 4 blank and click OK.
6. you would come back to “Choose Data source” window, make sure you the new data source is selected and click OK.                                                                                                                                          
7. “Microsoft Query” window opens up, click the SQL button from toolbar to enter the SQL query , sample query that I have used here is to retrieve phone call activities that were created on 10/06/2011:

select fap.subject , fap.activitytypecodename, fap.statecodename , fap.prioritycodename  , fap.createdon, fap.modifiedon , fap.description   from [Trading_Technologies_MSCRM].[dbo].[FilteredActivityPointer] fap
where (fap.createdon >'20101006') and (fap.createdon < '20101007')
and (fap.activitytypecodename = 'Phone Call')

8. Click OK on SQL window, you would see the result data in Microsoft Query window                                    
9. On this window, File –>  “Return Data to Excel”

10. You would see a “Import Data” window popped up, leave the default selections and click OK, you would see some halt – it is retrieving the data (check the status bar on Excel) and the you will see excel sheet populated with data:

That is it, hope this would be useful for you in CRM and non-CRM scenarios