Featured post

HenryDale.com – Maconomy implementation services

I am a Consultant Systems Accountant with 13+ years’ experience of implementing and managing Deltek-Maconomy finance and business systems in the Marketing Services industry. I have experience of numerous full lifecycle Maconomy implementations, including four different shared-service projects, and dozens of individual company migrations.

Recently I have been involved in a Maconomy X1 to 2.1 upgrade, my role being to adjust multiple system settings and configure the 2.1 Workspace Client to enable a seamless transition of users from the Portal and Java Client interfaces to using the Workspace Client.

I have a track record of producing standardised and efficient implementations with a range of methodologies for data gathering and cleansing, build file preparation, build management, build configuration and testing, data importing, brought-forward balance transfers, and post go-live support.

Whether you are planning to implement Maconomy, have already begun to implement, or have a system you want to upgrade or develop further, you can contact me for advice via the following means:

E: info@henrydale.com
M: +44 (0)7711 292694
Skype: henry-dale
LinkedIn: uk.linkedin.com/in/henrydale

SQL – Windows

List of all Windows

select modulename, windowname, (select Name from PopupItem where PopupItemNumber = internalwindowtype and PopupTypeName = 'InternalWindowTypeType') as "internal window type", internalwindowname, createdby, createddate, changedby, changeddate, versionnumber, developedby, hasaccessthroughmenu, defaultlayoutname from Window order by modulename, internalwindowtype, windowname

SQL – Timesheet

Daily Time Sheet header between dates

select EmployeeNumber, EmployeeName, TheDate, DateSubmitted from DailyTimeSheetHeader where thedate > '2013.12.31' and thedate < '2014.02.01' order by employeenumber, thedate

SQL – Tasks

List of all unique TaskName Description combinations

select distinct taskname, description from tasklistline order by taskname

Find data for specific Tasks

select distinct taskname, description, activitynumber from tasklistline where taskname in ('TaskName1','TaskName2', 'TaskName3', 'TaskName4', 'TaskName5')

Find Tasks where OverwriteActivity is not set

select taskname, description, overwriteactivity from tasklistline where tasklist like 'TaskListName' and overwriteactivity = 0

Find Tasks which require Employees

select TaskList, TaskName, Description, activity.ActivityNumber from tasklistline, activity where tasklistline.tasklist like 'TaskListName' and tasklistline.activitynumber = activity.activitynumber and activity.EmployeeRequired = 1

Find all Tasks that aren’t in a particular TaskList

select distinct taskname, description from tasklistline where taskname not in (select taskname from tasklistline where tasklist = 'TaskListName') order by taskname

Find all Tasks on Jobs of a particular JobGroup

select distinct l.tasklist, t.taskname, t.description, t.activitynumber from tasklistline t, tasklistheader l, jobheader j where l.tasklist = t.tasklist and l.tasklist = j.tasklist and j.jobgroup = 0 order by t.activitynumber

Find blocked instances of specific a Task

select TaskListHeader.TaskList, TaskListHeader.CreatedDate, TaskListHeader.CreatedBy, TaskListHeader.ChangedDate, TaskListHeader.ChangedBy, TaskListLine.TaskName, TaskListLine.Description, TaskListLine.Blocked from TaskListHeader, TaskListLine where TaskListLine.TaskList = TaskListHeader.TaskList and TaskListLine.TaskName = 'TaskName' and TaskListLine.Blocked = 1 order by TaskListHeader.tasklist

SQL – List custom Print Layouts

None of the standard layouts have a space in their name, so where a layout naming convention hasn’t been adhered to (tut tut) it is still possible to filter the custom layouts using the following:

select InternalWindowName, LayoutName, InternalRelationName from PrintOutLayout where LayoutName like '% %' order by InternalWindowName, LayoutName

One of the many benefits of using a Company Number prefix naming convention (best practice) is that the layouts will be ordered by Company.

To exclude the layouts which aren’t selected by print layout selection (eg Cheque layout), use the following modified query. This example was run on SQLServer database through Developer Tools using the Maconomy::sqlNative dropdown selection; you may find the string length syntax doesn’t work in other versions of SQL.

select InternalWindowName, LayoutName, InternalRelationName from PrintOutLayout where LayoutName like '% %' and len(InternalRelationName) > 0 order by InternalWindowName, LayoutName

A modification to the above will give you all the valid field names available in the Print Layout Selection for each Layout. Use the output from this to paste into a Print Layout Selection Import and then remove the lines not required to select each layout.

select l.InternalWindowName, l.LayoutName, l.InternalRelationName, f.InternalFieldName from PrintOutLayout l, DatabaseField f where f.InternalRelationName = l.InternalRelationName and l.LayoutName like '% %' and len(l.InternalRelationName) > 0 order by l.InternalWindowName, l.LayoutName, f.InternalFieldName

SQL – Job Parameters for a Job

Modify the Job Number for your specific requirements

select s.jobnumber, a.jobparametername, a.attributename, a.enteredattributevalue, (select name from popupitem where PopupItemNumber = a.popupvalue and PopupTypeName = a.popuptypename) as "popup name", (select name from PopupItem where PopupItemNumber = a.format and PopupTypeName = 'FormatType') as format, a.stringvalue, a.integervalue, a.realvalue, a.amountvalue, a.booleanvalue, a.datevalue, a.timevalue, a.popupvalue, a.popuptypename from JobParameterAttribute a, JobParameterSelection s where s.jobnumber = '10001' and s.EffectiveParameterName = a.JobParameterName and s.JobParameterType = a.JobParameterType order by a.JobParameterName

For a range of Jobs, use:

select s.jobnumber, a.jobparametername, a.attributename, a.enteredattributevalue, (select name from popupitem where PopupItemNumber = a.popupvalue and PopupTypeName = a.popuptypename) as "popup name", (select name from PopupItem where PopupItemNumber = a.format and PopupTypeName = 'FormatType') as format, a.stringvalue, a.integervalue, a.realvalue, a.amountvalue, a.booleanvalue, a.datevalue, a.timevalue, a.popupvalue, a.popuptypename from JobParameterAttribute a, JobParameterSelection s where s.jobnumber in ('10001','10008','10035') and s.EffectiveParameterName = a.JobParameterName and s.JobParameterType = a.JobParameterType order by a.JobParameterName

SQL – List of employees/users with specific access level

This example is for a standard Access Level setup for a Company 10 and relies on an Employee Number convention of prefixing with the Company Number. Modify to suit your own setup.

select e.name1, e.employeenumber, e.LocalSpec1Name, u.NameOfUser, u.UserAccessLevelName from employee e, UserAccessLevel u, UserInformation i where u.NameOfUser = i.NameOfUser and e.employeenumber = i.employeenumber and u.UserAccessLevelName = '10' and e.employeenumber like '10%' order by e.name1

Experienced Maconomy Implementation Consultant

Maconomy implementation consultant, Henry Dale, based in London, UK.

  • 13+ years’ experience of numerous full lifecycle Maconomy implementations, including four different shared-service projects, and dozens of individual company migrations;
  • Experience of upgrading Deltek Maconomy X1 to 2.1;
  • Maconomy 2.1 database and Workspace Client configuration and customisation;
  • A broad technical knowledge of Maconomy and its effective business configurations;
  • Know-how of project setup, resourcing, organising, and management;
  • A whole raft of best-practice techniques for data gathering, data cleansing, data conversion, system building, system configuration, system testing and configuration adjustments;
  • An instinct for sniffing out potential problems and how to head them off at the pass;
  • And a track record of hitting go-live deadlines and stress-free brought forward balance transfers.

Contact me via

E: info@henrydale.com
M: +44 (0)7711 292694
Skype: henry-dale
LinkedIn: uk.linkedin.com/in/henrydale

Looking up errors in Maconomy import files

If, when importing a data file to Maconomy, your import log file reports one error, it is a simple matter to look up the offending line in your import source file.

However, when you have a lot of errors from a large import file, it is time-consuming and inefficient to have to switch repeatedly from log file to source file to look up each line in turn.

In this situation it is useful to see the errors reported directly within the import source file, on the lines to which the errors relate.

This can be done simply and quickly using two Excel formulae.

Open the error file in Excel, or open the error file in a text editor and copy and paste the contents into a temporary worksheet.

Insert a blank column A, so the errors are listed in column B.

In the first row containing an error message, put the following formula in column A:

=VALUE(MID(Bx,15,5))
where x is the row number

Paste the formula down column A to the last row containing an error message.

You can safely ignore any #VALUE returns there are from warnings mixed in with the errors.

In your Excel import source file, insert a blank column B.

In the first data row of column B enter the formula =VLOOKUP(ROW(),sheet!$A$x:$B$y,2,0)
where sheet! Is the location of the error log file, and x & y are the first and last error lines in the log file
Paste this formula to the bottom row of import data in the file.
Using an autofilter on column B, filter out the #N/A values and blanks. Whatever remains is a line generating an error in your import.