Dexterity system commands

GP and other dex based products are still full of calls to system commands, even though every single one of these was declared obsolete with the release of Dexterity 3.0.  For a frame of reference, we are on Dexterity 18 now.

If you don't know what a system function is, it looks like this:

system 5082, l_platform;

It has become nearly impossible to find info on these old functions, making it difficult to reverse engineer, or even just read old Dex code.

Below I have listed what I have been able to gather.  I plan to add and update this going forward.

0351 - currency format - replaced by Currency_SetDecimalSymbol(), Currency_SetSymbol(), Currency_SetThousandsSymbol()
0352 - currency format - replaced by Currency_SetLeadingSymbol(), Currency_SetNegativeParens(), Currency_SetNumberOfDecimals()
0353 - currency format - replaced by Currency_SetLeadingZero(), Currency_SetNegativeAfterValue()
0354 - currency format - replaced by Currency_SetNegativeBeforeSymbol(), Currency_SetNegativeSymbol()
0355 - currency format - replaced by replaced by Currency_GetNativeSymbol()
0356 - currency format - replaced by Currency_GetDecimalSymbol(), Currency_GetSymbol(), Currency_GetThousandsSymbol()
0357 - currency format - Currency_GetLeadingSymbol(), Currency_GetNegativeParens(), Currency_GetNumberOfDecimals()
0358 - currency format - Currency_GetLeadingZero(), Currency_GetNegativeAfterValue()
0359 - currency format - Currency_GetNegativeBeforeSymbol(), Currency_GetNegativeSymbol()
0772 - checks mode used to exit runtime engine - replaced by Activity_GetExitMode()
0774 - checks mode used to exit runtime engine - replaced by Activity_GetExitMode()
1300 - fills list box with data source names - replaced by Login_GetDataSources()
1301 - logs user into data source - replaced by Login_LogIntoDataSource()
1302 - gets login info - replaced with Login_GetInfo()
1303 - opens login window - replaced with Login_OpenDexDialog()
1304 - disconnect from data source - replaced by Login_ExitDataSource()
1306 - sets whether delete table statement drops table or deletes data - replaced by Table_SetDeleteOptions()
1308 - validates login - replaced with Login_VerifyInfo()
1317 - delete all records - replaced by Table_Truncate()
1318 - validate table structure - replaced by Table_Compare()
1319 - forces client buffer refresh - replaced by Table_SetRefreshFlags()
1960 - allows user to select dictionary location - replaced by Launch_SelectDict()
1961 - allows user to select a launch file - replaced by Launch_SelectFile()
1962 - gets generic path of current launch file - replaced by Launch_GetFileName()
1964 - fills list fields with info from launch file - replaced by Launch_GetFileInfo()
1965 - gets path to a dictionary from the launch file - replaced by Launch_ReadDictPath()
1966 - writes path to a dictionary to the launch file - replaced by Launch_WriteDictPath()
1967 - gets file name from generic path - replaced by Launch_ParseFileFromPath(), Path_ParseFileFromPath()
2500 - sets style and color for required fields - replaced by Field_SetRequiredFormat()
2501 - sets style and color for required fields - replaced by Field_SetRequiredFormat()
2502 - sets style and color for required fields - replaced by Field_SetRequiredFormat()
2503 - gets runtime version - replacd by Runtime_GetVersionNum()
2504 - forces window redraw - replaced by Window_ForceRedraw()
2508 - writes a setting to dex.ini - replaced by Defaults_Write()
2509 - reads a setting from dex.ini - replaced by Defaults_Read()
2510 - changes default path for tables - replaced by Path_ChangeDefault()
2511 - ignore error checking on tables - replaced by Table_DisableErrorChecks()
2512 - changes enter key and tab key functonality - replaced by Runtime_SetFieldEnterMode()
2516 - open a text file - replaced by TextFile_Open()
2517 - close a text file - replaced by TextFile_Close()
2518 - reads a line from a text file - replaced by TextFile_ReadLine()
2519 - writes a line to a text file - replaced by TextFile_WriteLine()
2520 - change title of about box - replaced by Runtime_SetAboutMenu()
2521 - get current dicntionary file name - replaced by Dict_GetName()
2522 - insert string into text field - replaced by Field_InsertStringInText()
2523 - parses text field into string- replaced by Field_ParseText()
2524 - writes a line to a text file in DOS format - replaced by TextFile_WriteDOS()
2526 - get number of files open - replaced by File_Count()
2528 - delete a file from the OS - replaced by File_Delete()
2540 - gets required property of a field - replaced by Field_GetRequiredStatus()
2540 - sets required property of a field - replaced by Field_SetRequiredStatus()
2996 - DDE function - replaced by DDE_EndConversation() which is no longer supported either
2997 - DDE function - replaced by DDE_WriteToServer() which is no longer supported either
2998 - DDE function - replaced by DDE_ReadFromServer() which is no longer supported either
2999 - DDE function - replaced by DDE_ExecuteServerCommand() which is no longer supported either
3522 - gets IP address of client workstation - replaced by IP_GetIPNumber()
3523 - ping process server - replaced by IP_PingDPS()
5001 - help function - replaced by DexHelp_GetLinkedPrompt() which is no longer supported either
5004 - help function - replaced by DexHelp_FillFormList() which is no longer supported either
5005 - help function - replaced by DexHelp_FillWindowList() which is no longer supported either
5006 - help function - replaced by DexHelp_FillFieldList() which is no longer supported either
5007 - help function - replaced by DexHelp_GetFieldID() which is no longer supported either
5008 - help function - replaced by DexHelp_GetArrayIndex() which is no longer supported either
5009 - gets dex resource id - replaced by Resource_GetID()
5010 - converts generic to native path - replaced by Path_MakeNative()
5011 - converts native to generic path - replaced by Path_MakeGeneric()
5012 - gets count of resources - replaced by Resource_StartSeriesFill()
5013 - gets resource id and name - replaced by Resource_GetInfo()
5014 - fills list fields with display name and technical name of resources - replaced by Resource_FillSeriesList()
5015 - fills list field with table named - replaced by Table_FillList()
5016 - fills list field with table group names - replaced by Table_FillGroupList()
5017 - gets title of a window - replaced by Window_GetTitle()
5018 - validates data in a record in a table - replaced by Table_VerifyRecord()
5019 - create a temp table to hold data from shrunk table - replaced by Table_StartShrink()
5020 - copy record into temp table for shrinking - replaced by Table_CopyShrinkRecords()
5021 - finish table shrink - replaced by Table_EndShrink()
5022 - releases series fill resources - replaced by Resource_EndSeriesFill()
5023 - marks or unmarks items in list - replaced by Field_MarkListItems()
5024 - allow user to select path to a file - replaced by Path_SelectPathname()
5026 - release lock on forms or reports dictionary - replaced by Dict_UnlockCustom()
5027 - get count of forms or reports in dictionary - replaced by Dict_CountCustomResource()
5028 - get info on a form or report - replaced by Dict_GetCustomResourceInfo()
5029 - create new folder - replace by Path_CreateFolder()
5030 - get title of forms main window - replaced by Window_GetMainWindowTitle()
5031 - get path to runtime engine - replaced by Dict_GetPathname(), Path_GetForApp()
5032 - get display name for table - replaced by Table_GetDisplayName()
5033 - lock forms or reports dictionary - replaced by Dict_LockCustom()
5053 - enable modifier menu items - replaced by Tools_EnableModifier()
5054 - enable report writer menu items - replaced by Tools_EnableReportWriter()
5055 - get runtime version info - replaced by Runtime_GetModuleInfo()
5056 - get runtime version info - replaced by Runtime_GetModuleInfo()
5058 - get runtime version info - replaced by Runtime_GetModuleInfo()
5061 - check for background processing - replaced by Activity_GetBackgroundStatus()
5064 - appends string to text field - replaced by Field_AppendStringToText() which is no longer supported either
5082 - gets the type of OS - replaced by Runtime_GetOSType() which is no longer supported either
5090 - gets resource display name - replaced by Activity_GetResourceName(),Resource_GetDisplayName()
5091 - starts converting data from old table def to new table def - replaced by Table_StartConversion()
5092 - finish table conversion - replaced by Table_EndConversion()
5095 - sets default database type - replaced by Table_SetDBType()
5096 - gets title of window - replaced by Window_GetTitleByProduct()
5100 - fills list field with product names from launch file - replaced by Launch_FillListWithProds()
5101 - gets a product id from the launch file - replaced by Launch_GetProdID()
5103 - gets number of products in launch file - replace by Launch_CountProds()
5104 - gets product name from launch file - replaced by Launch_GetProdName()
5105 - gets position of product in launch file - replaced by Launch_GetProdPosition()
5125 - old ctree and pervasive SQL function to check whether table can shrink - replaced by Table_GetAutoShrinkMode()
5126 - old ctree and pervasive SQL function to shrink table - replaced by Table_AutoShrink()
5127 - old ctree and pervasive SQL function to rebuild indexes - replaced by Table_RebuildIndexes()
5147 - returns temp table physical name
5196 - specify whether tables will be auto created when accessed replaced by Table_SetCreateMode()
8000 - checks if a file exists and can be opened - replaced by File_Probe()

Taxes on Service Invoices in Signature 2016

A question came up recently on how to relate taxes in tax history (TX30000) to the service performed (SV000815)

To understand how the tables fit together, we must first understand how the records get into those tables and what they represent. (the below is not an exhaustive list of tables, just the ones we are interested in for this question)

SV00500 holds the contract information
SV00400 holds the equipment information
SV00403 holds the information on equipment assigned to contracts
SV00560 holds the main task information (such as cost and billable amounts)
SV00582 holds the information on tasks assigned to a contract and also holds information on cost and pricing

When a service call is created, there are typically 3 tables that hold the data.

SV00300 holds the main information about the call.
SV00301 holds the information on the appointments assigned to the call.
SV00302 holds the information on the tasks assigned to the call.

When a service invoice is created, information from the 8 tables above is pulled together to create records in the following:
SV00700 is the service invoice header.  The table holds up to 4 tax amounts for the invoice.
SV000810 is the service call cost information.  The table also holds the task code and billable amount is applicable.
RM10301 is the Receivables Header.
RM10101 is the Receivables distributions.
RM10601 is the Receivables taxes.

When the RM tables above are populated, the TX00102 and TX00201 tables are read based on the billable customer's tax schedule and the RM10601 is populated.  Whatever tax is calculated there is then added the the RM10101 distributions and then summed up and added to SV00700.

When the service invoice is posted, the following records are moved:
SV000810 to SV000815
RM10301 to RM20101
SV00700 to SV00701
RM10601 is copied to TX30000, but RM10601 records remain

When the receivable is moved to history, the following records are moved:
RM10101 to RM30301
RM10601 to RM30601
RM20101 to RM30101
TX30000 is removed.


So depending on what point in the workflow the transaction is in, there might not be a record in TX30000 or in SV000815.

If the TX30000 record is related to a service invoice, the SV00701 Tax_Amount1 thru Tax_Amount4 fields should hold the values from the first 4 lines if TX30000.TAXAMNT for the invoice.

The tables can be joined in a one to many relationship as follows:
SV00701 >> TX30000
on RMDNUMWK=DOCNUMBR
and RMDTYPAL=DOCTYPE
and 3=SERIES

SV00701 >> SV000815
on Call_Invoice_Number=Call_Invoice_Number
and CUSTNMBR=CUSTNMBR
and ADRSCODE=ADRSCODE
(Note that I did not include Service_Call_ID.  This is because on grouped invoices, the SV00701 Service_Call_ID is be 'GROUPED' while the SV000815 will show the actual call ids)


So if you need to get from TX30000 to SV000815 you have to go through SV00701
TX30000 << SV00701 >> SV000815

T-SQL performance killers

These are some performance killers (in no particular order) that are easily avoided.  None-the-less, I see them all the time in production code.

1. Forgetting the schema name for the table.
select col1, col2, col3 from table1

While this looks correct at first glance, it is a sneaky performance killer.  You will find code written like this all over the internet.  The reason is that people posting on the internet usually don't know what your schema name will be and maybe not even which type of database you are using.

SQL Server uses a query optimizer to build a plan for how it will execute a query.  This plan generation takes time.  Even a simple join or pivot can take a few seconds to generate a plan.  Once the plan is generated it is stored in a cache and SQL can re-use it for subsequent calls to that same query.  The catch is that in order to reuse the plan, SQL needs to ensure that the query is actually the same query the next time.  One of the ways it does this is by storing the schema that the plan was generated against.  If the schema is not specified for *all* the tables in a query, the plan is still generated but is not stored in a way that it can ever be re-used.

For example, consider the following query.  Let's say this query gets run 100 times a day by various users.
select a.col1 from table1 a inner join table2 b on a.x=b.x

Lets say that SQL takes 2 seconds (an eternity, but a real world number) to generate and store the plan for this query, and another .5 seconds to bring back the results.  The first time a user runs the query it will take 2.5 seconds for the results to return.  The next time a user runs that same query it will still take 2.5 seconds. Over the course of a day, that is 250 seconds (2.5*100)!!!

Here is the same query with the schema names.
select a.col1 from dbo.table1 a inner join dbo.table2 b on a.x=b.x

It will still take 2.5 seconds the first time a user runs it.  But each subsequent run will re-use the plan and run in .5 seconds.  Over the course of a day that is (99*.5)+2.5=52 seconds.  Just over 1/5 the time!  And that is just one query!  This can add up to a huge performance difference quickly!

2.  Select *
This is a lazy way of typing the query.  It saves the developer time and that is where the advantage ends.  Unless you really need to use *all* of the columns in a table, the query will always perform better if the columns are explicitly listed.  How much better will depend on the size and number of columns in the table.
Where I see this crop up most often is in a statement such as this:

if exists(select * from dbo.table)

This forces SQL to expand the * to all column names.  This admittedly only takes a fraction of a second, but when we are talking about a production accounting system, those fractions add up quickly.

This brings us to the next item.

3. Improper use of if exists statements
The best practice is:

if exists(select 1 from dbo.table where [condition])

This selects the first column regardless of name.  There will always be at least one column in a table.  A developer reading this instantly understands that we don't care at that point *what* is in the table, only *whether* there is a record.

I also often see non-inner joins in an exists statement:

if exists(select 1 from dbo.table1 left outer join dbo.table2 on table1.x=table2.x)

This is forcing SQL to build and plan for and return results of an outer join before evaluating exists.  It makes no sense to add this overhead.
Here is the equivalent, much more efficient statement:

if exists(select 1 from dbo.table1)

Another example of inefficient code is:

if(select count(1) from dbo.table1)!=0

Logically, this is the same as the if exists statement, but it does not perform as well because SQL must count all of the rows in the table before evaluating the condition.  If the table is large, this can add more than a second to the processing time each time it runs.

4. Unnecessarily using WITH RECOMPILE statement in a procedure.
This should generally only be used in a proc that builds dynamic SQL and executes it.  In this case, you don't want SQL to think that the execute plan it built last time for the proc itself will work this time because the parameters are different and thus the statement that is built is different.  Even in this case, you should generally try your best to write the proc in a way that does not require dynamic SQL and remove the WITH RECOMPILE statement.

I have never seen any other real-world case in which this clause did not hurt performance.  Indeed, it hurts performance in ALL cases.  It is just necessary in those very specific cases.

5. Unnecessarily using dynamic SQL
Dynamic SQL should be (and in almost all cases CAN be) avoided in procedures.  Most times that I see it used, it is just a lazy way to do something that should have been done in the application layer anyway.  In many of those cases there are ways to avoid it just by refactoring the SQL proc.  Here are some real word examples of its use:

SELECT @exestring = @curcompany + '.dbo.smCleanupPostingJournalEntries'
EXEC @exestring

This statement will perform better if the calling application gets the value for @curcompany and calls the correct proc in the first place.  Doing it this way also increases the chance of errors that do not bubble back up to the application properly.  This is what happens if the proc in the dynamic SQL fails or doesn't exist.

CREATE PROC dbo.SomeProc (@TempTableName VARCHAR(255))
AS
BEGIN
DECLARE @sqlString VARCHAR(2000)

SELECT @sqlString = 'UPDATE a set a.Myfield=b.Somefield FROM dbo.MyTable a inner join dbo.' + @TempTableName + ' b on a.Keyfield=b.Keyfield'

EXEC sp_sqlexec @sqlString
END

In this case the application is building a temp table then passing it in.  This make zero sense if the table is not a #temp table.  Even if it is #temp table, it will always perform better if you build the temp table inside the proc and then use it normally.  If you find a case where it is otherwise, I would be very interested to see it.

Update SSRS report path

Here is a handy little script to help update SSRS report paths

IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'cssp_Upsert_SSRS_Path '
)
DROP PROCEDURE dbo.cssp_Upsert_SSRS_Path
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.cssp_Upsert_SSRS_Path (
@ReportReference VARCHAR(128), --the existing report reference in WSRepts table, or the new one you want to add
@ReportName VARCHAR(255), --the name if the report without the extension (as it appears in the url)
@Product INT -- 1=Job Cost, 2=Service, 3=EMS, 4=shared
)
AS
/*
KEY2ACT, Inc.
History:
20180419 djedziniak initial version
*/
BEGIN
BEGIN TRY
SET NOCOUNT ON
DECLARE @DbName AS VARCHAR(128),
@ReportServerUrl AS VARCHAR(512),
@RptServerRef AS VARCHAR(255),
@lastSlashIndex AS INT,
@slashIndex AS INT,
@productfolder AS VARCHAR(255)
SELECT @DbName = DB_Name(),
@RptServerRef = coalesce((
SELECT Report_Server_URL
FROM DYNAMICS.dbo.SY40800
WHERE SITENAME = 'Company'
), '')

IF (LEN(RTRIM(@RptServerRef)) > 0)
BEGIN
/* Only when has a Report Server reference will it insert any records in regards to Reporting Services. */
SELECT @lastSlashIndex = 0,
@slashIndex = Charindex('/', @RptServerRef);

/* First, find the last forward slash */
WHILE (@slashIndex > 0)
BEGIN
IF (@slashIndex > @lastSlashIndex)
SELECT @lastSlashIndex = @slashIndex;

SELECT @slashIndex = Charindex('/', @RptServerRef, @slashIndex + 1);
END
IF (@lastSlashIndex > 0)
SELECT @ReportServerUrl = LEFT(@RptServerRef, @lastSlashIndex);
END

SELECT @productfolder = CASE @Product
WHEN 1
THEN '/Signature Job Cost/'
WHEN 2
THEN '/Signature Service/'
WHEN 3
THEN '/Signature Equipment/'
WHEN 4
THEN '/Signature Shared/'
ELSE ''
END

IF EXISTS (
SELECT NULL
FROM dbo.WSRepts
WHERE ReportReference = @ReportReference
)

UPDATE dbo.WSRepts
SET ReportLocation = coalesce(@ReportServerUrl + @DbName + @productfolder + @ReportName, '')
WHERE ReportReference = @ReportReference AND LEN(RTRIM(ReportLocation)) <= 0;
ELSE
INSERT INTO dbo.WSRepts (
ReportReference,
ReportLocation
)
SELECT @ReportReference,
coalesce(@ReportServerUrl + @DbName + @productfolder + @ReportName, '')
END TRY

BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SELECT @ErrorMessage = 'ERROR IN cssp_Upsert_SSRS_Path ' + @ErrorMessage
RAISERROR (
@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END
GO

GRANT EXECUTE
ON dbo.cssp_Upsert_SSRS_Path
TO DYNGRP
GO

PRINT 'CREATED cssp_Upsert_SSRS_Path '
GO

Changing outlook time increments

Have you noticed that when you create an appointment in outlook, it only allows you to choose a time on the half hour increments initially.  So If I need to schedule something from 10:15 to 10:30, I must do the following:

1. Click on 10:00 to get the appointment
2. Manually change the start time to 10:15 (which causes outlook to change the end time to 10:45)
3. Manually change the end time back to 10:30

After all that, the visual for the appointment will overlap the 10:30 slot, so it appears at first glance that there is a conflict.

This has annoyed me for many years, so today I decided t find a solution.


The solution is buried behind several layers of option window in a place you would not expect.

1. Right Click on an empty time slot on your calendar and choose View Settings
2. Click Other Settings
3. Change the Time Scale drop down to 15 minutes
4. Make sure the Font is set to 8pt
5. Click OK to close the windows

Now when you schedule an appointment, outlook will assume 15 minute increments.

This also changes the display of the calendar a bit.  Since the calendar window was optimized by someone short-sighted enough to think everyone would be happy with 30 minute increments, your entire work day may not be visible without scrolling.

So it is a question of what is more annoying.  Personally, I am going with the 15 minute increments and will live with a little scrolling.