Add code :
qbsSum.addGroupByField(fieldNum(InventSum,InventDimId));
in the “modifyQuery” method on the “InventDimCtrl_Frm_OnHand” class
Example code
query.clearGroupBy(); qbsSum.addGroupByField(fieldNum(InventSum,ItemId)); qbsSum.addGroupByField(fieldNum(InventSum,InventDimId));
Result:
static void TIDgetTax(Args _args)
{
TaxAmount tax;
CustInvoiceTrans CustInvoiceTrans;
;
CustInvoiceTrans = CustInvoiceTrans::findRecId(5637155209);
tax = Tax::calcTaxAmount(CustInvoiceTrans.TaxGroup, CustInvoiceTrans.TaxItemGroup, CustInvoiceTrans.InvoiceDate,
CustInvoiceTrans.CurrencyCode, CustInvoiceTrans.LineAmount, TaxModuleType::FreeTxtInvoice);
info(strfmt('%1', tax));
}
static void TID_ExecuteBatchJobDistribution(Args _args)
{
BatchHeader header;
SysRecurrenceData sysRecurrenceData;
Batch batch;
BatchJob batchJob;
RetailConnScheduleRunner _RetailConnScheduleRunner; // Class extends RunBaseBatch
BatchInfo processBatchInfo;
BatchRetries noOfRetriesOnFailure = 4;
;
// Setup the RunBaseBatch Job
header = BatchHeader::construct();
_RetailConnScheduleRunner = new RetailConnScheduleRunner();
processBatchInfo = _RetailConnScheduleRunner.batchInfo();
processBatchInfo.parmRetriesOnFailure(noOfRetriesOnFailure);
processBatchInfo.parmCaption(_RetailConnSchedule.Name); // Description Batch Job
processBatchInfo.parmGroupId('RTL'); // Batch Gorup
processBatchInfo.parmBatchExecute(NoYes::Yes);
header.addTask(_RetailConnScheduleRunner);
// Set the recurrence data
sysRecurrenceData = SysRecurrence::defaultRecurrence();
SysRecurrence::setRecurrenceStartDateTime(sysRecurrenceData, DateTimeUtil::addSeconds(DateTimeUtil::utcNow(), 20)); // Set range of recurrence
SysRecurrence::setRecurrenceNoEnd(sysRecurrenceData);
SysRecurrence::setRecurrenceUnit(sysRecurrenceData, SysRecurrenceUnit::Minute); // Set reccurence pattern
header.parmRecurrenceData(sysRecurrenceData);
// Set the batch alert configurations
header.parmAlerts(NoYes::No, NoYes::Yes, NoYes::No, NoYes::Yes, NoYes::Yes);
header.save();
// Update the frequency to run the job to every two minutes
ttsbegin;
select forupdate batchJob
join batch
where batchJob.RecId == batch.BatchJobId
&& batch.ClassNumber == classnum(RetailConnScheduleRunner);
sysRecurrenceData = batchJob.RecurrenceData;
sysRecurrenceData = conpoke(sysRecurrenceData, 8, [10]);
batchJob.RecurrenceData = sysRecurrenceData;
batchJob.update();
ttscommit;
}
Example on Product Receipt form to show name of vendor. Override init() method under Vendpackingslipjour datasource and write following code;
public void init()
{
super();
vendPackingSlipJour_ds.cacheAddMethod(tableMethodStr(VendPackingSlipJour, vendName), false);
}
public static void FNDMarkInventTrans(InventTransId _inventTransId,InventTransId _refInventTransId,InventQty _qtyTomark)
{
InventTrans issueInventTrans;
TmpInventTransMark tmpInventTransMask;
Map mapMarkNow;
container con;
real qty;
Map mapTmp;
MapEnumerator mapEnumerator;
InventTransOriginId issueInventTransOriginId =
InventTransOrigin::findByInventTransId(_inventTransId).RecId;
InventTransOriginId receiptInventTransOriginId =
InventTransOrigin::findByInventTransId(_refInventTransId).RecId;
InventQty qtyToMark = _qtyTomark;
ttsBegin;
issueInventTrans = InventTrans::findByInventTransOrigin(
issueInventTransOriginId);
[con, qty] = TmpInventTransMark::packTmpMark(
InventTransOrigin::find(issueInventTransOriginId),
issueInventTrans.inventDim(),
issueInventTrans.Qty);
mapTmp = Map::create(con);
mapEnumerator = mapTmp.getEnumerator();
while (mapEnumerator.moveNext())
{
tmpInventTransMask = mapEnumerator.currentValue();
if (tmpInventTransMask.InventTransOrigin == receiptInventTransOriginId)
{
tmpInventTransMask.QtyMarkNow = qtyToMark;
tmpInventTransMask.QtyRemain -= tmpInventTransMask.QtyMarkNow;
mapMarkNow = new Map(Types::Int64, Types::Record);
mapMarkNow.insert(tmpInventTransMask.RecId, tmpInventTransMask);
TmpInventTransMark::updateTmpMark(
issueInventTransOriginId,
issueInventTrans.inventDim(),
-qtyToMark,
mapMarkNow.pack());
}
}
ttsCommit;
}
reference code :
https://community.dynamics.com/ax/b/dynamicsaxnotesonthecuffs/archive/2013/01/08/marking-from-code-part-2
source code from form : BudgetControlStatistics
example code :
static void TIDF_getBudgetAvailable(Args _args)
{
//class and table
BudgetControlStatisticsManager budgetControlStatsManager = new BudgetControlStatisticsManager();
//table
BudgetTmpControlStatistics currentBudgetTmpControlStatistics;
BudgetCycle budgetCycle;
BudgetCycleTimeSpan budgetCycleTimeSpan;
FiscalCalendar fiscalCalendar;
DimensionAttributeValueCombination dimensionAttributeValueCombination;
DimensionHierarchy dimensionHierarchy;
//variable
DimensionValue dim1;
container conDimValue;
MainAccountNum mainAccountNum;
RecId ledgerDimension;
//financial dimension
mainAccountNum = "6100-1001";
dim1 = "5102";
select firstonly dimensionAttributeValueCombination
where
dimensionAttributeValueCombination.DisplayValue == strFmt("%1-%2",mainAccountNum,dim1)
&& dimensionAttributeValueCombination.LedgerDimensionType == LedgerDimensionType::BudgetControl;
//budget cycle
fiscalCalendar = fiscalCalendar::findByCalendarId("2016");
BudgetCycleTimeSpan = budgetCycleTimeSpan::findByNameAndCalendar("SEM",fiscalCalendar.RecId);
BudgetCycle = budgetCycle::findBudgetCycleByDate(BudgetCycleTimeSpan.RecId,today());
currentBudgetTmpControlStatistics = budgetControlStatsManager.fillBudgetTmpControlStatistics(Ledger::current(),
'ALL',
BudgetControlStatisticsPeriodOption::FiscalPeriod,
BudgetBalanceType::Accumulated,
BudgetCycle.RecId,
dimensionAttributeValueCombination.RecId,
0,
0,
true,
NoYes::No,
true);
while select currentBudgetTmpControlStatistics
{
info(strFmt("%1 -- %2 ",currentBudgetTmpControlStatistics.PeriodStartDate,currentBudgetTmpControlStatistics.TotalFundsAvailableAmountMST));
}
}
result :
Example code :
static void TIDF_autoApproveWFOvertime(Args _args)
{
WorkflowWorkItemTable WorkflowWorkItemTable;
while select WorkflowWorkItemTable
where
WorkflowWorkItemTable.UserId == '32501112'
&& workflowWorkItemTable.Type == WorkflowWorkItemType::WorkItem
&& workflowWorkItemTable.Status == WorkflowWorkItemStatus::Pending
&& WorkflowWorkItemTable.RefTableId == tableNum(HrsOvertime)
{
WorkflowWorkItemActionManager::dispatchWorkItemAction(
WorkflowWorkItemTable,
"Auto Approve by ADMIN", // comment
'32501112', // << user ID
WorkflowWorkItemActionType::Complete,
"HRSOvertimeApprove", // << menu item action approval workflow
false); //is not web Menu Item
}
}
example code :
static void TIDF_resumeWFOverTime(Args _args)
{
WorkflowTrackingStatusTable WorkflowTrackingStatusTable;
HrsOvertime hrsOverTime;
while select WorkflowTrackingStatusTable
where
WorkflowTrackingStatusTable.TrackingStatus == WorkflowTrackingStatus::Faulted
&& WorkflowTrackingStatusTable.WorkflowType == WorkflowTrackingStatusWorkflowType::Workflow
&& WorkflowTrackingStatusTable.ContextTableId == tableNum(HrsOvertime)
{
//consistency check valid data
select firstOnly hrsOverTime where hrsOverTime.RecId == WorkflowTrackingStatusTable.ContextRecId;
if(hrsOverTime)
{
Workflow::resumeWorkflow(WorkflowTrackingStatusTable.CorrelationId);
}
}
}
Example Code :
display Notes TIDgetUserIdAndRoles()
{
DirPerson DirPerson;
DirPersonUser DirPersonUser;
SecurityUserRole SecurityUserRole;
SecurityRole SecurityRole;
Notes returnNotes;
str value;
;
DirPerson = DirPerson::find(this.Person);
DirPersonUser = DirPersonUser::findWorker(this.RecId);
returnNotes = strFmt("User ID : %1 \n\n ",DirPersonUser.User);
returnNotes = strFmt("%1Security Roles : \n",returnNotes);
while select * from SecurityUserRole
where SecurityUserRole.User == DirPersonUser.User
join SecurityRole
where SecurityRole.RecId == SecurityUserRole.SecurityRole
{
if(subStr(SecurityRole.Name,1,1) == '@')
{
value = SysLabel::labelId2String2(SecurityRole.Name, companyinfo::languageId());
returnNotes = strFmt("%1 %2\n",returnNotes,value);
}
else
{
returnNotes = strFmt("%1 %2\n",returnNotes, SecurityRole.Name);
}
}
return returnNotes;
}
example code :
select top 1 name from DIRPARTYTABLE inner join HcmWorker ON HcmWorker.PERSON = DIRPARTYTABLE.RECID AND HcmWorker.RECID = (select top 1 worker from HCMPOSITIONWORKERASSIGNMENT INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION = HCMPOSITION.RECID AND HCMPOSITION.POSITIONID = 'P0003' where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO);
i found a nice code for checking user workflow hierarchy approver and solved the related error.
static void navax_workflowHierarchyTester(Args _args)
{
WorkflowTypeName workflowTemplateName = 'TIDEmploymentLeave';
//TrvExpTrans trvExpTrans;
//TrvExpNumber trvExpNum = '000015'; //Expense id
HcmEmploymentLeave hcmEmploymentLeave;
str nodeId = '23511014'; //Starting worker id
WorkflowHierarchyLevel level = 0;
WorkflowContext workflowContext;
SysWorkflowTable workflowTable;
WorkflowLimitHierarchyProvider workflowLimitHierarchyProvider;
WorkflowHierarchyProviderNode workflowHierarchyProviderNode;
HRPWorkerLimit workerLimit = new HRPWorkerLimit();
HcmWorker hcmWorker;
DirPersonUser dirPersonUser;
RefRecId hcmPositionRecId;
HcmPosition hcmPosition;
container spendingCon, approvalCon;
UserId userId;
select firstOnly workflowTable
where workflowTable.TemplateName == workflowTemplateName;
select firstOnly hcmEmploymentLeave
where hcmEmploymentLeave.HRSLeaveReqId == "LV-00635" ;
workflowContext = WorkflowContext::newWorkflowContext(
curext(),
tableNum(hcmEmploymentLeave),
hcmEmploymentLeave.RecId,
workflowTable.WorkflowCorrelationId);
workflowLimitHierarchyProvider = new WorkflowLimitHierarchyProvider();
//Level 1
while (nodeId && level < 20) //20 is just a fall back. In case it goes into an endless loop.
{
workflowHierarchyProviderNode = workflowLimitHierarchyProvider.getNextNode(nodeId, level, workflowContext);
nodeId = workflowHierarchyProviderNode.getnodeId();
hcmWorker = HcmWorker::findByPersonnelNumber(nodeId);
userId = DirPersonUser::findParty(hcmWorker.Person).User;
hcmPositionRecId = HcmWorker::getPrimaryPosition(hcmWorker.RecId);
hcmPosition = HcmPosition::find(hcmPositionRecId);
spendingCon = workerLimit.getWorkerSigningLimit(
hcmWorker.RecId,
SourceDocumentRelationType::ExpenseReport,
HRPLimitType::Spending,
hcmPosition.PositionId);
approvalCon = workerLimit.getWorkerSigningLimit(
hcmWorker.RecId,
SourceDocumentRelationType::ExpenseReport,
HRPLimitType::Approval,
hcmPosition.PositionId);
info(strFmt("UserId: %1 | WorkerId: %2 | PositionId: %3 | ReportsToPosition: %4 | SpendingLimit: %5 | ApprovalLimit: %6",
userId,
hcmWorker.PersonnelNumber,
hcmPosition.PositionId,
hcmPosition.reportsToPosition(),
con2Str(spendingCon),
con2Str(approvalCon)
));
level++;
}
}
Job
static void TIDF_uploadFAwithValueModels(Args _args)
{
AssetId assetId;
AssetGroupId assetGroupId;
AssetName assetName;
AssetLocationId assetLocationId;
AssetServiceLife assetServiceLife;
AssetLifeTimeRest assetLifeTimeRest;
AssetPostingProfile assetPostingProfile;
AssetBookId assetBookId;
TransDate depreciationStartDate,LastDepreciationDate,AcquisitionDate;
RecId recIdDefaultDimension;
AssetLongDescription AssetLongDescription;
AxAssetTable axAssetTable;
//table
AssetTable assetTable;
AssetGroup assetGroup;
AssetLocation assetLocation;
AssetBook assetBook;
AssetLedger assetLedger;
container financialDimensionFromExcel;
container conDimensionName;
SysExcelApplication application = SysExcelApplication::construct();
SysExcelWorkbooks workbooks = application.workbooks();
SysExcelWorkbook workbook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
str filename;
;
startLengthyOperation();
//variable yang diisi manual
//C:\#TECTURA\Master Data
filename = "C:\\#TECTURA\\Master Data\\FA Master_Budi00.xlsx";
assetPostingProfile = "FA POSTING";
depreciationStartDate = today();
LastDepreciationDate = today();
AcquisitionDate = today();
try
{
if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
{
workbook = workbooks.item(1);
workSheets = workbook.worksheets();
workSheet = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
cells = workSheet.cells();
conDimensionName = TIDgetDimensionName();
row = 5;
ttsBegin;
while (cells.item(row,2).value().bStr() != "")
{
//financial dimension
financialDimensionFromExcel = conNull();
//dimension value 1
financialDimensionFromExcel += cells.item(row,7).value().bStr();
//dimension value 2
financialDimensionFromExcel += cells.item(row,8).value().bStr();
//dimension value 3
financialDimensionFromExcel += cells.item(row,9).value().bStr();
//dimension value 4
financialDimensionFromExcel += cells.item(row,10).value().bStr();
//dimension value 5
financialDimensionFromExcel += cells.item(row,11).value().bStr();
//dimension value 6
financialDimensionFromExcel += cells.item(row,12).value().bStr();
//generateDefaultDimension
recIdDefaultDimension = TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
//variable
assetId = TIDComVariant2STR(cells.item(row,2).value());
assetGroupId = TIDComVariant2STR(cells.item(row,1).value());
assetLocationId = TIDComVariant2STR(cells.item(row,6).value());
assetName = TIDComVariant2STR(cells.item(row,3).value());
assetServiceLife = str2num(TIDComVariant2STR(cells.item(row,4).value()));
assetLifeTimeRest = str2num(TIDComVariant2STR(cells.item(row,5).value()));
assetBookId = TIDComVariant2STR(cells.item(row,13).value());
AssetLongDescription = TIDComVariant2STR(cells.item(row,14).value());
//table
assetTable = assetTable::find(assetId);
assetGroup = AssetGroup::find(assetGroupId);
assetLocation = AssetLocation::find(assetLocationId);
//validation
if(!assetGroup || !assetLocation)
{
throw error(strFmt("Row : %1 ,Asset group or asset location not existed, please check again",row));
}
//create asset Table
if(!assetTable)
{
axAssetTable = new AxAssetTable();
axAssetTable.parmAssetId(assetId);
axAssetTable.parmAssetGroup(assetGroupId);
axAssetTable.parmName(assetName);
axAssetTable.parmLocation(assetLocationId);
axAssetTable.parmLocationMemo(AssetLongDescription);
axAssetTable.save();
}
//create assetBook
assetBook = assetBook::find(assetId,assetBookId);
if(!assetBook)
{
assetBook.clear();
assetBook.initValue();
assetBook.AssetId = assetId;
assetBook.BookId = assetBookId;
assetBook.PostingProfile = assetPostingProfile;
assetBook.ServiceLife = assetServiceLife;
assetBook.LifeTime = assetServiceLife*12;
assetBook.LifeTimeRest = assetLifeTimeRest;
assetBook.DepreciationStartDate = depreciationStartDate;
assetBook.LastDepreciationDate = LastDepreciationDate;
assetBook.AcquisitionDate = AcquisitionDate;
assetBook.DefaultDimension = recIdDefaultDimension;
assetBook.insert();
}
else
{
assetBook.selectForUpdate(true);
assetBook.ServiceLife = assetServiceLife;
assetBook.LifeTime = assetServiceLife*12;
assetBook.LifeTimeRest = assetLifeTimeRest;
assetBook.DepreciationStartDate = depreciationStartDate;
assetBook.LastDepreciationDate = LastDepreciationDate;
assetBook.AcquisitionDate = AcquisitionDate;
assetBook.DefaultDimension = recIdDefaultDimension;
assetBook.update();
}
print strFmt('FA Group : %1 FAID : %2 Name : %3',assetGroupId,assetId,assetName);
row++;
}
ttsCommit;
info("Success");
application.quit();
}
}
catch(Exception::Error)
{
info("Fixed Asset upload Error");
application.quit();
}
}
TIDGetDimensionName
public static container TIDgetDimensionName()
{
DimensionAttribute dimAttr;
DimensionAttributeSetItem dimAttrSetItem;
DimensionEnumeration dimensionSetId;
DimensionAttributeValue dimAttributeValue;
container DimensionName;
dimensionSetId = DimensionCache::getDimensionAttributeSetForLedger();
while select dimAttr order by Name
where dimAttr.Type != DimensionAttributeType::MainAccount
join RecId from dimAttrSetItem
where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
dimAttrSetItem.DimensionAttributeSet == dimensionSetId
{
dimensionName += dimAttr.Name;
}
return DimensionName;
}
TIDCreateDefaultDimension
static DimensionDefault TIDcreateDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
DimensionAttributeValueSetStorage valueSetStorage = new DimensionAttributeValueSetStorage();
DimensionDefault result;
int i;
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;
//_attr is dimension name in table DimensionAttribute
container conAttr = _attr;
container conValue = _value;
str dimValue;
for (i = 1; i <= conLen(conAttr); i++)
{
dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));
if (dimensionAttribute.RecId == 0)
{
continue;
}
dimValue = conPeek(conValue,i);
if (dimValue != "")
{
// _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);
// Add the dimensionAttibuteValue to the default dimension
valueSetStorage.addItem(dimensionAttributeValue);
}
}
result = valueSetStorage.save();
return result;
}
TIDComVariant2STR
public static str TIDComVariant2STR(COMVariant _variant)
{
str valueStr;
;
switch(_variant.variantType())
{
case COMVariantType::VT_EMPTY :
valueStr = '';
break;
case COMVariantType::VT_BSTR :
valueStr = _variant.bStr();
break;
case COMVariantType::VT_R4 :
case COMVariantType::VT_R8 :
if(_variant.double())
{
valueStr = strFmt("@SYS311964",
num2Str0(_variant.double(), 0),
num2str(_variant.double(),
0,
numOfDec(_variant.double()),
1,
0));
}
break;
default :
throw error(strfmt("@SYS26908",
_variant.variantType()));
}
return valueStr;
}
Problem :
I tried to ping with Sharepoint Server IP and got response, but i cant ping with computer name. The EP itself is working fine if i open it on another computer that already joined domain.
temporary solution :
just go to drive “C:\Windows\System32\drivers\etc”
on file “Hosts” , add
example xxx.xxx.xxx.xx COMPUTERNAME
or you can set use IP on sharepoint at alternate access mapping menu :
on AOT datasets fields method, add this code below :
void dataSetLookup(SysDataSetLookup sysDataSetLookup)
{
List list = new List(Types::String);
Query query = new Query();
// Add the table to the query.
query.addDataSource(tableNum(HcmLeaveType));
//filter query
query.dataSourceTable(tableNum(HcmLeaveType)).addRange(fieldNum(HcmLeaveType,ValidToCredit)).value(SysQuery::value(NoYes::Yes));
/* uncomment if the relation is not based on reference recid
// Specify the fields to use for the lookup.
list.addEnd(fieldStr(HcmLeaveType,LeaveTypeId));
list.addEnd(fieldStr(HcmLeaveType,Description));
// Supply the set of lookup fields.
sysDataSetLookup.parmLookupFields(list);
// Specify the field that is returned from the lookup.
sysDataSetLookup.parmSelectField(fieldStr(HcmLeaveType,LeaveTypeId));
sysDataSetLookup.parmHideSelectField(true);
*/
// Pass the query to the SysDataSetLookup so that the query is used.
sysDataSetLookup.parmQuery(query);
}
preview after lookup filtered by code :
reference on MSDN :
https://msdn.microsoft.com/en-us/library/hh830903.aspx
Listpage type form on ax can be deploy directly to enterprise portal.
And steps below show you how to do it :
1. Do incremental CIL first before deploy
2. On menu item display, right click on it and select deploy to EP and select module.
3. After deploy, AX will create two web object on AOT, WebUrls (Web -> Web Menu Items -> Web URLs) and Web Page Definition (Web -> Web Files -> Web Page Definition)
4. On Web Page Definitions properties “Module”, add submodule if you want to add this listpage on subModule on Enterprise portal , then right click on it select Deploy element.
5. On Web URLs properties URL , add submodule same as page definition (optional), then right click on it and select import page
6. Restart IIS
https://hellodax.com/2015/05/22/restart-iis-command-prompt/
7. Clear AOT cache
8. To check the newly added custom webcontrol, just copy paste your Default Enterprise portal URLS (System Administration -> Setup -> Enterprise portal -> Websites) and webcontrol urls (from Web Urls Properties) to web browser.
default enterprise portal urls :
full urls on web browser :
another guide to add to quick launch sidebar :
https://hellodax.com/2015/05/22/add-new-custom-webparts-data-grid-to-enterprise-portal-quick-launch-sidebar-tutorial/
Problem :
Our client recently facing this error during creating any journal lines (LedgerJournalTrans) .
we did restart aos, full CIL, and any standard technical troubleshoot hoping it was a cache error or something like that. And we tried drop the table and inject it with the copied data. After doing some checking on table SystemSequences with table id 212 (ledgerJournalTrans), its nextVal RecId value record is already exist on that table.
reference for RecId on AX :http://www.axaptapedia.com/RecId
solution :
1. Stop the AOS
2. get the max recid from the table on SQL with:
select max(recId) from ledgerjournaltrans;
3. update the systemSequences table on SQL with:
update SYSTEMSEQUENCES set NEXTVAL = 'MAXRECID from table' where tabid= 'tableID'
4. restart AOS and run AX
Conclusion :
they did not doing some sort of AX Version upgrade that will impact whole data
and the main problem why the AX stored procedure on SQL returning next RecId value for the next record that already exist on the table remains a mystery.
Reference :
1. https://community.dynamics.com/ax/f/33/t/109379
2. https://waytoax.wordpress.com/2014/06/01/cannot-create-a-record-in-table-the-record-already-exists-2/
Deploying newly added webcontrol is much complicated than deploying SSRS.
I hope microsoft will simplify it on the next version of AX.
note :
this guide below is only for newly addded webcontrols.
if you already done the steps below, and want to update your webpage by using webcontrol, just right click on web controls AOT, and select deploy. It will replace the old web page with the new web page.
here is the steps for deploying webcontrols :
1. deploy the webcontrol on aot (Web -> Web Files -> Web Controls) , right click and choose “Deploy”
2. then deploy Web Content Managed on aot (Web -> Web Content -> Managed) , and choose the module
3. after you deploy web content managed , you will get two new object on AOT,
Web Urls (Web -> Web Menu Items -> URLs), and Web Page Definition (Web -> Web Files -> Page Definitions) , Right click on web page definitions , select “Deploy Elements”, right clicks on web urls, and select “Import Pages”
Web page definitions
Web Ulrs
4. Restart di IIS with IISRESET on your sharepoint server , see the reference post below
https://hellodax.com/2015/05/22/restart-iis-command-prompt/
5. Clear cache for making sure the enterprise portal is displaying the newly added webparts. (on some cases you must generate Incremental CIL too)
6. To check the newly added custom webcontrol, just copy paste your Default Enterprise portal URLS (System Administration -> Setup -> Enterprise portal -> Websites) and webcontrol urls (from Web Urls Properties) to web browser.
default enterprise portal urls :
web control urls :
full urls on web browser :
another guide to add to quick launch sidebar :
https://hellodax.com/2015/05/22/add-new-custom-webparts-data-grid-to-enterprise-portal-quick-launch-sidebar-tutorial/
after doing some checking on the table relation, i found one of the table relation is missing. this is probably some other developer forgot to fill the field on AOT table relations.
error cause :
just fill the field relations like picture below :
in my case, the AX business connector configuration on report server is set up to another AOS instance.
i did change the config to the right AOS , and restart SQL Report Server Services.
Export axmodel layer example :
Export-AXModel -Model CUS Model -config MicrosoftDynamicsAXUAT -file CUS.axmodel
Import axmodel with replace and create parent example :
Install-AXModel -File CUS.axmodel -Conflict Overwrite -Config MicrosoftDynamicsAXUAT -createparents
reference link :
http://axbloggerblog.blogspot.co.id/p/powershell-commands.html
do full compile, full sync db, full CIL and incremental. Re-deploy website EP if you are customize the EP
i’ll explain the code below on another day .
when we remove the highlights on calender with clicks, it will remove the data on ax
and do the same when we add highlights on calendar.
Preview :
on design
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="HRSUploadTimeSheet.ascx.cs" Inherits="HRSUploadTimeSheet" %>
<dynamics:AxDataSource ID="hrsScheduleRequestWorkerTmpDS" runat="server"
DataSetName="HRSScheduleRequestWorkerTmpDS"
ProviderView="HRSScheduleRequestWorkerTmp">
</dynamics:AxDataSource>
<dynamics:AxDataSource ID="HRSJmgProfileDayDS" runat="server"
DataSetName="HRSJmgProfileDayDS"
ProviderView="JmgProfileDay">
</dynamics:AxDataSource>
<dynamics:AxForm ID="AxForm1" runat="server">
<dynamics:AxMultiColumn ID="AxMultiColumn1" runat="server">
<dynamics:AxColumn ID="AxColumn1" runat="server">
<dynamics:AxGridView ID="AxGridView1" runat="server" BodyHeight=""
DataKeyNames="ProfileId" DataMember="HRSScheduleRequestWorkerTmp"
DataSetCachingKey="09d6aa10-4163-4e45-9074-8449211996ae"
DataSourceID="hrsScheduleRequestWorkerTmpDS" EnableModelValidation="True"
AllowPaging="False"
onselectedindexchanged="AxGridView1_SelectedIndexChanged"
ShowFilter="False">
<Columns>
<asp:TemplateField ConvertEmptyStringToNull="False"
HeaderText="<%$ AxLabel:@SYS303656 %>" SortExpression="PersonnelNumber">
<ItemTemplate>
<asp:Label ID="PersonnelNumber" runat="server" Text='<%# Bind("PersonnelNumber") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<dynamics:AxBoundField DataField="Name" DataSet="HRSScheduleRequestWorkerTmpDS"
DataSetView="HRSScheduleRequestWorkerTmp" SortExpression="Name">
</dynamics:AxBoundField>
<asp:TemplateField ConvertEmptyStringToNull="False"
HeaderText="<%$ AxLabel:@SYS7607 %>" SortExpression="ProfileId">
<ItemTemplate>
<asp:Label ID="ProfileId" runat="server" Text='<%# Bind("ProfileId") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</dynamics:AxGridView>
</dynamics:AxColumn>
<dynamics:AxColumn ID="AxColumn2" runat="server">
<asp:Calendar ID="Calendar1" runat="server"
OnPreRender="Calendar1_PreRender"
OnSelectionChanged="Calendar1_SelectionChanged"
ondayrender="Calendar1_DayRender" Height="226px"
Width="427px">
</asp:Calendar>
<dynamics:AxGridView ID="AxGridView2" runat="server" BodyHeight=""
DataKeyNames="RecId" DataMember="JmgProfileDay"
DataSetCachingKey="a1fd011a-2d33-4680-a45d-c70b2dcaf863"
DataSourceID="HRSJmgProfileDayDS" EnableModelValidation="True"
ondatabound="AxGridView2_databound" ShowFilter="False" AllowPaging="False"
>
<Columns>
<asp:TemplateField ConvertEmptyStringToNull="False"
HeaderText="<%$ AxLabel:@SYS67221 %>" SortExpression="Color">
<ItemTemplate>
<asp:Label ID="Color" runat="server" Text='<%# Bind("Color") %>' Visible="false"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ConvertEmptyStringToNull="False"
HeaderText="<%$ AxLabel:@SYS7607 %>" SortExpression="Profile">
<ItemTemplate>
<asp:Label ID="Profile" runat="server" Text='<%# Bind("Profile") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ConvertEmptyStringToNull="False" HeaderText="Profile Key"
SortExpression="HRSProfleKey">
<ItemTemplate>
<asp:Label ID="ProfileKey" runat="server" Text='<%# Bind("HRSProfleKey") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<dynamics:AxBoundField DataField="StartWorkTime" DataSet="HRSJmgProfileDayDS"
DataSetView="JmgProfileDay" SortExpression="StartWorkTime">
</dynamics:AxBoundField>
<dynamics:AxBoundField DataField="EndWorkTime" DataSet="HRSJmgProfileDayDS"
DataSetView="JmgProfileDay" SortExpression="EndWorkTime">
</dynamics:AxBoundField>
</Columns>
</dynamics:AxGridView>
</dynamics:AxColumn>
</dynamics:AxMultiColumn>
</dynamics:AxForm>
.cs file
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.DataVisualization.Charting;
using System.Web.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls.WebParts;
using System.IO;
using System.Data.OleDb;
using System.Data;
using Proxy = Microsoft.Dynamics.Framework.BusinessConnector.Proxy;
using Microsoft.Dynamics.Framework.BusinessConnector.Session;
using System.Drawing;
using Microsoft.Dynamics.AX.Framework.Portal.Data;
using Microsoft.Dynamics.Framework.BusinessConnector.Adapter;
public partial class HRSUploadTimeSheet : System.Web.UI.UserControl
{
private string profileId { get; set; }
private string scheduleRequestId { get; set; }
private string personnelNumber { get; set; }
private Boolean canEdit { get; set; }
Dictionary<DateTime, int> dict = new Dictionary<DateTime, int>();
public Dictionary<DateTime, int> SelectedDates
{
get
{
if (ViewState["Dates"] == null)
{
dict.Add(DateTime.MaxValue.AddDays(-2), 0);
ViewState["Dates"] = dict;
}
return (Dictionary<DateTime, int>)ViewState["Dates"];
}
set
{
ViewState["Dates"] = value;
}
}
private ISession AxSession
{
get
{
AxBaseWebPart webPart = AxBaseWebPart.GetWebpart(this);
return webPart == null ? null : webPart.Session;
}
}
protected void Page_Load(object sender, EventArgs e)
{
IAxaptaRecordAdapter currentRecord = AxBaseWebPart.GetWebpart(this).ExternalRecord;
if (currentRecord != null)
{
string status = currentRecord.GetField("HRSRequestStatus").ToString();
if (status != "0")
{
canEdit = false;
}
else
{
canEdit = true;
}
scheduleRequestId = currentRecord.GetField("ScheduleReqestId").ToString();
}
if (!IsPostBack)
{
getProfileIdAndPersonnelNumber(true);
getCalendarDataFromAX();
}
}
#region grid
protected void AxGridView2_databound(object sender, EventArgs e)
{
for (int i = 0; i <= AxGridView2.Rows.Count - 1; i++)
{
Label colorTxt = (Label)AxGridView2.Rows[i].FindControl("Color");
Color myColor = Color.FromArgb(Convert.ToInt32(colorTxt.Text));
AxGridView2.Rows[i].Cells[0].BackColor = myColor;
}
getProfileIdAndPersonnelNumber(true);
getCalendarDataFromAX();
FilterRows();
}
protected void AxGridView1_SelectedIndexChanged(object sender, EventArgs e)
{
getProfileIdAndPersonnelNumber();
getCalendarDataFromAX();
FilterRows();
}
protected void getProfileIdAndPersonnelNumber(Boolean isInit = false)
{
Label profileIdLbl;
Label PersonnelNumberLbl;
if (isInit)
{
profileIdLbl = (Label)AxGridView1.Rows[0].FindControl("ProfileId");
PersonnelNumberLbl = (Label)AxGridView1.Rows[0].FindControl("PersonnelNumber");
}
else
{
profileIdLbl = (Label)AxGridView1.Rows[AxGridView1.SelectedIndex].FindControl("ProfileId");
PersonnelNumberLbl = (Label)AxGridView1.Rows[AxGridView1.SelectedIndex].FindControl("PersonnelNumber");
}
profileId = profileIdLbl.Text;
personnelNumber = PersonnelNumberLbl.Text;
}
protected void getCalendarDataFromAX()
{
SelectedDates.Clear();
SelectedDates.Add(DateTime.MaxValue.AddDays(-2), 0);
DateTime keyDate = System.DateTime.Now;
int valueInt = 0;
Microsoft.Dynamics.AX.ManagedInterop.Container axContainer;
axContainer = (Microsoft.Dynamics.AX.ManagedInterop.Container)this.AxSession.AxaptaAdapter.CallStaticRecordMethod("HRSScheduleRequestWorkerTmp", "getDataScheduleByWorker", personnelNumber, scheduleRequestId);
for (int i = 1; i <= axContainer.Count; i++)
{
if (i % 2 != 0)
{
keyDate = Convert.ToDateTime(axContainer.get_Item(i));
}
else
{
valueInt = Convert.ToInt32(axContainer.get_Item(i));
if (SelectedDates.ContainsKey(keyDate))
{
SelectedDates.Remove(keyDate);
}
SelectedDates.Add(keyDate, valueInt);
}
}
ViewState["Dates"] = SelectedDates;
}
protected void ShowRows()
{
// Display all the rows
foreach (GridViewRow row in AxGridView2.Rows)
{
row.Visible = true;
}
}
protected void FilterRows()
{
ShowRows();
string colProfileId;
// Loop through the rows and apply the filter
int i = 0;
foreach (GridViewRow row1 in AxGridView2.Rows)
{
Label lblProfile = (Label)AxGridView2.Rows[i].FindControl("Profile");
// Get the filter column values
colProfileId = lblProfile.Text;
// If the name, visit and discharge fields are not equal to "All" and not equal to the filter value then the row is hidden.
if (colProfileId != profileId)
{
row1.Visible = false;
}
i++;
}
}
#endregion
#region calendar
protected void Calendar1_PreRender(object sender, EventArgs e)
{
// Reset Selected Dates
Calendar1.SelectedDates.Clear();
// Select previously Selected Dates
foreach (var dt in SelectedDates)
{
Calendar1.SelectedDates.Add(dt.Key);
Color myColor = Color.FromArgb(Convert.ToInt32(dt.Value));
Calendar1.SelectedDayStyle.BackColor = myColor;
}
}
protected void Calendar1_SelectionChanged(object sender, EventArgs e)
{
if (canEdit)
{
getProfileIdAndPersonnelNumber();
//Check if selected Date is in the saved list
// Remove the Selected Date from the saved list
if (SelectedDates.ContainsKey(Calendar1.SelectedDate))
{
SelectedDates.Remove(Calendar1.SelectedDate);
//delete record in ax , call custom table static method
this.AxSession.AxaptaAdapter.CallStaticRecordMethod("HRSScheduleRequestWorkerTmp", "deleteScheduleRequestWorker", personnelNumber, scheduleRequestId, Calendar1.SelectedDate.Date);
}
else
{
Label colorTxt = (Label)AxGridView2.Rows[AxGridView2.SelectedIndex].FindControl("Color");
Label profileKey = (Label)AxGridView2.Rows[AxGridView2.SelectedIndex].FindControl("ProfileKey");
SelectedDates.Add(Calendar1.SelectedDate, Convert.ToInt32(colorTxt.Text));
//insert to ax , call custom table static method
this.AxSession.AxaptaAdapter.CallStaticRecordMethod("HRSScheduleRequestWorkerTmp", "insertScheduleRequestWorker", personnelNumber, scheduleRequestId, Calendar1.SelectedDate.Date, profileId, profileKey.Text);
}
ViewState["Dates"] = SelectedDates;
}
}
protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
{
if (e.Day.IsSelected == true)
{
int color = SelectedDates[e.Day.Date];
Color myColor = Color.FromArgb(color);
e.Cell.BackColor = myColor;
}
}
#endregion
}
protected void getCalendarDataFromAX()
{
SelectedDates.Clear();
DateTime keyDate = System.DateTime.Now;
int valueInt = 0;
Microsoft.Dynamics.AX.ManagedInterop.Container axContainer;
axContainer = (Microsoft.Dynamics.AX.ManagedInterop.Container)this.AxSession.AxaptaAdapter.CallStaticRecordMethod("HRSScheduleRequestWorkerTmp", "getDataScheduleByWorker", personnelNumber, "SCH-000001");
for (int i = 1; i <= axContainer.Count; i++)
{
if (i % 2 != 0)
{
keyDate = Convert.ToDateTime(axContainer.get_Item(i));
}
else
{
valueInt = Convert.ToInt32(axContainer.get_Item(i));
if (SelectedDates.ContainsKey(keyDate))
{
SelectedDates.Remove(keyDate);
}
SelectedDates.Add(keyDate, valueInt);
}
}
ViewState["Dates"] = SelectedDates;
}
if you add custom DLL to your VS project. When you open up web page you will receive error “are you missing an assembly reference?”.
to resolve this issue.. just copy paste the custom dll to folder (Sharepoint Server) C:\inetpub\wwwroot\wss\VirtualDirectories\PORT_NUMBER\_app_bin .
do incremental CIL and reset IIS
Reset the IIS :
https://hellodax.com/2015/05/22/restart-iis-command-prompt/
select firstOnly DocuRef
order by docuRef.RecId DESC
where
DocuRef.RefRecId == _record.RecId
&& docuRef.RefTableId == _record.TableId
&& docuRef.RefCompanyId == curext();
filename = DocuRef.completeFilename();
differentiate the rich client and ep using the menu items name.
always remember to do INCREMENTAL CIL every time you modify any of workflow classses
public void submit(Args _args)
{
WorkflowComment note = "";
WorkflowSubmitDialog workflowSubmitDialog;
WorkflowCorrelationId workflowCorrelationId;
recId recId;
workflowTypeName workflowTypeName = workflowtypestr("TIDEmploymentLeave");
WorkflowComment initialNote = "";
HcmEmploymentLeave HcmEmploymentLeave;
FormDataSource HcmEmploymentLeave_ds;
EPWorkflowControlContext workflowControlContext;
str menuItemName = _args.menuItemName();
//from rich client
if(menuItemName == menuitemactionstr(TIDEmploymentLeaveSubmitMenuItem) || menuItemName == menuitemActionStr(HRSLeaveCreditSubmitMenuItem))
{
// Workflow is starting from the Windows client. This can be determined
// because the menu item for submitting the workflow on the Windows
// client was chosen by the user.
// Opens the submit to workflow dialog.
workflowSubmitDialog = WorkflowSubmitDialog::construct(_args.caller().getActiveWorkflowConfiguration());
workflowSubmitDialog.run();
if (workflowSubmitDialog.parmIsClosedOK())
{
// Find what record from the Work Orders table is being submitted to workflow.
recId = _args.record().RecId;
// Get comments from the submit to workflow dialog.
initialNote = workflowSubmitDialog.parmWorkflowComment();
try
{
// Update the record in the FCMWorkOrders table that is being submitted to workflow.
// The record is moved to the 'submitted' state.
ttsBegin;
HcmEmploymentLeave = _args.record();
HcmEmploymentLeave.selectForUpdate(true);
HcmEmploymentLeave.Status = HRSLeaveStatus::Submitted;
HcmEmploymentLeave.update();
// Activate the workflow.
workflowCorrelationId = Workflow::activateFromWorkflowType(workflowTypeName, recId, initialNote, NoYes::No);
ttsCommit;
// Updates the workflow button to diplay Actions instead of Submit.
_args.caller().updateWorkflowControls();
}
catch(exception::Error)
{
info("Error on workflow activation.");
}
}
}
//from EP
else
{
// Workflow is starting from Enterprise Portal.
// Retrieve the workflow controller context from the arguments passed in.
workflowControlContext = _args.caller();
// Find what record from the Work Orders table is being submitted to workflow.
recId = _args.record().RecId;
// Get comments from the submit to workflow dialog.
initialNote = workflowControlContext.getWorkflowComment();
try
{
ttsBegin;
workflowCorrelationId = Workflow::activateFromWorkflowType(workflowTypeName, recId, initialNote, NoYes::Yes);
HcmEmploymentLeave = _args.record();
HcmEmploymentLeave.selectForUpdate(true);
HcmEmploymentLeave.Status = HRSLeaveStatus::Submitted;
HcmEmploymentLeave.update();
ttsCommit;
}
catch(exception::Error)
{
info("Error on workflow activation.");
}
}
}
example error :
if you are using list page for EP,
at your list page interaction class on AX.
do override method initializeQuery and add sortbyfield like example below
public void initializeQuery(Query _query)
{
_query.dataSourceTable(tableNum(HRSBenefitEntitlement)).addOrderByField(fieldNum(HRSBenefitEntitlement,Employment));
super(_query);
}
do Incremental CIL after you modify the code.
example code :
Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue $webURL = "http://SERVER:PORT/sites/TEST/" $web = Get-SPWeb -Identity $webURL $web.ID
source : http://anthony-verschraegen.blogspot.com/2013/04/sharepoint-powershell-get-web-id-guid.html
the issue occur when i tried restarting IIS for updating new customize on EP portal.
not all the service on IIS pool are restarting due to some error.
so just head to IIS pool, make sure the service that you required is all on “STARTED” condition like picture below
public static server str DefaultDimension()
{
str field = SysComputedColumn::returnField(tableStr(HRSJobPositionDefaultDimension),
identifierStr(HcmPositionDefaultDimension),
fieldStr(HcmPositionDefaultDimension, DefaultDimension));
return SysComputedColumn::if(
SysComputedColumn::isNullExpression(field),
SysComputedColumn::cast("0",'BIGINT'),
SysComputedColumn::cast(field,'BIGINT'));
}
This example tutorial will guide you to add new default dim on table and forms.
1. Add new field with type Int64 and set the properties.
2. Add relation on table relation and set the properties.
3. on class declaration form , add code below :
public class FormRun extends ObjectRun
{
DimensionDefaultingController dimensionDefaultingController;
}
4. add tab page Financial Dimension on form design , and set properties like img below
5. on TabFinancialDimensions , override method pageActivated
public void pageActivated()
{
dimensionDefaultingController.pageActivated();
super();
}
6. on init form, like code below :
public void init()
{
super();
dimensionDefaultingController = DimensionDefaultingController::constructInTabWithValues(true, true, true, 0, this, TabFinancialDimensions, "@SYS138487", curext());
dimensionDefaultingController.parmAttributeValueSetDataSource(HRSJobPowerPlan_ds, fieldStr(HRSJobPowerPlan, DefaultDimension));
}
7. finnaly, on datasource form method override method :
on active()
public int active()
{
int ret;
ret = super();
dimensionDefaultingController.activated();
return ret;
}
on write()
public void write()
{
dimensionDefaultingController.writing();
super();
}
on delete()
public void delete()
{
dimensionDefaultingController.deleted();
super();
}
8. preview example form:
public static server str getOutstanding()
{
return strFmt("Sum(%1) + Sum(%2)",
SysComputedColumn::returnField(
tableStr(TIDIndentOrderUninvoiceTrans),
identifierStr(TIDIndentOrderPaymentTrans),
fieldStr(TIDIndentOrderPaymentTrans, amountTendered)),
SysComputedColumn::returnField(
tableStr(TIDIndentOrderUninvoiceTrans) ,
identifierStr(TIDIndentOrderPaymentTrans),
fieldStr(TIDIndentOrderPaymentTrans, grossAmount)));
}
on form class declaration
public class FormRun extends ObjectRun
{
QueryBuildRange indentTransDateRange;
QueryBuildRange invoiceDateRange;
QueryBuildRange customerRange;
QueryBuildRange storeRange;
}
on init form method
public void init()
{
super();
indentTransDateRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
.addRange(fieldnum(TIDIndentOrderUninvoiceTrans, PaymentTransDate));
invoiceDateRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
.addRange(fieldnum(TIDIndentOrderUninvoiceTrans, InvoicePostingDate));
customerRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
.addRange(fieldnum(TIDIndentOrderUninvoiceTrans, CustAccount));
storeRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tableNum(TIDIndentOrderUninvoiceTrans))
.addRange(fieldNum(TIDIndentOrderUninvoiceTrans, Store));
TransactionDate.dateValue(systemdateGet());
}
on execute Query datasource form
public void executeQuery()
{
this.Query().clearGroupBy();
indentTransDateRange.value(strFmt('<%1',(TransactionDate.dateValue() + 1)));
invoiceDateRange.value(strFmt('>%1',TransactionDate.dateValue()));
customerRange.value(strFmt(customer.text()+'*'));
storeRange.value(strFmt(StoreId.text()+'*'));
this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,Store));
this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,CustAccount));
this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,SalesId));
this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,GrossAmount));
this.Query().DataSourceNo(1).addSelectionField(fieldNum(TIDIndentOrderUninvoiceTrans,SumOfamountTendered),SelectionField::Sum);
this.Query().DataSourceNo(1).addSelectionField(fieldNum(TIDIndentOrderUninvoiceTrans,OutstandingAmount),SelectionField::Sum);
super();
}
private void setInvoiceHeader(SysExcelWorkSheet xlsWorkSheet,CustInvoiceJour custInvoiceJour)
{
xlsWorkSheet.cells().item(row,1).value("FK");
xlsWorkSheet.cells().item(row,2).value(str2num(CustTable::find(custInvoiceJour.InvoiceAccount).TECFakturPajakTypeId));
xlsWorkSheet.cells().range(strFmt("B%1:B%1",row)).numberFormat("00");
xlsWorkSheet.cells().item(row,3).value(enum2int(custInvoiceJour.TECIsSubtitute));
xlsWorkSheet.cells().item(row,4).value(str2num(strFmt("%1", strRem(custInvoiceJour.TECFakturPajakListId, ".-"))));
xlsWorkSheet.cells().range(strFmt("D%1:D%1",row)).numberFormat("0000000000000");
xlsWorkSheet.cells().item(row,5).value(mthOfYr(custInvoiceJour.InvoiceDate));
xlsWorkSheet.cells().item(row,6).value(year(custInvoiceJour.InvoiceDate));
xlsWorkSheet.cells().item(row,7).value(date2str(custInvoiceJour.InvoiceDate, 213, 1, 4, 1, 4, 4));
xlsWorkSheet.cells().item(row,8).value(str2num(strFmt("%1", strRem(custInvoiceJour.custTable_InvoiceAccount().TECNPWP, ".-"))));
xlsWorkSheet.cells().range(strFmt("H%1:H%1",row)).numberFormat("00000000000000");
xlsWorkSheet.cells().item(row,9).value(CustTable::find(custInvoiceJour.InvoiceAccount).name());
xlsWorkSheet.cells().item(row,10).value(strRTrim(CustTable::find(custInvoiceJour.InvoiceAccount).postalAddress().Street));
xlsWorkSheet.cells().item(row,11).value(round(custInvoiceJour.SalesBalance,0));
xlsWorkSheet.cells().range(strFmt("K%1:K%1",row)).numberFormat("0");
xlsWorkSheet.cells().item(row,12).value(round(custInvoiceJour.SumTax,0));
xlsWorkSheet.cells().range(strFmt("L%1:L%1",row)).numberFormat("0");
xlsWorkSheet.cells().item(row,13).value(0);
xlsWorkSheet.cells().item(row,14).value("");
xlsWorkSheet.cells().item(row,15).value(0);
xlsWorkSheet.cells().item(row,16).value(0);
xlsWorkSheet.cells().item(row,17).value(0);
xlsWorkSheet.cells().item(row,18).value(0);
xlsWorkSheet.cells().item(row,19).value(custInvoiceJour.InvoiceId);
row++;
totalCount++;
}
more example and source :
http://patrikluca.blogspot.cz/2010/04/export-to-excel-with-x-code-sequel.html
public container getSelectedItem()
{
container _returnContainer ;
HCMWorker Worker;
MultiSelectionHelper helper = MultiSelectionHelper::construct();
helper.parmDatasource(HCMWorker_DS);
Worker = helper.getFirst();
while (Worker.RecId != 0)
{
_returnContainer += Worker.RecId;
Worker = helper.getNext();
}
return _returnContainer;
}
static void TIDF_UploadFAJournal(Args _args)
{
container accEntryPattern;
container offSetEntryPattern;
//for default dimension
container financialDimensionFromExcel;
DimensionDefault defaultDimension;
int countLedger;
int countCon;
AssetBookTable _AssetBookTable;
AssetBookId _AssetBookId;
LedgerJournalTrans_Asset _LedgerJournalTrans_Asset;
LedgerJournalTrans _LedgerJournalTrans;
LedgerJournalTable _LedgerJournalTable;
AssetBookTableDerived _AssetBookTableDerived;
AssetBookTableDerivedJournal _AssetBookTableDerivedJournal;
AssetTransTypeJournal _AssetTransTypeJournal;
AssetTransType _AssetTransType;
LedgerJournalACType LedgerJournalACType;
ledgerJournalName ledgerJournalName;
journalID JournalID;
LedgerJournalId _journalId;
axLedgerJournalTable axLedgerJournalTable;
axledgerJournalTrans axLedgerJournalTrans;
LedgerJournalType JournalType = ledgerJournalType::Daily;
TransDate _TransDate;
LedgerTransType _LedgerTransType;
str AccountNum;
LedgerJournalACType _AccountType;
CurrencyCode _CurrencyCode;
real _ExhcangeRate;
real _AmountCurDebit;
real _AmountCurCredit;
str _InvoiceId;
TransTxt _TransTxt;
DueDate _DueDate;
DocumentNum _DoucumentNum;
DocumentDate _DocumentDate;
str Branch;
str Departement;
str Product;
str Project;
TaxGroupJournal _TaxJournal;
TaxGroupJournal _TaxItemJournal;
NoYes _Transferred;
BankTransactionType _BankTransactionType;
TaxCode _TaxCode;
LedgerJournalACType _OffsetAccountType;
str _OffsetAcountNum;
VendPostingProfile _PostingProfile;
SysExcelApplication application = SysExcelApplication::construct();
SysExcelWorkbooks workbooks = application.workbooks();
SysExcelWorkbook workbook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
str filename, numSeq;
str strAccount,strAccountOffset;
Name dimensionName1,dimensionName2,dimensionName3,dimensionName4;
str MainAcc,Dim1,Dim2,Dim3,Dim4,OffsetAcc;
str AccountType,OffsetAccountType;
str PayReff,BankTransType,DocNo,PostingProfile,noInvoice, transType;
TransDate DocDate;
str StrDate;
boolean _return = true;
container dimensionAttrName = Global::TIDgetDimensionName();
_OffsetAcountNum = '9900-0000';
startLengthyOperation();
filename = @"C:\\#Tectura#\fanddy\UploadFAJournal";//FA Dep Commercial.xlsx";//Depreciation commercial
//filename = _filePath;
try
{
if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
{
workbook = workbooks.item(1);
workSheets = workbook.worksheets();
workSheet = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
cells = workSheet.cells();
row = 6;
ttsBegin;
while (cells.item(row,5).value().bStr() != '' )
{
//financial dimension
financialDimensionFromExcel = conNull();
//dimension value 1
financialDimensionFromExcel += cells.item(row,10).value().bStr();
if(cells.item(row,10).value().bStr() != '' ) {countLedger++;}
//dimension value 2
financialDimensionFromExcel += cells.item(row,11).value().bStr();
if(cells.item(row,11).value().bStr() != '' ) {countLedger++;}
//dimension value 3
financialDimensionFromExcel += cells.item(row,12).value().bStr();
if(cells.item(row,12).value().bStr() != '' ) {countLedger++;}
//dimension value 4
financialDimensionFromExcel += cells.item(row,13).value().bStr();
if(cells.item(row,13).value().bStr() != '' ) {countLedger++;}
//dimension value 5
financialDimensionFromExcel += cells.item(row,14).value().bStr();
if(cells.item(row,14).value().bStr() != '' ) {countLedger++;}
//dimension value 6
financialDimensionFromExcel += cells.item(row,15).value().bStr();
if(cells.item(row,15).value().bStr() != '' ) {countLedger++;}
//start from A
_journalId = cells.item(row,1).value().bStr();
_TransDate = cells.item(row,2).value().date();
AccountNum = cells.item(row,3).value().bStr();
transType = cells.item(row,4).value().bStr();
_AssetBookId = cells.item(row,5).value().bStr();
_CurrencyCode = cells.item(row,6).value().bStr();
_ExhcangeRate = cells.item(row,7).value().double();
_AmountCurDebit = cells.item(row,8).value().double();
_AmountCurCredit = cells.item(row,9).value().double();
//_TransDate = str2Date(StrDate, 123);
defaultDimension = TIDcreateDefaultDimension(dimensionAttrName,financialDimensionFromExcel);
offSetEntryPattern =
[ _OffsetAcountNum,
_OffsetAcountNum,
countLedger
];
countCon = 1;
while(countCon <= countLedger)
{
offSetEntryPattern += conPeek(dimensionAttrName,countCon);
offSetEntryPattern += conPeek(financialDimensionFromExcel,countCon);
countCon++;
}
_AssetTransType = str2enum(_AssetTransType, transType);
_AssetTransTypeJournal = str2enum(_AssetTransTypeJournal, transType);
_LedgerJournalTable = LedgerJournalTable::find(_journalId);
if (_LedgerJournalTable)
{
_LedgerJournalTrans.clear();
_LedgerJournalTrans.JournalNum = _journalId;
_LedgerJournalTrans.TransDate = _TransDate;
_LedgerJournalTrans.AccountType = LedgerJournalACType::FixedAssets;
_LedgerJournalTrans.LedgerDimension = DimensionStorage::getDynamicAccount(AccountNum, LedgerJournalACType::FixedAssets);
_LedgerJournalTrans.DefaultDimension = defaultDimension;
_LedgerJournalTrans.CurrencyCode = _CurrencyCode;
_LedgerJournalTrans.ExchRate = _ExhcangeRate;
_LedgerJournalTrans.PostingProfile = _PostingProfile;
if(_AmountCurDebit != 0)
{
_LedgerJournalTrans.AmountCurDebit = _AmountCurDebit;
}
if(_AmountCurCredit != 0)
{
_LedgerJournalTrans.AmountCurCredit = _AmountCurCredit;
}
if(_OffsetAcountNum != "")
{
_LedgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger;
_LedgerJournalTrans.OffsetLedgerDimension = AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern);
}
_LedgerJournalTrans.Voucher = NumberSeq::newGetNumFromId(_LedgerJournalTable.ledgerJournalName().NumberSequenceTable).num();
_LedgerJournalTrans.insert();
if (_LedgerJournalTrans)
{
if (_AssetBookId)
{
_AssetBookTable = AssetBookTable::find(_AssetBookId);
if (_AssetBookTable)
{
_LedgerJournalTrans_Asset.clear();
_LedgerJournalTrans_Asset.RefRecId = _LedgerJournalTrans.RecId;
_LedgerJournalTrans_Asset.AssetId = AccountNum;
_LedgerJournalTrans_Asset.TransType = _AssetTransTypeJournal;
_LedgerJournalTrans_Asset.BookId = _AssetBookTable.BookId;
_LedgerJournalTrans_Asset.insert();
}
select firstOnly _AssetBookTableDerived
where _AssetBookTableDerived.BookId == _AssetBookId
&& _AssetBookTableDerived.AssetTransType == _AssetTransType;
if (_AssetBookTableDerived)
{
_AssetBookTableDerivedJournal.clear();
_AssetBookTableDerivedJournal.RefRecId = _LedgerJournalTrans.RecId;
_AssetBookTableDerivedJournal.AssetBookId = _AssetBookTableDerived.BookIdDerived;
if (_LedgerJournalTrans.AmountCurCredit > 0)
{
_AssetBookTableDerivedJournal.AmountCur = _LedgerJournalTrans.AmountCurCredit;
}
else if (_LedgerJournalTrans.AmountCurDebit > 0)
{
_AssetBookTableDerivedJournal.AmountCur = _LedgerJournalTrans.AmountCurDebit;
}
_AssetBookTableDerivedJournal.insert();
}
}
}
print strFmt('Row Number %1 - Asset ID : %2', row,AccountNum);
row++;
}
}
info(strFmt('Imported %1 Items, Journal Numbers : %2',row ,_journalId));
ttsCommit;
}
}
catch(Exception::Error)
{
_return = false;
}
application.quit();
}
static void TIDF_UploadJournal(Args _args)
{
container accEntryPattern;
container offSetEntryPattern;
container financialDimensionFromExcel;
container conDimensionName;
int countCon;
int countLedger;
RecId recIdDefaultDimension;
DimensionDynamicAccount parmLedgerDimension;
DimensionDynamicAccount parmOffsetLedgerDimension;
LedgerJournalTable ledgerJournalTable;
LedgerJournalACType LedgerJournalACType;
ledgerJournalName ledgerJournalName;
journalID JournalID;
axLedgerJournalTable axLedgerJournalTable;
axledgerJournalTrans axLedgerJournalTrans;
LedgerJournalType JournalType = ledgerJournalType::Daily;
SysExcelApplication application = SysExcelApplication::construct();
SysExcelWorkbooks workbooks = application.workbooks();
SysExcelWorkbook workbook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
str filename, numSeq;
boolean _return = true;
;
startLengthyOperation();
filename = "C:\\test.xlsx";
JournalID = "G0615-0040";// <<< JOURNALNUM
//filename = _filePath;
try
{
if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
{
workbook = workbooks.item(1);
workSheets = workbook.worksheets();
workSheet = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
cells = workSheet.cells();
row = 5;
ledgerJournalTable = ledgerJournalTable::find(JournalID);
conDimensionName = Global::TIDgetDimensionName();
ttsBegin;
while (cells.item(row,2).value().bStr() != "")
{
//empty container
financialDimensionFromExcel = conNull();
countLedger = 0;
//Create Trans ...
axLedgerJournalTrans = new axLedgerJournalTrans();
axLedgerJournalTrans.parmJournalNum(JournalID);
//start from A
axLedgerJournalTrans.parmTransDate(cells.item(row,1).value().date());
axLedgerJournalTrans.parmAccountType(str2Enum(LedgerJournalACType, cells.item(row,2).value().bStr()));
//dimension value 1
financialDimensionFromExcel += cells.item(row,17).value().bStr();
if(cells.item(row,17).value().bStr() != "") {countLedger++;}
//dimension value 2
financialDimensionFromExcel += cells.item(row,18).value().bStr();
if(cells.item(row,18).value().bStr() != "") {countLedger++;}
//dimension value 3
financialDimensionFromExcel += cells.item(row,19).value().bStr();
if(cells.item(row,19).value().bStr() != "") {countLedger++;}
//dimension value 4
financialDimensionFromExcel += cells.item(row,20).value().bStr();
if(cells.item(row,20).value().bStr() != "") {countLedger++;}
//dimension value 5
financialDimensionFromExcel += cells.item(row,21).value().bStr();
if(cells.item(row,21).value().bStr() != "") {countLedger++;}
//dimension value 6
financialDimensionFromExcel += cells.item(row,22).value().bStr();
if(cells.item(row,22).value().bStr() != "") {countLedger++;}
//ledger Dimension
if(axLedgerJournalTrans.parmAccountType() == LedgerJournalACType::Ledger)
{
accEntryPattern =
[ cells.item(row, 3).value().bStr(),
cells.item(row, 3).value().bStr(),
countLedger
];
countCon = 1;
while(countCon <= countLedger)
{
accEntryPattern += conPeek(conDimensionName,countCon);
accEntryPattern += conPeek(financialDimensionFromExcel,countCon);
countCon++;
}
parmLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accEntryPattern));
}
else
{
parmLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 3).value().bStr(),axLedgerJournalTrans.parmAccountType());
}
AxLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, LedgerDimension), false);
axLedgerJournalTrans.parmLedgerDimension(parmLedgerDimension);
//offset dimension
axLedgerJournalTrans.parmOffsetAccountType(str2Enum(LedgerJournalACType, cells.item(row,8).value().bStr()));
if(axLedgerJournalTrans.parmOffsetAccountType() == LedgerJournalACType::Ledger)
{
offSetEntryPattern =
[ cells.item(row, 9).value().bStr(),
cells.item(row, 9).value().bStr(),
countLedger
];
countCon = 1;
while(countCon <= countLedger)
{
offSetEntryPattern += conPeek(conDimensionName,countCon);
offSetEntryPattern += conPeek(financialDimensionFromExcel,countCon);
countCon++;
}
parmOffsetLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern));
}
else
{
parmOffsetLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 9).value().bStr(),axLedgerJournalTrans.parmAccountType());
}
axLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, offsetLedgerDimension), false);
axLedgerJournalTrans.parmOffsetLedgerDimension(parmOffsetLedgerDimension);
if(cells.item(row,5).value().double() != 0)
{
axLedgerJournalTrans.parmAmountCurDebit(cells.item(row,5).value().double());
}
if(cells.item(row,6).value().double() != 0)
{
axLedgerJournalTrans.parmAmountCurCredit(cells.item(row,6).value().double());
}
axLedgerJournalTrans.parmtxt(cells.item(row,7).value().bStr());
axLedgerJournalTrans.parmCurrencyCode(cells.item(row,10).value().bStr());
axLedgerJournalTrans.parmExchRate(cells.item(row,11).value().double());
axLedgerJournalTrans.parmInvoice(cells.item(row,12).value().bStr());
axLedgerJournalTrans.parmPaymReference(cells.item(row,13).value().bStr());
axLedgerJournalTrans.parmBankTransType(cells.item(row,14).value().bStr());
axLedgerJournalTrans.parmDocumentNum(cells.item(row,15).value().bStr());
axLedgerJournalTrans.parmDocumentDate(cells.item(row,16).value().date());
//
axLedgerJournalTrans.parmPostingProfile(cells.item(row,23).value().bStr());
recIdDefaultDimension = Global::TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
axLedgerJournalTrans.parmDefaultDimension(recIdDefaultDimension);
axLedgerJournalTrans.parmOffsetDefaultDimension(recIdDefaultDimension);
axLedgerJournalTrans.save();
row++;
}
ttsCommit;
info('Success !!');
application.quit();
}
}
catch(Exception::Error)
{
info("Journal Upload Error");
application.quit();
}
}
static void TIDF_uploadFAwithValueModels(Args _args)
{
AssetId assetId;
AssetGroupId assetGroupId;
AssetName assetName;
AssetLocationId assetLocationId;
AssetServiceLife assetServiceLife;
AssetLifeTimeRest assetLifeTimeRest;
AssetPostingProfile assetPostingProfile;
AssetBookId assetBookId;
TransDate depreciationStartDate,LastDepreciationDate,AcquisitionDate;
RecId recIdDefaultDimension;
AxAssetTable axAssetTable;
//table
AssetTable assetTable;
AssetGroup assetGroup;
AssetLocation assetLocation;
AssetBook assetBook;
AssetLedger assetLedger;
container financialDimensionFromExcel;
container conDimensionName;
SysExcelApplication application = SysExcelApplication::construct();
SysExcelWorkbooks workbooks = application.workbooks();
SysExcelWorkbook workbook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
str filename;
;
startLengthyOperation();
//variable yang diisi manual
filename = "C:\\FA master.xlsx";
assetPostingProfile = "FA POSTING";
depreciationStartDate = today();
LastDepreciationDate = today();
AcquisitionDate = today();
try
{
if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
{
workbook = workbooks.item(1);
workSheets = workbook.worksheets();
workSheet = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
cells = workSheet.cells();
conDimensionName = TIDgetDimensionName();
row = 5;
ttsBegin;
while (cells.item(row,2).value().bStr() != "")
{
//financial dimension
financialDimensionFromExcel = conNull();
//dimension value 1
financialDimensionFromExcel += cells.item(row,7).value().bStr();
//dimension value 2
financialDimensionFromExcel += cells.item(row,8).value().bStr();
//dimension value 3
financialDimensionFromExcel += cells.item(row,9).value().bStr();
//dimension value 4
financialDimensionFromExcel += cells.item(row,10).value().bStr();
//dimension value 5
financialDimensionFromExcel += cells.item(row,11).value().bStr();
//dimension value 6
financialDimensionFromExcel += cells.item(row,12).value().bStr();
//generateDefaultDimension
recIdDefaultDimension = TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
//variable
assetId = TIDComVariant2STR(cells.item(row,2).value());
assetGroupId = TIDComVariant2STR(cells.item(row,1).value());
assetLocationId = TIDComVariant2STR(cells.item(row,6).value());
assetName = TIDComVariant2STR(cells.item(row,3).value());
assetServiceLife = str2num(TIDComVariant2STR(cells.item(row,4).value()));
assetLifeTimeRest = str2num(TIDComVariant2STR(cells.item(row,5).value()));
assetBookId = TIDComVariant2STR(cells.item(row,13).value());
//table
assetTable = assetTable::find(assetId);
assetGroup = AssetGroup::find(assetGroupId);
assetLocation = AssetLocation::find(assetLocationId);
//validation
if(!assetGroup || !assetLocation)
{
throw error(strFmt("Row : %1 ,Asset group or asset location not existed, please check again",row));
}
//create asset Table
if(!assetTable)
{
axAssetTable = new AxAssetTable();
axAssetTable.parmAssetId(assetId);
axAssetTable.parmAssetGroup(assetGroupId);
axAssetTable.parmName(assetName);
axAssetTable.parmLocation(assetLocationId);
axAssetTable.save();
}
//create assetBook
assetBook = assetBook::find(assetId,assetBookId);
if(!assetBook)
{
assetBook.clear();
assetBook.initValue();
assetBook.AssetId = assetId;
assetBook.BookId = assetBookId;
assetBook.PostingProfile = assetPostingProfile;
assetBook.ServiceLife = assetServiceLife;
assetBook.LifeTime = assetServiceLife*12;
assetBook.LifeTimeRest = assetLifeTimeRest;
assetBook.DepreciationStartDate = depreciationStartDate;
assetBook.LastDepreciationDate = LastDepreciationDate;
assetBook.AcquisitionDate = AcquisitionDate;
assetBook.DefaultDimension = recIdDefaultDimension;
assetBook.insert();
}
else
{
assetBook.selectForUpdate(true);
assetBook.ServiceLife = assetServiceLife;
assetBook.LifeTime = assetServiceLife*12;
assetBook.LifeTimeRest = assetLifeTimeRest;
assetBook.DepreciationStartDate = depreciationStartDate;
assetBook.LastDepreciationDate = LastDepreciationDate;
assetBook.AcquisitionDate = AcquisitionDate;
assetBook.DefaultDimension = recIdDefaultDimension;
assetBook.update();
}
row++;
}
ttsCommit;
application.quit();
}
}
catch(Exception::Error)
{
info("Fixed Asset upload Error");
application.quit();
}
}
TIDGetDimensionName
public static container TIDgetDimensionName()
{
DimensionAttribute dimAttr;
DimensionAttributeSetItem dimAttrSetItem;
DimensionEnumeration dimensionSetId;
DimensionAttributeValue dimAttributeValue;
container DimensionName;
dimensionSetId = DimensionCache::getDimensionAttributeSetForLedger();
while select dimAttr order by Name
where dimAttr.Type != DimensionAttributeType::MainAccount
join RecId from dimAttrSetItem
where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
dimAttrSetItem.DimensionAttributeSet == dimensionSetId
{
dimensionName += dimAttr.Name;
}
return DimensionName;
}
TIDcreateDefaultDimension
static DimensionDefault TIDcreateDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
DimensionAttributeValueSetStorage valueSetStorage = new DimensionAttributeValueSetStorage();
DimensionDefault result;
int i;
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;
//_attr is dimension name in table DimensionAttribute
container conAttr = _attr;
container conValue = _value;
str dimValue;
for (i = 1; i <= conLen(conAttr); i++)
{
dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));
if (dimensionAttribute.RecId == 0)
{
continue;
}
dimValue = conPeek(conValue,i);
if (dimValue != "")
{
// _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);
// Add the dimensionAttibuteValue to the default dimension
valueSetStorage.addItem(dimensionAttributeValue);
}
}
result = valueSetStorage.save();
return result;
}
TIDComVariant2STR
public static str TIDComVariant2STR(COMVariant _variant)
{
str valueStr;
;
switch(_variant.variantType())
{
case COMVariantType::VT_EMPTY :
valueStr = '';
break;
case COMVariantType::VT_BSTR :
valueStr = _variant.bStr();
break;
case COMVariantType::VT_R4 :
case COMVariantType::VT_R8 :
if(_variant.double())
{
valueStr = strFmt("@SYS311964",
num2Str0(_variant.double(), 0),
num2str(_variant.double(),
0,
numOfDec(_variant.double()),
1,
0));
}
break;
default :
throw error(strfmt("@SYS26908",
_variant.variantType()));
}
return valueStr;
}
source :
http://krishhdax.blogspot.com/2012/05/ax2012-import-fixed-assets-table.html
https://axblog4u.wordpress.com/2012/09/06/tip-comvarianttype-for-real-values-in-dynamics-ax/
addressView.CountryRegionId = LogisticsAddressCountryRegion::findByISOCode(SysCountryRegionCode::countryInfo(curext())).CountryRegionId;
example code :
public static void autoSettlementJournalFromPAYDetails(JournalId _journalNum)
{
CustVendOpenTransManager manager;
CustTrans custTrans;
CustTransOpen custTransOpen;
LedgerJournalTrans ledgerJournalTrans;
AxLedgerJournalTable axLedgerJournalTable;
AxLedgerJournalTrans axLedgerJournalTrans;
ExchangeRateHelper exchangeRateHelper;
AmountCur totalSettleAmount;
AmountCur amountCurDebit,amountCurCredit;
HRSPayJournalDetails hrsPayJournalDetails;
HRSPayJournalTable hrsPayJournalTable;
int Row;
AxLedgerJournalTable = AxLedgerJournalTable::newLedgerJournalTable(LedgerJournalTable::find(_journalNum));
ttsBegin;
//details journal lines from Pay Journal Details
while select hrsPayJournalTable
join hrsPayJournalDetails
where
hrsPayJournalTable.HRSPayJournalId == hrsPayJournalDetails.HRSPayJournalId
&& HRSPayJournalTable.JournalNum == _journalNum
{
CustTrans = CustTrans::findFromInvoice(hrsPayJournalDetails.ReferenceNumber,HRSPayJournalDetails.AccountNum);
CustTransOpen = CustTransOpen::findRefId(CustTrans.RecId);
if(CustTransOpen)
{
//Create Trans ...
axLedgerJournalTrans = new axLedgerJournalTrans();
axLedgerJournalTrans.parmJournalNum(AxLedgerJournalTable.ledgerJournalTable().JournalNum);
axLedgerJournalTrans.parmTransDate(today());
axLedgerJournalTrans.parmDue(today());
axLedgerJournalTrans.parmAccountType(LedgerJournalACType::Cust);
axLedgerJournalTrans.parmLedgerDimension(DimensionStorage::getDynamicAccount(hrsPayJournalDetails.AccountNum, LedgerJournalACType::Cust));
axLedgerJournalTrans.parmAmountCurCredit(hrsPayJournalDetails.Credit);
axLedgerJournalTrans.parmAmountCurDebit(hrsPayJournalDetails.Debit);
axLedgerJournalTrans.save();
manager = CustVendOpenTransManager::construct(axLedgerJournalTrans.ledgerJournalTrans());
manager.updateTransMarked(custTransOpen,true);
if(hrsPayJournalDetails.Debit)
{
manager.updateSettleAmount(custTransOpen,hrsPayJournalDetails.Debit);
}
else
{
manager.updateSettleAmount(custTransOpen,hrsPayJournalDetails.Credit);
}
//update after settle
ledgerJournalTrans = axLedgerJournalTrans.ledgerJournalTrans();
ledgerJournalTrans.selectForUpdate(true);
ledgerJournalTrans.SettleVoucher = SettlementType::SelectedTransact;
ledgerJournalTrans.update();
Row++;
}
}
ttsCommit;
info(strFmt("Total Lines settled : %1",Row));
}
it is common for user to search item based on name. but currently on AX 2012, onHand form can’t provide search based on item name or search name. It will take a little modification from technical side.
example how to do it :
1. you can join directly on form datasource or create view like example (i’m using the view that already created for another purpose)
2. on InventSumTable add relations like this.
3. then go to form onHand (InventOnHandItem), and add the view to datasource
4. open class InventDimCtrl_Frm_OnHand and modify method modifyQuery , on class declaration parameter, add new parameter FormDataSource with default null parameter. then define new variable QueryBuildDataSource.
5. on the last line of the method modifyQuery , add code like example :
if(_tidInventLookUp)
{
qbsInventLookup = query.dataSourceName(_tidInventLookUp.name());
//filter current company language
qbr = SysQuery::findOrCreateRange(qbsInventLookup, fieldnum(TIDInventLookup, LanguageId));
qbr.value(queryValue(CompanyInfo::languageId()));
qbsInventLookup.addGroupByField(fieldNum(TIDInventLookup,Name));
qbsInventLookup.addGroupByField(fieldNum(TIDInventLookup,NameAlias));
}
5. then back to the form, on datasource inventSUm, on executeQuery , modify the code from
void executeQuery()
{
element.inventDimSetupObject().modifyQuery(inventSum_DS,inventDim_DS);
super();
}
to
void executeQuery()
{
element.inventDimSetupObject().modifyQuery(inventSum_DS,inventDim_DS,TIDInventLookUp_ds);
super();
}
6. drag the field from newly add datasource on form to the grid :
7. Voila done, example result, :
note: remember to GROUPBY on code if you add new field to the form, or the field on form will show “UNRETRIEVED”
the method already exist on class global
example code :
hcmWorkerCaller = HcmWorker::find(currentWorker());
code snapshot :
this error occured when i tried to start IIS
Solution :
Just make sure World Wide Web Publishing Service and IIS Admin Service already turned on
public void initializeQuery(Query _query)
{
// ListPageLabelChange =>
Common externalRecord;
FormDataSource frmDs;
FormRun formRun;
FormControl frmCtrl;
// ListPageLabelChange <=
_query.dataSourceTable(tableNum(HRSBenefitEntitlement)).addRange(fieldNum(HRSBenefitEntitlement,Employment)).value(SysQuery::value(HcmEmployment::findByWorkerLegalEntity(HcmWorker::userId2Worker(curUserId()),CompanyInfo::find().RecId).RecId));
super(_query);
// ListPageLabelChange =>
externalRecord = this.listPage().activeRecord(_query.dataSourceTable(tableNum(HRSBenefitEntitlement)).name());//No intrisic function for form DS?
if(externalRecord.isFormDataSource())
{
frmDs = externalRecord.dataSource();
formRun = frmDs.formRun();
if(formRun)
{
frmCtrl = formRun.design().controlName(formControlStr(TIDBenefitEntitlementListPage,HRSBenefitEntitlement_Entitlement));
if(frmCtrl)
{
frmCtrl.userPromptText("newName");
}
}
}
// ListPageLabelChange <=
}
clear usage data if no changes on form.
public client static void processMDBData(str _StrDSN, str _StrTableName, date _DateFrom, date _DateTo)
{
#AviFiles
SysOperationProgress progress = new SysOperationProgress();
OdbcConnection C;
Statement S;
LoginProperty LP = new LoginProperty();
ResultSet RS;
HcmPersonnelNumberId _EmplID;
TimeHour24 _Time;
str strquery,strDSN,tableName;
Date _Date;
int Idx;
HRSTMAttendanceTemp attDataTemp;
;
try
{
strDSN = _StrDSN;
tableName = _StrTableName;
ttsbegin;
LP.setDSN(_strDSN);
LP.setServer("S-AOS1TEST");
C = new OdbcConnection(LP);
S = C.createStatement();
strquery = strFmt("SELECT * FROM %1 where dateLog >= #%2# AND dateLog <= #%3#",tableName,_DateFrom,_DateTo);
RS = S.executeQuery(strquery);
while (RS.next())
{
//Progress.incCount();
_EmplID = Rs.getString(1);
_Date = RS.getDate(2);
_Time = str2time(RS.getString(3));
Idx = str2int(rs.getString(4));
//insert to temp table
if(_EmplID == '') continue;
select forupdate attDataTemp
where attDataTemp.PersonnelNumber == _EmplID && attDataTemp.Time == _Time
&& attDataTemp.TransDate == _Date;
if(!attDataTemp)
{
attDataTemp.clear();
attDataTemp.PersonnelNumber = _EmplID;
attDataTemp.Time = _Time;
attDataTemp.TransDate = _Date;
attDataTemp.Idx = Idx;
attDataTemp.insert();
}
else
{
attDataTemp.Idx = Idx;
attDataTemp.ClockId = '';
attDataTemp.update();
}
Progress.setText(strfmt("Upload Id %1 - %2 %3", _EmplID, _date, time2str(_time, 1, 1)));
}
ttsCommit;
}
catch
{
ttsAbort;
return;
}
}
HcmEmployment::findByWorkerLegalEntity(HcmWorker::userId2Worker(curUserId()),CompanyInfo::find().RecId).RecId
example code :
public static void main(Args _args)
{
recID recID = _args.record().RecId;
tableId tableId = _args.record().TableId;
HcmEmploymentLeave HcmEmploymentLeave = _args.record();
WorkflowWorkItemTable workItem = _args.caller().getActiveWorkflowWorkItem();
str menuItemName = _args.menuItemName();
WorkflowWorkItemActionDialog workflowWorkItemActionDialog;
WorkflowSubmitDialog workflowSubmitDialog;
Debug::assert(tableId == tablenum(HcmEmploymentLeave));
Debug::assert(recId != 0);
// The method has not been called correctly.
if (tableId != tablenum(HcmEmploymentLeave) ||
recId == 0)
{
throw error(strfmt("@SYS19306", funcname()));
}
// The journal has an active workflow.
if (workItem.RecId > 0)
{
try
{
// The journal does support workflow approvals.
workflowWorkItemActionDialog = WorkflowWorkItemActionDialog::construct( workItem,
WorkflowWorkItemActionType::Resubmit,
new MenuFunction(_args.menuItemName(),_args.menuItemType()));
workflowWorkItemActionDialog.run();
if (workflowWorkItemActionDialog.parmIsClosedOK())
{
workItem = _args.caller().getActiveWorkflowWorkItem();
WorkflowWorkItemActionManager::dispatchWorkItemAction( workItem,
workflowWorkItemActionDialog.parmWorkflowComment(),
workflowWorkItemActionDialog.parmTargetUser(),
WorkflowWorkItemActionType::Resubmit,
_args.menuItemName(),
false);
}
}
catch(Exception::Error)
{
throw error(strfmt("Leave %1 can't be submitted", HcmEmploymentLeave.HRSLeaveReqId));
}
}
// Make the form refresh its common workflow UI controls.
_args.caller().updateWorkflowControls();
}
solution : just make sure the custom web menu item for resubmit using EPWorkflowWorkItemActionManager class
example code :
if (box::yesNo('The data on temp table will be process, are you sure ?', dialogButton::Yes, 'Confirmation') == dialogButton::Yes)
{
HRSPopulateTimeAttendance.run();
}
i found this error when i tried deploying cube via ax wizard.
how to fixed error :
1. Just run the analysis service as admin domain or user domain that have the right priveleges. Set it up on SQL Server Management Configuration.
2. Then redeploy the cube :
related source :
1. https://msdn.microsoft.com/en-us/ms345578.aspx
2. https://technet.microsoft.com/en-us/library/cc197365%28v=office.12%29.aspx
i found this error when open report analysis,
and this solution below fixed it :
Go to computer management and on group SQLServerMSASUser , add user that running the report analysis.
this error occured when i ran batch job for importing data from ODBC
solution for this is add CLIENT on class identifier to make the code run on 32Bit and match the ODBC driver . example :
found error : Data or calculated fields with type ‘Record’ are not supported when open custom page on Enterprise Portal.
the reason behind is because the DISPLAY method on table datasource or related table on AX is returning record.
example the error cause :
on class : SalesPackingSlipJournalCreate\initHeader()
example :
on class : PurchPackingSlipJournalCreate\initHeader()
example :
AX Version : AX 2012 CU7
solution :
add ttsBegin before and ttsCommit after on numberSequence code
public static Price TIDgetSTDCostItemPrice(ItemId _itemId,TransDate _activationDate,InventSiteId _inventSiteId)
{
InventDim InventDimLoc;
InventItemPrice inventItemPriceLoc;
InventDimLoc.clear();
InventDimLoc.InventSiteId = _inventSiteId;
InventDimLoc = InventDim::findOrCreate(InventDimLoc);
inventItemPriceLoc = InventItemPrice::findCurrent(_itemId,CostingVersionPriceType::Cost,InventDimLoc.inventDimId,
_activationDate,_inventSiteId,InventItemCostingType::Standard);
return inventItemPriceLoc.Price;
}
static boolean sendEmail(str toAddress, str theSubject, str body, str fromAddress)
{
SysEmailParameters parameters = SysEmailParameters::find();
System.Net.Mail.SmtpClient mailClient;
System.Net.Mail.MailMessage mailMessage;
System.Net.Mail.MailAddress mailFrom;
System.Net.Mail.MailAddress mailTo;
System.Net.Mail.MailAddressCollection mailToCollection;
System.Net.Mail.MailAddressCollection mailCCCollection;
System.Net.Mail.AttachmentCollection mailAttachementCollection;
System.Net.Mail.Attachment mailAttachment;
System.Net.NetworkCredential mailCredentials;
System.Exception e;
List toList;
ListEnumerator le;
SMTPRelayServerName mailServer;
SMTPPortNumber mailPortNumber;
SMTPUserName mailUserName;
SMTPPassword mailPassword;
InteropPermission interopPermission;
;
try
{
interopPermission = new InteropPermission(InteropKind::ComInterop);
interopPermission.assert();
if (parameters.SMTPRelayServerName)
{
mailServer = parameters.SMTPRelayServerName;
}
else
{
mailServer = parameters.SMTPServerIPAddress;
}
mailPortNumber = parameters.SMTPPortNumber;
mailUserName = parameters.SMTPUserName;
mailPassword = SysEmailParameters::password();
mailCredentials = new System.Net.NetworkCredential(mailUserName,mailPassword);
mailClient = new System.Net.Mail.SmtpClient(mailServer, mailPortNumber);
mailFrom = new System.Net.Mail.MailAddress(parameters.SMTPUserName); //fromAddress);
toList = strSplit(toAddress,';');
le = toList.getEnumerator();
le.moveNext();
mailTo = new System.Net.Mail.MailAddress(strLTrim(strRTrim(le.current())));
mailMessage = new System.Net.Mail.MailMessage(mailFrom, mailTo);
mailToCollection = mailMessage.get_To();
while(le.moveNext())
{
mailToCollection.Add(strLTrim(strRTrim(le.current())));
}
mailMessage.set_From(mailFrom);
mailMessage.set_ReplyTo(mailFrom);
mailMessage.set_Priority(System.Net.Mail.MailPriority::High);
mailMessage.set_Subject(theSubject);
mailMessage.set_IsBodyHtml(true);
mailMessage.set_Body(body);
if(parameters.TIDUsingSSLorTSL)
{
mailClient.set_EnableSsl(true);
mailClient.set_UseDefaultCredentials(false);
mailClient.set_Credentials(mailCredentials);
}
else
{
mailClient.set_EnableSsl(false);
}
mailClient.Send(mailMessage);
mailMessage.Dispose();
CodeAccessPermission::revertAssert();
//info("Email has been send.");
}
catch (Exception::CLRError)
{
e = ClrInterop::getLastException();
while (e)
{
info(e.get_Message());
e = e.get_InnerException();
}
CodeAccessPermission::revertAssert();
return false;
}
}
source : my senior’s code
i modified code originated from DAXDUDE ,
i added inventQty = inventSumDateDim.postedQty()-InventSumDateDim.deductedQty()+InventSumDateDim.postedQty() to get physical inventory
static void TIDF_getInventPhysicalbyDate(Args _args)
{
InventDimParm inventDimParmCrit;
InventSumDateDim inventSumDateDim;
InventDim inventDim = InventDim::find('AllBlank');
InventQty inventQty;
;
// Set the dimensions
inventDimParmCrit.initFromInventDim(inventDim);
inventSumDateDim = inventSumDateDim::newParameters(today(), '010410003', inventDim, inventDimParmCrit);
inventQty = inventSumDateDim.receivedQty()-InventSumDateDim.deductedQty()+InventSumDateDim.postedQty();
info(strFmt("%1",inventQty));
}
source : http://daxdude.blogspot.com/2012/11/ax-2012-find-on-hand-inventory-by-date.html
this error occur when I run the SSRS report. Here are to solution :
1. on your windows server that contains SQL Server, go to C:\Program Files\Microsoft SQL Server, then do find the MRS for your environment.
2. find rssrvpolicy.config
3. replace PermissionSetName=”Execution” on class=”UnionCodeGroup” with PermissionSetName=”FullTrust”
source : https://msdn.microsoft.com/en-us/library/ms155866.aspx
This error occurs at on standar AX View because i add new relation on Derived table.
example base table :
how to fix it :
1. At the example views QUERY HcmBenPolWkrPosDtlInfo, find OMOperatingUnit datasource, and find the relation on the table.
2. Set the RELATIONS properties to NO on query datasource table , then right click on the relations, add new relations and manually configure the tables relations, do this for the all base table in datasource too
3. Then restore the query views, do restore to on the views, then re-compile the view
i found error log that make no sense after DB sync on AX 2012 R2 CU7. the table that the error pointed to already deleted, but the error still occurred.
here is the solution :
“If you are getting synchronization errors that do no make sense or that you think you should have already resolved there may be old records left in the SQLsyncinfo table or have old or incorrect object ID references. If you suspect this make a database backup, shutdown the AOS, in SQL run truncate table SQLSYNCINFO, start the AOS and run a new Synchronize from the AOT.”
source : http://blogs.msdn.com/b/axsupport/archive/2012/09/19/troubleshooting-aot-synchronize-errors.aspx
i installed AX 2012 R3, then i installed R3 CU8 update with offline package.
the problem is after installed, the version number is different.
although it was running well on the first heavy test, i’m afraid the R2 crash issue because different build number will raise again at R3.
to make it same , install AX R3 with slipstream CU8 package update, here is the full guide from MSDN blog :
http://blogs.msdn.com/b/axsupport/archive/2015/04/21/how-to-slip-stream-ax-2012-r3-cu-8.aspx
I just recently installed new fresh AX 2012 R3 and importing data using Test Data Tools :
https://technet.microsoft.com/en-us/library/dn756356.aspx .
but somehow when i start sync db after full compilation and full CIL, i got following error :
“Failed to create a session; confirm that the user has the proper privileges to log on to Microsoft Dynamics”.
i logged in using domain administrator.
here is the solution :
on the user mapping at SQL Server , make sure the db owner for administrator is checked. if you are using another user, just make sure db.datareader and db.datawriter is checked.
voila !! , the ax db sync successfully
more info :
https://community.dynamics.com/ax/f/33/t/48661
This is step by step to create custom webparts and added it to Enterprise Portal (EP)
1. at Visual Studio, add new EP VS Project
2. Right click at the solution project, and click add new item, select Microsoft Dynamics Ax -> EP User Control with form and grid.
3. Go to AOT, add new data set , then add datasource, link it to your table , do compile and do INCREMENTAL CIL.
4. Go back to VS, refresh schema for the datasource, and select your newly created datasource
5. Make sure your column etc are inside axForm, fill the properties DataKeyNames,DataMember and DataSourcesID
6. at gridview, select datasource, and edit colomn, select field you want to show on the grid
7. right click on VS solution project, and choose “Add your_webParts to AOT”
8. go back to AOT, on Web -> Web Content -> Managed , find your webcontrol, right click on it, and Deploy to EP, then choose which module this webcontrol belongs to. After you deploy it , AX will create an URL Menu Item automatically at (Web -> Web Menu Items -> URLs) and Page Definition (Web -> Web Files -> Page Definition
9. If your menu is on submenu of the module, example Home\EmployeeServices\ManageTime , just change it on properties “Web Module” at Page Definition. and change the url on on web url properties
10. The next step is we want to add newly web parts to EP Quick Launch. Find the page you want to customize on “Web Modules” on AOT, on the properties, look at value of properties “Quick Launch”
10. Go to Web -> Web Menus , find the Quick Launch, and drag and drop the newly add url menu item to Quick Launch Menu, then deploy the EP from System Admin -> Setup -> Enterprise Portal -> Deployment
11. go to Web -> Web Modules . just right click and deploy on the module you customize.
12. Last, restart IIS for refreshing the EP with newest customize, with command prompt see post :
https://hellodax.com/2015/05/22/restart-iis-command-prompt/
13. Preview
source : http://kiwiaxguy.blogspot.com/2014/07/setting-ledgerdimension-field-on.html
i modify some code from the source because the dimension name is hardcoded,
on class global :
public static container getDimensionNameTID()
{
DimensionAttribute dimAttr;
DimensionAttributeSetItem dimAttrSetItem;
DimensionEnumeration dimensionSetId;
DimensionAttributeValue dimAttributeValue;
container DimensionName;
dimensionSetId = DimensionCache::getDimensionAttributeSetForLedger();
while select dimAttr order by Name
where dimAttr.Type != DimensionAttributeType::MainAccount
join RecId from dimAttrSetItem
where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
dimAttrSetItem.DimensionAttributeSet == dimensionSetId
{
dimensionName += dimAttr.Name;
}
return DimensionName;
}
getLedgerDimension example code :
public static RecId getLedgerDimensionCombination(MainAccountNum _ledgerAccount, container dimValue)
{
DimensionServiceProvider DimensionServiceProvider = new DimensionServiceProvider();
LedgerAccountContract LedgerAccountContract = new LedgerAccountContract();
DimensionAttributeValueContract ValueContract;
List ListValueContract = new List(Types::Class);
dimensionAttributeValueCombination dimensionAttributeValueCombination;
DimensionStorage dimStorage;
container dimensionName = Global::getDimensionNameTID();
int loopDimValue = 1;
if(conLen(dimValue) > 0)
{
while(loopDimValue <= conLen(dimValue))
{
ValueContract = new DimensionAttributeValueContract();
ValueContract.parmName(conPeek(dimensionName,loopDimValue)) ;
ValueContract.parmValue(conPeek(dimValue,loopDimValue));
ListValueContract.addEnd(ValueContract);
loopDimValue++;
}
LedgerAccountContract.parmMainAccount(_ledgerAccount);
LedgerAccountContract.parmValues(ListValueContract);
dimStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
dimensionAttributeValueCombination = DimensionAttributeValueCombination::find(dimStorage.save());
return dimensionAttributeValueCombination.RecId;
}
else
{
return 0;
}
}
example code on how to use it :
static void TIDF_getLedgerDim(Args _args)
{
DimensionValue dim1,dim2;
container conDimValue;
MainAccountNum mainAccountNum;
mainAccountNum = "6100-1001";
dim1 = "5102";
dim2 = "OPERATIO";
conDimValue = [dim1,dim2];
info(strFmt("%1",getLedgerDimensionCombination(mainAccountNum,conDimValue)));
}
on AOT dataset method init() :
X++ code on init method example:
public void init()
{
super();
DNLLeaveRequest.DNLEmplRecId = DNLEmployeeTable::findByUserId().RecId;
DNLLeaveRequest.LeaveDateFrom = today();
DNLLeaveRequest.LeaveDateTo = today();
}
or you can code it on Tables\initvalue()
XML code on visual studio for EP pages :
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="TIDDNLLeaveRequestEP.ascx.cs" Inherits="TIDDNLLeaveRequestEP" %>
<dynamics:AxDataSource ID="AxDataSource1" runat="server"
DataSetName="TIDLeaveRequestDS" ProviderView="DNLLeaveRequest"
oninitializeddatasetrun="AxDataSource1_InitializedDataSetRun">
</dynamics:AxDataSource>
<dynamics:AxForm ID="AxForm1" runat="server" DataSourceID="AxDataSource1"
DataKeyNames="RecId,Added,AdvanceUsed,BlankLeave,carryused,ContactPhone,createdBy,createdDateTime,dataAreaId,DNLEmplRecId,DNLLeaveTypeRecId,IdApproval,IdPersonSubstitute,LeaveDateFrom,LeaveDateTo,modifiedBy,modifiedDateTime,Remarks,RequestDate,State,StatusFlag,SubmittedBy,SubmittedDateTime,TableId,TotalLeaveDays,TravellingLocation,getEmplName**,GetEntitlement**,getExpiration**,getLeaveDesc**,mostRecentComment**,requiredActionDueDate**,titleFields**"
DataMember="DNLLeaveRequest_Current" AutoGenerateInsertButton="True"
DefaultMode="Insert" UpdateOnPostBack="true" oninit="AxForm1_Init" >
<dynamics:AxMultiColumn ID="AxMultiColumn1" runat="server">
<dynamics:AxColumn ID="AxColumn2" runat="server">
<dynamics:AxGroup ID="groupLeaveRequest" runat="server" FormID="AxForm1"
Caption="Leave Request" CaptionAlign="Top" HorizontalAlign="Left"
Width="300px">
<Fields>
<dynamics:AxReferenceBoundField DataField="DNLEmplRecId"
DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest"
SortExpression="DNLEmplRecId" Mandatory="True">
</dynamics:AxReferenceBoundField>
<dynamics:AxReferenceBoundField AutoPostBack="True"
DataField="DNLLeaveTypeRecId" DataSet="TIDLeaveRequestDS"
DataSetView="DNLLeaveRequest" SortExpression="DNLLeaveTypeRecId"
Mandatory="True">
</dynamics:AxReferenceBoundField>
<dynamics:AxBoundField AutoPostBack="True" DataField="LeaveDateFrom"
DataSet="TIDLeaveRequestDS"
DataSetView="DNLLeaveRequest" SortExpression="LeaveDateFrom">
</dynamics:AxBoundField>
<dynamics:AxBoundField AutoPostBack="True" DataField="LeaveDateTo"
DataSet="TIDLeaveRequestDS"
DataSetView="DNLLeaveRequest" SortExpression="LeaveDateTo"
Mandatory="True" >
</dynamics:AxBoundField>
<dynamics:AxBoundField AutoPostBack="True" DataField="Remarks"
DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest"
SortExpression="Remarks">
</dynamics:AxBoundField>
<dynamics:AxBoundField AutoPostBack="True" DataField="TotalLeaveDays"
DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest"
SortExpression="TotalLeaveDays">
</dynamics:AxBoundField>
<dynamics:AxBoundField DataField="GetEntitlement**" DataSet="TIDLeaveRequestDS"
DataSetView="DNLLeaveRequest" ReadOnly="True">
</dynamics:AxBoundField>
</Fields>
</dynamics:AxGroup>
</dynamics:AxColumn>
</dynamics:AxMultiColumn>
</dynamics:AxForm>
C# code on visual studio for EP pages
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.DataVisualization.Charting;
using System.Web.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls.WebParts;
using Microsoft.Dynamics.AX.Framework.Portal.Data;
using Microsoft.Dynamics.AX.Framework.Services.Client;
using Microsoft.Dynamics.Framework.BusinessConnector.Session;
using Microsoft.Dynamics.Framework.BusinessConnector.Adapter;
using Microsoft.Dynamics.Framework.Portal;
using Microsoft.Dynamics.Framework.BusinessConnector.Proxy;
public partial class TIDDNLLeaveRequestEP : System.Web.UI.UserControl
{
DateTime dateFrom;
DateTime dateTo;
Int64 recIdLeaveType;
#region customMethod
private ISession AxSession
{
get
{
AxBaseWebPart webPart = AxBaseWebPart.GetWebpart(this);
return webPart == null ? null : webPart.Session;
}
}
static AxBoundField GetField(DataControlFieldCollection fields, string name)
{
foreach (DataControlField field in fields)
{
// Is this the field being searched for?
AxBoundField boundField = field as AxBoundField;
if (boundField != null && String.Compare(boundField.DataField, name, true) == 0)
{
return boundField;
}
}
// Nothing found, so return null.
return null;
}
private DataSetViewRow CurrentRow
{
get
{
try
{
DataSetView dataSetView;
dataSetView = this.AxDataSource1.GetDataSet().DataSetViews["DNLLeaveRequest"];
return (dataSetView == null) ? null : dataSetView.GetCurrent();
}
catch (System.Exception)
{
return null;
}
}
}
#endregion customMethod
protected void AxForm1_Init(object sender, EventArgs e)
{
//this.loadFirst();
this.AxForm1.DefaultMode = DetailsViewMode.Insert;
this.AxForm1.AutoGenerateInsertButton = true;
AxBoundField axbfNoNPK;
axbfNoNPK = GetField(groupLeaveRequest.Fields, "DNLEmplRecId");
axbfNoNPK.ReadOnly = true;
}
protected void Page_Load(object sender, EventArgs e)
{
}
protected void AxDataSource1_InitializedDataSetRun(object sender, AxDataSourceDataSetEventArgs e)
{
e.DataSet.Init();
}
}
preview :
This error will occur when you add new dataSource with function “addDataSource(tableNum(MyTable))” but not specify which it’s parent datasource
so example code before :
qbsInventDim = _query.addDataSource(tableNum(WMSLocation));
code after :
qbsInventDim = _query.dataSourceTable(tableNum(InventDim)).addDataSource(tableNum(WMSLocation))
code example :
salesLine.DefaultDimension = DimensionDefaultingService::serviceMergeDefaultDimensions(retailStoreTable.DefaultDimension,InventTable::find(TradeInRecord.ItemId).DefaultDimension);
code example :
public void PrintPenarikanBarang(TECTradeInId tradeInCode)
{
SrsReportRunController reportRunController;
Map queryContracts;
MapEnumerator mapEnum;
Query query;
QueryBuildRange range;
SrsReportDataContract contract;
TECTradeInPenarikanBarangReportContract rdpContract;
if(TECTradeInTable.TECTradeInCode)
{
reportRunController = new SrsReportRunController();
reportRunController.parmReportName(ssrsReportStr(TECPenarikanBarangTradeIn ,Report));
reportRunController.parmLoadFromSysLastValue(false);
// Set printer settings (print to file, format, filename, etc).
contract = reportRunController.parmReportContract();
rdpContract = contract.parmRdpContract() as TECTradeInPenarikanBarangReportContract;
queryContracts = contract.parmQueryContracts();
mapEnum = queryContracts.getEnumerator();
while(mapEnum.moveNext())
{
query = mapEnum.currentValue();
range = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(TECTradeInTable)),fieldNum(TECTradeInTable, TECTradeInCode));
range.value(queryValue(tradeInCode));
}
reportRunController.runReport();
}
}
code example
static void TIDF_getNextNumberSequenceWithoutUpdate(Args _args)
{
NumberSequenceTable numberSequenceTable;
numberSequenceTable = numberSequenceTable::findByNaturalKey('TIDFGC01');
info(strFmt("%1",NumberSeq::numInsertFormat(numberSequenceTable.NextRec,numberSequenceTable.Format)));
}
[SysEntryPointAttribute]
public void processReport()
{
//query class
QueryRun qr;
Query q;
QueryBuildDataSource qbds;
//table object
InventSum inventSum;
WMSLocation wmsLocation;
RetailStoreTable retailStoreTable;
TIDLocType tidLocType;
//
str headerName;
contract = this.parmDataContract() as TIDStoreDisplayReportContract;
parmShowQty = contract.parmShowQty();
parmFilterItem = contract.parmStoreDisplayFilterItem();
q = this.parmQuery();
q.dataSourceTable(tableNum(WMSLocation)).addRange(fieldNum(WMSLocation,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
switch(parmFilterItem)
{
case TIDStoreDisplayFilterItem::OnlyDisplay :
q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::Yes));
headerName = "Only Display Item";
break;
case TIDStoreDisplayFilterItem::NoDisplay :
qbds = q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
qbds.joinMode(JoinMode::NoExistsJoin);
qbds.relations(false);
qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
headerName = "No Display Item";
break;
case TIDStoreDisplayFilterItem::NotInDisplay :
q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::No));
headerName = "Not in Display Item";
break;
case TIDStoreDisplayFilterItem::HaveDisplay :
qbds = q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
qbds.joinMode(JoinMode::ExistsJoin);
qbds.relations(false);
qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
headerName = "Have Display Item";
break;
default :
headerName = "Store Display Item";
break;
}
// only display > 0
q.dataSourceTable(tableNum(InventSum)).addRange(fieldNum(InventSum,AvailPhysical)).value(queryValue(">0"));
qr = new QueryRun(q);
while(qr.next())
{
inventSum = qr.get(tableNum(inventSum));
wmsLocation = qr.get(tableNum(wmsLocation));
tidLocType = qr.get(tableNum(TIDLocType));
tempTable.clear();
tempTable.headerName = headerName;
tempTable.ItemId = inventSum.ItemId;
tempTable.EcoResDescription = TIDItemDisplayCustom::getItemDescription(inventSum.ItemId);
tempTable.EcoResProductName = TIDItemDisplayCustom::getItemName(inventSum.ItemId);
tempTable.NameAlias = TIDItemDisplayCustom::getItemModel(inventSum.ItemId);
tempTable.AvailPhysical = inventSum.AvailPhysical;
select firstOnly retailStoreTable where retailStoreTable.inventLocation == wmsLocation.inventLocationId;
tempTable.Store = retailStoreTable.StoreNumber;
tempTable.WMSLocationId = wmsLocation.wMSLocationId;
tempTable.IsDisplay = tidLocType.TIDIsDisplay;
tempTable.insert();
}
}
using aggregate tablix example
=Sum(IIF(Fields!IsDisplay.Value = "Yes", Fields!AvailPhysical.Value, Nothing))
sum all data on a tablix example
=Sum(IIf(Fields!BankAName.Value = "Standard Chartered Bank"
, Fields!Amount.Value
, Nothing)
, "DataSet1")
source :
http://stackoverflow.com/questions/18530541/ssrs-sum-expression-with-condition
AX single compilation from AOT can take hours. around 4-10 hours.
depends on your server hardware specs.
but with ax build, you can boost up the compilation process because its using pararel compilation.
on MSDN : https://msdn.microsoft.com/en-us/library/dn528954.aspx
and this feature only works on AX 2012 R2 CU7 and above version.
example on command prompt (run as admin) :
cd C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin
axbuild.exe xppcompileall /aos=01
static CurrencyExchangeRate getExchRate(CurrencyCode fromCurrency, CurrencyCode toCurrency, TransDate exchangeDate, RefRecId exchangeRateTypeRecId)
{
ExchangeRateHelper exchangeRateHelper;
;
exchangeRateHelper = ExchangeRateHelper::construct();
exchangeRateHelper.parmLedgerRecId(Ledger::current());
exchangeRateHelper.parmFromCurrency(fromCurrency);
exchangeRateHelper.parmToCurrency(toCurrency);
exchangeRateHelper.parmExchangeDate(exchangeDate);
exchangeRateHelper.parmExchangeRateTypeRecId(exchangeRateTypeRecId);
return ExchangeRateHelper::displayStoredExchangeRate_Static(exchangeRateHelper.getExchangeRate1(),
ExchangeRateCurrencyPair::getExchangeRateDisplayFactor(fromCurrency, toCurrency, exchangeRateTypeRecId, true));
}
static void OutputLicenseToText(Args _args)
{
#define.licenseVersion(2)
#define.KeywordLen(20)
#define.keywordLicense('License')
#define.keywordProperties('Properties')
#define.keywordCodes('Codes')
#define.keywordCodeLine('CodeLine')
#define.keywordDate('Date')
#define.keywordSerial('Serial')
#define.keywordValue('Value')
#define.blank('')
#define.space1(' ')
#define.space2(' ')
#define.space3(' ')
#define.spaceHash(' #')
#define.OutputFilename(@'C:\OutputLicenseKeys.txt')
#define.keywordInfo(1)
#define.keywordWarning(2)
SysConfig sysConfig;
SysLicenseCodeSort sysLicenseCodeSort;
container fileOut;
int i;
System.IO.StreamWriter sw;
InteropPermission perm = new InteropPermission(InteropKind::ClrInterop);
;
fileOut += "LicenseVersion " + strfmt("%1", #licenseVersion);
fileOut += #blank;
fileOut += #keywordLicense + #spaceHash + xSysConfig::find(ConfigType::LicenseName,0).Value;
fileOut += #blank;
fileOut += #space1 + #keywordProperties;
fileOut += #space2 + "Name" + #spaceHash + xSysConfig::find(ConfigType::LicenseName,0).Value;
fileOut += #space2 + #keywordSerial + #spaceHash + xSysConfig::find(ConfigType::SerialNo,0).Value;
fileOut += #space2 + #keywordDate + #spaceHash + xSysConfig::find(ConfigType::LicenseName,1).Value;
fileOut += #space1 + "EndProperties";
fileOut += #blank;
fileOut += #space1 + #keywordCodes;
// Build CodeLines
while select sysConfig
where sysConfig.configType == ConfigType::AccessCodes &&
sysConfig.value != #blank
join sysLicenseCodeSort
order by SortIdx
where sysLicenseCodeSort.Id == sysConfig.id
{
fileOut += #space2 + #keywordCodeLine + #spaceHash + int2str(sysConfig.id + 1);
fileOut += #space3 + #keywordValue + #spaceHash + sysConfig.value;
fileOut += #space2 + "EndCodeLine";
fileOut += #blank;
}
fileOut += #blank;
fileOut += #space2 + "EndCodes";
fileOut += #space1 + "EndLicense";
// Begin file output
perm.assert();
sw = new System.IO.StreamWriter(#OutputFilename);
for (i=1; i<=conLen(fileOut); i++)
{
sw.WriteLine(conPeek(fileOut, i));
}
sw.Flush();
sw.Close();
sw.Dispose();
CodeAccessPermission::revertAssert();
info("License successfully output to " + #OutputFilename);
}
source : http://alexondax.blogspot.com/2011/01/how-to-export-license-keys-that-are.html
static void TIDF_ExchangeRate(Args _args)
{
ExchangeRate exchangeRate;
ExchangeRateType ExchangeRateType;
ExchangeRateCurrencyPair exchangeRateCurrencyPair;
real exchRate;
CurrencyCode fromCurrency = "USD";
CurrencyCode toCurrency = "IDR";
TransDate transDate = today();
select firstonly exchangeRateCurrencyPair
where
exchangeRateCurrencyPair.ExchangeRateType == Ledger::find(Ledger::current()).TECFakturPajakAdjustExchangeRateType
&& exchangeRateCurrencyPair.FromCurrencyCode == fromCurrency
&& exchangeRateCurrencyPair.ToCurrencyCode == toCurrency;
exchRate = exchangeRate::findByDate(exchangeRateCurrencyPair.RecId,transDate).ExchangeRate;
info(strFmt("%1",exchRate/100));
}
when deploying SSRS report from ax 2012 R2 to R3 i got following error :
and i did some search for this error , here is the solution :
1. http://mafsarkhan.blogspot.com/2014/04/upgrade-ssrs-reports-from-dynamics-ax.html
2. https://technet.microsoft.com/en-us/library/hh389762.aspx
so i did export all the R2 reports in one project, replace all the version from 6.2.0.0 to 6.3.0.0 with notepad++, and reimport again, and done :d
public void init()
{
super();
element.updateDesign(InventDimFormDesignUpdate::Init);
//add by fanddy
if(element.args().record().TableId == tableNum(TECCustInOutClaimTable))
{
TECCustInOutClaimTable = element.args().record();
element.design().caption(strFmt("Customer Claim Line : %1, Receipt Number : %2",TECCustInOutClaimTable.TECCustInOutClaimId,TECCustInOutClaimTable.RetailReceiptId));
}
element.disableNewButton();
}
_loopApproval = 4;
while(_approvalQueue < conLen(approvalsName))
{
//conPeek(approvalsName,_approvalQueue)
_approvalQueue++;
_BudgetEntriesHeaderTMP.setFieldValue(strFmt("%1%2","ApprovalsName",_loopApproval),strFmt("%1",conPeek(approvalsName,_approvalQueue)));
_loopApproval++;
}
on class LedgerJournalWFApprSubmitToWF \ main
//add by fanddy, fix Bugs exceed budget still can submit on 30/03/2015
LedgerJournalCheckPost ledgerJournalCheckPost;
FormDataSource fds;
//end add by fanddy
Debug::assert(tableId == tableNum(LedgerJournalTable));
Debug::assert(recId != 0);
// The method has not been called correctly.
if (tableId != tableNum(LedgerJournalTable) ||
recId == 0)
{
throw error(strFmt("@SYS19306", funcName()));
}
//add by fanddy, fix Bugs exceed budget still can submit on 30/03/2015
else
{
ledgerJournalCheckPost= ledgerJournalCheckPost::newLedgerJournalTable(ledgerJournalTable,NoYes::No,NoYes::Yes);
ledgerJournalCheckPost.run();
if(!ledgerJournalCheckPost.getParmCheckJournalTID())
{
throw error("Can't submit this journal");
}
//refresh form dataSource , fix error when submit must restore form dataSource
fds = args.record().dataSource();
fds.research(true);
fds.refresh();
}
//end add by fanddy
i did some modify on class LedgerJournalCheckPost for returning checkJournal result.
on LedgerJournalCheckPost\run method
//edited by fanddy checkResultTID = this.checkJournal();
1. Create Forms , add datasource, add code as picture below
3. on init Form method add code below
3. on init Datasource method , add code below to clear automatic dynalinks between form.
3. add FORM HELP properties on EDT (Extended Data Type) , example EDT vendAccount .
4. Result as picture below.
public static RecId getNewDefaultDimension(RecId defaultDimension, Name dimName, str 255 dimValue)
{
container c;
RecId newdefaultDimension;
int i;
;
c = AxdDimensionUtil::getDimensionAttributeValueSetValue(defaultDimension);
i = conFind(c, dimName);
if (!i && !dimValue)
return defaultDimension;
if (i)
{
c = conDel(c, i+1, 1);
c = conDel(c, i, 1);
}
if (dimValue)
{
c += dimName;
c += dimValue;
}
c = conDel(c, 1, 1);
c = conIns(c, 1, conLen(c) / 2);
newdefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(c);
return newdefaultDimension;
}
public static container getDimensionName()
{
DimensionAttribute dimAttr;
DimensionAttributeSetItem dimAttrSetItem;
DimensionEnumeration dimensionSetId;
DimensionAttributeValue dimAttributeValue;
container DimensionName;
dimensionSetId = DimensionCache::getDimensionAttributeSetForLedger();
while select dimAttr order by Name
where dimAttr.Type != DimensionAttributeType::MainAccount
join RecId from dimAttrSetItem
where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
dimAttrSetItem.DimensionAttributeSet == dimensionSetId
{
dimensionName += dimAttr.Name;
}
return DimensionName;
}
1. Create new field as picture below
2. Go to the form, right click and choose restore to reload new field we added before. On the field method, add code below
public Common lookupReference(FormReferenceControl _formReferenceControl)
{
return NumberSequenceTable::lookupReference(_formReferenceControl);
}
3. Add reference group for the field at the form as picture below , and you are done.
4. Just run the form for testing.
public void lookup()
{
// super();
SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(SalesTable), this);
Query query = new Query();
QueryBuildDataSource qbds,qbds2,qbds3;
qbds = query.addDataSource(tablenum(SalesTable));
qbds.addOrderByField(fieldNum(SalesTable,SalesId));
qbds.addOrderByField(fieldNum(SalesTable,SalesName));
qbds.addOrderByField(fieldNum(SalesTable,SalesType));
qbds.addOrderByField(fieldNum(SalesTable,CustAccount));
qbds.addGroupByField(fieldNum(SalesTable,SalesId));
qbds.addGroupByField(fieldNum(SalesTable,SalesName));
qbds.addGroupByField(fieldNum(SalesTable,SalesType));
qbds.addGroupByField(fieldNum(SalesTable,CustAccount));
qbds2 = qbds.addDataSource(tableNum(TECDeliveryOrderTable));
qbds2.joinMode(JoinMode::NoExistsJoin);
qbds2.relations(false);
qbds2.addLink(fieldNum(SalesTable,SalesId),fieldNum(TECDeliveryOrderTable,TIDUnlockSalesId));
qbds3 = qbds.addDataSource(tableNum(SalesLine));
qbds3.joinMode(JoinMode::InnerJoin);
qbds3.relations(false);
qbds3.addLink(fieldNum(SalesTable,SalesId),fieldNum(SalesLine,SalesId));
qbds3.addRange(fieldNum(SalesLine,ItemId)).value(InventParameters::find().TIDItemIdServices);
sysTableLookup.addLookupfield(fieldnum(SalesTable, SalesId),true);
sysTableLookup.addLookupfield(fieldNum(SalesTable,SalesName));
sysTableLookup.addLookupfield(fieldNum(SalesTable,SalesType));
sysTableLookup.addLookupfield(fieldNum(SalesTable,CustAccount));
sysTableLookup.addLookupMethod("customerName");
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();
}
TreeNode objTreeNode;
objTreeNode = TreeNode::findNode(@"\Menu Items\Display\");
if (objTreeNode)
{
objTreeNode = objTreeNode.AOTfirstChild();
/*
change objTreeNode object properties
*/
while (objTreeNode)
{
objTreeNode.AOTsetProperties("PROPERTIES\n ViewUserLicense #" + 'TASK' + "\n MaintainUserLicense #" + 'TASK' + "\n ENDPROPERTIES\n");
objTreeNode.AOTsave();
objTreeNode = objTreeNode.AOTnextSibling();
}
}
source : http://dotnetxperience.blogspot.com/2012/10/modify-aot-items-from-code-ax-2012.html
static void TIDF_UploadUnitConversion(Args _args)
{
//coded by fanddy
// for upload Unit Conversion Purpose
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
COMVariantType type;
int row;
UnitOfMeasureConversion UnitOfMeasureConversion;
//table's field data
ItemId itemid;
UnitOfMeasureSymbol unitTo;
unitOfMeasureSymbol unitFrom;
Factor factor;
Filename fileName = 'C:\\#TECTURA#\\Master Data\\UploadFormula\\UnitConversionrev4.xlsx';
int loop;
int lastSerial = 1;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(fileName);
}
catch
{
throw error(strFmt("File: %1 cannot be opened", fileName));
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 2;
type = cells.item(row, 1).value().variantType();
ttsBegin;
while (type != COMVariantType::VT_EMPTY)
{
itemid = cells.item(row, 1).value().bStr();
unitFrom = cells.item(row, 2).value().bStr();
unitTo = cells.item(row, 3).value().bStr();
factor = cells.item(row, 4).value().double();
UnitOfMeasureConversion.clear();
UnitOfMeasureConversion.Product = EcoResProduct::findByProductNumber(itemid).RecId;
UnitOfMeasureConversion.FromUnitOfMeasure = UnitOfMeasure::findBySymbol(unitFrom).RecId;
UnitOfMeasureConversion.ToUnitOfMeasure = UnitOfMeasure::findBySymbol(unitTo).RecId;
UnitOfMeasureConversion.Factor = factor;
UnitOfMeasureConversion.Numerator = 1;
UnitOfMeasureConversion.Denominator = 1;
UnitOfMeasureConversion.insert();
row++;
type = cells.item(row, 1).value().variantType();
}
ttsCommit;
application.quit();
info(strFmt('Upload Success'));
}
static void findCompanyAccountingCurrency(Args _args)
{
info(Ledger::find(Ledger::current()).AccountingCurrency);
}
static void TIDF_TestCreateLoyaltyPointCustomer(Args _args)
{
//table
RetailLoyaltyCustTable retailLoyaltyCustTable;
RetailLoyaltyMSRCardTable retailLoyaltyMSRCardTable;
RetailLoyaltyMSRCardTrans retailLoyaltyMSRCardTrans;
AxRetailLoyaltyMSRCardTable AxRetailLoyaltyMSRCardTable;
RetailLoyaltySchemesTable RetailLoyaltySchemesTable;
//field
RetailLoyaltyCustId retailLoyaltyCustId;
CustAccount custAccount;
RetailLoyaltySchemeId retailLoyaltySchemeId;
RetailLoyaltyCardId retailLoyaltyCardId;
RetailLoyaltyPoints retailLoyaltyPoints;
retailLoyaltyCustId = "XXTESTXX03";
custAccount = "010203";
retailLoyaltySchemeId = "2";
retailLoyaltyCardId = "XXTESTXX10";
retailLoyaltyPoints = 50;
//save header
retailLoyaltyCustTable.clear();
retailLoyaltyCustTable.initValue();
retailLoyaltyCustTable.accountNum = custAccount;
retailLoyaltyCustTable.initFromCustTable(CustTable::find(custAccount));
retailLoyaltyCustTable.loyaltyCustId = retailLoyaltyCustId ;
retailLoyaltyCustTable.insert();
//save lines
AxRetailLoyaltyMSRCardTable = new AxRetailLoyaltyMSRCardTable();
AxRetailLoyaltyMSRCardTable.parmLoyaltyCustId(retailLoyaltyCustTable.loyaltyCustId);
AxRetailLoyaltyMSRCardTable.parmLinkId(retailLoyaltyCustTable.loyaltyCustId);
RetailLoyaltySchemesTable = RetailLoyaltySchemesTable::find(retailLoyaltySchemeId);
if(RetailLoyaltySchemesTable)
{
AxRetailLoyaltyMSRCardTable.parmLoyaltySchemeId(RetailLoyaltySchemesTable.loyaltySchemeId);
}
AxRetailLoyaltyMSRCardTable.parmLoyaltyTender(RetailLoyaltyTenderTypeBase::AsCardTender);
AxRetailLoyaltyMSRCardTable.parmCardNumber(retailLoyaltyCardId);
AxRetailLoyaltyMSRCardTable.save();
//upload points.
retailLoyaltyMSRCardTrans.clear();
retailLoyaltyMSRCardTrans.loyaltySchemeId = RetailLoyaltySchemesTable.loyaltySchemeId;
retailLoyaltyMSRCardTrans.loyaltyCustId = retailLoyaltyCustId;
retailLoyaltyMSRCardTrans.cardNumber = AxRetailLoyaltyMSRCardTable.retailLoyaltyMSRCardTable().cardNumber;
retailLoyaltyMSRCardTrans.entryType = RetailLoyaltyEntryTypeBase::Sale;
retailLoyaltyMSRCardTrans.expirationDate = maxDate();
retailLoyaltyMSRCardTrans.points = retailLoyaltyPoints;
//include store or staff or any
//retailLoyaltyMSRCardTrans.storeId
retailLoyaltyMSRCardTrans.insert();
}
TECDeliveryOrderTable_ds.query().dataSourceTable(tablenum(TECDeliveryOrderTable)).addSortField(fieldNum(TECDeliveryOrderTable,InvoiceDate),SortOrder::Descending);
Menu : ystem administration, Setup, Licensing. -> Load License File
public void lookup()
{
QueryBuildDataSource queryBuildDataSource;
//super();
//coded by fanddy,
SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(TIDInventLookup), this);
Query query = new Query();
queryBuildDataSource = query.addDataSource(tableNum(TIDInventLookup));
sysTableLookup.addLookupfield(fieldnum(TIDInventLookup,NameAlias));
sysTableLookup.addLookupfield(fieldnum(TIDInventLookup,DisplayProductNumber));
sysTableLookup.addLookupfield(fieldnum(TIDInventLookup,Name));
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();
}
try one or all of this solutions below :
1. Generate Incremental CIL
2. Compile on the customized report including report class (Contract, controller, DP). If you have modified TMP table , please do synchronize on that table.
3. Clean User Usage data on Tools-> Option -> Usages Data.
4. Restarting reporting services.
5. Refresh all cache on AOT, on AOT-> Tools -> Caches
5. Re-Deploy the SSRS report.
6. Restarting AOS. << sometimes it works like magic -_-“
private void runWithAssetGroupId(NoYes parmendOfYearOnly,AssetGroupId assetGroupId)
{
TmpDateSum tmpDateSum;
AssetTable assetTable;
AssetBook assetBook;
AssetBookTable assetBookTable;
//main account
AssetLedgerAccounts assetLedgerAccounts;
MainAccountNum mainAccountNum;
while select assetTable
join assetBook
join assetBookTable
where
assetTable.AssetGroup == assetGroupId
&& assetTable.AssetId == assetBook.AssetId
&& assetBook.BookId == assetBookTable.BookId
&& assetBook.Depreciation == NoYes::Yes
&& assetBookTable.CurrentOperationsTax == CurrentOperationsTax
&& assetBook.Status == AssetStatus::Open
{
lineTMP.clear();
assetLedgerAccounts = AssetLedgerAccounts::find(assetBook.BookId,AssetParameters::find().PostingProfile,enum2int(AssetTransType::Depreciation),TableGroupAll::GroupId,assetTable.AssetGroup);
mainAccountNum = MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.OffsetLedgerDimension).MainAccount).MainAccountId;
//Method calculation provided by AX
tmpDateSum.setTmpData(assetBook.initDepreciationProfile());
//set header line
lineTMP.AssetGroupId = assetTable.AssetGroup;
lineTMP.AssetId = assetTable.AssetId;
lineTMP.MainAccount = mainAccountNum;
lineTMP.rGrossFa = assetBook.AcquisitionPrice;
//cut off date
while select tmpDateSum
where
tmpDateSum.TmpNumerals01 != 0
&& tmpDateSum.TransDate <= endYearDate
{
lineTMP.Period = tmpDateSum.TransDate;
lineTMP.rAccDepr = tmpDateSum.TmpNumerals01;
lineTMP.rDeprExp = tmpDateSum.Total;
lineTMP.rNetBookvalue = tmpDateSum.TmpNumerals02;
lineTMP.insert();
}
}
}
Class : SRSReportServerWarmup
source :
http://blogs.msdn.com/b/axsupport/archive/2014/03/11/ax-content-warm-up-those-ssrs-servers.aspx
if(formHasMethod(args.caller(), identifierstr(TIDgetSelectedRecord))) // -< identifierstr(TIDgetSelectedRecord))) is method on caller form
{
salesAgreementGenerateReleaseOrder.TIDparmAgreementLine(args.caller().TIDgetSelectedRecord());
_container = salesAgreementGenerateReleaseOrder.TIDparmAgreementLine();
info(strFmt("%1",conPeek(_container,1)));
}
Args args = new Args(); args.record(myArgumentRecord); args.caller(this); new MenuFunction(menuItemOutputStr(MyOutputMenuItem), MenuItemType::Output).run(args);
When we create SO from sales agreement, we will face a problem that occur on standard version of AX 2012 if number of sales agreement record are thousands and we only want to choose let’s say 2 or 3 line from sales agreement line to Sales Line.
we can choose records from agreement line by fill the quantity, and the problem is standard AX still will loop all records no matter the quantity is fill or not. and all thosands will going through process that not needed.
Class : SalesAutoCreate\Create
void create()
{
#OCCRetryCount
try
{
setprefix("@SYS55110");
ttsbegin;
while (this.recordExist())
{
this.setCust();
setprefix(#PreFixField(CustTable,AccountNum));
this.setSalesTable();
this.setSalesLine();
setprefix(#PreFixField(SalesLine,ItemId));
this.nextRecord();
}
this.endUpdate();
ttscommit;
}
catch (Exception::Deadlock)
{
retry;
}
catch (Exception::UpdateConflict)
{
if (appl.ttsLevel() == 0)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::UpdateConflictNotRecovered;
}
else
{
retry;
}
}
else
{
throw Exception::UpdateConflict;
}
}
}
i tried debug and i found the issue that made sales agreement release order process become so slow. and made a modification as below.
Class : SalesAutoCreate_ReleaseFromAgreement\New
protected void new(Common _releaseOrderLine,
Object _callBackClass = null,
Common _releaseOrderTable = null)
{
//modify by fanddy
//purpose : speeding up release agreement from BMP
SalesCreateReleaseOrderLineTmp TIDSalesCreateReleaseOrderLineTmp;
releaseOrderLine = _releaseOrderLine as SalesCreateReleaseOrderLineTmp;
delete_from releaseOrderLine where releaseOrderLine.SalesQty < 1;
//end modify fanddy
releaseOrderTable = _releaseOrderTable as SalesCreateReleaseOrderTableTmp;
this.firstRecord();
createFromAgreementLine = AgreementLineQuantityCommitment::find(releaseOrderLine.AgreementLineQuantityCommitment, false);
createFromSalesAgreement = SalesAgreementHeader::find(createFromAgreementLine.Agreement ? createFromAgreementLine.Agreement : releaseOrderTable.AgreementHeader);
currentSalesId = '';
firstRecord = true;
super(_releaseOrderLine,_callBackClass);
// <GEERU>
countryRegion_RU = SysCountryRegionCode::isLegalEntityInCountryRegion([#isoRU]);
// </GEERU>
Voila !! Now your when you create an SO with 2-3 SO lines originated from thousands line data of sales agreement, the process has been boosted !!
SrsReportRunController controller = new SrsReportRunController(); SysUserLicenseCountRDPContract rdpContract = new SysUserLicenseCountRDPContract(); SRSPrintDestinationSettings settings; // Define report and report design to use controller.parmReportName(ssrsReportStr(SysUserLicenseCountReport, Report)); // Use execution mode appropriate to your situation controller.parmExecutionMode(SysOperationExecutionMode::ScheduledBatch); // Suppress report dialog controller.parmShowDialog(false); // Explicitly provide all required parameters rdpContract.parmReportStateDate(systemDateGet()); controller.parmReportContract().parmRdpContract(rdpContract); // Change print settings as needed settings = controller.parmReportContract().parmPrintSettings(); settings.printMediumType(SRSPrintMediumType::File); settings.fileFormat(SRSReportFileFormat::Excel); settings.fileName(@'\\share\UserLicenseCount.xlsx'); // Execute the report controller.startOperation();
an error will occured when you try validate ledgerJournalTrans.
how to fix this :
static void TIDF_fixBugsUploadFixedAssets(Args _args)
{
ledgerjournaltrans ledgerJournalTrans;
LedgerJournalTrans_Asset cekExist;
LedgerJournalTrans_Asset LedgerJournalTrans_Asset;
while select ledgerJournalTrans
where
ledgerJournalTrans.JournalNum == 'GEN-000007'
{
select cekExist where cekExist.RefRecId == ledgerJournalTrans.RecId;
if (ledgerJournalTrans.isFixedAssetsTransaction() && !cekExist)
{
ledgerJournalTrans_Asset.clear();
ledgerJournalTrans_Asset.initValue();
ledgerJournalTrans_Asset.RefRecId = ledgerJournalTrans.RecId;
ledgerJournalTrans_Asset.AssetId = ledgerJournalTrans.getAssetId();
ledgerJournalTrans_Asset.Company = ledgerJournalTrans.getAssetCompany();
ledgerJournalTrans_Asset.TransType = AssetTransTypeJournal::Acquisition;
ledgerJournalTrans_Asset.BookId = "ALL";
if (!ledgerJournalTrans_Asset.validateWrite())
throw Exception::Error;
ledgerJournalTrans_Asset.insert();
}
}
}
source : http://www.cnblogs.com/Fandyx/p/3343688.html
Found this error when trying synchronize table PayrollProviderTaxRegion
just set table property ‘InstanceRelationType’ to ‘InstanceRelationType’ field
source : https://amazingax.wordpress.com/2013/03/26/microsoft-dynamics-ax-2012-table-inheritance-part-1/
class : InventUpdate\writeInventTransAutoDim
Create a enum and set it on menu item properties as example below.
override method preRunModifyContract on report controller class as example code below.
protected void preRunModifyContract()
{
//modify the parameter value of the contract
// if( element.args().parmEnumType() == EnumNum( NoYes ) )
if(this.parmArgs().parmEnumType() == enumNum(TIDSalesDailyRecon))
{
switch(this.parmArgs().parmEnum())
{
case TIDSalesDailyRecon::SalesInvoice :
SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(RetailTransactionPaymentTrans),fieldNum(RetailTransactionPaymentTrans, RecId),SysQuery::value(5637149251));
break;
case TIDSalesDailyRecon::SalesReturn :
SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(RetailTransactionPaymentTrans),fieldNum(RetailTransactionPaymentTrans, RecId),SysQuery::value(this.parmArgs().record().RecId));
break;
case TIDSalesDailyRecon::SalesExIndent :
break;
}
}
}
this.parmArgs().record().RecId value is come from :
SrsReportRunController controller; Args = new Args() controller = new SrsReportRunController(); args.record(TABLES_record); controller.parmReportName(ssrsReportStr(REPORT_NAME, REPORT_DESIGN_NAME)); controller.startOperation();
source :
1. http://www.dynamics101.com/2014/01/using-controller-class-developing-ssrs-reports-microsoft-dynamics-ax-2012/
2. http://dynamics.folio3.com/x-tips-and-tricks-passing-parameters-microsoft-dynamics-ax/
void clicked()
{
PriceDiscAdmTrans copyLines;
super();
copyLines.data(PriceDiscAdmTrans);
copyLines.insert();
PriceDiscAdmTrans_ds.research();
}
add this code below to LedgerJournalDP\InsertLedgerJournalTmp
if(_accountOffsetAccount == AccountOffsetaccount::Account)
{
switch(_ledgerJournalTrans.AccountType)
{
case LedgerJournalACType::Ledger :
ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(_ledgerJournalTrans.LedgerDimension).MainAccount).MainAccountId;
break;
case LedgerJournalACType::Bank :
select * from dimAttrValueCombo
join Name from bankAccountTable
where bankAccountTable.AccountID == dimAttrValueCombo.DisplayValue
&& dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(bankAccountTable.LedgerDimension).MainAccount).MainAccountId;
ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue, bankAccountTable.Name);
break;
case LedgerJournalACType::Vend :
select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
VendLedgerAccounts = VendLedgerAccounts::find(_ledgerJournalTrans.PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(VendLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,VendTable::find(dimAttrValueCombo.DisplayValue).name());
break;
case LedgerJournalACType::Cust :
select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
CustLedgerAccounts = CustLedgerAccounts::find(_ledgerJournalTrans.PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(CustLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,CustTable::find(dimAttrValueCombo.DisplayValue).name());
break;
case LedgerJournalACType::FixedAssets :
select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
selecT firstOnly ledgerJournalTrans_asset where ledgerJournalTrans_asset.RefRecId == _ledgerJournalTrans.RecId;
assetLedgerAccounts = AssetLedgerAccounts::find(ledgerJournalTrans_asset.BookId,_ledgerJournalTrans.PostingProfile,enum2int(ledgerJournalTrans_asset.TransType),TableGroupAll::GroupId,AssetTable::find(dimAttrValueCombo.DisplayValue).AssetGroup);
ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.LedgerDimension).MainAccount).MainAccountId;
break;
}
}
else
{
switch(_ledgerJournalTrans.AccountType)
{
case LedgerJournalACType::Ledger :
ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(_ledgerJournalTrans.LedgerDimension).MainAccount).MainAccountId;
break;
case LedgerJournalACType::Bank :
select * from dimAttrValueCombo
join Name from bankAccountTable
where bankAccountTable.AccountID == dimAttrValueCombo.DisplayValue
&& dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(bankAccountTable.LedgerDimension).MainAccount).MainAccountId;
ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue, bankAccountTable.Name);
break;
case LedgerJournalACType::Vend :
select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
VendLedgerAccounts = VendLedgerAccounts::find(VendParameters::find().PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(VendLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,VendTable::find(dimAttrValueCombo.DisplayValue).name());
break;
case LedgerJournalACType::Cust :
select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
CustLedgerAccounts = CustLedgerAccounts::find(CustParameters::find().PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(CustLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,CustTable::find(dimAttrValueCombo.DisplayValue).name());
break;
case LedgerJournalACType::FixedAssets :
select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
selecT firstOnly ledgerJournalTrans_asset where ledgerJournalTrans_asset.RefRecId == _ledgerJournalTrans.RecId;
assetLedgerAccounts = AssetLedgerAccounts::find(ledgerJournalTrans_asset.BookId,AssetParameters::find().PostingProfile,enum2int(ledgerJournalTrans_asset.TransType),TableGroupAll::GroupId,AssetTable::find(dimAttrValueCombo.DisplayValue).AssetGroup);
ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.LedgerDimension).MainAccount).MainAccountId;
break;
}
}
This class purpose is to delete all trans data from database. This class is very useful for pre-live phase.
just run class : SysDatabaseTransDelete.
example code on form VendPurchaseOrderJournal , datasource VendPurchOrderJournal method init()
public void init()
{
QueryBuildDataSource queryDataSourceLink;
super();
if (element.args().dataset() == tablenum(PurchReqLine))
{
this.query().dataSourceTable(tablenum(VendPurchOrderJour)).clearDynalinks();
queryDataSourceLink = this.query().dataSourceName(identifierstr(VendPurchOrderJour));
queryDataSourceLink.relations(true);
queryDataSourceLink.addDynalink(fieldnum(VendPurchOrderJour, PurchId),
element.args().record(),
fieldnum(PurchReqLine, PurchId));
}
//add by fanddy , for inqueries confirm PO
if (element.args().dataset() == tablenum(TECPOGroupHeader))
{
this.query().dataSourceTable(tablenum(VendPurchOrderJour)).clearDynalinks();
queryDataSourceLink = this.query().dataSourceName(identifierstr(VendPurchOrderJour));
queryDataSourceLink.relations(true);
queryDataSourceLink.addDynalink(fieldnum(VendPurchOrderJour, TIDPOGroupId),
element.args().record(),
fieldnum(TECPOGroupHeader, POGroupId));
}
//END
}
Version : AX 2012 R2 CU7
 
Found this error this morning when select a record in budgetplanning lookup.
Error detail :
“Found a BudgetPlanningProcessTmp record instead of an expected BudgetPlanningProcess record. Check to see if an override of the resolveReference method is returning a record from the correct table.”
Solution :
Revert static method BudgetPlanGenerateBudgetTrnsForm::lookupBudgetPlanningProcess back to SYS layer using compare.
public void lookup(FormControl _formControl, str _filterStr)
{
//coded by fanddy
if (TECPOGroupStore.TIDTransferToInventSiteId)
{
InventLocation::lookupBySiteIdAllTypes(_formControl, TECPOGroupStore.TIDTransferToInventSiteId);
}
else
{
super(_formControl, _filterStr);
}
}
TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,Status)).value(sysquery::value(TECPOGroupStatus::Confirmed));
public static str 255 getDimensionValueTID(RecId defaultDimension, Name dimName)
{
DimensionAttributeValueSetStorage dimStorage;
;
dimStorage = DimensionAttributeValueSetStorage::find(defaultDimension);
return dimStorage.getDisplayValueByDimensionAttribute(DimensionAttribute::findByName(dimName).RecId);
}
how to use :
getDimensionValueTID(_ledgerJournalTrans.DefaultDimension, 'BusinessUnit')
found a better solution from post comment :
AxLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, offsetLedgerDimension), false);
some circumtances i must use global variable.
read :
http://msdn.microsoft.com/en-us/library/aa891830.aspx
more example how to use :
SysGlobalCache globalCache;
globalCache = ClassFactory.globalCache();
globalCache.set(strFmt("%1%2",curUserId(),"WhatEver"), 0, "ANY VALUE");
on other class.
SysGlobalCache globalCache;
globalCache = ClassFactory.globalCache();
info(strFmt("%1",globalCache.get(strFmt("%1%2",curUserId(),"WhatEver"), 0)));
public static str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR): return _cv.bStr();
case (COMVariantType::VT_R4): return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_R8): return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DECIMAL): return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DATE): return date2str(_cv.date(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY): return '';
default: throw error(strfmt('@SYS26908', _cv.variantType()));
}
return '';
}
how to use example :
YourClass::COMVariant2Str(cells.item(row,17).value());
just duplicate class tutorial_runBaseBatch, then modify the following class :
on class declaration :
public class JournalUploadExcextends RunBaseBatch
{
DialogField dfFileName;
FileName fileName ;
#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
fileName
#endmacro
}
on dialog() :
public Object dialog()
{
#File
DialogRunbase dialog = super();
#resAppl
;
dfFileName = dialog.addFieldValue(extendedTypeStr(FileNameOpen),fileName);
dialog.filenameLookupFilter(["All files", #AllFiles]);
return dialog;
}
on method getFromDialog() :
public boolean getFromDialog()
{
//do what ever you want in this class. i call upload journal method with PathFile as parameter
JournalUploadExc::JournalUpload(dfFileName.value());
return super();
}
voila.. !! done.. !!
public static boolean JournalUpload(str _filePath)
{
container accEntryPattern;
container offSetEntryPattern;
LedgerJournalACType LedgerJournalACType;
ledgerJournalName ledgerJournalName;
journalID JournalID;
axLedgerJournalTable axLedgerJournalTable;
axledgerJournalTrans axLedgerJournalTrans;
LedgerJournalType JournalType = ledgerJournalType::Daily;
SysExcelApplication application = SysExcelApplication::construct();
SysExcelWorkbooks workbooks = application.workbooks();
SysExcelWorkbook workbook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
str filename, numSeq;
boolean _return = true;
;
startLengthyOperation();
//filename = "C:\\GJCashBank30April2013.xlsx";
filename = _filePath;
try
{
if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
{
workbook = workbooks.item(1);
workSheets = workbook.worksheets();
workSheet = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
cells = workSheet.cells();
row = 5;
ttsBegin;
axLedgerJournalTable = new axLedgerJournalTable();
axLedgerJournalTable.parmJournalName("GenJrn");
axLedgerJournalTable.parmJournalType(JournalType);
axLedgerJournalTable.parmName("General Journal");
axLedgerJournalTable.save();
JournalID = axLedgerJournalTable.parmJournalNum();
while (cells.item(row,2).value().bStr() != "")
{
//Create Trans ...
axLedgerJournalTrans = new axLedgerJournalTrans();
axLedgerJournalTrans.parmJournalNum(JournalID);
//start from A
axLedgerJournalTrans.parmTransDate(cells.item(row,1).value().date());
axLedgerJournalTrans.parmAccountType( str2Enum(LedgerJournalACType, cells.item(row,2).value().bStr()));
if(cells.item(row,2).value().bStr() == "Ledger")
{
accEntryPattern =
[ cells.item(row, 3).value().bStr() + "-" + "Disp",
cells.item(row, 3).value().bStr(),
4,
"BusinessUnit",
cells.item(row,17).value().bStr(),
"Department",
cells.item(row,18).value().bStr(),
"ProductCode",
cells.item(row,19).value().bStr(),
"ProjectCode",
cells.item(row,20).value().bStr()
];
axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accEntryPattern));
}
if(cells.item(row,5).value().double() != 0)
{
axLedgerJournalTrans.parmAmountCurDebit(cells.item(row,5).value().double());
}
if(cells.item(row,6).value().double() != 0)
{
axLedgerJournalTrans.parmAmountCurCredit(cells.item(row,6).value().double());
}
axLedgerJournalTrans.parmtxt(cells.item(row,7).value().bStr());
axLedgerJournalTrans.parmOffsetAccountType( str2Enum(LedgerJournalACType,cells.item(row,6).value().bStr()));
if(cells.item(row,8).value().bStr() == "Ledger")
{
offSetEntryPattern =
[ cells.item(row, 9).value().bStr() + "-" + "Disp",
cells.item(row, 9).value().bStr(),
4,
"BusinessUnit",
cells.item(row,17).value().bStr(),
"Department",
cells.item(row,18).value().bStr(),
"ProductCode",
cells.item(row,19).value().bStr(),
"ProjectCode",
cells.item(row,20).value().bStr()
];
axLedgerJournalTrans.parmOffsetLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern));
}
//
axLedgerJournalTrans.parmCurrencyCode(cells.item(row,10).value().bStr());
axLedgerJournalTrans.parmExchRate(cells.item(row,11).value().double());
axLedgerJournalTrans.parmInvoice(cells.item(row,12).value().bStr());
axLedgerJournalTrans.parmPaymReference(cells.item(row,13).value().bStr());
axLedgerJournalTrans.parmBankTransType(cells.item(row,14).value().bStr());
axLedgerJournalTrans.parmDocumentNum(cells.item(row,15).value().bStr());
axLedgerJournalTrans.parmDocumentDate(cells.item(row,16).value().date());
//
axLedgerJournalTrans.save();
row++;
ttsCommit;
}
info('Imported Items');
}
}
catch(Exception::Error)
{
_return = false;
}
return _return;
}
static void main(Args _args)
{
FileName fileName ;
container c;
FileIOPermission permission;
TextBuffer textBuffer;
str readText;
str readLine;
int etgline;
int i = 0;
Dialog dialog = new Dialog("Journal Upload Excel");
DialogField dfFileName;
str 100 filename2;
container con;
;
#File
dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
dialog.filenameLookupFilter(["All files", #AllFiles]);
if (dialog.run())
{
fileName = dfFileName.value();
info(filename);
}
}
void clicked()
{
PurchTable purchTable;
PurchLine purchLine;
InventDim inventDim;
NumberSeq numberSeq;
//add by fanddy
AxPurchTable axPurchTable;
AxPurchLine axPurchLine;
PurchFormLetter purchformLetter;
//END
//create PO Header
purchTable.clear();
purchTable.initFromVendTable(VendTable::find(TECPOGroupHeader.VendAccount));
purchTable.DefaultDimension = InventTable::find(POGroupLine.ItemId).DefaultDimension;
axPurchTable = axPurchTable::newPurchTable(purchTable);
axPurchTable.parmPurchId(NumberSeq::newGetNum(PurchParameters::numRefPurchId()).num());
axPurchTable.parmPurchaseType(PurchaseType::Purch);
axPurchTable.parmDocumentStatus(DocumentStatus::PurchaseOrder);
axPurchTable.parmAccountingDate(systemDateGet());
axPurchTable.parmDeliveryDate(systemDateGet());
axPurchTable.parmPurchStatus(PurchStatus::Backorder);
axPurchTable.save();
info(strFmt('Create PO %1', Purchtable.PurchId));
//create PO Line
purchLine.clear();
purchLine.initValue();
purchLine.initFromPurchTable(purchTable);
purchLine.initFromInventTable(InventTable::find(POGroupLine2.ItemId));
axPurchLine = AxPurchLine::newPurchLine(purchLine);
axpurchLine.parmItemId('YOUR ITEMID');
axPurchLine.parmPurchQty(YOUR QTY);
axPurchLine.parmPurchPrice(YOUR PRICE);
axPurchLine.parmInventDimId('YOUR INVENTDIMID');
axPurchLine.save();
}
static void AutoAllocateCharges_beforeConfirmPO(Args _args)
{
PurchTable purchTable;
PurchFormLetter purchFormLetter;
Args args = new Args();
MarkupAllocation markupAllocation;
select firstOnly purchTable where purchTable.PurchId=='1016-PO-14-000191';
args.record(purchTable);
//auto allocate charges
markupAllocation = MarkupAllocation::newMarkupAllocation(args.record());
markupAllocation.run(args.parmObject());
purchFormLetter = PurchFormLetter::construct(DocumentStatus::PurchaseOrder);
purchFormLetter.update(purchTable, purchTable.PurchId);
}
MainAccountNum mainAccountNum;
if(_ledgerJournalTrans.AccountType== LedgerJournalACType::Ledger)
{
mainAccountNum= MainAccount::find(DimensionAttributeValueCombination::find(_ledgerJournalTrans.LedgerDimension).MainAccount).MainAccountId;
}
public void prePromptModifyContract()
{
//add a range in the report query
SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(SSRSReportDemo),fieldNum(SSRSReportDemo, RecId),SysQuery::value(this.parmArgs().record().RecId));
}
source : https://community.dynamics.com/ax/b/dynamics101trainingcenterax/archive/2014/01/30/using-controller-class-in-developing-ssrs-reports-in-microsoft-dynamics-ax-2012.aspx
public FreeText getDecendants(ItemId _itemid)
{
str Decendants;
EcoResProductCategory ecoResProductCategory;
EcoResCategory ecoResCategory;
EcoResCategory ecoResCategoryAscendants;
select firstOnly ecoResProductCategory
where
ecoResProductCategory.Product==EcoResProduct::findByProductNumber(_itemid).RecId;
select firstOnly ecoResCategory where ecoResCategory.RecId==ecoResProductCategory.Category;
Decendants = ecoResCategory.Name;
while select * from ecoResCategoryAscendants
order by ecoResCategoryAscendants.NestedSetRight
where ecoResCategoryAscendants.CategoryHierarchy == EcoResCategory.CategoryHierarchy
&& ecoResCategoryAscendants.NestedSetLeft < EcoResCategory.NestedSetLeft
&& ecoResCategoryAscendants.NestedSetRight > EcoResCategory.NestedSetRight
{
Decendants = strFmt("%1-%2",ecoResCategoryAscendants.Name,Decendants);
}
return Decendants;
}
example code :
retailPeriodicDiscount_ds.query()
.dataSourceTable(tableNum(RetailPeriodicDiscount))
.addRange(fieldNum(RetailPeriodicDiscount, PeriodicDiscountType))
.value(queryValue(_discountType));
example form on AX 2012 : RetailPeriodicDiscount
description on MSDN :
“This means that certain tasks may have improved performance by using the power of the SQL server.”
replace code below for a better perfomance.
MyTable myTableBuffer;
;
while select forUpdate mytableBuffer
{
mytableBuffer.field1 = field * 1.10;
}
with
MyTable myTableBuffer;
;
update_recordset myTableBuffer
setting field1 = field1 * 1.10;
source : http://msdn.microsoft.com/en-us/library/aa674382.aspx
AssetBook.DefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(financialDimension);
contributor : _le
Hi there ,
after the report being save to PDF on local drive, i call adobe to print barcode,
i have to did that, because SSRS can’t print on landscape printer.
here is my example code
void clicked()
{
//AIFD_PrintBarcode
AI_PrintBarcodeController aiBarcode = new AI_PrintBarcodeController();
AI_PrintBarcodeContract aiContract = new AI_PrintBarcodeContract();
ProdJournalProd prodJournalProdLoc;
SRSPrintDestinationSettings printSettings = new SRSPrintDestinationSettings();
PrintJobSettings printJobSettings = new PrintJobSettings();
Dialog dialog = new Dialog();
DialogField dialogFileName;
str adobeExe;
str adobeParm;
str fileName;
int fileCount;
//AIFD
InteropPermission permission;
str errorMessage;
//END
real WeightInreal;
permission = new InteropPermission(InteropKind::ClrInterop);
permission.assert();
super();
try
{
prodJournalProdLoc = ProdJournalProd_ds.getFirst(true);
while (prodJournalProdLoc)
{
fileCount++;
aiBarcode.parmReportName(ssrsReportStr(AI_PrintBarcoderReport,Report));
aiBarcode.parmShowDialog(false);
aiContract.parmRecordId(prodJournalProdLoc.RecId);
aiContract.parmDocumentTitle("x");
//printSettings = aiBarcode.parmReportContract().parmPrintSettings();
printSettings.printMediumType(SRSPrintMediumType::File);
//printSettings.landscape(false);
//printSettings.printerPageSettings('KRRLabelBarcode');
//info(strFmt("%1",printSettings.printerPageSettings()));
fileName = strFmt("D:\\barcode%1.pdf",fileCount);
printSettings.fileFormat(SRSReportFileFormat::PDF);
printSettings.fileName(fileName);
printSettings.printerName("TOSHIBA B-SA4TP TS");
printSettings.landscape(false);
printSettings.overwriteFile(true);
aiBarcode.parmReportContract().parmRdpContract(aiContract);
aiBarcode.parmReportContract().parmPrintSettings(printSettings);
aiBarcode.startOperation();
WeightInReal = sleep(500);
//printJobSettings.printerSettings('SysPrintForm');
printJobSettings.setTarget(PrintMedium::Printer);
printJobSettings.preferredTarget(PrintMedium::Printer);
adobeExe = WinAPI::findExecutable(fileName);
adobeParm = strFmt(' /t "%1" "%2" "%3" "%4"',
fileName,
"TOSHIBA B-SA4TP TS",
printJobSettings.printerDriverName(),
printJobSettings.printerPortName());
winAPI::shellExecute(adobeExe, adobeParm);
WeightInReal = sleep(500);
prodJournalProdLoc = ProdJournalProd_ds.getNext();
}
CodeAccessPermission::revertAssert();
}
catch(Exception::CLRError)
{
errorMessage = AifUtil::getClrErrorMessage();
CodeAccessPermission::revertAssert();
throw error(errorMessage);
}
//END
}
//Get PortComm number that already set from .txt file on local drive.
private str getPortComm()
{
#file
str getCOMport;
boolean cekPortExist;
AsciiIo file;
Container data;
int i;
System.IO.FileInfo fileInfo;
;
new InteropPermission(InteropKind::ClrInterop).assert();
fileInfo = new System.IO.FileInfo(@”C:\portcomm.txt”);
if(fileInfo.get_Exists())
{
file = new AsciiIo(@”C:\portcomm.txt”,#io_read);
file.inFieldDelimiter(‘\t’);
file.inRecordDelimiter(‘\n’);
if(file)
{
while(file.status() == IO_Status::Ok)
{
data = file.read();
for(i = 1; i <= conlen(data); i++)
{
getCOMport = any2str(conpeek(data, i));
}
}
}
}
return strLTrim(getCOMport);
}
————————————
//Get Weight from PortComm
private real AI_getWeight()
{
System.String netstring;
System.String sample;
int WeightInReal;
int tryCount;
str value;
str value2;
str portComm;
System.IO.Ports.SerialPort port3;
real weightFinal;
tryCount = 0;
portComm = this.getPortComm();
if(portComm)
{
port3 = new System.IO.Ports.SerialPort();
value = “0”;
port3.set_PortName(portComm);
port3.set_BaudRate(9600);
port3.set_Parity(System.IO.Ports.Parity::None);
port3.set_DataBits(8);
port3.set_StopBits(System.IO.Ports.StopBits::One);
port3.set_Handshake(System.IO.Ports.Handshake::None);
try
{
port3.Open();
while(tryCount<1)
{
WeightInReal = sleep(500);
netstring = port3.ReadExisting();
value = netstring;
value2 = value;
tryCount++;
}
port3.Close();
port3.Dispose();
}
catch(Exception::CLRError)
{
info(“Wrong Port Comm Number”);
}
value2 = strReplace(value,”=”,””);
value2 = strReplace(value2,”-“,””);
value2 = strLTrim(value2);
value2 = subStr(value2,1,6);
weightFinal = str2num(value2);
}
else
{
info(“Check file portcomm.txt at C:\””);
}
if(!weightFinal)
{
info(“Please check your device, clarify it already turned on”);
}
return weightFinal;
}
—————————
on method button clicked, override.. some example here for you
void clicked()
{
real _value;
InventTable inventTableLoc;
super();
_value = this.AI_getWeight();
Select * from inventTableLoc where inventTableLoc.ItemId == ProdJournalProd.ItemId;
if(_value)
{
if(inventTableLoc.AISatuanPrimer!=”Kg”)
{
ProdJournalProd.AISatuanTertier = _value;
ProdJournalProd_AISatuanTertier.enabled(False);
}
else
{
ProdJournalProd.AISatuanTertier =_value;
ProdJournalProd_AISatuanTertier.enabled(True);
}
}
ProdJournalProd_ds.executeQuery();
}
void clicked()
{
WMSPallet wmsPallet;
select wmsPallet where wmsPallet.wMSPalletId==InventDimGrid_wMSPalletId.valueStr();
if(wmsPallet.wMSLocationId != InventDimGrid_wMSLocationId.valueStr())
{
warning(strFmt(“Number of pallet %1 does not match in number of location %2″,InventDimGrid_wMSPalletId.valueStr(),InventDimGrid_wMSLocationId.valueStr()));
error(“Posting has been canceled”);
}
else
{
super();
}
}
Hello there,
just working on project that need to print somekind of barcode label for Production’s Serial Number.
you need to convert your string to Barcode string :
__________________________________________________________
public BarCodeString ShowBarcode(str _text)
{
Barcode barcode;
barcode = Barcode::construct(BarcodeType::Code128);
barcode.string(true, _text);
barcode.encode();
return barcode.barcodeStr();
}
_________________________________________________________
then do this on Visual Studio 😀
result :
Happy DAXing !!
