Linking to cicmpy table 1

[dgillz]

I have 3000 records in an excel spreadsheet I need to import to the cicmpy table in Macola ES.

If this were Progression, I would create a link table in MS Access, and write an append query to do this, and I would hae been done hours, no make that days ago. However I cannot link to the cicmpy table in MS Access 2000 because there are too many indexes on the table. Does this problem still exist in Access 2003?

So bottom line I am looking for an alternative that does not include learning XML, because I doubt seriously if I can learn to do that in the next 2 days or so. Exact tech support has been no help on this so far.

Any ideas out there?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[kscheeler]

What you want to do is create a view of the cicmpy table with all of the fields. Then link to the view, not directly to the table.

If you are trying to import customers or vendors, you should do it through the XML import from System, Utilities, XML Server, Financial, Import, Accounts Receivable or Accounts Payable. The import will create records based either on a .csv file or a .xml file. The layouts can be found on the customer portal. It will write records to the cicmpy table, the cicntp table for the default contact and to the addresses table for the 4 default address records needed.

Kevin Scheeler

 

[dgillz]

Kevin,

Do you have a document # on the import layouts?


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[kscheeler]

I can't find the document but I exported the layout that I've used. It's in a CSV format. Copy and import it into Access or Excel so you can see all of the columns.

You'll notice there are fields that you expect to populate but are not included in the layout. You'll have to populate them after importing. One is the state field. The 53 field CSV file can't be modified to add more fields but if you were to do it in XML, you could populate all of the fields you wanted to. I found this to be a quicker approach since I didn't know XML.

Remember, it will write to cicmpy, addresses and cicntp. Know what you've imported because if it's during testing, you're going to want to delete them out and you've got to delete them out of all 3 tables before you try to import them again.

The XML import function will validate the data in the CSV file, so if you left the country blank or put in an invalid entry, it will give you an error message and not import that customer.

I won't be at Engage. Too much is going to be happening then to be able to do it.

--------------------------------------------------------
No,Field name,Description,Type,Remarks,Form,
1,debnr,Debtor,Numstr,,A6,
2,naam,Name,Text,,A30,
3,adres1,Address line 1,Text,,A30,
4,adres2,Address line 2,Text,,A30,
5,postcode,Postcode,Text,,A8,
6,woonpl,City,Text,,A30,
7,landcode,Country,Text,,A3,
8,debzk,Search code,Text,Obsolete,A6,
9,valcode,Currency,Text,,A3,
10,telnr,Telephone number,Text,,A15,
11,faxnr,Telefax,Text,,A15,
12,cntpers1,Contact,Text,,A30,
13,mv1,Man/Woman/Unknown.,Text,,A1,
14,prdcode1,Title code,Text,,A4,
15,vrlttrs1,Initials,Text,,A10,
16,functie1,Job specification.,Text,,A15
17,telnrcp1,Telephone number,Text,,A15
18,banknr1,Bank account number,Elfpr,,A10
19,banknaam,Bank name,Text,Obsolete,A20
20,Empty,-,-,,A10
21,postbanknrd,Postbank account debtor,Numstr,Not supported,A10
22,betwijze,Payment method,Text,,A1
23,tegreknr,offset account,Numstr,,A9
24,dagbknr,Journal,Numstr,,A2
25,aandacht,Attention field,Y/N,,A1
26,categorie,Classification,Text,,A2
27,fakdebnr,Invoice debtor,Numstr,,A6
28,kredlimiet,Credit line,Number,,"N8,2"
29,bether,Reminder.,Y/N,,A1
30,betcond,Payment condition.,Text,,A2
31,blokkeer,Block,N/Y,,A1
32,verteg,Representative,Text,Not supported,A3
33,prijslijst,Price list,Text,,A3
34,ex_artcode,Extra item code,Text,,A2
35,levwijze,Delivery method,Text,,A3
36,korting,Discount percentage,Number,,"N3,2"
37,datlaanm,Date last reminder,Date,,A8
38,layoutcode,Layout code,Text,,A1
39,taalcode,Language code,Text,,A3
40,debsaldolj,Debit balance current financial year,Number,Obsolete,"N8,2"
41,crdsaldolj,Credit balance current financial year,Number,Obsolete,"N8,2"
42,debsaldosj,Debit balance secondary financial year,Number,Obsolete,"N8,2"
43,crdsaldosj,Credit balance secondary financial year,Number,Obsolete,"N8,2"
44,saldontvwd,debit balance to be posted.,Number,Obsolete,"N8,2"
45,saldontwvc,credit balance to be posted,Number,Obsolete,"N8,2"
46,omz_ex_lj,Revenue excluding VAT current financial year,Number,Obsolete,"N8,2"
47,omz_in_lj,Revenue including VAT current financial year,Number,Obsolete,"N8,2"
48,omz-ex_vj,Revenue excluding VAT previous financial year,Number,Obsolete,"N8,2"
49,omz_in_vj,Revenue including VAT previous financial year,Number,Obsolete,"N8,2"
50,bedrorder,Amount on order,Number,,"N8,2"
51,faktoring,Factoring,N/Y,,A1
52,btw_nummer,VAT number,Text,,A20
53,Datectrl,Check date,Date,,A6
--------------------------------------------------------

Kevin Scheeler

 

[NEmacGuy]

Dgillz,
Having gone thru what you are doing, I might not bother putting the records dirrectly into cicmpy.
If you do, under stand for every record in the Cimcpy you will need the following :

DivsionDebitor/Creditor - 1 record

Addresses - 4 records (del,pos,vis, inv)- has guid of cicmpy and cicntp

Cicntp 1 contact record - has guid of Cicmpy

cicmpyxref - 1 record

cifreefields - 1 record

There will be guid fields in many of these tables that will be relational to the other tables. You can do, I have. But I would rather use the excel add in for esynergy, point to your spreadsheet and upload the data.

 

[dgillz]

I am told that if I use XML to import cicmpy records the additional table records will be created sutomatically.

Also are you saying the excel addin allows you to import data to ES? News to me.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[NEmacGuy]

The cicmpy table in esynery is the same cicmpy table in ES import a customer to esynergy and they are there in ES (One-X). You may have to fill some fields later as all of the ES customer fields are not exposed in esynergy.

Note : I thought you were planning on writing the data dirrectly into the database. If you are importing XML into ES the XML server that will work. I do like the esynergy add-in.

 

[dgillz]

NEMacGuy,

where do I learn how to do this with the excel add-in?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[NEmacGuy]

As will most things with Exact / Macola, now-a-days by doing it your self. Or you could try to find a tech note on Exact site. See doc# 01.669.873 - Using the e-Synergy Excel Add-in. If you have esynergy, you go to the documents, maintance, installation, and click on the office add-in link. Then open excel, configure your setting (point to your synergy server), then go to the esynergy - upload option. It is pretty easy, you open a spreadsheet, match the colums in the spreadsheet to fields in the synergy customer card, then click upload.

 

[canimp]

PM me if you want copies of Excel Account Import templates and some advice.

 

[canimp]

I would add that I HIGHLY recommend that you do opt to create/import account records via XML versus direct write to the tables via SQL where ever possible.

As DGillz mentioned, import via XML forces the data through the actual business validation layers, ensuring that data writes to all of the necessary tables/fields, many of which are hidden entities unless you like pouring over profiler logs. For example, little known fact that the delvery method (ship via) also writes to a hidden field in the the Addresses table. Should you decide to update the ship via in Addresses without pushing it through XML, it is highly likely you will break some functionality. (Don't ask how I learned this) ;>

So many caveats: You might spend hours trying to figure out why sales/cost prices show in ES, but not in e-Synergy and it could be something as simple as the UOM being an incorrect <null>.

Not the popular answer, but an experienced one: Where ever possible, XML is your best method for correct import and validation of ALL types of data objects. It does take a different skill-set and knowledge of the import objects, but it is the safest bet - especially if you are running an integrated system.

BTDT - and advocate using the system validation.

 

[stravis]

All the notes on using XML are right on. Writing directly to the cicmpy, addresses, cicntp, etc... tables really requires a programmer who really understands the data and requires greater control than is provided by any data tool.

One additional method to use, if this is a new install of Macola ES, is to convert your data to Progression and then convert your Progression data to ES and then deliver to the client. This obviously only works for a one time conversion and you will have to make sure you get the Alpha-Numeric account code thing right when you do your conversion. Cicmpy data in a Alpha-Numeric account code company is not compatible with data in a cicmpy Numeric account code company, regardless of whether or not there are any alpha characters in any of your account codes.

Scott Travis
infoSpring, LLC.

 

[canimp]

Belated footnotes:
- if you have the option, converting to numeric account codes up front is a good long-term thought as this will smooth the upgrade path to Globe which only allows for numeric account codes
- if you are using the PACES tool for conversion from Progression to Macola ES, there is now a cmd line parameter that will enable account code cross-reference / recoding during the conversion (similar to the GL Account cross reference)