x Want to participate? Join the group to interact
  • Replies 5 replies
  • Subscribers 138 subscribers
  • Views 1766 views
  • Users 0 members are here
Browse Forums
Announcements
234
topics
29
replies
General Discussion
5k
topics
13k
replies
Sage Data & Analytics
56
topics
95
replies
Sage Enterprise Intelligence
209
topics
410
replies
Sage Intelligence Reporting
121
topics
322
replies
Dennis Aubrey

BPSUPPLIER SCREEN (X3) - CONTACT default field KCNTFLG not in MSSQL database tables - CONTACT or CONTRACTCRM

Posted By

October 25th, 2020 

Hello Teams,

Need your help. :) 

BPSUPPLIER-->CONTACT-->CONTACTCRM 

Where to find the field KCNTFLG - which is the Default Contact Flag in the MSSQL x3 database tables. 

Goal - I am looking for where the CNTFLG field is stored in the MSQL database tables. I looked at CONTACT and CONTRACTCRM. (Also searched for the field CNTFLG in all tables in X3)

CREATE TABLE [xxxx].[CONTACT](
[UPDTICK_0] [int] NOT NULL,
[BPATYP_0] [tinyint] NOT NULL,
[BPANUM_0] [nvarchar](15) NOT NULL,
[CCNCRM_0] [nvarchar](15) NOT NULL,
[CNTFNC_0] [tinyint] NOT NULL,
[CNTSRV_0] [nvarchar](30) NOT NULL,
[CNTMSS_0] [nvarchar](20) NOT NULL,
[BPAADD_0] [nvarchar](5) NOT NULL,
[TEL_0] [nvarchar](20) NOT NULL,
[FAX_0] [nvarchar](20) NOT NULL,
[WEB_0] [nvarchar](80) NOT NULL,
[MOB_0] [nvarchar](20) NOT NULL,
[DPO_0] [tinyint] NOT NULL,
[EXPNUM_0] [int] NOT NULL,
[CREUSR_0] [nvarchar](5) NOT NULL,
[CREDAT_0] [datetime] NOT NULL,
[UPDUSR_0] [nvarchar](5) NOT NULL,
[UPDDAT_0] [datetime] NOT NULL,
[CREDATTIM_0] [datetime] NOT NULL,
[UPDDATTIM_0] [datetime] NOT NULL,
[AUUID_0] [binary](16) NOT NULL...

And

CREATE TABLE [xxxx].[CONTACTCRM](
[UPDTICK_0] [int] NOT NULL,
[CNTNUM_0] [nvarchar](15) NOT NULL,
[CNTFULNAM_0] [nvarchar](60) NOT NULL,
[CNTTTL_0] [tinyint] NOT NULL,
[CNTTYP_0] [tinyint] NOT NULL,
[CNTLNA_0] [nvarchar](35) NOT NULL,
[CNTFNA_0] [nvarchar](20) NOT NULL,
[CNTBIR_0] [datetime] NOT NULL,
[CNTLAN_0] [nvarchar](3) NOT NULL,
[CNTCSP_0] [nvarchar](20) NOT NULL,
[CRY_0] [nvarchar](3) NOT NULL,
[CRYNAM_0] [nvarchar](40) NOT NULL,
[ADD_0] [nvarchar](50) NOT NULL,
[ADD_1] [nvarchar](50) NOT NULL,
[ADD_2] [nvarchar](50) NOT NULL,
[ZIP_0] [nvarchar](10) NOT NULL,
[CTY_0] [nvarchar](40) NOT NULL,
[SAT_0] [nvarchar](35) NOT NULL,
[CNTETS_0] [nvarchar](20) NOT NULL,
[CNTFAX_0] [nvarchar](20) NOT NULL,
[CNTMOB_0] [nvarchar](20) NOT NULL,
[CNTEMA_0] [nvarchar](80) NOT NULL,
[CNTFBDMAG_0] [tinyint] NOT NULL,
[CREUSR_0] [nvarchar](5) NOT NULL,
[CREDAT_0] [datetime] NOT NULL,
[UPDUSR_0] [nvarchar](5) NOT NULL,
[UPDDAT_0] [datetime] NOT NULL,
[EXPNUM_0] [int] NOT NULL,
[CREDATTIM_0] [datetime] NOT NULL,
[UPDDATTIM_0] [datetime] NOT NULL,
[AUUID_0] [binary](16) NOT NULL,
[UIDCRDNUM_0] [nvarchar](10) NOT NULL,
[SSCNUM_0] [numeric](28, 8) NOT NULL,
[RDEPITNUM_0] [nvarchar](10) NOT NULL,
[ROWID] [numeric](38, 0) IDENTITY(1,1) NOT NULL,

Thank you,

Dennis

  • Correction - 

    BPSUPPLIER-->CONTACT-->CONTACTCRM 

    Typo - CONTACT AND CONTACTCRM TABLES 

  • Here is the print screen.

  • in reply to Dennis Aubrey

    Screen - Print Screen. 

  • October 26th, 2020 - Solution 1 - 

    Field KCNTFLG (Contact Default Flag) is not in the MSSQL database. See print screen below. This field is the field name for the Screen Layout(s) only. To find the default Contact in CONTACT or CONTACTCRM here is the SQL Statement. (See below). I was looking at this differently at first, My Goal was - Looking just at the CONTACT and CONTACTCRM MSSQL Tables - What it the Default Contact? I was thinking that field KCNTFLG should be in the CONTACT or CONTACTCRM MSSQL table. Since there is already a Key which Links between BPA, BPS, CNT and CRM. From a design point of view, if CONTACT and CONTACTCRM links to other Modules in Sage X3, the Default Contact Address Code could be different. So adding the CNTNAM_0 which is the Contact Code Number to the BTS Supplier Table makes sense here. This is a good design. 

    I hope this will help others in the future. 

    Thank you,

    Dennis Aubrey

    My SQL Query - Testing 


    SELECT
     BPA.*
    ,BPS.*
    ,CNT.*
    ,AIN.*
    FROM
    BPADDRESS BPA
    inner join BPSUPPLIER BPS on( BPS.BPSNUM_0 = BPA.BPANUM_0)
    left outer join CONTACT CNT on(CNT.BPANUM_0 = BPA.BPANUM_0 ) -- AND BPS.CNTNAM_0 = CNT.CCNCRM_0
    left outer join CONTACTCRM AIN on(AIN.CNTNUM_0 = CNT.CCNCRM_0)
    where
    BPS.CNTNAM_0 = CNT.CCNCRM_0
    AND CNT.BPANUM_0 = 'SXXXXX';

       

  • I have found that the CCNCRM from CONTACT table or CNTNAM from BPCUSTOMER table would represent the default contact.  You can always test this by changing the default contact while observing SQL to see if that value changed.

*Community Hub is the new name for Sage City