lunes, 25 de julio de 2011

A null value not allowed has been selected in the database.

I'm getting the error:
No se puede seleccionar un registro en Proyectos (ProjTable).
Se ha seleccionado un valor NULL no permitido en la base de datos.
When we have a null value selected from the database, the table buffer is unable to show the values to the form, nor the table view in the AOT. Here is what I found in SQLServer:
In my case the answer was easy - these are string fields so I could edit them directly in the db.
UPDATE [DynamicsAXDev].[dbo].[PROJTABLE]
   SET [EVE_PROJWFSENTCOMMENT] = ''
 WHERE [EVE_PROJWFSENTCOMMENT] is null;
I'm wondering if it was because I'm importing records using an 'old' ProjTable as my colleague informs me that he added a new field to the table recently. 

If you have a large table with both many columns and rows then you will need to use some SQL and Select columns with NULL values only. I'll copy the code here to make me look intelligent:
declare @col varchar(255), @cmd varchar(max) 
DECLARE @TableName Varchar(100) 
SET @TableName='ProjTable' 

DECLARE getinfo cursor for 
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID 
WHERE t.Name = @TableName 
 
OPEN getinfo 
 
FETCH NEXT FROM getinfo into @col 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM ' + @TableName + ' WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end' 
    EXEC(@cmd) 
 
    FETCH NEXT FROM getinfo into @col 
END 
 
CLOSE getinfo 
DEALLOCATE getinfo
Do check out MSDN's Null Values for Data Types article as to what to insert into these evil null value fields when they appear.

Even after performing all of the above I was still receiving the error message. It was time to close and reopen my Ax client.

miércoles, 20 de julio de 2011

Dialog class and the 'Next' big thing

Many effects are very easy to achieve in Ax 2009 but others leave us searching the interweb for a solution.  We needed a 'Next' (or 'Continue') and 'Cancel' button in a simple message box with instructions.  For some reason the standard 'Ok' button text was not sufficient. 

   // Dialog with instructions
    Dialog                          dialog;
    FormBuildCommandButtonControl   okButton;
    #define.okButton('OkButton')    //See Dialog class
    ;
    
    dialog = new Dialog("@SYS76421");

    // Instructions, Instructions, Instructions
    dialog.addText("@EVE580");
    okButton = dialog.formBuildDesign().control(#okButton);
    if (okButton)
    {
        // Select Next to resume (EN) / Selecciona Siguiente para continuar (ES)
        okButton.helpText("@EVE581");
        // Next (EN) / Siguiente (ES)
        okButton.text("@SYS6441");
        okButton.defaultButton(true);
    }

    dialog.run();
    if (dialog.closedOK())
    {
        Box::info("We closedOK!");
        return;
    } else {
        return;
    }

As always, someone cleverer than me was able to grab a handle to what was going on.  In my version of Axapta the commented code in the Dialog.initButtons() function will get you on your way to get a handle on those Dialog buttons.

viernes, 15 de julio de 2011

Enumerator value, it's symbol

I stumbled across an useful tid-bit of code on the web yesterday, courtesy of Preston A. Larimer.
/// enumName('valueCriteria', valueCriteria::Max) returns Max
static str enumName(str _Enum, anyType _enumValue)
{
   DictEnum dictEnum;
   ;
   dictEnum = new DictEnum(xutilelements::getNode(
       xutilelements::find(utilelementtype::Enum,_Enum)).applObjectId());

   return dictEnum.index2Symbol(_enumValue);
}
It took me so long to find as I was searching for the 'value' field but no matter what I did I was receiving the asociated translated text - ValueCriteria ⇒ "Maximum" in the above ejample.

There are no checks, however, so add them for empty parameter values.

Edit:  Ohh I just found a similar post where someone else not only got there first, but they arrived in a less sweaty state as well.
strfmt("%1", enum2symbol(enumnum(WorkTimeControl), workTimeControl));

domingo, 10 de julio de 2011

Button hell

You've been asked to modify an entity such as that the purchase order or projects tables/forms. Part of the changes implies that you have to disable certain functionality and/or input controls on the form. Hey, it's nice to have this level of detail in the client requirements document isn't it?

First of all we found that adding new fields and related helper tables was easy. It's one of the advantages of Ax 2009 that we're getting quite used to when compared to other frameworks.

Implementing the 'side effects' was a little more strenuous, such as when a project is cancelled we had to apply checks to see if there are any open purchase orders beforehand or we're required to deactivate any associated budget/predicted costs.

Now finally the easy part, deactivating the buttons or sub-menu options on the form... 

Eeek!?

It's not as trivial as you first imagine. And it takes getting used to. Let's take a look at the ProjTable form. We have two methods for updating the buttons and the form input controls, setButtonAccess and setFieldAccess. As the form is so complex they've logically split up the work within setButtonAccess into sub groups such as setButtonInvoice and setButtonFunction. But it's what each one of these then do which takes a little more time to get to grips with. Each method will instantiate the same class and use a method to control the button's enabled or visibility status, thus:
    ProjStatusType projStatusType = projTable.status();
    ;
    ctrlInvoices.enabled(projStatusType.ctrlInvoices());
It's a helper class that we really should familiarize ourselves with and some of its functionality is used to enable or disable the Invoice control, depending upon the project. It's a little complex; however, as we have both project stages and project types to contend with. Here is a simplistic view of these classes:
The ProjStatusType class (which is constructed from a sub class such as ProjStatusType_Created or ProjStatusType_ReportedFinished) instanciates the ProjTableType class which is actually constructed from a subclass such as ProjTableType_Internal or ProjTableType_Cost.  In my brain it took 5 minutes to understand plus another 10 as the Morph X editor is not the most friendly environment to work in.  So when we call the ctrlInvoices() method we're possibly calling a method in ProjStatusType or ProjStatusType_Created or ProjTableType or ProjTableType_Internal, we'll have to go and investigate each class each time we need to change the business logic on each button or input control.  Eeek!  This is exactly what object orientated design is about, however.

When we start, we may just IGNORE the helper class and implement a simple switch statement on the ProjTable_ds.active() method like the pseudo code in the following one and then go for a coffee happy in the knowledge that we've finished ahead of schedule:
    switch (projTable.Status)
    {
        case ProjStatus::Approved :
            CtrlProjValidateSetupEmpl.enabled(projTable.Type != ProjType::Internal);
            CtrlProjValidateSetupCategory.enabled(projTable.Type != ProjType::Internal);
            CtrlPosting.enabled(true);
            CtrlExternal.enabled(true);
            projTable_ds.object(fieldnum(ProjTable, Dimension)).enabled(false);
            break;

        case ProjStatus::PendingApproval :
        case ProjStatus::Created :
            CtrlProjValidateSetupEmpl.enabled(projTable.Type == ProjType::Internal);
            CtrlProjValidateSetupCategory.enabled(projTable.Type == ProjType::Internal);
            CtrlPosting.enabled(false);
            CtrlExternal.enabled(false);
            projTable_ds.object(fieldnum(ProjTable, Dimension)).enabled(true);
            break;

        case ProjStatus::Canceled :
            CtrlProjValidateSetupEmpl.enabled(false);
            CtrlProjValidateSetupCategory.enabled(false);
            CtrlPosting.enabled(false);
            CtrlActivity.enabled(false);
            CtrlExternal.enabled(false);
            projTable_ds.object(fieldnum(ProjTable, Dimension)).enabled(false);
            break;

        default :
            CtrlProjValidateSetupEmpl.enabled(false);
            CtrlProjValidateSetupCategory.enabled(false);
            CtrlPosting.enabled(false);
            CtrlActivity.enabled(false);
            CtrlExternal.enabled(false);
            projTable_ds.object(fieldnum(ProjTable, Dimension)).enabled(false);
            break;
    }
Easy to understand isn't it? Took us a few minutes and no one else ever looks at our code anyway, right?

Problemo, as they say in Spanish.  It's not that maintainable for others, or ourselves in the long term, plus what happens if we have changed the ctrlInvoice button on the ProjTable form but not from other references to the project table.  These classes are used not only on this particular form but all over the place and if we took just a little effort to modify them to the clients requirement we will be pleasantly surprised to find out that we can't create an invoice for a closed project associated with a supplier as we modified the helper class method in that other form.  Less buggy code, and our original ProjTable form is still relatively clean without that switch statement.

Adding a new method to deactivate a button using this technique requires at the very least new methods to be created in two different classes, more so if we are applying business logic to various project types and statuses in use.

Developer tip: Use the ClipX application for those repetitive copy/paste operations. I can also recommend listening to some really racy music to make your life seem 'not so bad' and pass your time away.

Finally another advantage of using this approach is that after we've wired in the class to each button we can recompile the class and change the business logic without having to recompile/restore the original ProjTable form.  Sweet!

And then suddenly it's all broken.

There's an option at the top of the project form to include sub-projects. Our classes and inheritance applied to our buttons is suddenly rendered useless when we have a project that's marked as closed but has a child project that is in progress.... The code just marks the visibility/enabled option as true rather than trying to recurse though the sub-projects checking for their status.  All of our helper classes' architecture aren't wired up to recurse through the child projects.  Bah, humbug.

I also see in the code that the management of the buttons (setButtonAccess) is handled in a different function to that of the enabling/disabling of the fields (setFieldAccess). Fine, but... The two functions are not called from the same parent functions which seems wrong to me. setButtonAccess Is called when we requery the form, change record in the form or change the selected record's status while setFieldAccess called when we change or update the record in the form. I don't see why these to functionalities are launched at different times in the form life cycle.

In conclusion

Many of these complex forms use these helper classes.  After a little effort they do make our life easier and create less buggy code.  Take a little time to learn them and investigate what they do.

miércoles, 6 de julio de 2011

A few interesting string sizes

...Well they're interesting to *me* at least.

EDT NameString SizeNotes
Title10 
XXXString2020I make generic strings for myself.
NameAlias20 
String3030Don't forget to create a new Label and HelpText
String5050Don't forget to create a new Label and HelpText
Str6060Don't forget to create a new Label and HelpText
Name60 
Description60 
ErrorTxt80 
NotesLine254 
CCNotesShort500'Note', 10 lines high on the forms
SysImportDescription1000 
LabelString1999 
Notes(Memo)If you use lots of these babies consider creating a separate 0..1 table

A few observations. CCNotesShort is module specific and there aren't that many generic strings that we can use. When making any new EDT we prepend our vendor code to it. Mega CorpMEGString20.  I'd prefer to make our own 'generic' EDT such as in the previous example rather than use NameAlias for a 20 length string.  The important thing is to reuse them once they're created.

There's more[+]
Rajdip Das has publicised his job for iterating through the EDT looking for strings of a certain length.  As is always the case, I can't just copy and paste it, I have to tinker...
/// http://dax-world.blogspot.com/2010/01/edt-find-with-string-length-value.html
/// IDS:20110701 EVE
static void EVE_Raj_EDTStringfind(Args _args)
{
    Dictionary dict;
    DictType dictType;
    TreeNode treeNode = TreeNode::findNode("Data Dictionary\\Extended Data Types\\ABCModelType");

    #AviFiles
    sysExcelApplication excel;
    SysExcelWorkbooks books;
    SysExcelWorkbook book;
    SysExcelWorkSheets sheets;
    SysExcelWorkSheet sheet;
    com com;
    Integer row = 2;

    Dialog d = new Dialog("Input Box");
    DialogField dfStrValue;
    Integer tmpStringLen = 0;
    Integer tmpStringLenMax, tmpStringLenMin;                       // IDS:20110701 EVE

    SysOperationProgress progress = new SysOperationProgress();
    ;

    // Dialog initialised and accept value and asign
    dfStrValue = d.addField(typeId(Integer),"Enter EDT String Length");
    if(!d.run())
        return;

    tmpStringLen = dfStrValue.value();
    tmpStringLenMax = tmpStringLen + real2int(tmpStringLen * 0.25);  // IDS:20110701 EVE
    tmpStringLenMin = tmpStringLen - real2int(tmpStringLen * 0.25);  // IDS:20110701 EVE

    if(!tmpStringLen)
        throw error("Plaese Enter Valid Number.");

    // Excel Object initialisations
    excel = sysExcelApplication::construct();
    books = excel.workbooks();
    book = books.add();
    sheets = excel.worksheets();
    sheet = sheets.itemFromNum(1);
    com = sheet.comObject();

    // Heading Initialisations
    sheet.cells().item(2,1).value("EDT Name");
    sheet.cells().item(2,5).value("Str Size");
    sheet.cells().item(2,6).value("Label");

    // Progress Bar Initialisations
    progress.setCaption("Generating Lines");
    progress.setAnimation(#AviTransfer);
    progress.setTotal(500);


    // loop for searching EDTs
    while(treeNode)
    {
        dict = new Dictionary();
        dictType = dict.typeObject(dict.typeName2Id(treeNode.treeNodeName()));

        //if(dictType.stringLen() == tmpStringLen)
        if(dictType.stringLen() >= tmpStringLenMin                  // IDS:20110701 EVE
            && dictType.stringLen() <= tmpStringLenMax)
        {
            row++;
            sheet.cells().item(row,1).value(treeNode.treeNodeName());
            sheet.cells().item(row,5).value(dictType.stringLen());
            sheet.cells().item(row,6).value(dictType.label());

            progress.setText(strfmt('Copying EDT %1.', treeNode.treeNodeName()));
            progress.setCount(row);
        }

        treeNode = treeNode.AOTnextSibling();
    }

    sheet.cells().item(1,1).value(strfmt("Found EDT: %1.", row)); // Count & Print for Filtered EDT

    box::info(strfmt("Total EDT Found: %1.", row), "Information");
    excel.visible(true);
}
Have I missed any generic strings that you occasionally use?

domingo, 3 de julio de 2011

No olvide TTSBEGIN/TTSCOMMIT y la cláusula FORUPDATE.

I know that I'm working in Spanish but I can't believe that Google throws (threw!) up an empty search result for the title of this blog.  I'm here to save the proud Hispanic population...

Here is my original error message:
No se puede editar un registro en Relaciones empresariales (smmBusRelTable).

No se puede completar la operación porque no se ha seleccionado el registro para su actualización. No olvide TTSBEGIN/TTSCOMMIT y la cláusula FORUPDATE.

Here are two of the most common reasons:
1. We forgot to add the forupdate clause in the select statement:
select forupdate smmBusRelTable
    where smmBusRelTable.PartyType == DirPartyType::Organization

2. Annoying one when you find out it's for this reason:
smmBusRelTable = smmBusRelTable::find(busRelAccount, true);
We forgot to add the second parameter 'true', to be able to commit changes to the buffer returned. All that the standard find function does is add a smmBusRelTable.selectForUpdate(true); line.

3. The least common one for me are unbalanced ttsbegin + ttscommit / ttsabort commands.

If you can't find the solution to your error here, try Googling: The operation cannot be completed, since the record was not selected for update. Remember TTSBEGIN/TTSCOMMIT as well as the FORUPDATE clause.

viernes, 1 de julio de 2011

Select current financial Start/End Dates

I think this blog is going to be about publicising my mistakes with Axapta, let's take the simple problem of obtaining the Start and End of the current financial year, a.k.a. fiscal year.

To begin with, it started off with the simplest of formulas to get the first and last day of the year, thus:
/// Returns a container of dates with the start and end dates of the current year.
/// Not to be confused with the start and end of the current financial year.
/// Example '[01/01/2011,31/12/2011]'
public static container getCurrentYearStartEnd()
{    
    int     yr = DateTimeUtil::year(DateTimeUtil::getSystemDateTime());
    date    dtStart, dtEnd;
    ;
    
    // see http://sysdictcoder.com/blog/constructing-date-values/
    //dtStart = str2Date(strfmt("01/01/%1",yr), 123);
    //dtEnd   = str2Date(strfmt("31/12/%1",yr), 123);

    dtStart = mkDate(1, 1, yr);
    dtEnd   = mkDate(31, 12, yr);

    return [dtStart, dtEnd];
}
Now Spain is quite reasonable, they work their finances from the 1st of January to the 31st of December.  We're not so fortunate in multinational companies however, so we need to do this right.  Revisiting the LedgerPeriod buffer we can see that they have already done the hard work for us, it's sweet.
/// Returns a container of dates with the start and end dates of the current financial year.
/// In Spain the fiscal year starts on January 1 and ends December 31.
/// The Australian government's financial year begins on July 1 and concludes on June 30 of the following year.
/// @link http://en.wikipedia.org/wiki/Fiscal_year
/// Example '[01/04/2011,31/03/2012]'
public static container getCurrentFinancialYearStartEnd()
{    
    date frmdt = DateTimeUtil::date(DateTimeUtil::getSystemDateTime());
    ;
    return LedgerPeriod::findFiscalYearDates(frmdt);
}
That wasn't so bad after all was it?  Let us know if there was an even easier/more correct way.