7/30/2011

How to Enter Analytical Accounting Negative beginning balances

In Analytical Accounting, the dimensions and corresponding codes are always linked to the General ledger accounts. Analytical transaction are triggered only when a GL distribution is being made in any of the modules of GP which is supported by AA. Hence, We cannot post to AA without actually posting to GL Accounts. What if we have a situation to enter AA negative balances? We know that AA does not support entering negative balances. In this post we will see in which scenarios we will encounter the possibility of entering negative balances and how we can achieve this functionality through a workaround while respecting the business logic of AA.

Scenario

A client is migrating their balances to GP and as part of the migration, the account framework is changed to a new structure. As part of the new structure, they would like to maintain their employee loan balances in AA linked to 1 main GL Account for Employee Loans & Advances. In their old system on the contrary, each of the employee Loans and Advances are assigned to 1 GL account each so they do not have worries on negative balances as they just manage it by debiting and crediting the account to corresponding accounts. At the time of transfer, they realize they have their balances to be transferred to each of employees are as follows

EMP BALANCE
TOM -$1000.00
JERRY $10000.00

Situation

The beginning GL balance entry has only one GL account for Employee Loans & Advances with a net balance of $9000.00 so we cannot simply punch the above employees balances into the AA distribution.

Solution

1. Post the beginning balance entry with AA distributions as follows

EMP BALANCE
JERRY $9000.00
2. We are now left with $1000.00 for Jerry and –$1000.00 for Tom to be distributed in AA.
3. After the beginning GL balance entry (TB/BS) is posted, Create another GL entry with the following accounts and AA distributions as well.
ACCOUNT DR/CR BALANCE AA CODE
EMP LOANS & ADV DR $1000.00 JERRY
EMP LOANS & ADV CR $1000.00 TOM
4. That’s it. The net impact of the above entry is that your GL balance will see an additional debit and credit of $1000.00 with net change $0.00 but AA balances will be updated with the corresponding balances.
 
5. Remember that we encounter this situation mostly in beginning balances and will not have it occurring in normal day to day business as debits and credits in GL will take care of the same. 
 
If anyone has a better solution, Please do suggest here.

7/29/2011

Top 100 Most Influential People in Microsoft Dynamics: My Take

I haven’t talked about the Dynamics world Most influential Top 100 list until now. I have seen so much talk around Microsoft dynamics blogosphere earlier in terms of seeking votes for their nominations put up on the list. Seriously, I do not know the tools and techniques deployed to measure the influence of these nominations. First, I’d give full credit to the Dynamics world team.

I was interested in looking at their selection criteria for a moment because the final list in itself made me think deeply on it.

The selection criteria, to quote from the Dynamics world website “ We base our selections on a number of criteria including: Number of nominations received; Number of Microsoft Dynamics employees employed; Number of clients your ISV/VAR has; As an end user how many user licenses taken; Social Media Followers (bloggers/twitter/ LinkedIn) (bonuses for group owners); Number of years experience of Microsoft Dynamics; MVP Recipients; Developers of Add-Ons and new verticals; Speakers and writers about Microsoft Dynamics; Forum contributors and Number of votes received. We attempt to limit what can actually be described as a sphere of influence. The advantage that we have had in quantifying influence in Microsoft Dynamics is that we are dealing with a group of people with similar interests, and so it is possible to be able to quantify one persons influence over the group against another person’s influence.”

Let us look at the criteria of number of Microsoft dynamics employees employed.

This means the person has to be necessarily a owner of the business, essentially a Microsoft Dynamics Partner or Microsoft itself. If this is the case, What about other influential people who contribute sleepless to the dynamics community not necessarily the owners of the business. OK but again this can well be defeated by stating that this is one of whole criterion.I think based on this criteria, Mr.Steve Ballmer shall stand first in the crowd as he directly or indirectly runs the Microsoft Dynamics which is an integral part of Microsoft. But Mr.Ballmer is in Top 41 position surprisingly. Moreover, If we ask who is the top most influential among all, I’d say it must be none other than Mr.Steve Ballmer himself because he can influence the Microsoft Dynamics division than anyone else so seeing others ahead of him is something that is really surprising and strange. When I looked at the list, the other thing that disappointed me is not to find my personal favorites or for that matter the whole Dynamics GP community favorite, David Musgrave.

Having said that I must appreciate the Dynamics world for launching such a campaign that was much talked about for a while. I think they can look ahead to start giving some labels or badges for the winners or some cash prizes so that the campaign will get more awareness in the coming years. In fact as I write this, I can see that Dynamics world is busy with another campaign on social media. Good Luck to them.

7/28/2011

Dynamics blogger Video channel on You tube

I remember those days past a year or so when I created Twitter account where I started syndicating the GP blog posts and later did a lot experiments like Facebook group, MSdynamicswire.com website and network creation.  dynamics blogger.com is another new addition that is created recently quarter ago. Although not all experiments are successful, yet, I derive some learning out of each experiment and I enjoy it. Now comes another experiment that is Dynamics Blogger YouTube video channel.

I realized presenting your thoughts in a video format is much easier than writing long sentences. However, I must say I also realized how tough it is to create a video of your own but I’m confident I’ll learn it the hard way in due course of time.

Here comes the latest videos on the channel.

Dynamics GP 2010 Auto Login – This video shows few steps to activate this feature

Excel And Dynamics GP Export Solutions in Smart list – This video shows how to use export solutions in smart list with an example.

Dynamics GP MVPs’– Missing the List

The current MVPs’ of Dynamics GP are definitely the most deserving professionals and credits shall be given to the MVP selection committee. However, I feel there are other deserving professionals left out of the list. The people that comes to my mind who should be on this list but left out are

1. Steve Chapman from Rose Business Solutions

2. Christina Philips from BKD Technologies

I find the videos of Steve Chapman on You Tube to be an invaluable contribution to the dynamics GP community as a whole. I like his presentation of those videos and the amount of knowledge that is hidden in his videos. Till date, Only two people I’d say are the most active contributors to You Tube videos for Dynamics GP. One is Steve Chapman on Rose Business Solutions Channel and the other is Mark Polino. I don’t need to say anything much about Mark other than that he is just great and outstanding in his contribution to Dynamics GP community. Personally, I’m influenced by the videos of Steve and in fact I’m just starting to learn creating videos. Needless to say, I could understand the pain and amount of time it takes to create one video.

Christina Philips articles on her blog are straight to the point. I like her selection of articles for writing. I find her articles interesting and unique in subject. She has been writing for several years on Dynamics GP.

I personally don’t know what criteria is set by MVP selection panel but definitely these are two experts missing off the list for their respective contributions.

There are lot other professionals doing their best to the community, I’d mention in my other upcoming articles.

7/27/2011

Analytical Accounting: Dimension Code Macro

Macro to enter new dimension codes in Analytical accounting module is provided below.


TypeTo field aaTrxDimCode , 'CODE'
  MoveTo field Inactive  # 'FALSE'
  MoveTo field aaTrxDimCodeDescr
  TypeTo field aaTrxDimCodeDescr , 'CODE DESCRIPTION1'
  MoveTo field aaTrxDimCodeDescr2
  TypeTo field aaTrxDimCodeDescr2 , 'CODE DESCRIPTION2'
  MoveTo field aaNode
  MoveTo field 'Lookup Button 3'
  ClickHit field 'Lookup Button 3'
NewActiveWin dictionary 'Analytical Accounting'  form aaTreeNodeLookup window Main
  DblClickHit field '(L) TreeView' item 1  # 'NODE' 1
NewActiveWin dictionary 'Analytical Accounting'  form aaTrxDimensionCodeMnt window Main
ActivateWindow dictionary 'Analytical Accounting'  form aaTrxDimensionCodeMnt window Main
  MoveTo field 'Save Button'
  ClickHit field 'Save Button'
  MoveTo field aaTrxDimCode


Notes:
  1. Paste the above contents into a Microsoft word document and do a mail merge to insert the records into the fields highlighted above.
    • CODE – Replace this with your Dimension Code ID values
    • CODE Description 1 – Replace this with description values
    • CODE Description 2 – Replace this with description 2 values
    • NODE – Replace this with the node for this dimension.
  2. Once Mail merge is  complete, paste the contents into a notepad.
  3. Add the following lines at the beginning of the notepad (beginning lines)

NewActiveWin dictionary 'Analytical Accounting'  form aaTrxDimensionCodeMnt window Main
ActivateWindow dictionary 'Analytical Accounting'  form aaTrxDimensionCodeMnt window Main

    4.   Save the notepad as AAcode.mac

     4. Open AA Dimension code window, select the transaction dimension and place the cursor in the Transaction Dimension code field.

   5. Press CTRL + F8 to  browse AAcode.mac and click open.

7/18/2011

Integration Manager crashes

Sometimes we might not be that lucky to get through with our imports through Integration Manager remotely. A remote session will kill the ongoing Integration and leave the Dynamics GP running despite the crash. I’ve made interesting observations that will save your time next time you encounter the same for some reason.

1. Integration Manager crashes while doing the integrations using terminal server or Citrix like environment. Or for that matter applications like Team viewer, LogmeIn so on.

2. It will crash only when we try to do the integrations using Integration Manager standard destination adapter that requires Dynamics GP session to be on. This doesn’t happen for IM eConnect adapter.

3. This happens when the integration tries to open another GP window during the course of Integration.

Troubleshooting

1.There is no proven solution to the above problem.

2. However when it happens, the first thing to do is to go to Task Manager and kill Dynamics.exe process that keeps running.

3. Log on to Dynamics GP and run the check links on Work tables where import is being made. If Sales documents are getting imported, then Run check links on Sales work tables as the integration manager crash will also lead to leaving some orphan records in Dynamics GP work tables. So We need to clean this mess up.

4. Don’t attempt the Integration again remotely after this and wait to do the integration in normal session.

5. If it cannot wait, then see if you can accomplish the same integration using econnect destination adapter. If yes, reconfigure your import to this adapter and re do the integration. It will go through this time.

Sales Orders Import: Location ID does not exist

While importing sales documents in Dynamics GP through Integration Manager or eConnect, We encounter the following error message.

Microsoft.Dynamics.GP.eConnect:  Number = 287  Stored Procedure taIVTransactionLineInsert  :  Description = Location ID/Site ID (TRXLOCTN) does not exist in Site/Location Setup - IV40700

When we see this error, Our immediate focus would be on Quantities/Sites window where the Item number should be assigned to the relevant site ID in import. However, In some cases the integration fails despite assigning the site to the item number. Reason for this error would be due to the presence of Kits. If any of the line items contain Kits and we receive this error, We shall go to the Kit components and check if the components are assigned to the respective sites in import.

In one of my imports earlier, I’ve struggled for 4 hours struggling to find which item was not assigned to the site. After constant trials, I found the problem lies in the kit components. Specially, this proves to be a nightmare when we integrate documents using eConnect destination adapter in Integration Manager or eConnect itself because the eConnect would tell us the problem but it fails to tell us the exact item that is creating this problem unlike Integration Manager Dynamics GP destination adapter which tells exactly which item is creating an issue.

7/15/2011

Import Kits

Importing Kits is another exercise that is difficult to integrate with Integration Manager as there is no such adapter available. What if we have thousands of Kits to be created in GP. In such cases, Using Table import is a wise option to import thousands of kits in split of seconds. Kits Import is a straight import in GP as the Kit Master is the only table tat we should be looking at. Follow the steps below to import the kits.

  1. Make sure Kits are defined with the item type Kit and the Kit components as items with Sales Inventory types in Item master as a prerequisite for Kits Import.
  2. Prepare the CSV file with the following fields. Make sure to remove the label Headers in the file after preparation of fields.
    • Item Number – Fill this field with Kit parent code.
    • Sequence Number – Fill this field with Sequence number 16384, 32768 so on for each component line item.
    • Component Item Number – Fill this field with the component code of the Kit parent code.
    • Component Item U Of M – Assign the U Of M for each of the components.
    • Component Item QTY – Provide the QTY of each of the components that makes up the Kit.
    • Component Serial Numbered – If it is not serial numbered, Assign 0. If Yes, Assign 1.
  3. Go to Cards | Inventory | Kits | Tools | Integrate | Table Import and provide the required fields as well as mappings with the source file and destination file, something like below.

Item Kit

4. Click Import.

Import Inventory BOM FAQs

In my earlier post I’ve covered how to import Inventory BOMs. In this post, I’ll publish a couple of FAQs’ which are very critical for the import.

1. I’ve successfully imported the Inventory BOM into both BOM Header as well as Component Tables. When I go and check the BOM Master record in GP, I could only see my Header record but no components are visible?

A. Make sure You’ve assigned constant value of 5 for all your components BM Comp state field. Value 5 stands for Saved status.

2. I’ve successfully imported the BOM into the relevant tables. When I go and check the BOM Card in GP, I could see the header as well as components below it. But when I lookup for Bill Number in Bills and Items lookup, I could not see any of Bill numbers imported?

A. Make sure you’ve provided the date values in creation date and modified date fields. If these fields are left blank, the BOM will not be visible in Lookups.

3. I’ve successfully imported the BOM into the relevant tables. When I go and check the BOM Card in GP, I could see the header as well as components below it. But when I click View Indented Bill, it says the BOM is deleted?

A. Make sure you’ve provided the date values in creation date and modified date fields. If left blank, the Indented Bill won’t display the results.

Import Inventory BOM

Integration Manager for Dynamics GP doesn’t provide the ability to import the Inventory Bill of Materials (BOM). To overcome this limitation and to import Inventory BOMs’ into GP, We can make use of table import method instead.

Importing Bill of Materials requires two tables BOM00101, BOM00111. To import the BOMs’ (Cards | Inventory | Bill of materials) using Table Import, Follow the steps below

  1. Prepare 2 CSV Files for the import. One is BOM Header and the other file is BOM Components.
  2. BOM Header must have the following fields.
    • Item Number – This is the parent code and must be an inventory item existing in the inventory. This is called Bill Number
    • Bill Status – Status of the Bill. Set it to 1 as constant. Status of 1 accord Active status for the BOM
    • Status Ord – Assign constant value of  1 for active bills.
    • BM Stock method – Assign 1 for Build if necessary, 2 for stock, 3 for Build status
    • Effective Date – Leave the date blank or fill the date with the date
    • Obsolete Date – Leave the date blank
    • U of M – Assign the same UOM assigned for the parent code in Inventory
    • Note Index – Leave the field until import. Once imported, update the Note Index using sp_getNextnote index to update the note fields.
    • Created Date – Provide the date of creation
    • Modified Date – Provide the modified date
  3. BOM Components would have the following fields
    • Item Number – Parent Code of BOM. Same as the Item Number in BOM Header file.
    • Bill Status – Status of the bill same as BOM Header Bill status
    • Status Ord – Same as Status Ord for BOM Header
    • Ord – This is the unique identifier for BOM components for each parent code. GP increments each line item by 16384. So If 1 BOM has 3 lines, each line Ord will be 16384, 32768, 49152. Next BOM will again start with 16384 so on. We can easily achieve this in Excel to assign numbers.
    • Component Item Number – Provide the component Item Number. This must be an item number in Inventory.
    • Component Status – Assign 1 for Active status
    • Comp State – BOM Component state. Always Assign constant 5 as value though there are other values as any other value doesn’t show up the components in the Bill at least when I tested it.
    • Cost Type – Assign 1 for Per Unit cost or 2 for Setup cost
    • Design QTY – Provide the quantity of component
    • Scrap % – Provide the value or leave blank
    • Effective Date – Provide the date or leave blank
    • Obsolete Date – provide the date or leave blank
    • U of M – Provide the component U of M. Make sure this is same as the one set for the component item in Inventory.
    • Note Index – leave this for import and update the field after import using the stored procedure for get next note index.
  4. Once the 2 files are ready for import, Remove the Labels in each file as the Table import should not have any headers such as Item Number, U of M so on otherwise it treats that also as data.
  5. Go to Cards | Inventory | Bill of Materials | Tools | Integrate | Table Import and select Bill of Materials Header. Provide the Source file, Definition ID, Destination Table, Mappings so on something that looks like below. Once mapped, Click Import.

BOM Header

6. After importing BOM Header, Again Go to Cards | Inventory | Bill of Materials | Tools | Integrate | Table Import and select Bill of Materials Component. Provide all the required fields and mappings same as what we did for BOM header. After mapping it looks something like this. Click Import.

BOM Detail

7. Once imported, Run the Stored procedure for note index for both BOM header as well as component to update the Note index field.

I’ll publish a couple of FAQs’ on this BOM Import in my next post.