SQL Script to get all accounts set as available in Purchasing lookups

Ian Grieve3 minute read0 comment

Microsoft Dynamics GPFollowing on from the last post, in which I posted a script to get accounts linked to a vendor, this post is a script which returns all accounts which are set as available in lookups in the Purchasing series.

The SQL is straightforward, but I had to work out the binary entries to check for by saving and checking each combination.

/*
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
	['Account Master'].ACTINDX
	,['Account Index Master'].ACTNUMST
	,['Account Master'].ACTDESCR
FROM
	GL00100 AS ['Account Master']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
WHERE
	DSPLKUPS IN
				(
				0x04000000 -- purchasing
				,0x05000000 -- sales/purchasing
				,0x06000000 -- inventory control/purchasing
				,0x07000000 -- sales/inventory control/purchasing
				,0x0C000000 -- purchasing/payroll
				,0x0D000000 -- sales/purchasing/payroll
				,0x0E000000 -- inventory control/purchasing/payroll
				,0x0F000000 -- sales/inventory control/purchasing/payroll
				,0xFFFFFF7F -- never set
				)
GO
PrintFriendlyLinkedin

SQL Script to get all vendor purchasing accounts

Ian Grieve3 minute read3 Comments

Microsoft Dynamics GPI recently needed to select all assigned purchasing accounts for a particular vendor; this would include both the normal purchasing one on the Vendor Account Maintenance window and the additional ones in the Additional Vendor Accounts window.

The SQL is fairly straightforward, but I had to make sure each account was only included once:

/*
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 DISTINCT
	['Account Master'].ACTINDX
	,['Account Index Master'].ACTNUMST
	,['Account Master'].ACTDESCR
FROM
	PM00200 AS ['PM Vendor Master']
LEFT JOIN
	PM00203 AS ['Vendor Accounts']
		ON
			['Vendor Accounts'].VENDORID = ['PM Vendor Master'].VENDORID
LEFT JOIN
	GL00100 AS ['Account Master']
		ON
			['Account Master'].ACTINDX = ['Vendor Accounts'].ACTINDX
		OR
			['Account Master'].ACTINDX = ['PM Vendor Master'].PMPRCHIX
LEFT JOIN
	GL00105 AS ['Account Index Master']
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
WHERE
	['PM Vendor Master'].VENDORID = 'ADVANCED0001'
GO
PrintFriendlyLinkedin

SQL Script to find SQL Objects containing specific text

Ian Grieve3 minute read0 comment

Microsoft SQL ServerI did some work for a client recently which involved some custom SQL objects created by a consultant at their previous VAR. It appeared that there was a consistent prefix on the created objects which meant we could use a script I’ve previously posted to identify them all.

However, we quickly realised there were other objects which did not adhere to the naming convention we’d identified. Fortunately, I remembered writing a script to update database names in SQL views after copying live to test which identified the views to update by using the sys.sql_modules object.

Using that oncept, I came up with the below script which will search for the text specified in the variable at the top in all objects:

/*
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).
*/
DECLARE @SearchString VARCHAR(100) = 'string to find'

SELECT DISTINCT
	['sys objects'].name AS 'Object Name'
	,['sys objects'].type_desc AS 'Object Type Description'
FROM
	sys.sql_modules AS ['sys modules']
INNER JOIN
	sys.objects AS ['sys objects']
		ON
			['sys objects'].object_id = ['sys modules'].object_id
WHERE
	['sys modules'].definition like '%' + @SearchString + '%'
PrintFriendlyLinkedin

Implementing Fastpath’s Audit Trail: View reports

Ian Grieve2 minute read0 comment

FastpathThis post is part of the series on Implementing Fastpath's Audit Trail and is part of the parent Implementing Fastpath's Assure Suite series.

In yesterday’s post, I took a look at the Data Changes view available in the Fastpath Assure portal for Audit Trail. This is the way of looking at the raw data, but is not the friendliest method of reviewing the audited changes which is available, It is suitable for someone like me who understands the data structure of Microsoft Dynamics GP, but the ordinary end-user needs something more user friendly.

Fortunately, this is available from the reports which are included as standard. To access these reports, expand Audit Trails and select Reports; there is a long list of reports then shown in the navigation pane:

Audit Trail Available Reports

Continue reading “Implementing Fastpath’s Audit Trail: View reports”

PrintFriendlyLinkedin

Implementing Fastpath’s Audit Trail: Review data changes

Ian Grieve2 minute read1 Comment

FastpathThis post is part of the series on Implementing Fastpath's Audit Trail and is part of the parent Implementing Fastpath's Assure Suite series.

With Audit Trails installed, configured with triggers created and a scheduled data collect in place, the audited changes are available for users to view in the Fastpath Assure portal.

The first palc you can see them, and the one covered in this post, is in Data Changes (accessible from (Audit Trail » Data changes):

Data Changes

Continue reading “Implementing Fastpath’s Audit Trail: Review data changes”

PrintFriendlyLinkedin

ClassicPress Development with GitHub: Create Release

Ian Grieve2 minute read0 comment

GitHubWhen I started developing plugins for ClassicPress I decided that I needed to be using source control. As ClassicPress is intending to use GitHub for their plugin directory, it made sense for me to use it as well. This post is part of a series on ClassicPress Development with GitHub.

When developing with GitHub, you can make a release; this is a way of grouping together all of the changes since the last release to make it easy to download that particular code set. One point to note, is that while GitHub will automatically create a zip of the source code, this isn’t suitable to use for a ClassicPress release as it will include -master in the contained directory name. However, you can upload a zip file containing the code in the correct folder.

To create a new release, load the repository page on GitHub and click the releases button (red ringed):

Repository page

Continue reading “ClassicPress Development with GitHub: Create Release”

PrintFriendlyLinkedin

Implementing Fastpath’s Audit Trail: Ad hoc data collect

Ian Grieve2 minute read0 comment

FastpathThis post is part of the series on Implementing Fastpath's Audit Trail and is part of the parent Implementing Fastpath's Assure Suite series.

Ina previous post I covered the scheduled collection of Audit Trails data. This will allow you to schedule a regular collection of data which, for example, may run every day in the early hours, but sometimes you might need to audit ome data same day. To facilitate this, the Assure portal includes an ad hoc data collection routine for Audit Trails.

To run this ad hoc collect data, select the Manage Data page (Audit Trail » Setup » Manage Data) and click the arrows button.

Clicking the Collect button will run the data collection and pull all new data through to Fastpath:

Collect Data changes

Click to show/hide the Implementing Fastpath's Audit Trail Series Index

Implementing Fastpath’s Audit Trail
Implementing Fastpath's Audit Trail: Installation
Implementing Fastpath's Audit Trail: Configuration
Implementing Fastpath's Assure Suite: Run minimum permissions script
Implementing Fastpath's Assure Suite: Schedule Audit Trails data collection
Implementing Fastpath's Audit Trail: Grant AT Access
Implementing Fastpath's Audit Trail: Select company to audit
Implementing Fastpath's Audit Trail: Select tables to audit
Implementing Fastpath's Audit Trail: Create Triggers
Implementing Fastpath's Audit Trail: Apply templates
Implementing Fastpath's Audit Trail: Apply triggers to other companies
Implementing Fastpath's Audit Trail: Transfer between systems
Implementing Fastpath's Audit Trail: Ad hoc data collect
Implementing Fastpath's Audit Trail: Review data changes
Implementing Fastpath's Audit Trail: View reports
PrintFriendlyLinkedin

Implementing Fastpath’s Assure Suite: Assign Permissions

Ian Grieve2 minute read0 comment

FastpathThis post is part of the series on Implementing Fastpath's Assure Suite.

In the last post, I showed how to create a new user which would allow that user to log into the portal, but that would not allow them to do anything else; to allow them to do something more you need to assign permissions.

There is two ways to assign security and I find the first way to be, by far, the easiest.

Firstly, you can assign security by user (Setup » Users) by selecting the user and then by clicking the toggle next to the role to which you want to assign them:

Assign roles to users

Continue reading “Implementing Fastpath’s Assure Suite: Assign Permissions”

PrintFriendlyLinkedin

Implementing Fastpath’s Assure Suite: Create new portal user

Ian Grieve2 minute read1 Comment

FastpathThis post is part of the series on Implementing Fastpath's Assure Suite.

The management of Fastpath Assure is done through the Fastpath Assure portal (in the UK, the address is https://uk.fastpathassure.com/). The administrator account will be configured by Fastpath when the portal is created with additional users being created by that administrator.

To create a new user, log into the portal and open the Users page (Setup » Users). Existing users will be displayed; at the top of the view click the green plus icon to create a new user:

Continue reading “Implementing Fastpath’s Assure Suite: Create new portal user”

PrintFriendlyLinkedin

Implementing Fastpath’s Audit Trail: Transfer between systems

Ian Grieve2 minute read0 comment

FastpathThis post is part of the series on Implementing Fastpath's Audit Trail and is part of the parent Implementing Fastpath's Assure Suite series.

As mentioned in a previous post, triggers are created in one company and can be deployed to other companies on the same system, but in a lot of cases, you might need to create them on a development system and then deploy them to the live one.

Fastpath Audit Trails does allow you to do this via the Save Settings function available as a button on the toolbar:

Save settings button

Continue reading “Implementing Fastpath’s Audit Trail: Transfer between systems”

PrintFriendlyLinkedin