Wednesday, February 6, 2019

NAV CAL - Convert Dateformula to Int


  • LeadTime is your DateFormula field
  • Create IntLeadTime variable as int

  • CALCDATE(ItemVend."Lead Time Calculation",TODAY) - TODAY;
Posted by at No comments:

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


Posted by at No comments:

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


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

Posted by at No comments:

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])
Posted by at No comments:

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])
Posted by at 1 comment:

Friday, January 25, 2019

LS NAV - Replenishment Tables - Configuration Package Tables

  • Item Vendor (99) - Lead times per vendor (Item Vendors)
  • Item (27) - Control if item can be replenished
    • Replen. Item Store Rec (10012206) - Replen. Item Store List Card (Item Store Replenishment Rule Card)
    • Status: Active
    • Ordered by: Central
    • Ordering Method: Calculated
    • Blocked: False
    • Item Status Link (10001404) - Retail Item Status
      • Block Purchasing: False
      • Block Transferring: False
Posted by at No comments:

Tuesday, January 22, 2019

LS NAV/BC - Useful / Helpful LS NAV Reports

https://community.dynamics.com/nav/b/exploredynamicsnav
Report Layouts list of all Reports in BC
  • Core NAV
    • Item Card>Items by Location
    • Customer\Item Sales
    • Item Sales by Customer
    • Sales Statistics
    • Inventory-Sales Statistics
    • Price List
    • Item Age Composition
    • Inventory Value
    • Inventory Availability - Reorder Points, order recommendation
    • Item Expiration - Quantity report (R5809) 
    • Invt. Valuation - Cost Spec. - Shows cost breakdown of all items
    • Item Charges - Specification - Shows all item charges
    • Inventory to G/L Reconcile (For reconciling to GL)
      • Use this report for auditing to GL
    • Inventory Valuation - WIP
    • Items to be counted
      • Phys Inventory Journal > functions > calculate counting period
    • Warehouse Shipment Status - shows all orders and what shipments they are on
    • Total Purchases?
      • Vendor Purchases by Item
    • Total Sales?
      • Salesperson Statistics by inv
    • Profitability?
      • Salesperson Statistics by inv
  • Core Purchasing
    • Vendor Purchase Statistics (Shows LCY)
    • Item Charges - Specification
    • Purchase Order>Purchase Advice (low stock, no stock)

  • LS NAV
    • Item Sales Statistics
    • Payments by Store Report 0 - Shows cash in all stores
      Payments by Periods Report
    • Dyn. Item Hierarchy
    • Sales History - Dyn. Item Hierarchy
    • Shelf Label Printing> Actions>Functions>Print New Label Price Report
    • Average Basket Size

Posted by at No comments:
Subscribe to: Posts (Atom)