AX 2012 get list of security roles from SQL with label

--List of security roles

select sr.RECID [Role Id], case when left(sr.Name, 1) = '@' then xr.Text else sr.NAME end as [Role name]

from AXPRDDB_model.dbo.SecurityRole sr with (nolock) 

left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when left(sr.NAME, 1) = '@' then (case when cast(SUBSTRING(sr.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(sr.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us'

--List of users with roles select sur.USER_ [User Id], sr.AotName, case when left(sr.Name, 1) = '@' then xr.Text else sr.NAME end as [Name], sur.VALIDFROM, sur.VALIDTO from AXPRDDB.dbo.SECURITYUSERROLE sur with (nolock) inner join AXPRDDB_model.dbo.SecurityRole sr with (nolock) on sur.SECURITYROLE = sr.RecId left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when left(sr.NAME, 1) = '@' then (case when cast(SUBSTRING(sr.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(sr.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us' --List of users with duties select t.AOTNAME, case when left(t.Name, 1) = '@' then xr.Text else t.NAME end as [Role name], case when left(t.DESCRIPTION, 1) = '@' then xrd.Text else t.DESCRIPTION end as [Role name] from SecurityRole s with (nolock) inner join  SecurityRoleTaskGrant g with (nolock) on s.RECID = g.SECURITYROLE inner join SecurityTask t with (nolock) on g.SECURITYTASK = t.RECID left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when left(t.NAME, 1) = '@' then (case when cast(SUBSTRING(t.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(t.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us' left join AXPRDDB_model.dbo.ModelElementLabel xrd with (nolock) on (case when left(t.DESCRIPTION, 1) = '@' then (case when cast(SUBSTRING(t.DESCRIPTION, 5, 7) as int) = xrd.LabelId and SUBSTRING(t.DESCRIPTION, 2, 3) = xrd.Module then 1 else 0 end) else 0 end) = 1 and xrd.Language = 'en_us' where s.AOTNAME = 'LedgerAccountant'

Cannot Edit A LIne in SourceDocumentLine....

http://community.dynamics.com/ax/f/33/p/148665/337531#337531

http://community.dynamics.com/ax/f/33/p/94859/314786#314786

1.       As checked,  the table “PurchLine” and table “SourceDocumentLine” relation is one-to-one, but a record in SourceDocumentLine don’t have a correspondent purchase line(RecId = 6089).

update SourceDocumentLine set SourceDocumentHeader = 9999999 where RecId = 6089

2.        I tried to unbind their relation, the PO confirm is can be.

 Conclusion:

              Unbind record SourceDocumentLine relation to PurchLine, then be ok.

AX SSRS - copy report design from one VS to another VS

Purpose of such action

Scenario:

SSRS Report has alot of report design. If export/import via XPO, may cause AOT to hang, or load too long while importing. This works for situation where you only want to update one report design. Only works where you have access rights to develop in Visual Studio.

Steps:

1. Open visual studio, browse through the Application Explorer and locate the SSRS > Report, and open the report you required. 
2. Open only the desired report design, nothing else. 
3. Select any object, right click and open its properties (textbox properties). Copy the textbox name (for example, textbox123).
4. In the report designer, find or click Ctrl + f, find any object in the report design, in this case, find "textbox123". It will open the XML code of the report.
5. Copy the XML code, do the same at the destination Visual Studio, and paste the code there.

copy object name

find object name to open the XML codes

Create and implement sequence number

Scenario:

New Reservation code, ST

Step 1:

Create new sequence number into number sequence setting

Step 2:

Insert code into table layer method.

Step 3:

Implement methods into form base datasource methods.

Custom lookup based on fields in Form

Sample situation: Payment category field need to filter based on Sales Order sales type (SalesTable.AvSalesType).
 If sales type == sales contract then filter payment category where AvTurnIn == Yes.
If sales type == service invoice then filter payment category where AvServiceInvoice == Yes.

Code:
Override AvPayment.AvPaymentCategory.lookup().

public void lookup(FormControl _formControl, str _filterStr)
{
    Query query = new Query();
    QueryBuildDataSource queryBuildDataSource;
    QueryBuildRange queryBuildRange;
    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(AvPaymentCategory), _formControl);
    sysTableLookup.addLookupField(fieldNum(AvPaymentCategory, AvPaymentCateogry));
    sysTableLookup.addLookupField(fieldNum(AvPaymentCategory, Description));
    queryBuildDataSource = query.addDataSource(tableNum(AvPaymentCategory));

    if (SalesTable.AvSalesType == AvSalesType::SalesContract)
    {
        queryBuildRange = queryBuildDataSource.addRange(fieldNum(AvPaymentCategory, AvTurnIn));
    }
    else
    {
        queryBuildRange = queryBuildDataSource.addRange(fieldNum(AvPaymentCategory, AvServiceInvoice));
    }

    queryBuildRange.value('1');

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();

}

How to use Code Table create Enum

1. Go to Form AvCodeTable to create the new code:

2. Create new EDT extend from AvCodeTable:

3. a) Create Table References:

    b) Create filter Value 5 (select value filter from the Form AvCodeTable field Code type ID refer to step 1) :

4. Add the field to Table:

5. Add relation 

6. Able to browse it at table now:

Adding FileNameOpen Extended Data Type to a Form

After add EDT FileNameOpen to form using New -> stringEdit, 

add some form methods:

str fileNameLookupFilename()
{
    return '';
}

str fileNameLookupTitle()
{
    return "@SYS53008";
}

str fileNameLookupInitialPath()
{
    return '';
}

container fileNameLookupFilter()
{
    return ['All files','*.*'];
}

After get the file path, call insert method:

public void AvInsertAttachment(filename file, int i)

{    

    DocuRef docuRef;

    DocuActionArchive archive;

    ;           

    

    if(file)

    {

        ttsbegin;

        docuRef.clear();

        docuRef.RefRecId = DirPartyEntity.RecId;

        docuRef.RefTableId = tableNum(CustTable);

        docuRef.RefCompanyId = DirPartyEntity.dataAreaId;

        docuRef.Name = strFmt("Consultant Attachment %1", i);

        docuRef.TypeId = 'File';

        docuRef.insert();

        archive = new DocuActionArchive();

        archive.add(docuRef, file);

        ttsCommit;

    }

}

Result: