Monday, April 27, 2015

Dynamics NAV and Tasklet Factory - Install Tasklet on Motorola MC65

On Motorola home screen, click bottom right hand icon> find WLAN
Create Profile

Plug usb into machine,
windows mobile device center installs
Navigate to file explorer "\"
Copy the install into the root folder

On device, navigate to File explorer>My Device
find install, click once
It will install silently
Click start, scroll to bottom look for Mobicontrol
Run Mobicontrol
If it says connected, everything is working, it should show up in the Mobicontrol Manager window

Navigate to https://mobi.taskletfactory.com/MC/Agents/147
to download MCAGENT
Click on MCAGENT to install
It will install silently
Contact Tasklet factory to complete the install

Thursday, April 23, 2015

Dynamics NAV - CAL Notes


  • Sample String: TEST

    • Left(TEST,2) = COPYSTR('TEST',1,2)
    • Right(TEST,2)  =  COPYSTR('TEST',STRLEN('TEST')-2,2)

  • Convert to string

    • Format(MyVar, 15)

  • Remove all whitespace to the left
    • DELCHR(Format(MyVar, 15),'<',' ')

  • Convert String to Number

    • Setup MyVar as global int
    • Evaluate(MyVar, StringToConvert)
    • MyVar is populated with int value of StringtoConvert
  • How to cycle through records to get anything you need
    • Create global variables for the Tables and Variables you wish to track
      • Table Records
        • Eg. Itm>Rec>Item Table
        • SH>Rec>Sales Header Table
      • Variables
        • InvNo>Text>30
        • CusNo>Text>30
      • Use this code to find what you need Aftergetrecord
      • Assuming we're setting the InvNo to some value, then cycling through the SH to find the Customer numbers
      • InvNo := 'INV000234'; //Sets value of InvNo
      • SH.SETRANGE("No.",InvNo); //Filters the SH table where No. = InvNo
      • If SH.FIND('-') THEN  //Starts to cycle through the filtered recordset. If it finds a record, move on
      • BEGIN //Start If Begin..End block
      • REPEAT //Start repeat loop
      • CusNo := SH."Customer No."; //Sets the CusNo variable to the "Customer No." field for the current record it's on
      • UNTIL SH.NEXT = 0; //ends loop when there are no more records
      • END; //Closes If BEGIN..END block
    • This code can be used in  a page or report to cycle through each aftergetrecord isntance to get the customer number for that specific record, then display it
    • To display it, simply insert a new line, and type in the Variable name in the column source and caption of your page or report
  • To reload a record from the database, or refresh data for a record, use the GET() function
  • Set a range using a date (all dates from 0D to 14D before today)
    • customer.setrange("Created date", 0D ,CALCDATE('<-14D>',TODAY))
  • Use date values
    • DATE2DMY(31,12,1900)
  • Filter to blank dates only
    • IAE.SETFILTER(OrigInboundDate,0D);
  • filter to nonblank dates only
    •     IAE3.SETRANGE(OrigInboundDate,0D,TODAY);
  • SETRANGE on date
    • ILE.SETFILTER("Posting Date",'%1..%2',PeriodStartDate[Bucket],PeriodStartDate[Bucket+1]);
  • Check if you are at the last record in a list
    • customer.LASTRECORD
  • Setrange on an option field
    • vendor.SETRANGE(vendor."Application Method",vendor."Application Method"::"Manual");
    • vendor.SETRANGE(vendor."Application Method",vendor."Application Method"::" ");
  • Setfilter using wildcards (*) and case insensitive (@)
    • SETFILTER(Description,'@*'+ItemDescFilter+'*');
  • Use COMMIT at the end of your REPEAT..NEXT loops to prevent table locking and complete rollback of all records if an error occurs
    • The routine runs slower, but will not lock up tables, and will not roll back ALL changes if the routine errors out or is cancelled
  • Set the focus to a subform
    • CurrPage.SalesLines.PAGE.ACTIVATE;

Dynamics NAV 2015 - How to use a CAL Variable on a Report


  • Define your Global variable in the CAL
    • Example MyVar
  • On the report, create a DataItem > Integer > MyDataItemName 
    • In the properties, set MaxIteration to 1
    • This controls how many times the report will cycle through the fields you add
  • Insert a new line
    • Insert MyVar > MyVarName
  • You will now be able to pull MyVarName on your Report Layout

Dynamics GP - After applying a payment to an invoice, the amount remaining is incorrect

Problem:
Amount remaining on an invoice is incorrect

Cause:
Sometimes, if something goes wrong during payment application, the transaction can get locked or stuck. The amount applied may be updated, but the transaction may not be truly applied.
When the transaction is unstuck, and the payment redone and reapplied, it causes the amount remaining to deduct a second time, resulting in an incorrect amount remaining.

Solution:

  • Unapply all applications to the offending transaction
  • Check the application tables to confirm no other records
    • select ortrxamt, curtrxam, * from rm20101 where DOCNUMBR = 'myinv            ' (check the curtrxamt on the transaction)
    • select * from rm20201 WHERE APTODCNM = 'myinv            ' (confirm nothing else applied)
    • If there is nothing applied, then manually update the CURTRXAMT field in the RM20101 table
      • Update RM20101 set curtrxamt = 'myamount' where docnumbr = 'myinvoice'
  • If this is a multicurrency transaction, you also have to update the Multicurrency RM table MC020102
    • select ORCTRXAM, ORORGTRX, * from mc020102 where DOCNUMBR = 'myinv'
    • update mc020102 set ORCTRXAM = ORORGTRX where DOCNUMBR = 'myinv '



Monday, April 20, 2015

SSRS - How to get a Sum total of an Average

Problem:
We need to calculate the average sales for each item.
But then we need a sum of all the averages in the total of the report.

Solution:

  • Calculate average per item normally
  • Create a footer on the item group
  • Use the avg function in a runningvalue formula in the footer to get a running total
  • Using visibility code, Only show the footer for the last item

SQL SSAS - Scheduled job to auto-process in a specific order


  • In SQL Management Studio, connect to the cube.
  • Script out the "process" for each dimension and save the script.


  • Create a SQL Agent job to run the script as a SSAS Command

Tuesday, April 14, 2015

Dynamics NAV 2015 - How to create a new Web Client Instance


  • Ensure your Web Components have been installed for NAV
  • Run the Microsoft Dynamics NAV 2015 Administration Shell as Administrator
    • Not the Developer Shell, look for this specific shortcut
  • Run 
  • This will create a new Web Client Instance, however, it will copy the web.config from your primary web client instance 
    • This results in the new Web Client instance still pointing to the old NAV server instance
    • To fix this, modify the web.config on the new web client instance, or modify the C:\Program Files\Microsoft Dynamics NAV\80\Web Client\instanceweb.config file which will be used as the template for the new instance
  • Navigate to C:\inetpub\wwwroot (you may need to give your account full access to this folder in order to change the web.config)
  • Look for the Instance folder
  • Go into it
  • Edit the web.config file (or navsettings.json)
  • Search for any references to the Primary NAV Instance, and the ClientServicesPort
  • Replace port and instance with new port and instance
    • Replace server with "localhost"
  • Go to the C:\inetpub\wwwroot\TEST2015\WebClient
    • Edit the instance web.config and replace all instances of the old instance name and port
  • Restart everything

To Remove a web server instance

  • In IIS
    • Expand Sites>Web Client>Instance
    • Delete the WebClient at the lowest level for the instance first
    • Remove the folder in IIS
    • Delete the instance folder from C:\inetpub\wwwroot

Dynamics NAV 2015 - How to create a Lookup, Dropdown, or option list

Situation:
Trying to create Lookup field on one Page that uses data from another Table to populate a field in the current table.

Solution:
This example - Creating a customer lookup on an invoice

  • Create Customer Table with CustomerID
  • Create Field for CustomerID on Invoice Header Table

  • On Invoice Header Page
    • Add CustomerID to Page
    • Set SourceExpr to point to the table you want to use as the lookup
    • Set TableRelation to the table/field you want to lookup on
      • In the table, set the DropDown list under View>Field Groups 
      • Name: DropDown, Group>three dots>Select fields you want in lookup
    • If you want advanced lookup options
      • Under Properties for the CustomerID field
        • Lookup = Yes
        • LookupPageID = Customer
More tips on customizing the dropdown here

To Create a Custom Drop Down list
  • On the Table, set the datatype to Option
  • Fill in the OptionString Field with your options lit separated by commas
  • If you want separate descriptions, Fill in the OptionCaption field on the Properties of the field on the page
    • example <Wedding,Baby Shower,Bridal Shower,Anniversary,Birthday,Grad,Home,Other>
To Create a Custom Drop Down list from a global Variable as an Input on the Request Page
  • Create Global Variable - Type Option
    • Enter all option values
  • On request page, insert global variable
  • On Report page, insert global variable so it can be seen by the report
  • Use SSRS or CAL Logic to reference the field
To create a dropdown field on a report request page 
  • Go to the table where the field is being taken from
  • Set the TableRelation to the table you want to get your dropdown values from
    • On the Table you're getting the dropdown values from, Design>View>Field Groups
    • Create a field group named "DropDown" and select the columns you want to display
    • Whenever this table is used as a related dropdown, it will use the DropDown field group settings

To create a completely custom lookup, use the Onlookup trigger on the table.
This logic will replace the default lookup.

Dynamics NAV LS Retail - Import Barcodes

Insert into CRONUS$Barcodes([Barcode No_]
      ,[Item No_]
      ,[Show for Item]
      ,[Description]
      ,[Blocked]
      ,[Last Date Modified]
      ,[Variant Code]
      ,[Unit of Measure Code]
      ,[Discount _])
Values
(Barcodeno,ItemNo,1,Description,0,getdate(),variantcode,uofmcode,0)

Dynamics GP 2013 R2 - "Smartlist builder needs to finish installing" every time you login

Original Solution here
Solution
The following steps can be used to resolve the issue.
1. Make a complete backup of the Dynamics and company SQL databases.
2. In the Microsoft Dynamics GP installation directory, rename the slbuild.dic and Application.SmartListBuilder.dll files.
3. In the Addin folder in the Microsoft Dynamics GP installation directory, rename the eOne.Dynamics.GP.DrillDownBuilder.Engine and eOne.Dynamics.GP.ExcelBuilder.Engine.dll files.
4. In the Control Panel, open the Programs and Features window. Find the Microsoft Dynamics GP 2013 installation and click Change. Remove the SmartList Builder Feature from the installation.
5. In the Control Panel, open the Programs and Features window if it isn't still open. Find the Microsoft Dynamics GP 2013 installation again and click Change. Add the SmartList Builder Feature to the installation.
6. In SQL Management Studio, run the following scripts to reset the status and version so the update will run again in Microsoft Dynamics GP Utilities.
update DYNAMICS..DB_Upgrade SET db_status=0 WHERE PRODID=3830
update DYNAMICS..DB_Upgrade SET db_verMajor=10, db_verBuild=84, db_verOldMajor=10, db_verOldBuild=84 WHERE PRODID=3830
update DYNAMICS..DU000020 SET versionMajor=10, versionBuild=84 WHERE PRODID=3830
7. Launch Microsoft Dynamics GP Utilities, log in as 'sa', and run through the Dynamics and company database update.
8. Verify the following scripts run without error in SQL Management Studio
select Extender_Type, Extender_ID from DYNAMICS..DDB10000
select Extender_Type, Extender_ID from DYNAMICS..ERB10100
select Extender_Type, Extender_ID from DYNAMICS..NLB10100
select Extender_Type, Extender_ID from DYNAMICS..SLB10100
select Extender_Type, Extender_ID from DYNAMICS..SLB10400
select Blank_Field_CB from DYNAMICS..ERB11100
select Blank_Field_CB from DYNAMICS..NLB11100
select Blank_Field_CB from DYNAMICS..SLB11100
9. Install the SmartList Builder download from the eOne Solutions site (eonesolutions.com)

Monday, April 13, 2015

NAV 2015 - How to connect to the Web Client

Once it has been installed from the NAV installer, you can access it by going to this location

http://ComputerName:Port/WebServerInstance/WebClient
example:
http://myserver:8080/NAV2015/WebClient
http://myserver:8080/DynamicsNav90/WebClient



  • To check what ports and names were actually installed, and whether or not your site is running, ope nthe IIS Manager
  • Expand your sites and confirm that the website is running
    • Expand the Site - the folder name is your WebserverInstance Name
  • If you have anything else running on the same port, it will disable any other sites configured for that port
  • Add the address to your trusted sites, also add this site
    • https://ajax.aspnetcdn.com

Friday, April 10, 2015

Dynamics NAV 2013 LS Retail - R5705 Transfer Receipt

When trying to modify the layout of this report, you get an error

Open the text file and find this line

<rd:ReportUnitType>Cm</rd:ReportUnitType>

There are two lines like this, delete the second one.
Save and import normally.

Tuesday, April 7, 2015

Dynamics NAV 2013 - Reporting Tricks


  • Display dynamics report values in the Page Header
    • Create a placeholder with a formula of =ReportItems!MatrixfieldName.Value where Matrixfieldname is the actual textbox name of a field on your report
    • The function will display whatever value you have in that field
  • Get the sum of group values, and not the sum of every single detail value
    • =Sum(Max(Fields!MyField.Value,"MyGroupName"))
  • Use CAL code to populate variables OnAfterGetRecord of your desired record
    • Try to use regular table joins to get to the most detailed level of your report first
      • Use CAL to grab additional information at each stage of the join
      • Use SSRS row hiding rules to hide the invalid detail lines for each header
    • Find the record and populate a variable
      • Itm.SETRANGE("No.","Item No.");
      • IF Itm.FIND('-') THEN
      • ItemDesc := Itm.Description;
      • You need to use a repeat until loop if you are getting more sub-records
    • Create a DataItem group named  Integer at the indentation level you need for your record details
      • Type in the variable name MatType as a DataSource and Name in the Report Dataset Designer
      • click on properties, set Maxiteration to 1
      • Use this column on your layout report
      • Create Subgroups if you are getting more sub-records
  • Set Default Report Filters
    • On Report, DataItem header
    • Populate the ReqFilterfields property
  • Get filter value and display on report
    • ILEFilter :=Item.GETFILTER("Location Filter"); 
  • Open Report with default set in Request page = current selected record on page
    • Example
      • GiftRec := Rec;
      • GiftRec.SETRANGE("No.",GiftRec."No.");
      • REPORT.RUNMODAL(50010,TRUE,TRUE,GiftRec);
  • To Get the fieldcaption of any field to display as a label that will change with language changes
    • Create a field in the report source using this formula
    • "Sales Line".FIELDCAPTION("Location Code")
  • To hide or skip records based on a request page parameter
    • Bank Account Ledger Entry - OnPreDataItem()
    • IF ShowUnmatched = FALSE THEN
    •   CurrReport.SKIP;

Dynamics NAV 2015 - An error occurred when opening the report designer. A supported version of Report Builder could not be found.

Problem: NAV 2015 installs Report Builder for SQL 2014 which cannot open the old report layouts from NAV 2013

Solution: