Infor M3 Financial Reports

 

 

I have developed some great financial report tools for Infor M3.
Financial Reports are richer if you can get data to Excel exactly the way you want. If you have data on Excel you can customize your reports, add charts...
This features work with M3 using SQL Server as database. The key is that calculations are made by the server and not by your excel worksheet. This turns calculations faster.

Here is a sample of a Portuguese account balance (Balancete).

Here is a sample of a very simple custom report you can build with this tool.

If this solution interest you please contact.

How does it work?
Simple: you have new functions in Excel to get you data out of M3.

Here is the full set of functions to get data:

M3Balance

(Company, Division, AccountingYear, Period, Account, TotalforYear)

Returns balance for M3 level 1 key.

Parameter description

Company -

M3 Company as registered in MNS095.

Division -

M3 Division as registered in MNS100.

AccountingYear -

Is the Accounting Year from which the balance is to be obtained.

Period -

Is the period reference for the value to be obtained.

Account-

The level 1 key to obtain the balance. This parameter works as StartsWith.

For instance, if you have a key structure like 111 - Account 1; 112 - Account3; .... ; 119 - Account 9, and the

account parameter is filled with "11" the balance calculated is the balance for all the accounts (111;112;...;119).

TotalforYear -

If filled with "Y" calculates the balance for the year with period less or equal to the selected period.

Any other value will calculate balance for the refered period only.

Example

=M3Balance( 1 ,"200", 2016, 6 ,"11","Y")

Calculates the balance for the balance key 1 starting with "11" from period 0 to 6 of the 2010 Year

=M3Balance( 1 ,"200", 2016, 3 ,"119","P")

Calculates the balance for the balance key 1 starting with "119" from period 3 of the 2010 Year

M3BalanceDeb

(Company, Division, AccountingYear, Period, Account, TotalforYear)

Returns debt balance for M3 level key 1

Parameter description

This function uses the same parameters and description as function M3Balance

M3BalanceCre

(Company, Division, AccountingYear, Period, Account, TotalforYear)

Returns credit balance for M3 level key 1

Parameter description

This function uses the same parameters and description as function M3Balance

M3KSBalance

(Company, Division, AccountingYear, Period, TotalforYear, BalanceKey, Key1Start, Key1End

[, Key2Start, Key2End, Key3Start, Key3End , Key4Start, Key4End, Key5Start, Key5End, Key6Start, Key6End, Key7Start, Key7End ] )

Returns balance for selected M3 balance key using key ranges

Parameter description

Company -

M3 Company as registered in MNS095.

Division -

M3 Division as registered in MNS100.

AccountingYear -

Is the Accounting Year from which the balance is to be obtained.

Period -

Is the period reference for the value to be obtained.

TotalforYear -

If filled with "Y" calculates the balance for the year with period less or equal to the selected period.

Any other value will calculate balance for the refered period only.

BalanceKey -

Is the balance key to be used. Balance Key file can be created in M3 program GLS690.

Key1Start -

Is the starting range for the first key of the selected balance key.

Key1End -

Is the finish range for the first key of the selected balance key.

[Key2Start] -

Optional parameter for 2nd key start range for the selected balance key.

[Key2End] -

Optional parameter for 2nd key end range for the selected balance key.

[Key3Start] -

Same as Key2Start but for 3rd Key.

[Key3End] -

Same as Key2End but for 3rd Key.

[Key4Start] -

Same as Key2Start but for 4th Key.

[Key4End] -

Same as Key2End but for 4th Key.

[Key5Start] -

Same as Key2Start but for 5th Key.

[Key5End] -

Same as Key2End but for 5th Key.

[Key6Start] -

Same as Key2Start but for 6th Key.

[Key6End] -

Same as Key2End but for 6th Key.

[Key7Start] -

Same as Key2Start but for 7th Key.

[Key7End] -

Same as Key2End but for 7th Key.

M3KSBalanceDeb

(Company, Division, AccountingYear, Period, TotalforYear, BalanceKey, Key1Start, Key1End

[, Key2Start, Key2End, Key3Start, Key3End , Key4Start, Key4End, Key5Start, Key5End, Key6Start, Key6End, Key7Start, Key7End ] )

Returns Debt balance for selected M3 balance key using key ranges

Parameter description

This function uses the same parameters and description as function M3KSBalance

M3KSBalanceCre

(Company, Division, AccountingYear, Period, TotalforYear, BalanceKey, Key1Start, Key1End

[, Key2Start, Key2End, Key3Start, Key3End , Key4Start, Key4End, Key5Start, Key5End, Key6Start, Key6End, Key7Start, Key7End ] )

Returns Credit balance for selected M3 balance key using key ranges

Parameter description

This function uses the same parameters and description as function M3KSBalance

Sharepoint 2013 Dropdown list populated from another sharepoint list

This is my solution to dynamically fill a filter dropdown list based on another sharepoint list field

1- Define a folder to place javascript files. I used the following path:
"~sitecollection/_catalogs/masterpage/Display Templates/"

2- Upload the following files to the location you defined on step 1. Use the import files button on your sharepoint designer.

File 1: the jquery library.
File 2: the jquery SP Service library. 
File 3: The custom code for the dropdown list

3 - Use the designer to edit the default list form for New Item (in this case nx.aspx)

4 - Add the following references to forms design. 

<SharePoint:ScriptLink ID="jquery" runat="server" LoadAfterUI="True" Name="~sitecollection/_catalogs/masterpage/Display Templates/jquery-1.12.3.js"/>

<SharePoint:ScriptLink ID="spservices" runat="server" LoadAfterUI="True" Name="~sitecollection/_catalogs/masterpage/Display Templates/jquery.SPServices-2014.02.js"/>
 
<SharePoint:ScriptLink ID="trsp" runat="server" LoadAfterUI="True" Name="~sitecollection/_catalogs/masterpage/Display Templates/CostumDropdown.js"/>

Note that you should update the path if you uploaded the scripts into a different path.

 A good place for you to drop this lines of code is before the form table. Search for <Sharepoint:FormField and FieldName=the first field of your form. Paste the lines before the first <tr> as shown above.

5 - Identify the html element id of the field you wish to apply your dropdown list

6 - Using the Sharepoint designer Edit the custom javascript (file 3 uploaded on step 2).

$(document).ready(function(){
    add_dropdown("lista1", "ctl00_ctl26_g_928acec0_5b91_4790_a300_9119e1116afe_ff21_ctl00_ctl00_TextField","http://w2012sharep/sites/ETN/Contactos/","Contactos",  "Company" );
});

You only need to update the entry parameters for add_dropdown function:
1 - Name of the list (must be different for each dropdown)
2 - Element id you have reached in step 5.
3 - URL where the source of your dropdown list is.
4 - Name of the sharepoint list
5 - Name of the field on the source list


To add a new dropdown just copy the call for the function
$(document).ready(function(){
    add_dropdown("lista1", "ctl00_ctl26_g_928acec0_5b91_4790_a300_9119e1116afe_ff21_ctl00_ctl00_TextField","http://w2012sharep/sites/ETN/Contactos/","Contactos",  "Company" );

    add_dropdown("lista2", "ctl00_ctl26_g_928acec0_5b91_4790_a300_9119e1116afe_ff21_ctl00_ctl00_TextField","http://w2012sharep/sites/ETN/Contactos/","Contactos",  "Phone" );

});



And your set to go.

Sharepoint - Set current user to a form field as a default value

This is my solution to set the current user as a default value of a Sharepoint 2013 form field.

1- Define a folder to place javascript files. I used the following path:
"~sitecollection/_catalogs/masterpage/Display Templates/"

2- Upload the following files to the location you defined on step 1. Use the import files button on your sharepoint designer.

File 1: the jquery library.
File 2: the jquery SP Service library.
File 3: the custom javascript for the field you want to update.


Edit the file 3 in sharepoint designer after import (or in another editor before import) and replace 'Req' by the name of your field name in the list:
var fieldName = 'Req';

3 - Use the designer to edit the default list form for New Item (in this case nx.aspx)

4 - Add the following references to forms design. 

<SharePoint:ScriptLink ID="jquery" runat="server" LoadAfterUI="True" Name="~sitecollection/_catalogs/masterpage/Display Templates/jquery-1.12.3.js"/>

<SharePoint:ScriptLink ID="spservices" runat="server" LoadAfterUI="True" Name="~sitecollection/_catalogs/masterpage/Display Templates/jquery.SPServices-2014.02.js"/>
 
<SharePoint:ScriptLink ID="trsp" runat="server" LoadAfterUI="True" Name="~sitecollection/_catalogs/masterpage/Display Templates/trsp.js"/>

Note that you shloud update the path if you uploaded the scripts into a different path.

 A good place for you to drop this lines of code is before the form table. Search for <Sharepoint:FormField and FieldName=the first field of your form. Paste the lines before the first <tr> as shown above.


5 - Disable minimum download strategy

If you try to run your form you will experience that the code works just sometimes and not every time. You should disable minimum download strategy feature. To do this go to site settings, manage site features and disable it.

And we are done!
Here is my sample on field "Req"

Magento 2.0 csv file Translation Portuguese / Tradução para Português do Magento 2.0

For the translation of Magento to Portuguese please download the file below and place it in:

Para a tradução para Português do Magento 2.0 faça download do ficheiro abaixo e coloque em:

/opt/bitnami/apps/magento/htdocs/app/design/frontend/Magento/luma/i18n

Magento 2.0 Translation Portuguese / Português

Lubuntu view Trash on Desktop

Trash Can is by default hidden on Lubuntu.

To activate the trash on the desktop click on your Start Menu and on Preferences Select "Desktop Preferences". Then click on Tab "Desktop Items" and activate the option Show "Trash Can" folder on the desktop.

Lubuntu - Easy set up a system

After installing Lubuntu you will need some standard apps to start working.
You can easily setup some standard applications by using simple command line language.
Click on the start menu and roll over system tools and choose UXTerm.

The syntax to install software is then following
sudo apt-get install <application>

A simple example how to install skype is this simple command line
sudo apt-get install skype 
(You can select the above line and copy. To paste into the the UXTerm press the mouse-wheel to paste)
Assuming that your password is not blank, you will be prompted at least once for your sudo/administrator password.

Here is a quick list of some useful applications to start working with Lubuntu
sudo apt-get install apache-openoffice - will install openoffice applications suite
sudo apt-get install skype - will install popular skype for instant messaging and video calls
sudo apt-get install chrome-browser - will install Google Chrome Browser
sudo apt-get install gimp - will install freeware gimp for image handling.

Please help me complete this list. Make a suggestion for the missing apps in the comments.
Thanks.

Windows Profile (User Settings) Failure

Problem Environment: General Windows

Sometimes if the a crash happens on your windows your profile get's damaged.
You desktop files and all settings are missing and you appear to be in a totally new profile.

Most cases I experienced can be solved simply.

Here is my solution:

1. Open My Computer
2. On your system installation (usually C:) right click and select properties.
3. Click on Tools Tab
4. Click on error checking button
5. Select option Automatically fix file system errors
6. You will be prompted to run this maintenance at next reboot. Click ok
7. Reboot

The reboot will fix file and disk errors. After this your profile should be again usable.

 

Disable cache for file/table in Infor Lawson M3

Problem Environment: Infor Lawson M3 Java 

In Infor Lawson M3 Java uses cache. This is great to improve performance but may cause some issues. There are several reasons for you to want to disable cache on a file/table. For instance a service that updates data from an outside environment. Of course Infor Lawson does not subscribe some methods. This is understandable because external interference can cause great damage. But if you know what to do and need to disable cache here is how to do it:

1. Open a file in you environment folder \env\[environment]\properties\mvx\res\Transaction.properties with notepad.
2. You should find a line with this "transaction.cache.readonly.count=5". Increase this number by adding the number of tables you want to disable cache.
3. Next on the file you should find "transaction.cache.readonly.4=-DDOCUX". Add one entry for each file you want to disable. Don't forget to increase the number as in ".4" for each file.
4. Save the changes.
5. Shutdown environment.
6. Start environment.

How to Deny Access to Internet

Problem Environment: General Windows

Some users don't need internet access at all. Internet access is nowadays mandatory.
But in some cases it's only a source of distraction besides a an open gate for virus.
If you do not have a proxy server there is a simple way to deny internet access.

You can limit access to internet in some computers by setting the proxy server pointing to itself.
Every request will be redirected to the local computer. As the computer cannot answer to the request the user is presented with a simple network error.

You can do this at work or at home if you do not want a specific computer to have internet access.

It's as simple as that.

1. Open your Control Panel and then open Internet Options.
2. Open Connection Tab and click on Lan Settings.
3. Enable proxy server and fill the address with 127.0.0.1.

To re-enable internet access just do the same steps and remove check on the use of proxy server.

This a simple way of denying internet access. 

Is it safe? Most users don't have the knowledge to override this settings but some may have. So this method is not 100% secure. If you are in a corporate environment you can complement this method by using group policy editor. In case you have users capable of overriding this trick you should consider implementing a proxy server.

Anyway it is still a nice and simple way to distance someone from unwanted activity's.

Remove Fake Antivirus - Chapter 1

Problem Environment: General Windows

Some sites are full ads, banner, pop-ups.
Eventually you will click on one of those undesirable ads.
The result of this click is some times a new self called Anti-Virus installed on your machine.
Some may seem pretty real. Software simulate scans of you computer files. Alerts you to a possible danger and infections.

So how can you check if this software is real? Here are some hints to help you.

1.  Did you installed this new software by yourself? Did you buy this new software that is running in your machine? Did you downloaded a new antivirus from a secure site and installed it in your pc? If the answer is no you are most likely infected with some kind virus.
2. Real Anti-Virus Software proudly publishes their company or product name in the software. Check this new so called antivirus for product or company names. If the product/company name does not ring a bell it's most probably a virus itself.

Here is a list of some real antivirus: AVG, Avast, Norton, Kaspersky, Trend Micro, McAfee. This list is for sure incomplete. If you have any doubts about the software just go google it. Be sure to type the exact name on the software. Open the company page and check the content. Check for solid info about removing virus, product details, partners, etc. If still not sure google again in forums or computer science related sites for opinion about this product.

So if you are infected, how can you remove this software?
This type of virus had some evolution. Their first appearances were simple windows that would redirect you to a website with the intention to sell their "fish". Some would block the whole screen with a message.
I named this Chapter 1 because some of this software has new undercover functions. New variants added some functions like disable Administration.

I you have a simple software variant of this virus on your computer it's quite simple to resolve. Follow the next steps to clean your computer:

1. Reboot you Windows to safe mode. To reboot Windows to safe mode press F8 on computer startup.
2. If you are still using Windows XP immediately after startup you will be prompted to start system restore. If you are using another Windows version search for "System Restore". This will guide to a wizard that will help you in the process. The goal for this is to set you computer as it was on date before your infection. So be sure to choose a date prior to infection.
3. After System Restore you will be prompted to reboot you system. Startup you computer in regular mode. If the unwanted software disappeared you still should do some additional steps. Go to Step 7.
4. If system restore did not solve your issue you will have to struggle a bit more. Usually this software is located in the temp folder or application data folder. Search for your user folder in your system drive. If  you are still using Windows XP it should be Documents and Settings\[user]\Local Settings\Temp. If you are using using other newer version of Windows it should be Users\[user]\Local Settings\Temp. After you find this folder delete all content. 
5. Locate folder Application Settings. In Windows XP "c:\Documents and Settings\[user]\Local Settings\Application Data" on other Windows "c:\Documents and Settings\[user]\Local Settings\Application Data". Locate executable files (with extension .exe) in this folder and all subfolders with odd names. Like A00001.exe. Rename suspicious files changing their extension. By changing the .exe extension, Windows does not know what to do with that file when it's called. Example rename file A00001.exe to A00001.zzz.
6. Repeat steps 4 and 5 for all other existing user profiles on your computer.
7. Go to Control Panel and Internet Options and delete all history, temporary internet files and cookies.
8. If you have other browsers installed, like Google Chrome, Mozilla Firefox, Safari, etc.. look delete browsing history. On Google Chrome and Mozilla Firefox press Crtl+Shift+Del and clean all browsing history.
9. Scan all files from your computer with a real anti-virus.

The war against computer virus is never won. It's a daily battle. For sure this post "Chapter 1" will be completed or revised in the future considering new virus evolution and features.

I hope this post helped you with your issue.
You are welcome to complete or comment this article.

Installing Wordpress on Windows - sqmapi.dll not found

Problem Environment: Windows 2003 Server

I was trying to install Wordpress on my old Windows 2003 Server.
Here is the link with the installation instructions for Wordpress Windows setup.
After running the Web Platform Installer, that sets up IIS, MySql, PHP, etc.. it was time to setup Wordpress itself.

After that a error message appeared saying that sqmapi.dll was not found. Due to this error Wordpress was not installed.
My first attempt was to Google for this error. I found no relevant solution.

Here's what I've done.

I've searched My Computer for this sqmapi.dll. Search found one copy at the folder
C:\Program Files\IIS\Microsoft Web Deploy V3.

I thought maybe it was not on the search path for Windows.
So I went to My Computer Properties, Environment Variables, and added the path.

If you are a rookie in this sort of things don't forget to add a ; before the path you want to add and check if the path variable has everything else that was there before and you new entry.

After reboot I tried installing Wordpress again.
Done!! Problem Solved By IT.

General Computer Problems

I'm an IT Manager.
Solving problems is my job.

The purpose of this blog is to provide help on problems and issues concerning Computer Science.

How many times did you googled your problem and did not find an answer?
Many times right? But somehow you finished solving the problem by your own.
If you share your answer the next time anyone google's for a problem the answer will be available.
This is exactly what I want to do: Share the information for all those who need it.

All comments are welcome as they will contribute to a better answer.