Removing a SyteLine or ICS Form via SQL

As an application engineer I find that there are certain circumstances where having a GUI screen to perform a task is not always possible/practical.  There are many times that the client has given me access to the database server but not the application server and there are simply times I am going through upgrade steps and simply don’t have a working environment yet to perform tasks via the front end.   This week I needed to perform a SyteLine 7 to SyteLine 9 upgrade that put me in such a situation and I had to get creative to find a workaround.  For this customer one of the upgrade steps was to purge out all user scoped versions of forms from SyteLine 7 (not very many folks will spend time going through the process of FormSync and nor would I) however the newest version of FormSync was displaying nondescript errors on the delete operation.  T-SQL to the rescue!

On a side note, I want to point out that I absolutely love what Infor has done with new SyteLine Infor CloudSuite and believe whole heartedly that no other company gives as much built in functionality for the cost per license.  It is a very stable and mature product and it’s few and far between that I am forced to work around an obstacle from the back end.  Secondly, I enjoy writing neat scripts to streamline my interaction with the ERP system and will almost always jump at the opportunity even when my plate is full.

For this task I wanted to whip up something that I could use in the future as part of the SQL cleanup process of removing user scoped forms automatically without having to ever open FormSync.   My advice to everyone is to utilize the great tools that Infor provides to maintain your database health however I do realize that there are cases when fixing from the back end is a better route to go (assuming you know what you are doing and the logic is sound).  As always I provide this “as is” and is meant for educating and not to replace functionality which is already available via the product.

Note: I wrapped the code in a BEGIN / ROLLBACK TRAN so that results can be viewed without actually causing true deletions of forms.  When I ran it, I used a COMMIT TRAN instead so that it would really delete the unneeded form.  Download Code Here

BEGIN TRAN

SET NOCOUNT ON
DECLARE 
 @FormID INT
,@FormName nvarchar(50)
,@Scope INT

set @Scope = 3 --scope 1 for SITE, scope 2 for GROUP, scope 3 for USER
if @Scope < 1 or @Scope > 3
  RAISERROR('SCOPE MUST BE 1, 2 or 3',20,1) with LOG

DECLARE FormDelCur cursor local static for
SELECT Forms.ID, Forms.Name FROM Forms WHERE 
  [ScopeType] = @Scope
  --AND [Name] = N'JobOrders' 

open FormDelCur
fetch next from FormDelCur into @FormID, @FormName

while @@FETCH_STATUS = 0
BEGIN TRY
   DELETE FROM Forms WHERE ID = @FormID
   DELETE FROM FormEventHandlers WHERE FormID = @FormID
   DELETE FROM FormComponents WHERE FormID = @FormID
   DELETE FROM ActiveXComponentProperties WHERE FormID = @FormID
   DELETE FROM Variables WHERE FormID = @FormID
   DELETE FROM FormComponentDragDropEvents WHERE FormID = @FormID
   DELETE FROM DerivedFormOverrides WHERE FormID = @FormID
   DELETE FROM ActiveXScripts WHERE [Name] = @FormName AND [ScopeType] = @Scope
   DELETE FROM ActiveXScriptLines WHERE [ScriptName] = @FormName AND [ScopeType] = @Scope
   print 'Successfully deleted form ' + @FormName + ' with ' + CASE when @Scope = 1 then 'SITE SCOPE'
                                                                    when @Scope = 2 then 'GROUP SCOPE'
								    when @Scope = 3 then 'USER SCOPE'
							       END
   fetch next from FormDelCur into @FormID, @FormName
END TRY
BEGIN CATCH
   print 'Unable to delete form ' + @FormName + 'with ' + CASE when @Scope = 1 then 'SITE SCOPE'
                                                               when @Scope = 2 then 'GROUP SCOPE'
							       when @Scope = 3 then 'USER SCOPE'
							  END
   fetch next from FormDelCur into @FormID, @FormName
END CATCH
close FormDelCur
Deallocate FormDelCur
ROLLBACK TRAN
--COMMIT TRAN