If you have hundreds users, manually rebuilding the User/Module Authorization will be painful.  The below query will copy over the User/Module Authorization from source DB to target DB.  Just run it against your target DB.

insert  modulemembers (objecttype, objectname, modulename, originalmodulename, modulememberspec)
select objecttype, objectname, modulename, originalmodulename, modulememberspec
from [Your_Source_DB_name].[dbo].[modulemembers]
where objectname like ‘OS_%’

If you even need to find out all store procedures that contain certain phrase, such as “PurchaseOrders”, the following query will help.

select so.name, sc.text from syscomments sc inner join sysobjects so on sc.id = so.id
where so.xtype = ‘P’ and sc.text like ‘%PurchaseOrders%’

This can be extended to other object type, for example ‘TR’ for trigger.   Here is a list of object type.

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SO = Sequence object

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

If you are a Syteline administrator, you will often need to refresh your Test/Training database with update-to-date Production database.

The most common way for copying DBs in SQL server is to use the backup/restore function.

1) Bring down all Infor service.

2) Backup your Syteline production database.

In SQL management studio, right click database name, task-> backup.

3) Restore it to Test/Training DB.

In SQL management studio, right click database name, task-> restore –> database.

Select source from device, give it the backup file location and file name.

Click Option.  Select the “Overwrite the existing database” option.  Provide the restore to DB

4) Once the restore process is done, right click on your Test/Training database name, go “Property –> File”.  Make sure the database owner is sa.  If it is not, change it to sa.

5) Run the below command to your Test/Training database.

ALTER DATABASE dbname SET TRUSTWORTHY ON

6) Bring back all Infor service.

7) Login to your Test/Training Syteline system.  Go to Site/Entities form, change the “Database Name” and “Form Database Name” to correct name.  Since this is a copy of your production database, it carries that Production DB name over, and you will need to change them to correct Test/Training DB name.

8) In a multi-site environment, there are a few more steps need to be completed.  First, if you are using intranet licensing, you may have problem login, even with sa account.  Need to do the following in SQL.

1.  Temporarily disable any replication for the Site Admin category in the database to prevent any later updates from replicating over to the production database. This is generally only an issue if the test, pilot, or development database is housed on the same server as production, but I would strongly recommend disabling replication under any circumstances to avoid any possible unexpected issues.

To disable replication for Site Admin, run the following in SQL against the database that has been copied over:

update rep_rule set disable_repl = 1 where category = ‘Site Admin’

2. With replication disabled in step 1, now update the site record to temporarily disable intranet licensing, which should allow the ‘sa’ user to login without error. To disable intranet licensing, run the following query in SQL:

update site set IntranetLicensing = 0

9) Now, you should be able to login with sa account.  Complete the above step 7) to update the DB name.  This need to be done for all sites.

10) If the Site name is differ between your production and pilot environment, you need to run the “Change Site Name” utility to correct the site name.

11) Finally, regen Replication Trigger.

12) Once all the updates are finished, replication triggers are regenerated,  you should be able to return to the Sites/Entities form and re-enable Intranet Licensing for all sites, and then do the same for Disable Replication in the Replication Rules form.

13) Update the license

Done.

Here is the way to check a form’s mode in script.

If Instr(1, ucase(ThisForm.Caption), “(FILTER IN PLACE)”) > 0  Then
ReturnValue = “-1”
Exit Sub
End If

This is about Progress version of Syteline, on how multi-currency works in Financial Statements.

FINANCIAL STATEMENTS
The exchange rate table (could be local or global) normally used by the database from which the Financial Statement is being printed will be used for the translations. Therefore, unless Current Rate translation is to be used on all accounts, the exchange rates in the Corporate database would best be entered correctly throughout the fiscal year, even if no translations are needed between those currencies until the financial statements are printed. Alternatively, exchanged rates may be entered and back-dated (e.g.,;, for subsidiaries acquired in the middle of the year), assuming a sufficient rate history has been stored correctly elsewhere (e.g.,’, on paper or in the new subsidiary’s system).
FINANCIAL STATEMENT OUTPUT
gl/calc-bal.p:
If None translations is requested, calculate balance as it is done currently.
If Current Rate translations is requested, calculate balance as above, then find current exchange rate and translate and round to the Corporate’s currency.
If End of Period translations is requested, translate each pertot.amt at its end of period exchange rate, and translate the remaining ledger amounts at each one’s end of period exchange rate. Then round the final balance.
If Average Period rate translation is requested, proceed like End of Period, but use the following formula to calculate the average exchange rate for each period.
Avg. Rate = (SUM(Rate * Effective Time))/
( Length of Period)
where Effective Time is the amount of time during which the rate was in effect during the period ( in seconds), and length of Period is also in seconds.
For speed, you may want to calculate, before starting through the sequence lines, the average buying and selling rates for all periods appearing on the report ( if any sequence lines use this method), and save them in a workfile or array for use here.
If Spot Rate translation is requested, DO NOT USE pertot records. Translate each ledger amount at the exchange rate in effect on its Transaction Date. Round the final balance.
We Process all posted transactions and upgrade them to the current exchange rate. For each voucher that has a gain or loss, we post the mount to Accounts payable and the Gain or Loss Account.
FINANCIAL STATEMENTS
Users are able to print Financial Statements in different currencies. Prior to SYMIX 4.0, they could only accomplish this when they were consolidating multiple divisions. The “final” Financial statement would be printed in the currency of the division running the report. The exchange rates used to convert the data were the ones stored in the division running the report. Now in V4.0, users can choose which currency they wish to print their financial statements in. The exchange rates used to print the report are the rates stored in the database that contains the ledger records being processed. The translations are for display purposes only. No posting of any kind takes place & no gain or loss is calculated.
Translations are defined for each line of the financial statement. Users choose to use the Buying or Selling Exchange rate. They choose a translation method: None ( no translations), Spot (historic rate for each transaction), Current ( current exchange rate), Average Period (weighted average exchange rate in effect for the accounting period for each transaction), End of Period (exchange rate in effect at the end of the accounting period for each transaction)

I have been trapped on this more than one time.  In order for a method call validator to work, the return value has to be bigger than 5.  This is clearly state in the WinStudio document: “If the method returns a value less than 5, WinStudio passes the validation. Otherwise, WinStudio fails the validation. The values 1 through 4 are used for warnings.”

All the standard Syteline Validators are using return value 1, so you would naturally copy that logic, and have it kept pumping on your face.  The value 1 through 4 are reserved for system use.  For customization developer like you and me, we have to be taller than 5.

Today I have waste two hours on this.  I knew it can not be 1, because I was trapped on this about a year ago.  But I forgot what the exact value benchmark is.  I was putting a value 4 on it.  And keep searching other area for error.  Finally I checked back into the document and found out again the magic number 5.  What a fool. 

From out of box Syteline, the pull down list of customer# field is sorted by customer#.  The same thing happens to the vendor# field.  Many Syteline users would prefer that the pull down list is sorted by customer name / vendor name in alphanumeric order, especially when you have hundreds customers/vendors.

Syteline Application Case

Change the sorting of Customer#/Vendor# pull down list, from Customer#/Vendor# to Customer Name/Vendor Name.

Syteline Technical Components

Component class, List Source

Customization Solution

The pull down list of a field is usually controlled by the component class that the field inheritance.

Get into the design mod of the form, example like RMA form.  Click the Customer field, then, in component property sheet, locate the Inheritance, Component Class.  Open the component class “RMACustomerZero”

Open the List Sources

Click the “Filter etc.” button

Enter “Name” in OrderBy field.

Then click OK all the way back.  Save the form.  Now, try the pull down list, it is now sorted by name in alphanumeric order.

The out of box Syteline missed some of the detail carelessly.  For example, the Grid view for RMA is sorted by RMA date ascending.  So it shows the oldest RMA up front and you are seeing those years old closed RMAs.

Syteline Application Case

Change the sorting of Grid view in RMA form, to show the latest RMA up front.

Syteline Technical Component

Form, Primary Collection, Collection Property

Customization Solution

This is pretty easy to change.  Go to Edit mod, in the form collection property, change the Order by to “RmaNum Desc”.  Done.

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.