NAV - How to set a default freeze pane on a form

https://docs.microsoft.com/en-us/dynamics-nav/freezecolumnid-property

• Design>Page>Repeater>Properties
• Choose FreezeColumnID
• All columns up to that column will be frozen

GP - How to check if users are deleting tax details from SOP documents

SOP10100 - SOP Header
SOP10200 - SOP Line
sop10105 - Tax Line Details
LNITMSEQ - Tax summary

Put a trigger on the SOP10105 to detect insert,delete,modify

Or, put a trigger on SOP10200 TaxAmnt changing

GP SQL View - Historical Inventory Aging

-------------------------------------------------------------------------------------------

Use this view to build a historical inventory aging by filtering and calculating age of stock in each cost layer. Need to combine with inventory aging logic.

-------------------------------------------------------------------------------------------

SELECT  CASE A.RCPTSOLD

          WHEN 1 THEN 'Closed'

          WHEN 0 THEN 'Open'

          ELSE 'NA'

        END AS 'Cost Layer Status' ,

        A.RCPTNMBR AS 'In Receipt Number' ,

        CASE A.PCHSRCTY

          WHEN 1 THEN 'Adjustment'

          WHEN 2 THEN 'Variance'

          WHEN 3 THEN 'Transfer'

          WHEN 4 THEN 'Override'

          WHEN 5 THEN 'Receipt'

          WHEN 6 THEN 'Return'

          WHEN 7 THEN 'Assembly'

          WHEN 8 THEN 'In-Transit'

          ELSE 'NA'

        END AS 'In Transaction Type' ,

        A.DATERECD AS 'In Date Received' ,

        A.ITEMNMBR AS 'In Item Number' ,

        A.TRXLOCTN AS 'In Transaction Location' ,

        A.QTYRECVD AS 'In Quantity Received' ,

        A.QTYSOLD AS 'In Quantity Sold' ,

        A.UNITCOST AS 'In Unit Cost' ,

        A.RCTSEQNM AS 'In Receipt Sequence Number' ,

        ISNULL(B.ORIGInDOCID, ' ') AS 'Out Document Number' ,

        ISNULL(B.DOCDATE, ' ') AS 'Out Document Date' ,

        ISNULL(B.ITEMNMBR, ' ') AS 'Out Item Number' ,

        ISNULL(B.TRXLOCTN, ' ') AS 'Out Transaction Location' ,

        ISNULL(B.QTYSOLD, 0) AS 'Out Quantity Sold' ,

        ISNULL(B.UNITCOST, 0) AS 'Out Unit Cost' ,

        ISNULL(B.SRCRCTSEQNM, ' ') AS 'Out Source Receipt Sequence Number' ,

        ISNULL(C.SOPTYPE, ' ') AS 'SLS SOP Type' ,

        ISNULL(C.SOPNUMBE, ' ') AS 'SLS SOP Number' ,

        ISNULL(C.UNITPRCE, 0) AS 'SLS SOP Unit Price'

FROM    IV10200 AS A

        LEFT OUTER JOIN IV10201 AS B ON A.ITEMNMBR = B.ITEMNMBR

                                        AND A.TRXLOCTN = B.TRXLOCTN

                                        AND A.RCTSEQNM = B.SRCRCTSEQNM

        LEFT OUTER JOIN ( SELECT    CASE SOPTYPE

                                      WHEN 1 THEN 'Quote'

                                      WHEN 2 THEN 'Order'

                                      WHEN 3 THEN 'Invoice'

                                      WHEN 4 THEN 'Return'

                                      WHEN 5 THEN 'Back Order'

                                      WHEN 6 THEN 'Fulfillment Order'

                                      ELSE 'NA'

                                    END AS SOPTYPE ,

                                    SOPNUMBE ,

                                    ITEMNMBR ,

                                    LOCNCODE ,

                                    UNITCOST ,

                                    UNITPRCE

                          FROM      SOP30300

                        ) AS C ON B.ORIGInDOCID = C.SOPNUMBE

                                  AND B.ITEMNMBR = C.ITEMNMBR

                                  AND B.TRXLOCTN = C.LOCNCODE

Dynamics GP - A unique document number could not be found. Please check setup.

run this

select MAX(a.MSTRNUMB) from (select MSTRNUMB from SOP10100 union all select MSTRNUMB from SOP30200) a

Set your SOP Setup master number to the next number

Dynamics GP - Auto Enable Activity Tracking - Expose log through smartlist builder

--------------------------------------------------

Set on a job schedule to run as often as you need to check for new users

---------------------------------------------------

update Dynamics.dbo.SY60100

set TRKUSER = 1

where TRKUSER = 0

print 'Changed : ' + str ( @@ROWCOUNT )

-------------------------------------------

Create smartlist

--------------------------------------------

select * from dynamics.dbo.sy05000

Give permission to SQL object

Give permission to smartlist object

Give GP permission to view smartlists with sql data

Give GP permission to Smartlist

Jet Reports - NAV - How to add a dimension filter in an NL function

https://support.jetglobal.com/hc/en-us/community/posts/115008497188-Filter-by-nonglobal-dimension

Project is the dimension

=NL("Rows","G/L Entry",,"Posting Date","3/1/08","G/L Account","33000","Project","C123")

Dynamics NAV - Setup and use Workflow

• Confirm that a nav user account is setup for the service account
• User Setup>Setup Email address on current User
• User Setup>Setup Email address on User account Nav Job Queue service is running with with e-mail address that it should be sending as (this should be the same as the login for the smtp setup)
• Check your Notification Entry table and clear any old jobs that may be failing and preventing new mail from being sent
• Setup SMTP Mail Setup
• smtp.office365.com
• port:587
• Authentication: Basic
• UserID: full email address that does not have authenticator required
• Password: e-mail password
• Secure connection: Yes
• Setup Approval Users
• Workflows>New Workflow from Template
• Workflow Lines>Repsonses>Set approval type and due date
• Create Job Queue for Mail Notify C1509

To send Overdue notifications

• Create a new Overdue Workflow from the Workflow Templates>Administration>Overdue Template
• Enable it
• Now you can run the Send Overdue Approval Notifications job
• You can only send notifications about overdue requests, so ensure that your due date formula in the workflow is set appropriately.