x Want to participate? Join the group to interact
  • State Verified Answer
  • Replies 3 replies
  • Subscribers 138 subscribers
  • Views 713 views
  • Users 0 members are here
Browse Forums
Announcements
237
topics
29
replies
General Discussion
5k
topics
13k
replies
Sage Data & Analytics
56
topics
95
replies
Sage Enterprise Intelligence
210
topics
416
replies
Sage Intelligence Reporting
121
topics
322
replies
STX_AKM

Best way to import BP Contacts under Common Data > BPs > BP's > Contacts tab?

SOLVED
Posted By

Hi

I'd like to import email contacts under this contact function for about 1000 BP's. I have my list of BP's and the email address. I will flag them all with code being their BPCNUM followed by _STA. All fields will be the same such as language, country etc. We want to store statement email address here. 

However, I can't find a great way of doing this.

I've tried copying the AIN import template which imports to CONTACTCRM and adding the table CONTACT to this, it exports the data as i'd expect but importing just ignores the CONTACT table despite no errors and stating "1 record updated". Literally exporting, amending it, importing does not update the CONTACT table.



I've tried SQL inserting into CONTACTCRM and CONTACT but all fields are flagged as not null, but most will be null so ofc doesn't work. 


insert into [DB].[FolderName].[CONTACTCRM] (CNTNUM_0,CNTFULNAM_0,CNTTTL_0,CNTLNA_0,CNTLAN_0,CRY_0,CRYNAM_0,CNTTYP_0,CNTFNA_0,CNTBIR_0)
Values
('10000_STA','EMAIL\','1','Email','BRI','GB','United Kingdom','1','Email','1753-01-01 00:00:00.000'),
('10002_STA','EMAIL\','1','Email','BRI','GB','United Kingdom','1','Email','1753-01-01 00:00:00.000')

What's the best way to do this? I modified BPC import and removed the tables like address etc, but that doesn't work because the import script wont validate and that export would be incredibly messy to manipulate and import anyway, its an established business.

Any thoughts, this seems overly complicated to what I thought it would be?

Kind regards

  • +1
    verified answer

    Hi Adam,

    Have you looked at the BPC import template? The template includes the CONTACT table and updates are allowed.

    Note:

    • When importing suppliers or customers if a contact Code is included, the CNTLNA field for the CONTACTCRM table must be populated in the import file.
    • This field corresponds to KCNTLNA which is the data entry field for Name on the Contacts tab screen for both Suppliers and Customers.
  • 0 in reply to pnightingale

    Hi 

    Thanks for looking. Yes I think i referred to BPC in my post though. The import using that is tricky at this stage because the template requires at least 1 physical address If I'm not mistaken. I'd have to export that template, which would be multiline per BP, insert new lines after each BP for their new CONTACTCRM and CONTACT records. I can't think of a way how inserting new lines after each BP for 1000 records would work. If the export was a single line export, it would make this simple. Or, if the contact already existed it would work but it does't.

    Am i missing something with that do you think?

    Kind regards

  • +1 in reply to pnightingale
    verified answer

    OK so had another go at this after a colleague helped. You can strip down BPR template to quite the bare minimum. removing everything except BPARTNER and CONTACT from the template. I had an issue when validating stating that the action is not possible. I had previously abandoned at this point assuming what I'd stripped had broken it but thats a "feature" of X3 that you need to save, select a different template, go back to your custom template and then it will validate. 

    I extracted the BPARTNER fields using SQL and used that to create a single line import template. I did a vlookup to grab the emails required, populated CCNCRM and CNTLNA using the BPNUM and a string. That imported fine.

    Thanks

*Community Hub is the new name for Sage City