SytelineMaster

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.