SQL Scripts for Microsoft Dynamics GP: Assembly Transaction Quantities Required

Ian Grieve1 minute read0 comment

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of work status assemblies and the quantity required for the assembly.

CREATE VIEW uv_AZRCRV_AssembleQuantityRequired AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	TRX_ID
	,ITEMNMBR
	,Assemble_Quantity
FROM
	BM10300 --Assembly Component (BM10300)
WHERE
	Component_ID = 0
AND
	Parent_Component_ID = -1
GO

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Verify PM Batches Exist
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert Mfg BOMs from Text File
SQL Function To Return Approver
List of Active Fixed Assets
Insert Manufacturing Routings from Text File
Table Function to Split String on Delimiter
List of Open Payables Transactions
Insert Creditor Item Numbers
Return Top Level BOM for Manufacturing Orders
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
list of Open Payables Distributions
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
Payment Run Apply Query
List GL Transactions
Simple RMA Audit
Change Vendor Change Approvals Joins and Fields
Insert National Accounts from CSV
List GL Accounts With Notes
Import Site Bins From CSV
Remove Multicurrency from Sales Transactions
Change Email Notification Assignment
List General Ledger Transactions (Excluding Year End Journals)
List Taxes Linked to GL Accounts
Allow Workflow Originator to be an Approver
Add Joins and Fields to PM Document Approval Notification Emails
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Upload and Verify Tax Commodity Codes
Delete Corrupt Extended Pricing Data
Assign All Items to All Site Bins
Sales Transactions (Work) Against a Specific Site
Change Web Service URi
SQL View to Return Quantity Available
Verify Tax Detail Assigned to Vendor
Insert Extended Pricing Price Sheet Header
Prefix Companies Names with System Designator
SQL View to Return Category Linked to Segment 3 in COA
Update Site Descriptions From CSV
Copy Workflow from Source to Destination Database
Extract GL Period Balances
Sales by Customer By Year
Purchased Items With Serial Numbers and Linked Sales Transactions
SQL View to Return Purchase Orders
Select All Primary Keys and Generate ALTER Script
Copy Workflow Calendar from Source to Destination Database
SQL Trigger on PO invoice Insert to Change GL posted Date
Sales by Salesperson By Year
Script to Set Transactions as Included on VAT Daybook Return
SQL Script to Return PO Receipts
Insert Extended Pricing Price Sheet UofM Work
View for Payables Transactions Extract
Export Open/History PM Transactions After a Specified Date
Copy Email Messages from a Source to Destination Database
PO Receipt History View
Insert Extended Pricing Price Sheet Assignments
Extract Payables Transactions from All Companies
List Open Purchase Orders
SQL View to Create Division Tree for Management Reporter
Select Chart of Accounts
Activate Horizontal Scroll Bars for All Existing Users
Workflow Assignment Review
Update Item Replenishment Method for Manufacturing
Get Alpha Characters from an Alphanumeric String
Set Vendor On Hold If EFT Details Changed
List Open Purchase Order Lines
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Delete Orphaned Vendor EFT Details
Sales Invoice Query
Round Extended Pricing Price Sheet Item Value
Get Numeric Characters from an Alphanumeric String
Trigger to Activate Horizontal Scroll Bars for New Users
View to Return List of Payments and Linked Invoices
Select Duplicate Extended Pricing Price Sheet Work Records
RM Aged Debt Report
Select Next Temporary Creditor ID
Select a List of Vendor Addresses
Set Vendor On Hold When Created
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Check for Corrupt Extended Pricing Records
Sales Line Items
Compare Ship To Address on Work Sales Trx Against Customer
SQL View to Return PO Commitment Detail
List Bank Accounts with Linked GL Accounts
Validate and Insert/Update Vendor Emails from a Text File
Return Items with Incorrect Quantities
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
Update Ship To Name on Work Sales Transactions to Match the Customer Name
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
List of PM Invoices for Vendors with POs
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Mfg Cost Accounts from Mfg Item Class Setup
PO Commitment Detail
Update Inventory Accounts from Item Class
Create Macro to Delete Items
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Item Resource Planning on Item Quantity Master from Text File
SQL View to Return List of Posted Vendor Document Numbers
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Item Engineering File from a Text File
List of Exchange Rates
PrintFriendlyLinkedin

In Microsoft Dynamics 365 Business Central (Administration), how do I… Create a Sandbox for a Preview Release

Ian Grieve3 minute read0 comment

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Administration), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

While I intend to cover the Release 2023 Wave 1 preview which has just been made available, the same process will hold true for future releases as well.

With previews you can only create these as new environments; you cannot upgrade an existing sandbox environment to a preview. To create the new sandbox, you need to access the Business Central Admin Center.

On the top action bar click the New button to open the Create Environment sidebar.

Enter your new Environment Name, set the Type to Sandbox and select your Country; the final field is the Version, which will default to the currently available live version, but if you click the drop down you ill be able to select the clearly marked Preview version:

Business Central Admin Center showing the Create environment sidebar after clicking the New button on the action bar

Continue reading “In Microsoft Dynamics 365 Business Central (Administration), how do I… Create a Sandbox for a Preview Release”

PrintFriendlyLinkedin

SQL Scripts for Microsoft Dynamics GP: Sales Invoice Query

Ian Grieve5 minute read0 comment

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns work status sales invoices which are linked to a history status sales order and along with data for the work transaction returns the originating transactions order number and document date.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Sales Transaction Work'].SOPNUMBE
	,['Sales Transaction Work'].SOPTYPE
	,['Company Master'].CMPNYNAM AS COCMPNYNAM
	,['Company Master'].ADDRESS1 AS COADDRESS1
	,['Company Master'].ADDRESS2 AS COADDRESS2
	,['Company Master'].ADDRESS3 AS COADDRESS3
	,['Company Master'].CITY AS COCITY
	,['Company Master'].STATE AS COSTATE
	,['Company Master'].ZIPCODE AS COZIPCODE
	,['Company Master'].COUNTY AS COCOUNTRY
	,['Company Master'].TAXREGTN AS COTAXREGNO
	,['Company Master'].UDCOSTR1 AS COREGNO
	,['Company Master'].PHONE1 AS COPHONE1
	,['Internet Addresses - Company'].INET1 AS COEMAIL
	,['Internet Addresses - Company'].INET2 AS COWEBSITE
	,['Sales Transaction Work'].CUSTNMBR
	,['Sales Transaction Work'].CUSTNAME
	,['Sales Transaction Work'].CSTPONBR
	,['Sales Transaction Work'].SOPNUMBE AS INVNO
	,['Sales Transaction History'].SOPNUMBE AS ORDNO
	,['Sales Transaction History'].DOCDATE
	,['Currency Setup'].CRNCYSYM
	,['RM Customer Master'].TXRGNNUM AS CUSTTAXREGNO
	,['Customer Master Address'].ADDRESS1 AS BTADDRESS1
	,['Customer Master Address'].ADDRESS2 AS BTADDRESS2
	,['Customer Master Address'].ADDRESS3 AS BTADDRESS3
	,['Customer Master Address'].CITY AS BTCITY
	,['Customer Master Address'].STATE AS BTSTATE
	,['Customer Master Address'].ZIP AS BTZIP
	,['Customer Master Address'].COUNTRY AS BTCOUNTRY
	,['Sales Transaction Work'].CUSTNMBR AS CUSTCODE
	,['Sales Transaction Work'].ADDRESS1 AS STADRESS1
	,['Sales Transaction Work'].ADDRESS2 AS STADDRESS2
	,['Sales Transaction Work'].ADDRESS3 AS STADDRESS3
	,['Sales Transaction Work'].CITY AS STCITY
	,['Sales Transaction Work'].STATE AS STSTATE
	,['Sales Transaction Work'].ZIPCODE AS STZIP
	,['Sales Transaction Work'].COUNTRY AS STCOUNTRY
	,['Sales Transaction Work'].ORSUBTOT
	,['Sales Transaction Work'].ORTAXAMT
	,['Sales Transaction Work'].ORSUBTOT + ['Sales Transaction Work'].ORTAXAMT AS ORTOTAL
	,['Sales Transaction Work'].DUEDATE
FROM
	SOP10100 AS ['Sales Transaction Work'] WITH (NOLOCK) --Sales Transaction Work (SOP10100)
INNER JOIN
	RM00101 AS ['RM Customer Master'] WITH (NOLOCK) --RM Customer MSTR (RM00101)
		ON
			['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
INNER JOIN
	RM00102 AS ['Customer Master Address'] WITH (NOLOCK) --Customer Master Address File (RM00102)
		ON
			['Customer Master Address'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
		AND
			['Customer Master Address'].ADRSCODE = ['Sales Transaction Work'].PRBTADCD
INNER JOIN
	SOP30200 AS ['Sales Transaction History'] WITH (NOLOCK) --Sales Transaction History (SOP30200)
		ON
			['Sales Transaction History'].SOPTYPE = ['Sales Transaction Work'].ORIGTYPE
		AND
			['Sales Transaction History'].SOPNUMBE = ['Sales Transaction Work'].ORIGNUMB
INNER JOIN
	DYNAMICS..SY01500 AS ['Company Master'] WITH (NOLOCK) --Company Master (SY01500)
		ON
			['Company Master'].INTERID = DB_NAME()
LEFT JOIN
	DYNAMICS..MC40200 AS ['Currency Setup'] WITH (NOLOCK) --Currency Setup (MC40200)
		ON
			['Currency Setup'].CURNCYID = ['Sales Transaction Work'].CURNCYID
LEFT JOIN
	SY01200 AS ['Internet Addresses - Company'] WITH (NOLOCK) --Assembly Component (BM10300)
		ON
			['Internet Addresses - Company'].Master_Type = 'CMP'
		AND
			['Internet Addresses - Company'].ADRSCODE = 'PRIMARY'
WHERE
	['Sales Transaction Work'].SOPTYPE = 3

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Verify PM Batches Exist
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert Mfg BOMs from Text File
SQL Function To Return Approver
List of Active Fixed Assets
Insert Manufacturing Routings from Text File
Table Function to Split String on Delimiter
List of Open Payables Transactions
Insert Creditor Item Numbers
Return Top Level BOM for Manufacturing Orders
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
list of Open Payables Distributions
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
Payment Run Apply Query
List GL Transactions
Simple RMA Audit
Change Vendor Change Approvals Joins and Fields
Insert National Accounts from CSV
List GL Accounts With Notes
Import Site Bins From CSV
Remove Multicurrency from Sales Transactions
Change Email Notification Assignment
List General Ledger Transactions (Excluding Year End Journals)
List Taxes Linked to GL Accounts
Allow Workflow Originator to be an Approver
Add Joins and Fields to PM Document Approval Notification Emails
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Upload and Verify Tax Commodity Codes
Delete Corrupt Extended Pricing Data
Assign All Items to All Site Bins
Sales Transactions (Work) Against a Specific Site
Change Web Service URi
SQL View to Return Quantity Available
Verify Tax Detail Assigned to Vendor
Insert Extended Pricing Price Sheet Header
Prefix Companies Names with System Designator
SQL View to Return Category Linked to Segment 3 in COA
Update Site Descriptions From CSV
Copy Workflow from Source to Destination Database
Extract GL Period Balances
Sales by Customer By Year
Purchased Items With Serial Numbers and Linked Sales Transactions
SQL View to Return Purchase Orders
Select All Primary Keys and Generate ALTER Script
Copy Workflow Calendar from Source to Destination Database
SQL Trigger on PO invoice Insert to Change GL posted Date
Sales by Salesperson By Year
Script to Set Transactions as Included on VAT Daybook Return
SQL Script to Return PO Receipts
Insert Extended Pricing Price Sheet UofM Work
View for Payables Transactions Extract
Export Open/History PM Transactions After a Specified Date
Copy Email Messages from a Source to Destination Database
PO Receipt History View
Insert Extended Pricing Price Sheet Assignments
Extract Payables Transactions from All Companies
List Open Purchase Orders
SQL View to Create Division Tree for Management Reporter
Select Chart of Accounts
Activate Horizontal Scroll Bars for All Existing Users
Workflow Assignment Review
Update Item Replenishment Method for Manufacturing
Get Alpha Characters from an Alphanumeric String
Set Vendor On Hold If EFT Details Changed
List Open Purchase Order Lines
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Delete Orphaned Vendor EFT Details
Sales Invoice Query
Round Extended Pricing Price Sheet Item Value
Get Numeric Characters from an Alphanumeric String
Trigger to Activate Horizontal Scroll Bars for New Users
View to Return List of Payments and Linked Invoices
Select Duplicate Extended Pricing Price Sheet Work Records
RM Aged Debt Report
Select Next Temporary Creditor ID
Select a List of Vendor Addresses
Set Vendor On Hold When Created
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Check for Corrupt Extended Pricing Records
Sales Line Items
Compare Ship To Address on Work Sales Trx Against Customer
SQL View to Return PO Commitment Detail
List Bank Accounts with Linked GL Accounts
Validate and Insert/Update Vendor Emails from a Text File
Return Items with Incorrect Quantities
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
Update Ship To Name on Work Sales Transactions to Match the Customer Name
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
List of PM Invoices for Vendors with POs
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Mfg Cost Accounts from Mfg Item Class Setup
PO Commitment Detail
Update Inventory Accounts from Item Class
Create Macro to Delete Items
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Item Resource Planning on Item Quantity Master from Text File
SQL View to Return List of Posted Vendor Document Numbers
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Item Engineering File from a Text File
List of Exchange Rates
PrintFriendlyLinkedin

Enable Auto Unlock for BitLocker Protected External Drive

Ian Grieve3 minute read0 comment

MicrosoftI travel a lot for work and sometimes carry files with me on a SSD which has BitLocker enabled to ensure no-one else can access the files should I lose the device. In January 2020 I published an article on using PowerShell to unlock an encrypted drive using PowerShell.

I did it that way as I was working onsite and wanted to be able to plug a drive in when needed, but not manually type the password every time; I didn’t want to have the drive auto-unlock when plugged in as I was signed in under a generic admin account (I had the PowerShell script) tucked away in a location which would be difficult, if not impossible, for anyone else to find. I also deleted it at the end of the day so there wasn’t a copy left behind.

As mentioned above, you can configure windows to automatically unlock a drive when it is plugged in. I find this particular option useful when working on my own laptop or PC.

You can do this through the Manage BitLocker Control Panel applet.

Once you’ve launched this find the drive in the list and mark the Turn on auto-unlock option:

BitLocker Drive Encryption settings app
PrintFriendlyLinkedin

SQL Scripts for Microsoft Dynamics GP: Workflow Assignment Review

Ian Grieve2 minute read0 comment

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created to allow a client to easily review the workflow step assignments.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Workflow Tasks Table'].Workflow_Name
	,['Workflow Tasks Table'].WorkflowInstanceID
	,['Workflow Tasks Table'].WorkflowTaskAssignedTo
	,['Workflow Step Instance Table'].Workflow_Step_Status
	,['Workflow Step Instance Table'].Workflow_Step_Assign_To
	,['Workflow Instance Master'].Workflow_Status
	,['Workflow Instance Master'].Workflow_Originator
FROM
	WFI10004 AS ['Workflow Tasks Table'] --Workflow Tasks Table (WFI10004)
INNER JOIN
	WFI10003 AS ['Workflow Step Instance Table'] --Workflow Step Instance Table (WFI10003)
		ON
			['Workflow Tasks Table'].WorkflowInstanceID = ['Workflow Step Instance Table'].WorkflowInstanceID
INNER JOIN
	WFI10002 AS ['Workflow Instance Master'] --Workflow Instance Master (WFI10002)
		ON
			['Workflow Tasks Table'].WorkflowInstanceID = ['Workflow Instance Master'].WorkflowInstanceID

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Verify PM Batches Exist
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert Mfg BOMs from Text File
SQL Function To Return Approver
List of Active Fixed Assets
Insert Manufacturing Routings from Text File
Table Function to Split String on Delimiter
List of Open Payables Transactions
Insert Creditor Item Numbers
Return Top Level BOM for Manufacturing Orders
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
list of Open Payables Distributions
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
Payment Run Apply Query
List GL Transactions
Simple RMA Audit
Change Vendor Change Approvals Joins and Fields
Insert National Accounts from CSV
List GL Accounts With Notes
Import Site Bins From CSV
Remove Multicurrency from Sales Transactions
Change Email Notification Assignment
List General Ledger Transactions (Excluding Year End Journals)
List Taxes Linked to GL Accounts
Allow Workflow Originator to be an Approver
Add Joins and Fields to PM Document Approval Notification Emails
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Upload and Verify Tax Commodity Codes
Delete Corrupt Extended Pricing Data
Assign All Items to All Site Bins
Sales Transactions (Work) Against a Specific Site
Change Web Service URi
SQL View to Return Quantity Available
Verify Tax Detail Assigned to Vendor
Insert Extended Pricing Price Sheet Header
Prefix Companies Names with System Designator
SQL View to Return Category Linked to Segment 3 in COA
Update Site Descriptions From CSV
Copy Workflow from Source to Destination Database
Extract GL Period Balances
Sales by Customer By Year
Purchased Items With Serial Numbers and Linked Sales Transactions
SQL View to Return Purchase Orders
Select All Primary Keys and Generate ALTER Script
Copy Workflow Calendar from Source to Destination Database
SQL Trigger on PO invoice Insert to Change GL posted Date
Sales by Salesperson By Year
Script to Set Transactions as Included on VAT Daybook Return
SQL Script to Return PO Receipts
Insert Extended Pricing Price Sheet UofM Work
View for Payables Transactions Extract
Export Open/History PM Transactions After a Specified Date
Copy Email Messages from a Source to Destination Database
PO Receipt History View
Insert Extended Pricing Price Sheet Assignments
Extract Payables Transactions from All Companies
List Open Purchase Orders
SQL View to Create Division Tree for Management Reporter
Select Chart of Accounts
Activate Horizontal Scroll Bars for All Existing Users
Workflow Assignment Review
Update Item Replenishment Method for Manufacturing
Get Alpha Characters from an Alphanumeric String
Set Vendor On Hold If EFT Details Changed
List Open Purchase Order Lines
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Delete Orphaned Vendor EFT Details
Sales Invoice Query
Round Extended Pricing Price Sheet Item Value
Get Numeric Characters from an Alphanumeric String
Trigger to Activate Horizontal Scroll Bars for New Users
View to Return List of Payments and Linked Invoices
Select Duplicate Extended Pricing Price Sheet Work Records
RM Aged Debt Report
Select Next Temporary Creditor ID
Select a List of Vendor Addresses
Set Vendor On Hold When Created
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Check for Corrupt Extended Pricing Records
Sales Line Items
Compare Ship To Address on Work Sales Trx Against Customer
SQL View to Return PO Commitment Detail
List Bank Accounts with Linked GL Accounts
Validate and Insert/Update Vendor Emails from a Text File
Return Items with Incorrect Quantities
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
Update Ship To Name on Work Sales Transactions to Match the Customer Name
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
List of PM Invoices for Vendors with POs
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Mfg Cost Accounts from Mfg Item Class Setup
PO Commitment Detail
Update Inventory Accounts from Item Class
Create Macro to Delete Items
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Item Resource Planning on Item Quantity Master from Text File
SQL View to Return List of Posted Vendor Document Numbers
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Item Engineering File from a Text File
List of Exchange Rates
PrintFriendlyLinkedin

In Microsoft Dynamics 365 Business Central (Administration), how do I… Start a Free Trial (Updated for the new “customised trial”)

Ian Grieve3 minute read0 comment

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Administration), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

Back in August when I was early in this series, I wrote an article about how you sign up for a free trial of Microsoft Dynamics 365 Business Central; well, in the last few days Microsoft have changed the process, in what is a somewhat contentious way. It seems that for the US and UK when you try to sign up for a free trial Microsoft are often guiding you to sign up to a customised trial through Bam Boom Cloud instead of just starting a free trial the way you did originally.

James Crowter has a full explanation of this change on Linked In, including why it is contentious, but in summary Microsoft have picked a favoured partner and are sending all referrals to them based on the answers you give to three questions; I tired a few different combinations and it was about 50/50 as to whether they recommended a customised trial with Bam Boom Cloud or a normal free trial. James said from his testing it was less than 11 users and not requiring an extended licence (i.e. manufacturing or service) that would get you guided to the customised trial.

The steps to sign up for a free trial are outlined below.

Continue reading “In Microsoft Dynamics 365 Business Central (Administration), how do I… Start a Free Trial (Updated for the new “customised trial”)”

PrintFriendlyLinkedin

ClassicPress Plugins Available From azurecurve | Development in 2023: Series Index

Ian Grieve3 minute read4 Comments

[featured-index key=’classicpress’]I started developing plugins for WordPress back in 2013; this was so that I could add some required functionality to this site which wasn’t available as standard or in third-party plugins. I switched to developing for ClassicPress in late-2018 and migrated all of my sites to it in early 2019.

I made this change because WordPress introduced the Gutenberg editor (later known as the block editor) which has many problems which are still unaddressed and getting worse as they transition to blocks site-wide.

I decided right at the start when I started developing plugins for myself that I would also make them available to others to use as well. Not all of the plugins I have developed are available, as some have very specific functionality which wouldn’t easily be used by other people, but the vast majority are available for all.

I now have 55 plugins available for ClassicPress and, as it has been over 12 months since I posted about many of them), I’ve going to do another series announcing each one which may help new people discover them.

All of these plugins were developed for ClassicPress, but will still work on WordPress; I’ve included automatic updates for them so once installed you will receive updates in the exact same way as you do for other plugins.

As I say, there are 55 plugins and I’ll only be posting these plugin announcements on weekends, so you will need to check in over the few months to see them all.

The series index, below, will update automatically as each post goes live.

Continue reading “ClassicPress Plugins Available From azurecurve | Development in 2023: Series Index”

PrintFriendlyLinkedin

SQL Scripts for Microsoft Dynamics GP: Update Item Replenishment Method for Manufacturing

Ian Grieve2 minute read0 comment

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script updates the replenishment method on all item/site combinations:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
UPDATE
	['Item Quantity Master']
SET
	REPLENISHMENTMETHOD = 1
FROM
	IV00102 AS ['Item Quantity Master'] --Item Quantity Master (IV00102)
INNER JOIN
	IVR10015 AS ['Item Engineering File'] --IVR10015
		ON
			['Item Engineering File'].ITEMNMBR = ['Item Quantity Master'].ITEMNMBR
WHERE
	['Item Quantity Master'].RCRDTYPE = 2
AND
	['Item Engineering File'].MAKEBUYCODE_I = 1
GO

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Verify PM Batches Exist
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert Mfg BOMs from Text File
SQL Function To Return Approver
List of Active Fixed Assets
Insert Manufacturing Routings from Text File
Table Function to Split String on Delimiter
List of Open Payables Transactions
Insert Creditor Item Numbers
Return Top Level BOM for Manufacturing Orders
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
list of Open Payables Distributions
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
Payment Run Apply Query
List GL Transactions
Simple RMA Audit
Change Vendor Change Approvals Joins and Fields
Insert National Accounts from CSV
List GL Accounts With Notes
Import Site Bins From CSV
Remove Multicurrency from Sales Transactions
Change Email Notification Assignment
List General Ledger Transactions (Excluding Year End Journals)
List Taxes Linked to GL Accounts
Allow Workflow Originator to be an Approver
Add Joins and Fields to PM Document Approval Notification Emails
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Upload and Verify Tax Commodity Codes
Delete Corrupt Extended Pricing Data
Assign All Items to All Site Bins
Sales Transactions (Work) Against a Specific Site
Change Web Service URi
SQL View to Return Quantity Available
Verify Tax Detail Assigned to Vendor
Insert Extended Pricing Price Sheet Header
Prefix Companies Names with System Designator
SQL View to Return Category Linked to Segment 3 in COA
Update Site Descriptions From CSV
Copy Workflow from Source to Destination Database
Extract GL Period Balances
Sales by Customer By Year
Purchased Items With Serial Numbers and Linked Sales Transactions
SQL View to Return Purchase Orders
Select All Primary Keys and Generate ALTER Script
Copy Workflow Calendar from Source to Destination Database
SQL Trigger on PO invoice Insert to Change GL posted Date
Sales by Salesperson By Year
Script to Set Transactions as Included on VAT Daybook Return
SQL Script to Return PO Receipts
Insert Extended Pricing Price Sheet UofM Work
View for Payables Transactions Extract
Export Open/History PM Transactions After a Specified Date
Copy Email Messages from a Source to Destination Database
PO Receipt History View
Insert Extended Pricing Price Sheet Assignments
Extract Payables Transactions from All Companies
List Open Purchase Orders
SQL View to Create Division Tree for Management Reporter
Select Chart of Accounts
Activate Horizontal Scroll Bars for All Existing Users
Workflow Assignment Review
Update Item Replenishment Method for Manufacturing
Get Alpha Characters from an Alphanumeric String
Set Vendor On Hold If EFT Details Changed
List Open Purchase Order Lines
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Delete Orphaned Vendor EFT Details
Sales Invoice Query
Round Extended Pricing Price Sheet Item Value
Get Numeric Characters from an Alphanumeric String
Trigger to Activate Horizontal Scroll Bars for New Users
View to Return List of Payments and Linked Invoices
Select Duplicate Extended Pricing Price Sheet Work Records
RM Aged Debt Report
Select Next Temporary Creditor ID
Select a List of Vendor Addresses
Set Vendor On Hold When Created
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Check for Corrupt Extended Pricing Records
Sales Line Items
Compare Ship To Address on Work Sales Trx Against Customer
SQL View to Return PO Commitment Detail
List Bank Accounts with Linked GL Accounts
Validate and Insert/Update Vendor Emails from a Text File
Return Items with Incorrect Quantities
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
Update Ship To Name on Work Sales Transactions to Match the Customer Name
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
List of PM Invoices for Vendors with POs
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Mfg Cost Accounts from Mfg Item Class Setup
PO Commitment Detail
Update Inventory Accounts from Item Class
Create Macro to Delete Items
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Item Resource Planning on Item Quantity Master from Text File
SQL View to Return List of Posted Vendor Document Numbers
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Item Engineering File from a Text File
List of Exchange Rates
PrintFriendlyLinkedin

Browser Extension to Force All Microsoft Dynamics 365 Business Central Pages to Open Maximised

Ian Grieve3 minute read0 comment

Microsoft Dynamics 365 Business CentralOne of the things I’ve been finding somewhat annoying with Dynamics BC is that pages open in a small size and need to be manually maximised. It turns out that I am not the only one to think this; Milan Milinčević, a Dynamics 365 Business Central developer with experience of .NET and JavaScript, also thought this, but had the skills to do something about it and created a plugin for Chrome and which is available in the Google web store.

Company Information page in the not full width size

He blogged about the BC Maximizer extension, including technical details for those interested, and included an animated gif showing the plugin in action:

Animation showing the BC Maximizer browser extension in action

I installed the extension into Vivaldi (a Chromium based browser) to give it a test drive; it does exactly what Milan said it would do and makes every page open full width. I’m going to install it onto my other machines and give it a proper testing, but so far it looks really good.

A extension for Edge is also currently under going review before being available in the Microsoft app store, although you can enable loading of apps from the Google web store in Edge. When asked on Twitter he also responded that he would look into what was needed to make a Firefox version of the extension.

You can get the extension from the Google web store.

PrintFriendlyLinkedin

New Functionality In Microsoft Dynamics 365 Business Central 2023 Wave 1: Copy And Paste Readable Hyperlinks

Ian Grieve3 minute read0 comment

Microsoft Dynamics 365 Business CentralThis post is part of the New Functionality In Microsoft Dynamics 365 Business Central 2023 Wave 1 series in which I am taking a look at the new functionality introduced in Microsoft Dynamics 365 Business Central 2023 Wave 1.

The seventh of the new functionality in the User experiences section is Copy And Paste Readable Hyperlinks.

Working productively with Business Central often requires multi-tasking across windows or capturing links to records that are shared with others. Each of these needs links or windows to clearly distinguish themselves from the next, making it easy to switch contexts.

Enabled for: Users, automatically
Public Preview: Mar 2023
General Availability: Apr 2023

Feature Details

Business Central now uses a simple and clear schema for link and window captions that is automatically updated as you navigate within the web client. As a result, copying a link to any Business Central page or record and pasting that in a rich-text editor, such as an Outlook email, Microsoft Teams chat, or your project OneNote, will display a more meaningful hyperlink that is consistent no matter how or where you copy the link. Similarly, using modern browser features such as collections in Microsoft Edge allows you to capture a more readable collection of records. When managing multiple streams of work during your workday, switching across windows using Alt+Tab now clearly indicates which page or record you are about to switch to.

Continue reading “New Functionality In Microsoft Dynamics 365 Business Central 2023 Wave 1: Copy And Paste Readable Hyperlinks”

PrintFriendlyLinkedin