Here I'm sharing code to import GL Opening Balances Lines.
Note : The below code import only Lines.
-> Copy and paste below code in AOT->Jobs(Compile Job before using)
->open excel and fill the data according to the below format and save in CSV format
->Run job and browse file and press ok
Csv Format :
column1 - GL Journal Number
column2 - MainAccountNum
column3 - Dimension1
column4 - Dimension2
column5 - Dimension3
column6 - Debit Amount
column7 - Credit Amount
column8 - Transaction Date
Column9 - Description
static void GL_OpeningBalances(Args _args)
{
CommaIO csvFile;
container readCon, accPattern,offSetPattern, dimPattern1;
counter icount,inserted;
Dialog dialog;
DialogField dfFileName;
FileName fileName;
AxLedgerJournalTable axLedgerJournalTable ;
AxLedgerJournalTrans axLedgerJournalTrans;
MainAccount mainAccount;
MainAccountNum mainAccountNum;
DimensionDynamicAccount ledgerdimension;
str transdate;
;
inserted =0;
#File
dialog = new Dialog("Pick the file");
// dialog
dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
dialog.filenameLookupFilter(["All files", #AllFiles]);
if(dialog.run())
{
filename = dfFileName.value();
}
csvFile = new CommaIO(filename,'r');
if(csvFile)
{
ttsBegin;
while (csvFile.status() == IO_Status::OK)
{
readCon = csvFile.read();
if(readCon)
{
MainAccountNum = conPeek(readCon,2);
mainAccount = mainAccount::findByMainAccountId(MainAccountNum);
if(!mainAccount)
{
info(strFmt("%1",MainAccountNum));
}
transdate = conPeek(readCon, 8);
axLedgerJournalTable = new AxLedgerJournalTable();
axLedgerJournalTrans = new AxLedgerJournalTrans();
axLedgerJournalTrans.parmJournalNum(conPeek(readCon, 1));
axLedgerJournalTrans.parmTransDate(str2Date(transdate,123));
axLedgerJournalTrans.parmAccountType(LedgerJournalACType::Ledger);
accPattern = [conPeek(readCon,2) + conPeek(readCon,3) + conPeek(readCon,4) + conPeek(readCon,5), conPeek(readCon,2),conPeek(readCon,3),conPeek(readCon,4),conPeek(readCon,5)];
axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accPattern));
axLedgerJournalTrans.parmDefaultDimension(LedgerJournalTable::find(conPeek(readCon, 1)).DefaultDimension);
axLedgerJournalTrans.parmOffsetAccountType(LedgerJournalACType::Ledger);
axLedgerJournalTrans.parmAmountCurDebit(conPeek(readCon, 6));
axLedgerJournalTrans.parmAmountCurCredit(conPeek(readCon, 7));
axLedgerJournalTrans.parmTxt(conPeek(readCon,9));
axLedgerJournalTrans.save();
icount++;
inserted++;
}
}
ttsCommit;
}
info(strfmt("%1 records Inserted out of %2",inserted,icount));
}
Note : Based on ledger account dimension structure your account pattern will change
@ Rahul Talasila
Note : The below code import only Lines.
-> Copy and paste below code in AOT->Jobs(Compile Job before using)
->open excel and fill the data according to the below format and save in CSV format
->Run job and browse file and press ok
Csv Format :
column1 - GL Journal Number
column2 - MainAccountNum
column3 - Dimension1
column4 - Dimension2
column5 - Dimension3
column6 - Debit Amount
column7 - Credit Amount
column8 - Transaction Date
Column9 - Description
//Code Starts From Here- @ Rahul Talasila
{
CommaIO csvFile;
container readCon, accPattern,offSetPattern, dimPattern1;
counter icount,inserted;
Dialog dialog;
DialogField dfFileName;
FileName fileName;
AxLedgerJournalTable axLedgerJournalTable ;
AxLedgerJournalTrans axLedgerJournalTrans;
MainAccount mainAccount;
MainAccountNum mainAccountNum;
DimensionDynamicAccount ledgerdimension;
str transdate;
;
inserted =0;
#File
dialog = new Dialog("Pick the file");
// dialog
dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
dialog.filenameLookupFilter(["All files", #AllFiles]);
if(dialog.run())
{
filename = dfFileName.value();
}
csvFile = new CommaIO(filename,'r');
if(csvFile)
{
ttsBegin;
while (csvFile.status() == IO_Status::OK)
{
readCon = csvFile.read();
if(readCon)
{
MainAccountNum = conPeek(readCon,2);
mainAccount = mainAccount::findByMainAccountId(MainAccountNum);
if(!mainAccount)
{
info(strFmt("%1",MainAccountNum));
}
transdate = conPeek(readCon, 8);
axLedgerJournalTable = new AxLedgerJournalTable();
axLedgerJournalTrans = new AxLedgerJournalTrans();
axLedgerJournalTrans.parmJournalNum(conPeek(readCon, 1));
axLedgerJournalTrans.parmTransDate(str2Date(transdate,123));
axLedgerJournalTrans.parmAccountType(LedgerJournalACType::Ledger);
accPattern = [conPeek(readCon,2) + conPeek(readCon,3) + conPeek(readCon,4) + conPeek(readCon,5), conPeek(readCon,2),conPeek(readCon,3),conPeek(readCon,4),conPeek(readCon,5)];
axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accPattern));
axLedgerJournalTrans.parmDefaultDimension(LedgerJournalTable::find(conPeek(readCon, 1)).DefaultDimension);
axLedgerJournalTrans.parmOffsetAccountType(LedgerJournalACType::Ledger);
axLedgerJournalTrans.parmAmountCurDebit(conPeek(readCon, 6));
axLedgerJournalTrans.parmAmountCurCredit(conPeek(readCon, 7));
axLedgerJournalTrans.parmTxt(conPeek(readCon,9));
axLedgerJournalTrans.save();
icount++;
inserted++;
}
}
ttsCommit;
}
info(strfmt("%1 records Inserted out of %2",inserted,icount));
}
Note : Based on ledger account dimension structure your account pattern will change
@ Rahul Talasila
Very helpful post! Importing GL opening balances can be tricky, and this step-by-step example with CSV formatting makes it much easier to follow. On a related note, if you’re also exploring modern app development concepts, understanding what is redux thunk can give you similar clarity when handling async operations in frontend frameworks. Thanks for sharing such practical insights!
ReplyDelete