Cowmaster's Corner: February 2019

Tuesday, February 26, 2019

NAV BC - Extension example - Subscribe to Publisher and execute custom codeunit

When the customer is changed, the built-in publisher publishes the event and the Rec and xRec variables
The subscriber is triggered by the publish, picks up the Rec and xRec variables as parameters and executes the current AL function to display only the address before (xRec) and after (Rec) change fields
Procedure also executes a custom CAL function 50002

codeunit 50100 ExtToolbox

{

    trigger OnRun()

    begin

    end;

    var

        myInt: Integer;

    [EventSubscriber(ObjectType::Page, Page::"Customer Card", 'OnAfterValidateEvent', 'Address', true, true)]

    procedure ChangeMessage(var Rec: Record Customer; var xRec: Record Customer)

    var

        msg: Text[30];

        msg2: Text[30];

        MyObject: Codeunit 50002;

    begin

        msg := xRec.Address;

        msg2 := Rec.Address;

        Message('Old Value:' + msg + ' | New Value: ' + msg2);

        MyObject.Run;

    end;

}

NAV BC - Extension example - Create custom button, execute custom codeunit

This code creates a button on Page "Customer Card" after the Contact action button named "C50002".
When pressed, it executes custom codeunit 50002

pageextension 50102 CustomCodeunitTest extends "Customer Card"

{

    layout

{

}

    actions

{

        // Add changes to page actions here

        addafter(Contact)

{

            action("C50002")

{

                Promoted = true;

                PromotedCategory = Process;

                ApplicationArea = All;

                trigger OnAction();

                begin

                    MyObject.Run;

                end;

}

}

}

var

        MyObject: Codeunit 50002;

}

Dynamics NAV and Power BI

https://community.dynamics.com/nav/b/fajdigainfo/archive/2016/10/30/how-to-enable-microsoft-powerbi-in-nav-2017

Dynamics NAV - Transfer Order Process

Create Transfer Order
Enter Lines and quantities
Post>Ship

Releases document
Awaits receiving

Enter Qty. to Receive

Post>Receive
If entire qty is received, document will delete itself

If you change the original qty down to match the received qty AFTER partial receipt, post the document does nothing, and the only way to remove the document is to manually delete it

Dynamics NAV - Custom objects 50000 not showing up in VSCode symbol references

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-running-cside-and-al-side-by-side

NAV Service>Development

Enable loading application symbol references at server startup = true
restart service

Add this option to your NAV development shortcut
"C:\Program Files (x86)\Microsoft Dynamics 365 Business Central\130\RoleTailored Client\finsql.exe" generatesymbolreference=yes

Close and relaunch finsql
This will update the symbol references every time you MAKE A CHANGE to the CAL and RECOMPILE
If you just change object id's or descriptions, this is not detected as a change, and the symbol references are not updated

In VSCode, Ensure you have compiled all objects in NAV

Restart NAV Instance
AL:Download Symbols to update VSCode with object names from NAV

Dynamics NAV - Codeunit Extension

You cannot modify or extend an existing codeunit
You can create an entirely new codeunit
You can build subscriber functions that use existing publisher functions
Ensure you have compiled all objects in NAV

AL:Download Symbols to update VSCode with object names from NAV

tcodeunit = new codeunit structure

tprocedure = new method structure

Define Eventsubscriber trigger event (find details in development environment)
Define procedure to accept any parameters coming from the publisher
define vars
define code

codeunit 50100 ExtToolbox

{

    trigger OnRun()

    begin

    end;

    var

        myInt: Integer;

    [EventSubscriber(ObjectType::Page, Page::"Customer Card", 'OnAfterValidateEvent', 'Address', true, true)]

    procedure ChangeMessage(var Rec: Record Customer; var xRec: Record Customer)

    var

        msg: Text[30];

        msg2: Text[30];

    begin

        msg := xRec.Address;

        msg2 := Rec.Address;

        Message('Old Value:' + msg + ' | New Value: ' + msg2);

    end;

}

Monday, February 25, 2019

Dynamics NAV - Events, Publishers and Subscribers

https://docs.microsoft.com/en-us/dynamics-nav/walkthrough--publishing--raising--and-subcribing-to-an-event-in-microsoft-dynamics-nav

Two types of Events exist

NAV CAL Events

Eg. OnValidate

SQL Trigger Events

Eg. OnAfterModifyEvent

Publisher

A custom function with a single parameter

C50000 = Publisher

Global Function = AfterValidate

Parameter = Name

Properties as follows

Event: Publisher
Eventtype: Integration

If we wanted to Publish P21 Customer.Name OnValidate()

In Customer Object, Name OnValidate() CAL

Create global = Publisher,Codeunit 50000,Publisher
Add line Publisher.AfterValidate(Name)
Now, whenever Customer.Name OnValidate() occurs, Publisher.AfterValidate will "Publish"

Subscriber

A custom function C50001:Custom,function CusNameChanged containing custom CAL with Properties as follows

Event = Subscriber
EventPublisherObject = P21 Customer Card
Eventfunction = AfterValidate

Do not change the default Parameters that get created after selecting the eventfunction or your trigger will not work

Enter any custom CAL on this function

Now, the Event is linked to the publisher, which is linked to the subscriber
Whenever the Event happens, the Publisher triggers, and the Subscriber executes

Event: Customer.Name OnValidate() executes
Publisher: Publisher.AfterValidate(Name) executes and sends trigger
Subscriber: Custom.CusNameChanged() executes

LS One - Functionality Notes

Can Ls One track price changes?

Yes. LS One automatically tracks all changed in the audit database
However, the GUI for the site manager only filters by date and user
A custom SSRS report should be built to handle audit log queries

Can LS One automatically clear old audit logs?

It has a manual function in site manager that can be run to remove all audit logs older than a certain date

Friday, February 22, 2019

Dynamics NAV - Payment Tolerance, Write-offs, Clearing small account balances

AR and AP Writeoffs

Use Payment Tolerances to auto-writeoff small balances when applying payments against invoices

Setup payment tolerances

Payment tolerances can be setup directly on the Currency Card
OR
General Ledger Setup>Actions>Change Payment Tolerance
Define % or amount per currency or for all to change for multiple currencies at once

The % and the Amount must be > 0 or the Payment Tolerance Account columns on the customer and Vendor Posting Group screens will not be visible
The Max tolerance field is what really controls the range

Enable warnings if you would like to know when the tolerance rules are being applied

Define Payment Tolerance Accounts

General Posting Setup>Sales Pmt. Tol Debit Acc.
General Posting Setup>Sales Pmt. Tol Crebit Acc.

If you would like to only use this for customers, and not vendors, simply leave the Pmt. Tol fields blank for the vendor posting groups, and vendor tolerances will not post

Process

Create Cash receipt for amount over or under invoice, but within tolerance range
Select document to apply to
A prompt will appear to confirm if to apply tolerance, or leave remaining amount
The invoice will be fully paid off
The payment GL Entry will will have an additional line for the small amount going to the Tolerance Account

Account Closing

Income Statement Accounts

Year-end routine will generate a journal to clear all income statement accounts

Recurring Journals

Create a recurring Journal
Recurring Method B Balance
Click Allocations
Enter Account No. = Writeoff Account>Allocation % = 100%
Repeat this for all accounts that will need to be periodically cleared
Post the journal to clear the balances
The recurring journal will remain for the next time this exercise needs to be done

Power BI - This visual contains restricted data

Give Access to workspace/report

Power BI>Workspaces
Select workspace>Share icon (second icon in action column, tiny chart with an arrow)
Enter email access
Remove option to allow user to share if you do not want them sharing as well
Click Share
User will get an e-mail with a link
User Clicks link
User can now access workspace or report

Assign to Group if Group permissions (Row Level Security) have been set

PowerBI>Workspaces>Select Workspace>Dataset
Click ... next to dataset
Click Security
Select Role
Add user to role
User can now access the data in the report in the workspace

*SPECIAL NOTE* If your Power BI is in Teams

User must be a member of the team to view the report

Thursday, February 21, 2019

TableExtension ExtCus :: Unsupported table change. Table:Customer; Change:Remove

You must define upgrade code to deprecate the field,
OR add "schemaUpdateMode": "Recreate" to your launch.json to completely remove and recreate your extension (deletes all extension data)

NAV Extensions - Extension Management - Publish, Synchronize, Install. Compatibility and duplicates.

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-how-publish-and-install-an-extension-v2

Publish

Adds the Extension to your Extension Management List on the server
Unpublishing removes the extension and ALL data associated with it

Synchronize

Synchronizes to the schema
Must be done to allow install
Synchronize in Clean mode will remove all versions and data for the extension

Install

Enables the extension for use in the current tenant/company
Uninstall removes the functionality from the tenant/company, but retains the data

An extension is identified by it's ID in the app.json when you create a new project from View>Command Palette>AL:Go!

"id": "d759b508-4de7-440a-8c2d-1d37267ea925",

You cannot use object names or id's that already exist, or have already been used by other extensions

When attempting to publish to the server, it will check that none of the object names or id's already exist for existing extensions
If any objects already exist, publishing will fail, and Visual Studio code will indicate the duplicated objects

Extensions will work with CAL modifications, however CAL modifications will not be able to interact with any Extension objects

Tuesday, February 19, 2019

NAV Extensions - Extend Page, Convert CAL text report to AL Extension Report

Export report from NAV Development environment as text file (C:\CAL\1COA.txt)
Launch Development shell as Admin

cd "C:\Program Files (x86)\Microsoft Dynamics 365 Business Central\130\RoleTailored Client"

Run the following to convert the CAL text to AL code

./txt2al --source="C:\CAL" --target="C:\AL" --rename

This will generate converted files in the C:\AL folder
Copy these files into your Visual Studio Code Project folder

Modify the *.al file to point to the rdlc
Remove the # characters
Change dataitem line to reflect actual table names

Change dataitem(DataItem1; Table18)
to dataitem(Customer; Customer)

Create page action button to launch the report

pageextension 50100 MyExtension extends "Chart of Accounts"

{

layout

{

}

actions

{

// Add changes to page actions here

addafter("Detail Trial Balance")

{

action("Custom Report")

{

Promoted = true;

PromotedCategory = Process;

ApplicationArea = All;

trigger OnAction();

begin

clear(myReport);

myReport.Run;

end;

}

}

}

var

MyReport: Report CusList;

}

By default, filters on tables from the request page will filter JUST the data in that specific table first, and then join the data as outer joins unless you use cal to explicitly apply the filter to the datasets in the prereport.

Eg.
Item No - OnPreDataItem()
"Item".SETFILTER("Item"."No.",'1000');

NAV CAL - Check for Non-Blank Date

IF DateFilt > 0D THEN BEGIN
END;

NAV CAL - Add a variable filter on request page to filter calculated field

Report>RequestPage

Add Container Name
Add Group Name
Create Global Variable to store request input
Add field>Select Global Variable
This Variable will hold the input from the request page

CAL on Report

OnAfterGetRecord>
Compare Request page variable to report field and skip record if no match
IF Variable <> Reportfield THEN CurrReport.SKIP

Monday, February 18, 2019

SQL - Run sql from a batch file

https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-run-transact-sql-script-files?view=sql-server-2017

Create text file with sql script named myScript.sql
create batch file with following command

sqlcmd -S myServer\instanceName -i C:\myScript.sql

Run batch file

Full script with logging

https://www.sqlservercentral.com/forums/topic/batch-file-to-run-sql-scripts

@ECHO OFF

SET SQLCMD="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"

SET PATH="C:\path\to\sql\files\"

SET SERVER="Server\Instance"

SET DB="Database"

SET LOGIN="sa"

SET PASSWORD="pass"

SET OUTPUT="C:\OutputLog.txt"

CD %PATH%

ECHO %date% %time% > %OUTPUT%

for %%f in (*.sql) do (

%SQLCMD% -S %SERVER% -d %DB% -U %LOGIN% -P %PASSWORD% -i %%~f >> %OUTPUT%

)

NAV Extensions - Extend a Table

tableextension 50100 ExtCus extends 18

{

    fields

    {

        field(50100; LongName; Text[50])

        {

            Caption = 'Long Name';

        }

    }

}

Sunday, February 17, 2019

Agile meetings

Define Allowances and Consequence

Allowed bad behaviour x times, then consequences

Adapt all the time
Empower your team members
Tell your story
Always Try to achieve happiness. Believe that it is possible.
Weekly, generally Friday to recap the week

Good News

What did you achieve?
What worked?

Bad News

What got stuck/delayed/blocked?
What didn't work?

Next Steps

Goals to achieve for the next week based on feedback
Improve/Maintain the good
Adjust,abandon, or try new approaches for the bad

Thursday, February 14, 2019

NAV Object Ranges

https://community.dynamics.com/business/b/businesscentraldevitpro/archive/2018/10/17/which-object-ranges-can-we-use-with-microsoft-dynamics-365-business-central

SCRUM Basics and VSTS

Capture requirements, build FDD

Capture User stories

[Role:]As a Purchasing Agent i need to
[Requirement:] see Total Sales from all Stores
[Reason:] to make better purchasing decisions
Generate Requirements

Discuss and agree on acceptance criteria

Build test cases to identify all error handling points

Generate Tasks

Assign tasks to team members
Team members Enter expected times for completion against each task

Missed Tasks are added as new Requirements>Tasks and linked to the original requirement

Every morning, 15 minute stand up meeting
Each person will answer 3 questions

What did you achieve?

Task was Completed? Delayed? Blocked?
Log entry created in activity log for all team members per day
All completed work is reviewed and confirmed before status updated
Task status/hours updated

What do you expect to complete today? (Exit Criteria)
Scrum master sends recap email to document and guide team members

NAV Extensions - NAV 2018+ and Dynamics 365 Business Central - How to make an extension

Dynamics 365 Business Central uses Extensions V2 (All previous versions must be upgraded to v2 to work on Business central)

Events

https://community.dynamics.com/nav/b/navision360/archive/2017/11/13/introduction-to-events-in-microsoft-dynamics-nav

Extensions

http://thinkaboutit.be/2018/11/how-do-i-develop-an-extension-in-business-central-on-premises/

https://community.dynamics.com/nav/b/katsonsnavblog/archive/2017/07/19/how-to-do-al-vs-code-for-beginners-start-of-video-series

Download Business Central On-Premise from Partnersource

https://mbs.microsoft.com/partnersource/northamerica/support/support-news/bconpremdownload

Install Business Central On-Premise
Import developer license

Import-NAVServerLicense BC160 -LicenseData ([Byte[]]$(Get-Content -Path "fin.flf" -Encoding Byte))

Enable Developer Service

Business Central Administration>Development>Tick Enable Developer Service Endpoint

Ensure windows user is setup as Super in Company

Download and install Visual Studio Code

https://code.visualstudio.com/
Click Extensions (Last icon on the top left list, square icon)

Search for AL
Select AL Language>Install
Restart VB Code
View>Command Palette>AL:Go!
Select Path to store solution
Select Launch.json

Change "name" from Your own server to server instance name ("BC130")
Change "server" to true address path ("http://localhost:8080")
Add "port" = 7049
Authentication = "Windows"
Enable download Symbols

View>Command Palette> AL:Download Symbols

Add TranlsationFile

in app.json
Change line "runtime": "2.3"
to "runtime": "2.1"
After this line,
Add line

,"features": ["TranslationFile"]

save

Publish Extension to environment

F5 or AL:Publish
Resolve NetFx40_LegacySecurityPolicy error

C:\Program Files\Microsoft Dynamics 365 Business Central\130\Service
Edit Microsoft.Dynamics.Nav.Server.exe.config in notepad as administrator
Find <NetFx40_LegacySecurityPolicy enabled="true" />
change to <NetFx40_LegacySecurityPolicy enabled="false" />
Restart nav service

Web client opens with published extension

If you try to publish again, you may get the error "cannot synchronize because a newer version is already synchronized"

Run this in the admin shell to remove it completely even after uninstalling and unpublishing
https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-unpublish-and-uninstall-extension-v2
Sync-NAVApp -ServerInstance BC130 -Name 'Hello World' -Mode Clean

Sample working launch.json

{

"version": "1.0.0.1",

"configurations": [

{

"type": "al",

"request": "launch",

"name": "BC130",

"server": "http://mypc:8080",

"serverInstance": "BC130",

"port": 7049,

"authentication": "Windows",

"startupObjectId": 22,

"startupObjectType": "Page",

"breakOnError": true

}

]

}

Wednesday, February 13, 2019

Windows - Control program startup order and timing

http://www.winpatrol.com/download.html

Monday, February 11, 2019

Barcode Fonts Code 128, Code 39

http://www.barcodelink.net/barcode-font.php

Free 3 of 9

Must use * before and after
* + 12345 + *
Example *12345* scans as 12345
Ensure Bold, Italics, etc. are turned off
For SSRS the formula looks like

="*"+Fields!Barcode.Value+"*"

Code128

Does not work without encoder

Wednesday, February 6, 2019

Dynamics NAV CAL - Flowfield to get last price, status or value based on max date

Create two flowfields

LastPriceDate = Max of the date column
Save the table
LastPrice = Price filtered on LastPriceDate = Date

Dynamics NAV - What triggers a Purchase Order Archive? What exactly gets archived?

Create PO

Nothing happens

Print PO

Nothing happens

Release PO

Vendor Perf. Header: Copies current PO data
Vendor Perf. Line: Copies current PO Lines

Print PO

Nothing happens

Edit Existing PO Lines

Does NOT Update or add to existing Vendor Perf. Lines
Does NOT archive

Receive PO

Purchase Header Archive: Copies current PO Data before receipt
Purchase Line Archive: Copies current lines before receipt
Vendor Perf. Header: Updates using current PO data after receipt
Vendor Perf. Line: Updates using current PO Lines after receipt
For Lines changed to 0 order qty, Vendor Perf. Line will record a 0 qty receipt at the current receipt date

Any line with 0 receipt qty, but with a receipt date represents a cancelled line

Add New Lines to PO

Vendor Perf. Line: Copies new PO Lines

Invoice PO

Vendor Perf. Header: Updates using current PO data after invoice
Vendor Perf. Line: Updates using current PO Lines after invoice

In English, this means

PO is archived only when received
Vendor Fulfilment uses the original numbers on PO Lines when the release button is pressed the first time.
If a new line is entered, the next time the release button is pressed, that line is added to the Vendor fulfilment calculation
Vendor fulfilment will NEVER be affected by any future changes to the PO
When the PO is Received or Invoiced, Vendor Fulfilment lines are updated with receipt and invoice information only. The original Recorded PO amounts at the time of releasing the PO do not change in the Vendor Fulfilment calculations.

NAV CAL - Convert Dateformula to Int

LeadTime is your DateFormula field
Create IntLeadTime variable as int
CALCDATE(ItemVend."Lead Time Calculation",TODAY) - TODAY;

Tuesday, February 5, 2019

BIT_RMHATB

USE [TWO]
GO

/****** Object: Table [dbo].[BIT_RMHATB] Script Date: 02/05/2019 11:39:46 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BIT_RMHATB](
[APPLY_AMOUNT] [numeric](19, 5) NOT NULL,
[AGING_AMOUNT] [numeric](19, 5) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUSTNAME] [char](65) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [BALNCTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [USERDEF1] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CNTCPRSN] [char](61) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PHONE1] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SLPRSNID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SALSTERR] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PYMTRMID] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTPER] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTPAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUSTCLAS] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SHRTNAME] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ZIP] [char](11) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [STATE] [char](29) NOT NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUDSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AGNGDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CHCUMNUM] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCNUMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [RMDTYPAL] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DCURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORTRXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CURTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AGNGBUKT] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CASHAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [COMDLRAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SLSAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [COSTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [FRTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [MISCAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [TAXAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DISAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DDISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [TRXDSCRN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCABREV] [char](3) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CHEKNMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DUEDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [GLPOSTDT] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DISCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [POSTDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DINVPDOF] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DCURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCASAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORSLSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCSTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORDAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORFRTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORMISCAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORTAXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORORGTRX] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DDENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DMCTRXSTT] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [Aging_Period_Amount] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APFRDCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APFRDCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [FROMCURR] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APTODCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APTODCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APPTOAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ACURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DATE1] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [POSTED] [tinyint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ADISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PPSAMDED] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [GSTDSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ACURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [RLGANLOS] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORAPTOAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ADENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AMCTRXSTT] [smallint] NOT NULL

GO

SET ANSI_PADDING OFF
GO

Dynamics GP - SSRS - How to format the GP Phone number in SSRS

= Format(Convert.ToDouble(Fields!PHONE1.Value), "(###) ###-#### 'Ext.' ####")

Friday, February 1, 2019

Power BI - Division by Zero Error encountered

https://community.powerbi.com/t5/Desktop/division-by-zero/td-p/48702

Create new Measure
syntax is IF(Condition, Then, Else)

=IF([QuantityKG] = 0,0,DIVIDE([NetValue],[QuantityKG])

Power BI - How to calculate Sum of a Max

If you don't do this, the totals on the report for this field calculate in Maxes instead of sums.

Create new measure.

CalcQtyAvlbl = SUMX(

SUMMARIZE(Combined_StockBudget, Combined_StockBudget[QtyAvailable], "QtyAvlbl", MAX(Combined_StockBudget[QtyAvailable])),

[QtyAvlbl])