SytelineMaster

Even thought the UET(User Extended Table) is a more prefer way to add fields into Syteline Form.  But sometime, user feel that the UDF(User Defined Field) is more straight forward for adding field to Form, but then later, when they like to show those UDFs into report, it becomes no that straight forward anymore.

Adding UDFs into Crystal Report is actually more about understanding the UDF database structure, than using Crystal function. 

Syteline stores all UDFs in table UserDefinedFields.  And the primary keys for this table is TableName, which refers to the table that UDF belong to, and RowID, which link to the Rowpointer of record in the table. 

So in Crystal Report, you will need to add the UserDefinedFields table to the report, and link it to the report main table or SP.  You can easily link the RowID = Rowpointer, but you have to use filter function to specify table name to TableName manually. 

For those who come from old school of Progress Syteline (Symix) like me, “For Each” command is so nature to us, in terms of loop through record for processing.  But SQL is pretty much bulk processing language.  There is simply no equivalent command as “For Each” in T-SQL. 

There are basically two way to loop through each record in a record set for processing in T-SQL.  The first one, also the most popular one being used through out Syteline SP, is to use the CURSOR. 

Use CURSOR

Here is a example:

DECLARE CUR_ITEMWHSE CURSOR LOCAL STATIC FOR
    SELECT
        item,
        whse,
        qty_on_hand,
        qty_alloc_co
    From itemwhse
    where itemwhse.qty_on_hand > 0 and qty_alloc_co > 0

OPEN CUR_ITEMWHSE
    WHILE @Severity = 0
    BEGIN
        FETCH CUR_ITEMWHSE INTO
            @item,
            @whse,
            @qty_on_hand,
            @qty_alloc_co

        IF @@FETCH_STATUS = -1
            BREAK

        …  /* Your process logic here */

     END

CLOSE CURSOR CUR_ITEMWHSE

Other way is to use the temp table.

Use Temp Table

You will first need to define a temp table with a field “Processed”

declare @Consumers table (
    [OrderID] [int] NOT NULL,
    [OrderDate] [smalldatetime] NULL,
    …   

    [Processed] [smallint] null
primary key (OrderID)
)

You then loop through the record set by select the first 1, which hasn’t been processed.

/* Loop through Consumers  to process     */       
While Exists(Select 1 from @Consumers where processed = 0)
begin
select top 1
    @OrderID = [OrderID],
    @OrderDate = [OrderDate],
     …
      from @Consumers where processed = 0

    … /* Your process logic here  */

Update @Consumers

set processed = 1 where OrderID = @OrderID

end

Be noted that these two method can not be mixed.  You can’t declare cursor for a temp table.

SQL server provides way to connect to remote database (SQL, Oracle and many others).  Within your store procedure, you can dynamically connect to the remote DB, fetch data, process them into Syteline DB. 

Business Case:

Let’s say you have a web store database, in a remote database server.  You want to replicate the orders into Syteline

Syteline Technical Component:

SQL Store Procedure

Solution:

In your store procedure, you will need to first connect to the remote DB server, and login to the database.  Here is a example with server name DBSRVR.

if not exists(select 1 from sys.servers where name = ‘DBSRVR’)
    EXEC sp_addlinkedserver
        ‘DBSRVR’,
        N’SQL Server’

exec sp_addlinkedsrvlogin ‘DBSRVR’
                         ,’false’
                         ,null
                         ,’sa’
                         ,’sapassword’

After getting the connection, you can use the select statement to fetch data into a temp table for further process.

Insert @Orders (
            [OrderID]
           ,[OrderDate]
           ……
           )
    Select
            [OrderID]
           ,[OrderDate]

           ……

        from [DBSRVR].[WEBORDER].dbo.orders

Once you done with all the process, you may want to drop off the connection

— Drop off from DBSRVR 
if exists(select 1 from sys.servers where name = ‘DBSRVR’)
Exec sp_dropserver ‘DBSRVR’, ‘droplogins’

To check what Database Servers are currently link to your DB Server, just query the sys.servers table. 

Select * from sys.servers

Sometime, while you are making modification on a Syteline form in design mode, system would crush on you.  Even after you log out and log back in, and try get into the design mode again, the error message would keep coming up, preventing you using any function in design mode. 

This usually only happen to your PC.  To fix it, do the following:

1) log off Syteline.

2) Go to C:\Documents and Settings\YOUR WINDOW USER NAME\Local Settings\Application Data\Infor\WinStudio.  You should see two xml files there.  Delete them.

3) Log back in Syteline, go to design mode.  You should now see anything working OK again.

In Syteline 8, some time in some forms, the horizontal scrollbar in Grid mod would some how missing.  User can not see all records in the data set.

This often due to that when developer making modification to the form, some how make the height of Grid mod screen disagree with the height of the main screen.  To correct that, enter into design mod, in the form property, copy down whatever value in the Height property.  Then click on the grid to go to FormCollectionGrid, locate the height property, paste the value in.  This way, you make sure the height for both Grid and main screen are the same.  This should fix the problem

Here is a basic way to call a SQL Store Procedure (SP) within a script, either Form Script or Inline Script.

The sample we use below is calling a SP: GetCustType and return the CustType value to a form variable.

Dim nRetVal As InvokeResponseData
Dim nRequest As New InvokeRequestData()

nRequest.IDOName = “SP!”
nRequest.MethodName = “GetCustType”

nRequest.Parameters.Add(ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty(“CustNum”))
nRequest.Parameters.Add(“”)

nRetVal = IDOClient.Invoke(nRequest)

ThisForm.Variables(“vCustType”).Value = nRetVal.Parameters(1).ToString

And here is the SP:

PROCEDURE [dbo].[navGetCustType]
(@CustNum [CustNumType],
@CustType [CustTypeType] output)

as

select @CustType = cust_type
from customer
where cust_num = @CustNum and cust_seq = 0

Here is a multiple parameters  sample:

Sub nGetUnitDetails()
Dim nRetVal As InvokeResponseData
Dim nRequest As New InvokeRequestData()

nRequest.IDOName = “SP!”
nRequest.MethodName = “nGetUnitDetails”
nRequest.Parameters.Add(ThisForm.Components(“editUnit”).Text)
nRequest.Parameters.Add(CDate(ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty(“RmahdrRmaDate”)))
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)

nRetVal = IDOClient.Invoke(nRequest)

ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“Warranty”, nRetVal.Parameters(2).ToString)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“Item”, nRetVal.Parameters(3).ToString)
ThisForm.GenerateEvent(“ItemDataChange”)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“CoNum”, nRetVal.Parameters(4).ToString)
ThisForm.GenerateEvent(“OrderNumLoseFocus”)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“CoLine”, nRetVal.Parameters(5).ToString)
ThisForm.GenerateEvent(“DisplayRMAWarningMsgSp”)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“CoRelease”, nRetVal.Parameters(6).ToString)
ThisForm.GenerateEvent(“CoLineChanged”)
‘New for Parag
‘ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“UnitCreditConv”, nRetVal.Parameters(7).ToString)
‘ThisForm.GenerateEvent(“RecalcTotals”)

If Not nRetVal.Parameters(3).ToString = “” Then
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“QtyToReturnConv”, “1”)
End If

End Sub

Every combobox in Syteline form has a pull-down list.  For example the item pull-down list may show item# and description.  You may want to add more fields to this pull-down list to show more information for your user.

Business Case:

The current Syteline customer ship-to pull-down list only show the ship-to seq and the customer name, let say you also want to show the city and state for each ship-to, so user can more easily identify the correct ship to customer.

Syteline Technical Component:

Component class, List Source

Solution:

The pull-down list is defined in List Source property.  In this case, the ship-to component is inheriting the property from component class “CustSeq”.  You will need to modify this component class.  Open this component class “CustSeq”

Then open the list source property:

You can see this list source is pulling from IDO SLCustomers, and the property (fields) it pulls are CustSeq, Name, CreditHold and such.  Insert City and State in front of CreditHold.  Then in the Column to Display, let it to display column 1 to 4.  This way, it will display CustSeq, Name, City and State.

Now the Ship-to customer pull-down will show the City and State, along side the seq and Name

In Syteline form, quite often, you would need to assign some field value, base on another field value entered.

Business case:

Let say in Customer Order, for certain payment terms, you would not allow partial shipment.  So in the CO header form, when user select certain payment term, you want the system automatically uncheck the “Ship Partial” check box.

Syteline Technical Components:

Inline Script, Event Handler

Solution

In Syteline 7 & 8, there is quite a few differ ways to accomplish this.  The first one we are going to discuss here is to use Inline Script.

1)      In Customer Order Form, for form component: TermCodeEdit, add a data change event: TermChange.  And the event handler will call an Inline Script

2)      The Inline Script:

Option Explicit On

Option Strict On

Imports System

Imports Microsoft.VisualBasic

Imports Mongoose.IDO.Protocol

Imports Mongoose.Scripting

Namespace SyteLine.GlobalScripts

Public Class EvHandler_TermChange_0

Inherits GlobalScript

Sub Main()

if ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty(“TermsCode”) = “128” then

ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh _

(“ShipPartial”, “0”)

end if

ReturnValue = “0”

End Sub

End Class

End Namespace

This script will assign the ShipPartial to 0, when user select TermsCode = 128.

This is considered by some people as Holy Grail of Syteline Form Personalization.

Here is what we want to do.  In “Time Phased Inventory” form, we want to change the color to yellow for those rows that is PO.

First, we need to create a new form script method, called SetPOColor()

Sub SetPOColor()
Dim i as Integer
Dim j as Integer
Dim sReference As String
Dim sRef3 As String
Dim sRef4 As String
Dim oSupDem As IWSIDOCollection

oSupDem = ThisForm.PrimaryIDOCollection.GetSubCollection(“SLSupDems”, -1)
j = oSupDem.GetNumEntries

‘    MsgBox(“j = ” + CStr(j))

For i = 1 To j

sReference = ThisForm.Components(“SLSupDemsGrid”).GetGridValueByColumnName(i, “SLSupDemsReferenceSubGridCol”)

sRef3 = Mid(sReference, 1, 3)
sRef4 = Mid(sReference, 1, 4)
If sRef3 = “PO ” Or sRef4 = “XPO ” Then
ThisForm.Components(“SLSupDemsGrid”).SetGridRowColColor(i, 0, “255,255,0”, “”)
End If
Next i
ThisForm.Components(“SLSupDemsGrid”).ForceRepaint
End Sub

Secondly, we create a new event handler for event “StdObjectSelectCurrentCompleted”, to call the above form script method SetPOColor().

Done.  Now here we are:

When deploying an UET customization in Syteline 7 & 8, there is SP can be used to script out the UET. Just do

exec ExportUETClassSP ‘classname’

Some server don’t this SP installed.  I just keep a copy here for in case

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[ExportUETClassSP] (

@PClassName Infobar

)

AS

DECLARE

@ClassName ClassNameType

, @ClassLabel LabelType

, @ClassDesc DescriptionType

, @SysHasFields ListYesNoType

, @SysHasTables ListYesNoType

, @SysApply UetSysApplyType

, @SysDelete ListYesNoType

, @FldName FldNameType

, @FldDataType UetDataTypeType

, @FldInitial UetDefaultType

, @FldDecimals UetScaleType

, @FldDesc ToolTipType

, @FldUDT sysname

, @FldPrec tinyint

, @IndexName IndexNameType

, @IndexDesc DescriptionType

, @IndexUnique ListYesNoType

, @IndexWord ListYesNoType

, @IndexSeq UetIndexSeqType

, @IndexAsc ListYesNoType

, @TableName TableNameType

, @TableRule QueryExpressionType

, @ExtendAllRecs ListYesNoType

, @AllowRecordAssoc ListYesNoType

, @Active ListYesNoType

, @SQLCmd InfobarType

, @SQLCmdWait InfobarType

, @Severity INT

, @Quote NCHAR(1)

, @RecordDate CurrentDateType

, @RowPointer RowPointerType

, @CommittedRowPointer RowPointerType

, @CreatedBy UsernameType

, @UpdatedBy UsernameType

, @CreateDate CurrentDateType

, @InWorkflow FlagNyType

SET @Severity = 0

SET @Quote = ””

DECLARE UserClassCrs CURSOR LOCAL STATIC

FOR SELECT

uc.class_name

, uc.class_label

, uc.class_desc

, uc.sys_has_fields

, uc.sys_has_tables

, uc.sys_apply

, uc.sys_delete

FROM user_class uc

WHERE class_name = @PClassName

OPEN UserClassCrs

WHILE @Severity = 0

BEGIN — cursor loop

FETCH UserClassCrs INTO

@ClassName

, @ClassLabel

, @ClassDesc

, @SysHasFields

, @SysHasTables

, @SysApply

, @SysDelete

IF @@FETCH_STATUS = -1

BREAK

SET @SQLCmd = ‘INSERT INTO user_class ( class_name, class_label,

class_desc, sys_has_fields, ‘

SET @SQLCmd = @SQLCmd + ‘sys_has_tables, sys_apply, sys_delete

) VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@ClassName) + ‘, ‘ +

dbo.Quote(@ClassLabel)

SET @SQLCmd = @SQLCmd + ‘, ‘ + dbo.Quote(@ClassDesc) + ‘, ‘ +

STR(ISNULL(@SysHasFields, 0))

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@SysHasTables, 0)) +

‘, ‘ + dbo.Quote(@SysApply)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@SysDelete, 0)) + ‘ )’

print @SQLCmd

DECLARE UserClassFldCrs CURSOR LOCAL STATIC

FOR SELECT

ucf.class_name

, ucf.fld_name

, ucf.sys_apply

, ucf.sys_delete

FROM user_class_fld ucf

WHERE class_name = @PClassName

OPEN UserClassFldCrs

WHILE @Severity = 0

BEGIN — cursor loop

FETCH UserClassFldCrs INTO

@ClassName

, @FldName

, @SysApply

, @SysDelete

IF @@FETCH_STATUS = -1

BREAK

SET @SQLCmd = ‘INSERT INTO user_class_fld ( class_name,

fld_name, sys_apply, sys_delete ) VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@ClassName) + ‘, ‘ +

dbo.Quote(@FldName) + ‘, ‘ + dbo.Quote(@SysApply) + ‘, ‘ +

STR(ISNULL(@SysDelete, 0)) + ‘ )’

SET @SQLCmdWait = @SQLCmd

SELECT @FldName = fld_name

, @FldDataType = fld_data_type

, @FldInitial = fld_initial

, @FldDecimals = fld_decimals

, @FldDesc = fld_desc

, @SysApply = sys_apply

, @SysDelete = sys_delete

, @FldUDT = fld_UDT

, @FldPrec = fld_prec

FROM user_fld uf

WHERE uf.fld_name = @FldName

SET @SQLCmd = ‘INSERT INTO user_fld ( fld_name, fld_data_type,

fld_initial, fld_decimals, fld_desc, sys_apply, sys_delete,

fld_UDT, fld_prec ) VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@FldName) + ‘, ‘ +

dbo.Quote(@FldDataType) + ‘, ‘ + dbo.Quote(@FldInitial)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@FldDecimals, 0)) +

‘, ‘ + dbo.Quote(@FldDesc) + ‘, ‘ + dbo.Quote(@SysApply)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@SysDelete, 0)) + ‘,

‘ + dbo.Quote(@FldUDT) + ‘, ‘ + STR(ISNULL(@FldPrec, 0)) + ‘ )’

PRINT @SQLCmd

PRINT @SQLCmdWait

END — Cursor Loop UserClassFld

CLOSE UserClassFldCrs

DEALLOCATE UserClassFldCrs

DECLARE UserIndexCrs CURSOR LOCAL STATIC

FOR SELECT

ui.class_name

, ui.index_name

, ui.index_desc

, ui.index_unique

, ui.index_word

, ui.sys_apply

, ui.sys_delete

FROM user_index ui

WHERE class_name = @PClassName

OPEN UserIndexCrs

WHILE @Severity = 0

BEGIN — cursor loop

FETCH UserIndexCrs INTO

@ClassName

, @IndexName

, @IndexDesc

, @IndexUnique

, @IndexWord

, @SysApply

, @SysDelete

IF @@FETCH_STATUS = -1

BREAK

SET @SQLCmd = ‘INSERT INTO user_index ( class_name, index_name,

index_desc, index_unique, index_word, sys_apply, sys_delete )

VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@ClassName) + ‘, ‘ +

dbo.Quote(@IndexName) + ‘, ‘ + dbo.Quote(@IndexDesc)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@IndexUnique, 0)) +

‘, ‘ + STR(ISNULL(@IndexWord, 0))

SET @SQLCmd = @SQLCmd + ‘, ‘ + dbo.Quote(@SysApply) + ‘, ‘ +

STR(ISNULL(@SysDelete, 0)) + ‘ )’

PRINT @SQLCmd

DECLARE UserIndexFldCrs CURSOR LOCAL STATIC

FOR SELECT

uif.class_name

, uif.index_name

, uif.index_seq

, uif.fld_name

, uif.index_asc

FROM user_index_fld uif

WHERE class_name = @PClassName

AND index_name = @IndexName

OPEN UserIndexFldCrs

WHILE @Severity = 0

BEGIN — cursor loop

FETCH UserIndexFldCrs INTO

@ClassName

, @IndexName

, @IndexSeq

, @FldName

, @IndexAsc

IF @@FETCH_STATUS = -1

BREAK

SET @SQLCmd = ‘INSERT INTO user_index_fld ( class_name,

Scripting UET Definitions D-8

Modifying Infor ERP SyteLine

Copyright © 2009 Infor

index_name, index_seq, fld_name, index_asc ) VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@ClassName) + ‘, ‘ +

dbo.Quote(@IndexName) + ‘, ‘ + STR(ISNULL(@IndexSeq, 0))

SET @SQLCmd = @SQLCmd + ‘, ‘ + dbo.Quote(@FldName) + ‘, ‘ +

STR(ISNULL(@IndexAsc, 0)) + ‘ )’

PRINT @SQLCmd

END — Cursor Loop UserIndexFld

CLOSE UserIndexFldCrs

DEALLOCATE UserIndexFldCrs

END — Cursor Loop UserIndex

CLOSE UserIndexCrs

DEALLOCATE UserIndexCrs

DECLARE TableClassCrs CURSOR LOCAL STATIC

FOR SELECT

tc.table_name

, tc.class_name

, tc.table_rule

, tc.extend_all_recs

, tc.sys_apply

, tc.sys_delete

, tc.allow_record_assoc

, tc.active

FROM table_class tc

WHERE class_name = @PClassName

OPEN TableClassCrs

WHILE @Severity = 0

BEGIN — cursor loop

FETCH TableClassCrs INTO

@TableName

, @ClassName

, @TableRule

, @ExtendAllRecs

, @SysApply

, @SysDelete

, @AllowRecordAssoc

, @Active

IF @@FETCH_STATUS = -1

BREAK

SET @SQLCmd = ‘INSERT INTO table_class ( table_name, class_name,

table_rule, extend_all_recs, sys_apply, sys_delete,

allow_record_assoc, active ) VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@TableName) + ‘, ‘ +

dbo.Quote(@ClassName) + ‘, ‘ + dbo.Quote(@TableRule)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@ExtendAllRecs, 0)) +

‘, ‘ + dbo.Quote(@SysApply)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@SysDelete, 0)) + ‘,

‘ + STR(ISNULL(@AllowRecordAssoc, 0))

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@Active, 0)) + ‘)’

PRINT @SQLCmd

END — Cursor Loop TableClass

CLOSE TableClassCrs

DEALLOCATE TableClassCrs

END — Cursor Loop UserClass

CLOSE UserClassCrs

DEALLOCATE UserClassCrs

RETURN @Severity