x Want to participate? Join the group to interact
  • State Verified Answer
  • Replies 3 replies
  • Subscribers 76 subscribers
  • Views 981 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
SIQ

Auto Increment Field

SOLVED
Posted By

Hi im struggling to setup an auto increment field

i have a field called project no on the opportunity entity which i need to populate with the client code from the company record (comp_clientcode) and if it is a master opportunity (oppo_type = 'master') then add a PO01 

so if the client code for the company is TEST01 and its the first master opportunity created against that company then the project code needs setting to TEST01_PO01 and this needs to be incremented by 1 whenever a new master oppo is created against that company

ive tried setting up a trigger to try and set this but cant seem to be able to get it to work - any ideas or any sample code anyone can share please

Parents Reply Children
  • +1 in reply to Sage CRM
    verified answer

    I would also see this as a new hidden field on the Company e.g. 'No. Of Master Oppo' (integer)

    Then a trigger on the opportunity when it saves. 

    DECLARE @Code nVarChar(30)
    DELCARE @Proj INT
    DELCARE @Type nVarChar(30)
    DECLARE @OppoNo nVarChar(30)
    DECLARE @ID INT
    DELCARE @Comp INT

    SELECT
    @ID = oppo_opportunityid,
    @Comp = oppo_primarycomanyid,
    @OppoNo = oppo_ProjectNo (whatever your field is called),
    @Type = oppo_type
    FROM Inserted

    SELECT @Code = comp_ClientCode,
    @Proj = CASE WHEN comp_NoOfMasterOppo IS NULL OR comp_NoOfMasterOppo = 0 THEN 1 ELSE comp_NoOfMasterOppo + 1 END
    FROM Company WHERE comp_companyid = @Comp

    BEGIN
    IF @Type = 'Master' AND @OppoNo IS NULL
        BEGIN
        UPDATE Opportunity 
        SET oppo_OppoNo = @Code+'_PO'+CASE(@Proj AS nVarChar(30)) 
        WHERE oppo_opportunity = @ID 

        UPDATE Company
        SET comp_NoOfMasterOppo = @Proj
        WHERE comp_companyid = @Comp
        END
    END

  • 0 in reply to Matthew Shaw

    brill - thanks for this!

*Community Hub is the new name for Sage City