In Syteline, there are many ways to default field value.  The most simple way is to use the default value property of the field component.  But if you need to set the default value based on some other field value, that simple property value would not cut it.

Syteline Application Case

We want to default ship via code in Customer Ship To form based on customer type.

Syteline Technical Components

Event Handler, Inline Script, SQL Store Procedure.

Customization Solution

Since the customer type is in Customer form, but not in Ship To form, we need to get that in.

1) Create a simple SP: nGetCustType

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

as

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

2) Add a new event handler to event：StdObjectNewCompleted, to call the following 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_StdObjectNewCompleted_5
Inherits GlobalScript

Sub Main()

Dim nRetVal As InvokeResponseData
Dim nRequest As New InvokeRequestData()

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

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

nRetVal = IDOClient.Invoke(nRequest)

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

if ThisForm.Variables(“vCustType”).Value = “AAA” or _
ThisForm.Variables(“vCustType”).Value = “BBB”  then
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh _
(“ShipCode”, “UPRP”)
else
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh _
(“ShipCode”, “UP7A”)
end if
ReturnValue = “0”
End Sub
End Class
End Namespace

Within this script, we call the SP, get the Customer Type, then based on the customer type to set the ShipCode value.

We will use a sample to run through the process of adding a CLM into IDO. 

Part of making the Customer Document Profile function available to RMA verification report, we need to create a new CLM to IDO SLRmas.  The CLM is based on SP  navProfileRMAVerificationSp. 

create PROCEDURE [dbo].[navProfileRMAVerificationSp]
(
   @RMAStatusOpen                   ListYesNoType       = NULL,
   @RMAStatusClosed                 ListYesNoType       = NULL,
   @RMAStatusStopped                ListYesNoType       = NULL,
   @RMAStatusHistory                ListYesNoType       = NULL,
   @RMAStarting                     RmaNumType          = NULL,
   @RMAEnding                       RmaNumType          = NULL,
   @RMADateStarting                    DateType            = NULL,
   @RMADateEnding                    DateType            = NULL,
   @WhseStarting                    WhseType            = NULL,
   @WhseEnding                        WhseType            = NULL,
   @CustomerStarting                CustNumType          = NULL,
   @CustomerEnding                  CustNumType          = NULL
) AS

DECLARE @UserParmSite SiteType,
        @RMAStatus nvarchar(4)

if @RMAStatusOpen = 1 set @RMAStatus = @RMAStatus + ‘O’
if @RMAStatusClosed = 1 set @RMAStatus = @RMAStatus + ‘C’
if @RMAStatusStopped = 1 set @RMAStatus = @RMAStatus + ‘S’
if @RMAStatusHistory = 1 set @RMAStatus = @RMAStatus + ‘H’

SELECT @UserParmSite = parms.site FROM parms
SET @WhseStarting    = ISNULL(@WhseStarting, dbo.LowCharacter())
SET @WhseEnding      = ISNULL(@WhseEnding, dbo.HighCharacter())
SET @RMAStarting          = CASE WHEN @RMAStarting IS NULL THEN dbo.LowCharacter()  ELSE dbo.ExpandKyByType(‘RmaNumType’,@RMAStarting) END
SET @RMAEnding            = CASE WHEN @RMAEnding   IS NULL THEN dbo.HighCharacter() ELSE dbo.ExpandKyByType(‘RmaNumType’,@RMAEnding)   END
SET @CustomerStarting = ISNULL(dbo.ExpandKyByType(‘CustNumType’, @CustomerStarting), dbo.LowCharacter())
SET @CustomerEnding = ISNULL(dbo.ExpandKyByType(‘CustNumType’, @CustomerEnding), dbo.HighCharacter())

   SELECT
     rma.rma_num
   , rma.cust_num
   , rma.cust_seq
   , CASE WHEN DocProfileCustomer.RowPointer IS NULL THEN 0 ELSE 1 END
   , DocProfileCustomer.Method
   , DocProfileCustomer.Destination
   , DocProfileCustomer.NumCopies
   , ISNULL(Customer.lang_code,
    (SELECT lang_code FROM Customer WHERE Customer.cust_num = rma.cust_num
    AND Customer.cust_seq =0)) as LangCode
   , DocProfileCustomer.CoverSheetCompany
   , DocProfileCustomer.CoverSheetContact
   FROM rma
   LEFT OUTER JOIN Customer
     ON rma.Cust_Num = Customer.cust_num
     AND rma.Cust_Seq = Customer.cust_seq
   LEFT OUTER JOIN DocProfileCustomer
      ON DocProfileCustomer.CustNum = rma.cust_num
     AND DocProfileCustomer.CustSeq = rma.cust_seq
     AND DocProfileCustomer.RptName = ‘RMA Verification Report’
     AND DocProfileCustomer.active = 1
   WHERE  CHARINDEX(rma.stat, @RMAStatus) <> 0
     AND rma.rma_num BETWEEN @RMAStarting AND @RMAEnding
     AND rma.whse BETWEEN @WhseStarting AND @WhseEnding
     AND rma.cust_num BETWEEN @CustomerStarting AND @CustomerEnding
     AND (@RMADateStarting is null or rma.rma_date >= @RMADateStarting)
     AND (@RMADateEnding is null or rma.rma_date <= @RMADateStarting)

Create new IDO as extension of existing IDO

Use the new IDO Wizard to create a new IDO, which is extend of a existing Syteline IDO

Add Table

Once the IDO created, we need to add table DocProfileCustomer to the IDO

The links for the table should be CustNum and CustSeq，link to rma.cust_num and rma.cust_seq.

Add New Property

Every output field from the SP navProfileRMAVerificationSp need to be mapped to a IDO property.  So we need to create those property upfront. 

Bound property

Field like rma.rma_num, rma.cust_num and rma.cust_seq are bound to table fields

Derrvied property

For fields that no directly link to table fields

Add Method

Finally, we will add the CLM (Custom Load Method)

Then map the output fields of store procedure to IDO properties

Now this new CLM should be ready to use in our form. 

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

Google Insights for Search – Web Search Interest: syteline – Worldwide, 2004 – present

Looks like Syteline is fading slowly in the past 5 years.  The pick up in early this year may be related to the ad campaign Infor recently run.  Hope they really do something, no just sit there simply claim themselves as third biggest ERP software firm.