Name is Anant Dubey and the intent to create this blog is to discuss the problems and issues that developer face in the dynamics AX development and to share the new things that come up with the new version of AX.

Tuesday, 11 July 2017

AX 2012 for Retail: Common practices and brief explanations for daily EPOS procedures and operations

The intent of this article is to assist with discovering the basic retail daily procedures for a Cashier and Manager from the design point of view.  While there are many combinations of how daily procedures are performed, this article is simply displaying some of the basic workflows in the day of a retail store
The information provided in this article assumes that you possess basic knowledge with AX and AX Retail functionality.  This is needed to understand certain parts the material and concepts as not all details will be provided and explained.  Lastly before we get started, keep in mind that not all available POS operations will be discussed and each business will have their own combination of daily procedures. 
List of Common Operations and Processes:
You can also view the full POS operations list in the following TechNet article: Set up permissions and operations [AX 2012] –
POS Operations
1. Time clock – Display the time clock to clock in, clock out, record breaks and lunch breaks, and view time clock entries
2. Declare starting amount – Record the amount that is in the cash drawer when the day or shift starts.
3. Create customer order – creates a sales orders or quote directly in AX
4. Float Entry – Register a float entry to the cash drawer, such as an addition of money to drawer.
5. Tender removal – Record the removal of money from the cash drawer.
6. Income accounts – Record money that is put into the cash drawer for a reason other than a sale.
7. Expense accounts – Record money that is removed from the cash drawer for occasional expenses.
8. Stock Count – Record the current inventory counts.
9. Safe drop – Record the amount of money that is taken to the safe.
10. Bank drop – Record the amount of money that is sent to the bank.
11. Print X – provides a summary of the shifts transaction count and amounts
12. Tender declaration – Record the amount counted in the cash drawer
13. Suspend shift – Suspend the current shift. The shift can be logged on to later.
14. Blind close – Set the current shift to blind close, and log off the cashier.
15. Show blind closed shifts – View a list of shifts that have been blind closed.
16. Close shift (Print Z) – Closes the shift and prints the Z-Report
AX Client processes
1. P-job – Syncs transaction data from POS to AX.
2. Post inventory – Performs inventory reservations for unposted POS transactions.
3. New statement – Creates a new statement for the selected store.
4. Calculate statement – Assigns the statement ID to all unposted transactions based on the statements Setup settings and summarizes the data.
5. Post statement – Creates sales orders, inventory movements, and journal entries for the data captured by the calculate statement.
6. General journal – Create a new journal entry to transfer and reconcile the money deposited into the bank from POS sales.
Basic Setup Configurations:
Retail Parameters for Posting
     1. Discount specific posting – Select this check box to enable periodic offers to be posted to the ledger accounts. Periodic discounts include mix and match discounts, quantity discounts, and discount offers
     2. Aggregation – Select the checkboxes to post the amounts as a sum or leave unchecked to post as separate lines.
     1. Default customer – This customer is used for posting if a specific customer was not added to a POS transaction.
     2. Statement method – The statement calculation method that is used at the store. The value can be Staff, POS terminal, Total, or Shift.
     3. Closing method – The closing method that is used at the store. The value can be either Date and time or Shift.
     4. Rounding – The general ledger account to which any rounding differences are posted.
     5. Maximum differences – The maximum difference amount that is allowed when Posting, Shift, or Transactions are posted.
     6. Remove/add payment method – The payment method that is used to add or remove money from the cash drawer.
     7. Hide training mode – Select this check box to hide training mode on the store’s registers.
     8. Payment Methods (Set up menu bar) – Used to set up the payment methods used at the POS.
Warehouse setting
     1. Retail
          a. Store – Checkmark this to identify the warehouse as a Retail store.
          b. Negative inventory – check to allow posting to occur for retail transactions even if there are insufficient product quantities available
Quick walkthrough of daily POS procedures:
1. As a cashier, you log into POS and start a new shift. 
2. Select the “Timeclock” operation to “Clock-in” for the day.
3. Count the amount of money in the cash drawer and use “Declare start amount” to enter the amount.
     a. If needed during the day, use the “Declare start amount” for additional money added to the till. Example: Started with $150, but now want the till count to be $200 (using sales cash for other $50).
4. Perform sales transactions and customer orders.
5. When the cash drawer obtain a certain threshold, select “Bank drop” to remove funds from the drawer as needed.  This is put into a numbered back deposit bag and is taken to the bank.
6. When the cash drawer obtain a certain threshold, select “Safe drop” to remove money from the drawer and take it to the safe in the back office.
7. Go to lunch by selecting “Timeclock” and selecting the option to “Break for lunch”.
8. Log off the shift using one of the following:
     a. If someone is taking over the POS station and the shift, simply Log Off the user account.
     b. If someone is taking over the POS station but not your shift, select “Suspend shift” to log out of POS to go to lunch.
     c. If no one is using the POS station or shift, you can use either of the two options to simply Log Off or Suspend shift..
9. Return from lunch and log into POS with your user account selecting to “Resume shift”.
10. You run out of quarters and select “Float Entry” to add a new roll of quarters obtained from the back office.
11. Another cashier needs $10 bills.  Instead of running to the back office, you select “Tender Removal” to remove money from your till and give that to the other cashier.
12. A local community club uses the parking lot for car wash and provides you with payment.  Select “Income Account” to add the funds to the cash drawer. 
13. You and your co-workers work on a holiday.  Your manager orders pizza for the team and uses elevated privileges at your POS station to perform an “Expense Account” transaction to pay for the pizza.
14. At the end of your shift, select Print X to print the current shift tender balances to the receipt printer to help balance the tender declaration.
15. Select the “Blind close shift” operation and take the cash drawer to the back office for counting. 
16. Log into POS in the back office or another location to enter counted amounts.
17. Select “Show blind closed shifts” to recall your shift.
18. Count the money in the cash drawer and select “Tender declaration” to enter the amounts.
19. Select “Close Shift” to close out the shift, which also prints a Z-Report.
20. Select the “Timeclock” operation to “Clock-out” for the day.
21. Put the counted money and Z-Report in a deposit bag for a Manager to review (do not perform a bank drop operation).
22. Manager logs into AX client in the back office.
23. Runs the P-job to upload the transactional data from POS to the AX database. (normally done in batch throughout the day)
24. Throughout the day the “Post inventory” process may have been running.  But this is not needed as an end of the day procedure, it is normally used for updating inventory on-hand quantities during the day.  
      See Blog Post: Using Post Inventory along with Calculate and Post Statement
25. Create a new statement for the store.
26. Calculate the statement by “Date and Time” or “Shift” (normally done in batch each night).
27. Manager then audits the counted money with the Z-Reports for all cashier shifts, performing any reconciliation as needed and updating the calculated statement. (normally not be performed when batch processing is used for Calculate and Post Statement unless posting difference are outside of thresholds).
28. Post statement to create (normally done in batch each night)
29. The money is then taken to the bank that night and put in the bank’s deposit drop box.  
30. During reconciliation by an accountant or manager, General Journal entries would be created to move funds from the Retail Ledger accounts (cash, credit card, etc) to Bank accounts to account for the money deposited into the bank.
Example diagram: For additional walk through details with amounts and screenshots related to the diagram below, see the attached Word document “Example Scenario of Stores Daily Procedures” at the bottom of this page.
IMPORTANT NOTE: I recommended, not require, setting up payment methods with their own Ledger accounts and not Bank accounts.  You can utilize the Bank account option, but all transactions will affect the Bank account directly and usually causes Bank reconciliation nightmares.  When using Ledger accounts you can reconcile this with Bank accounts by creating journals entries to move deposited funds into their respective bank accounts.  This is a better process for moving money to the bank than having all transactions automatically written directly to the bank account. 
IMPORTANT NOTE: The Safe drop and Bank drop were designed to be used when the till amount reaches a certain threshold.  At that point, money is take to the bank or back office safe to reduce the amount of cash in the drawer for security purposes.  Based on the payment method (normally Cash-Cash) you chose at the POS that you were taking funds from, you can assign specific accounts to the Safe drop or Bank drop in that payment method to show the amount taken out of the till.  If no account is assigned to these entities, it is assumed that the cashier is taking this money back out from the safe to count as part of the drawer amount at the end of the day which affects the Cash payment method account.
IMPORTANT NOTE: I highly recommend assigning a certain set permissions to each employee.  For instance, you may want a manager or supervisor approval to perform any sort of bank or safe drop.
Process questions:
  1. At the beginning of the day, I declare a start amount.  Throughout the day I have several transactions and do some Safe Drops when the till is full to move cash from the till to the safe.  At the end of the day, I need to do a Tender Declaration and then a Bank Drop to bring money to the bank.  How should I perform this process?
    1. Bank Drops were designed for money taken to the bank throughout the day to track money that was taken from the till.  If you want to perform another Bank Drop at the end of the day for the till count, then you would not include that amount in the original Tender Declaration.  The only amount in the Tender Declaration at this point would be for any amounts left in the drawer for tomorrow.
    2. Option 1: If you the Store setting is to use the “Last” Tender declaration calculation, then in the above scenario, you would need to perform an additional tender declaration after the Bank Drop to count how much money is now left in the drawer after the bank drop.  This would record both the full till before the bank drop and the till after the bank drop.
           NOTE: You cannot perform a $0 Tender declaration, so you would not be able to perform a second Tender declaration if all funds were put into the Bank drop.
    3. Option 2: If you the Store setting is to use the “Sum” Tender declaration calculation, then in the above scenario, you would need to perform an additional tender declaration after the Bank Drop to count how much money was removed for the bank drop.  This would record the full till before the bank drop and then add that to the secondary (negative) tender declaration after the bank drop.
  2. During the day, if I need to move money from the safe back to the till, how do I do that?
    1. Perform a Float Entry to add money to the till.  For this to be accurate, it would be advisable to set the Safe Drop posting account to be the same as the Cash payment method posting account.  Therefore, a Float Entry and Safe Drop would be affecting the same account.
  3. At the end of the day when I do my Tender Declaration, does that include the Bank Drop and Safe Drop amounts?  I’m assuming no as that seems to be calculated in the difference already.
    1. When performing the Tender Declaration at the POS, this should not include the Bank/Safe drop amounts.  These are auto calculated with the Cash-Cash payment method and offset by the Tender Remove/Float payment method.  This money is not meant to be taken back out for counting again, it was considered “counted” when the Bank/Safe drop was performed.
  4. Do I need to do the Bank Drop and Tender Declaration in any particular order?
    1. No, but make sure you do both and do so before the close shift in order for the reconciliation to be accurate.
  5. I don’t see a way to move the amounts in my Safe Drops anywhere.  When I run my P job to bring in transactions, they always show up as a Safe Drop.  How do I move that from the Safe Drop to a Bank Drop?
    1. If you are using different ledger accounts for Safe Drops than you are with the Cash payment method, then this is not possible at the POS. 
    2. Once that money is in the safe, you would normally not want to go back and record further movement of that money through each POS stations shift.  But if you do want to record that at the POS, you cannot simply perform a negative Safe drop.  If the Cash, Safe drop, and Bank drop use the same ledger account, the recommended workaround would be to first perform a Float entry to put that money back into the POS and then perform a Bank Drop to take that money along with other funds out for the Bank Drop.
    3. If you are in the back office and sending Safe money to the bank and have access to the AX client, my recommendation would be to perform a General Journal entry to record the safe to bank amount.  You would also want to record the money taken from the tills as a Bank Drop and include a second line in the General Journal entry to that amount as well.  This will help with reconciling the bank statements.
  6. Using Cash-Other or Cash-“CAD” (or other currencies)
    1. It is highly recommended due to the back end processes that Tender Removal/Float and other Cash operations all use Cash-Cash versus a Cash-Other payment method such as when entering Tender Removals, Floats, Tender Declarations, Declare Start Amounts.

Ref: -

Monday, 14 November 2016

How to create entity relationship diagram (ERD) in AX 2012

Recently I have to generate a ER-diagram for specific list of tables in Dynamics Ax.  Dynamics Ax has built in feature.
Prerequisite of this article has Microsoft Visio.

Create a project for all tables which require in ER-diagram.
For this article I  created a new project with Name customer Address and drop CustTable and DirpartyTable in it.
In AOT or development workspace. Click on tools and reverse engineer menu .

4-1-2015 12-11-00 AM

Following form will open, Select location where file with ERX will be generated. Select your private or public projects.
4-1-2015 12-12-013
Click ok to generate it.

When file is generated, go on windows start up menu and open MS visio. Select database modeling Diagram Template.
4-1-2015 12-14-013

When Database Model diagram is open, From database menu=> Import=> Import Erwin ERX file…
load erx file generated in previous step. This menu is only available when you select Database diagram as template in Visio.
4-1-2015 12-15-44 AM
click on Browse.. button and load erx file.4-1-2015 12-17-52 AM

Click ok
A small dialog shows the import status
4-1-2015 12-18-19 AM
Next step you have to enable view for Tables, So you can select tables for ER-Diagram
Database=>View=> Tables and views
4-1-2015 12-18-57 AM
You will found following pane at left side of screen.
4-1-2015 12-19-20 AM
Click on required table to add it on page and it will show required tables with relationship.
4-1-2015 12-22-046

You can re-size these diagrams.


How to connect or access external database from AX 2012

Sometimes we have to communicate with outside of Dynamics Ax with in boundaries of Dynamics ax.
Consider following scenario where I have to communicate with StudentDb in Sql server and insert rows in Student Info. We can do this with odbc connection.

Now create New AX job and paste following code there

LoginProperty loginProperty;

OdbcConnection odbcConnection;

Statement statement;

ResultSet resultSet;

LoginProperty myLoginProperty;

str sql, criteria;

int output;

SqlStatementExecutePermission perm;

str myUserName="dynamicworld\\aliraza.zaidi";

str myPassword="abcd";

str myConnectionString;








myLoginProperty = new LoginProperty();





//Create a connection to external database.

odbcConnection = new OdbcConnection(myLoginProperty);


if (odbcConnection)



sql ="INSERT INTO ..[StudentInfo]([FirstName],[LastName]) VALUES ('aliraza','zaidi')";

//Assert permission for executing the sql string.

perm = new SqlStatementExecutePermission(sql);



//Prepare the sql statement.

statement = odbcConnection.createStatement();

output = statement.executeUpdate(sql);







error("Failed to log on to the database through ODBC.");


How to clear the cache of all clients in AX 2012

According to that reference we have to reset the GUID, so all Clients have to reset its cache, this method required AOS restart.
static void ResetAUC(Args _args)
SysSQMSettings _Settings;
update_recordset _Settings setting GlobalGUID = str2Guid(#EmptyGuidString);
info("Now we have to Restart the AOS Service to use a new GlobalGUID.");

Its works for me.


List of functions in SSRS reports in AX 2012

During working in SSRS reports I have to handle division by Zero error for calculating accumulated cost by dividing Amount by Quantity.

This expression works for me.

IIf(Sum(Fields!xyz.Value) = 0, “N/A”, Sum(Fields!abc.Value) / IIf(Sum(Fields!abc.Value) = 0, 1,Sum(Fields!xvz.Value)))
I did little research on Expressions and function used in these SSRS expressions

Expressions are used for manipulate or update the value, for example, cost show by dividing amount by Quantity. Rounding of decimal. Similarly Expression can be used to highlight the filed if condition meets. Style font can be changed, even you can uses switch statement, to change the display value of report.

Types of Expressions
Operators – Arithmetic, Comparison, Logical
Common Functions – Text, Date & Time, Math, Inspection, Program Flow, Aggregate, Financial, Conversion, Miscellaneous
We can see each and every one very detail in following.

Global expressions executes/works in Page Header and Footer parts only.
ExecutionTime shows date and time at when report executes
PageNumber shows page number of each and every page but allowed only in page header and footer
ReportName displays name of the active report what name we have assigned to the active report
UserId shows current user name like company/alirazazaidi
Language displays language like US-English…
^ power of
* multiplication
/ divides two numbers and returns a floating point result
\ divides two numbers and returns a integer result
Mod divides two numbers and returns remainder only
+ adds two numbers and concatenation for two strings
- subtraction and indicates negative value for numeric values
Known operators : < <= > >= <> 
Like compares two strings and return true if matched or else returns False. Ex: =Fields!Title.Value Like Fields!LoginID.Value
Is compare two object reference variables Ex: = Fields!Title.Value Is Null
+ and & symbols uses for concatenation
Known: And, Not, Or 
Xor SELECT * FROM users where firstname = ‘Larry’ XOR lastname = ‘Smith’
AndAlso First condition will check first and if it is true only, goes to next or else it won’t need to check. Because our execution time is saving in a logical operation in which more conditions is combined using AndAlso function.
OrElse same like above
Common Functions
Asc, AscW returns an integer value represents character code corresponding to a character
Chr, chrw returns the character associated with the specified character code
Filter =Filter(Fields!Title.Value,”Pr”,true,0
=Format(Fields!Price.Value, “#,##0.00″), Format(Fields!Date.Value, “yyyy-MM-dd”)
FormatCurrency =formatcurrency(Fields!SickLeaveHours.Value,3)
FormatDateTime =FormatDateTime(Fields!BirthDate.Value,Integer)
0 returns 10/10/2014
1 returns Friday, October 10, 2014
2 returns 6/3/2014
3 returns 12:00:00AM
4 returns 00:00
FormatNumber =FormatNumber(Fields!EmployeeID.Value,2)
Examples: 4.00
FormatPercent =”Percentage : ” & formatpercent(Fields!SickLeaveHours.Value)
GetChar =GetChar(Fields!Title.Value,5)
InStr =InStr(Fields!Title.Value,”a
InStrRev =Instrrev(Fields!Title.Value,”a
LCase Change strings into lower case
Left Returns left side characters from a string
Len Finds length of a string
LSet Returns some length of a string from left
LTrim Trim left side of a string
=Ltrim(” “&Fields!Title.Value)
Mid Returns characters from the mentioned starting position
Replace Replaces one string with another
Right Returns right side characters from a string
RSet Returns some length of a string from left
RTrim Trim left side of a string
=Rtrim(Fields!Title.Value & ” “)
Space Specifies some spaces within strings
=Fields!Title.Value & Space(5) & Fields!Title.Value
StrComp Returns a value indicating the result of a string comparison
vbBinaryCompare 0 Perform a binary comparison.
vbTextCompare 1 Perform a textual comparison.
string1 is less than string2 -1
string1 is equal to string2 0
string1 is greater than string2 1
string1 or string2 is Null Null
StrDup Returns a string or object consisting of the specified character repeated the specified number of times.
StrReverse =StrReverse(Fields!Title.Value)
Trim =Trim(” “& Fields!Title.Value & ” “)
UCase =Ucase(Fields!Title.Value)
Date & Time
CDate Converts a object into date format
DateAdd Returns a datetime that is the result of adding the specified number of time interval units to the original datetime.
DateDiff Find number of days, months and years between two dates
DatePart DatePart(DateInterval.Weekday, CDate(“2009/11/13″), FirstDayOfWeek.Monday) returns 5 (Friday)
DateSerial for first day of the month
=DateSerial(Year(Now), Month(Now), 1)
for the last day of the month
=DateSerial(Year(Now), Month(Now)+1, 0)
DateString Returns string value of system date
DateValue Returns current date
Day Returns day value from date
FormatDateTime =FormatDateTime(Fields!BirthDate.Value,Integer)
0 returns 6/3/2014
1 returns Friday, June 03, 2014
2 returns 6/3/2014
3 returns 12:00:00AM
4 returns 00:00
Hour =Hour(Fields!BirthDate.Value)
Minute =Minute(Fields!BirthDate.Value)
Month =Month(Fields!BirthDate.Value)
MonthName =MonthName(Month(Fields!BirthDate.Value))
Now Indicates current month
=Now() or =Now
Second =Second(Fields!BirthDate.Value)
TimeOfDay =TimeOfDay()
Returns a date value containing the current time of day according to your system
Timer =Timer()
Returns number of seconds elapsed since midnight
TimeSerial =TimeSerial(24,60,60)
Returns a date value representing a specified hour, minute and second
TimeString =TimeString()
Returns string value representing the current time of day according to your system
TimeValue Returns a date value set to jan 1 of year 1
Today Returns Current date
Weekday Returns an integer value representing day of week
WeekdayName =WeekdayName(Weekday(Fields!BirthDate.Value))
Returns name of the day of week
Year =year(Fields!BirthDate.Value)
Returns year of specified date
Abs Returns the absolute value
BigMul Returns multiplication value of two specified numbers
Ceiling Returns next highest value
Returns cos value for specified number
Returns hyperbolic cos value
Returns integer portion
Returns largest integer
Returns integer portion of a number
Returns logarithm value
Returns the base 10 logaritm value
Returns larger value in the specified values
Returns smaller value in the specified values
Returns power of value for specified number
Returns a random number
Returns rounded value to the nearest integer
Returns the sin value
Returns the hyperbolic sin value
Returns square root value
Returns the tan value
Returns the hyperbolic tan value
Returns a boolean value indicating whether the specified object is array or not
Returns a boolean value indicating whether the specified object is Date or not
Returns a boolean value depends on specified object is Nothing or not
Returns a boolean value depends on specified object is Numeric value or not
Program Flow
=CHOOSE(3, “Red”, “Yellow”, “Green”, “White”)
Returns a specific value using index in a list of arguments
Returns any one value depends on condition
Evaluates list of expressions
Returns average value for all specified values
Returns count of all specified values
Returns count of all distinct values
Returns count of rows
Returns first for all specified values
Returns last for all specified values
Returns max for all specified values
Returns min for all specified values
Returns standard deviation value
Returns Population standard deviation value
Returns sum of all values
Returns variance of all values
Returns population variance of all values
Returns running aggregate of the specified
DDB DDB (Double Declining Balance) method computes depreciation of an asset for a specified period.
Syntax: DDB (Cost, Salvage, life, period, factor)
FV FV (Future Value) of an investment based on periodic, constant payments and a constant interest rate.
Syntax: FV (rate, nper, pmt, pv, type)
IPmt IPmt (Interest Payment) for a given period for an investment based on periodic, constant payment and a constant interest rate
IPMT (rate, per, nper, pv, fv, type)
IRR IRR (Interest Rate of Return) for a series of cash flows represented by the numbers in values.
MIRR MIRR ( Modified internal rate of return ) for a series of periodic cash flows
NPer Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
NPER (rate, pmt, pv, fv, type)
NPV Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
Syntax: NPV(rate,value1,value2, …)
Pmt Calculates the payment for a loan based on constant payments and a constant interest rate.
PPmt Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
PV Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.
Rate Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions.
SLN Returns the straight-line depreciation of an asset for one period.
SYD Returns the sum-of-years’ digits depreciation of an asset for a specified period.
CBool Convert to boolean
CByte Convert to byte
CChar Convert to char
CDate Convert to date
CDbl Convert to double
CDec Convert to decimal
CInt Convert to integer
CLng Convert to long
CObj Convert to object
CShort Convert to short
CSng Convert to single
CStr Convert to string
Fix =Fix(32.342143)
Returns integer portion of a number
Hex =Hex(Fields!EmployeeID.Value)
Returns a hexadecimal value of a number
Int =Int(43.44)
Returns integer portion of a number
Oct =Oct(Fields!EmployeeID.Value)
Returns a octal value of a number
Str =Str(Fields!EmployeeID.Value)
Returns string value of a number
Val =Val(“32.43″)
Returns numeric value in string format
Previous =Previous(Fields!EmployeeID.Value)
Returns the previous value

Reference :