Table of Contents

Overview

Importing is used when

You can also import


It is best to do a single line spreadsheet as a test - using information you will later delete - in order to be sure the results are what you expect after the upload.

Understanding the relationships

A person and a company are different entities - both may be customers and or suppliers

A person may also be a contact at multiple company debtors or creditors. 

A Lead is a Debtor at a status of Lead

The importing process does not check for duplicates.  If you expect duplicates you can either

  • Import the persons separately with the customer/supplier search code and role

First get the data into the spreadsheets

See Attachments to this page and download them

The Reference field needs to be unique for each record - if any reference field already exists in the system the record will not be imported

Creating Leads or Debtors with Persons as Contacts

Can be done in a single import from either the lead form (below) or the company form (Select Lead from options).

From the Lead screen

A check if all headings are correct

Gives a chance to change the headings if needed

Any data fields that were not matched with system data

The rest of the data would have been imported for those records

A Sample Spreadsheet is here - it has a relevant example.

example lead import sheet with test data.xlsx

Definitions


Details of the columns

Note: Underscore indicates that any text can preced or follow the section of text.  So email_ indicates the heading must start with email and anything else can follow it

contact

Company - Contact of the Company created

country

Company - Country of the Company created

_currency_

Company - Currency code of the Company created

email_

Company - Email of the Company created

xref

Company - External Reference field of the Company created

fax_

Company - Fax_ of the Company created

_oversea_

Company - Flag if the company is an overseas company not subject to GST

mobile_

Company - Mobile_ of the Company created

name

Company - Name of the Company created

notes

Company - Notes of the Company created

phone_

Company - Phone_ of the Company created

postcode

Company - Postcode of the Company created

state

Company - State of the Company created

street

Company - Street of the Company created

street2

Company - Street2 of the Company created

suburb

Company - Suburb of the Company created

web

Company - Web url of the Company created

abn

Company ABN

_search_

Company - and hence lead - Creates company search code

_delivery_name_

Lead - Address Delivery Name

*Next Call Back

lead - Asterix indicates this is a Profile option you configure - this is a free text field at the moment

*Lead Status

lead - Asterix indicates this is a Profile option you configure

*Products of interest

lead - Asterix indicates this is a Profile option you configure

*Reason they are a lead

lead - Asterix indicates this is a Profile option you configure

*Lead Urgency

lead - Asterix indicates this is a Profile option you configure

bti

Lead - Bank Transaction Identifier of the Company created

bankacc_name_

Lead - Bankacc_Name_ of the Company created

bankacc_no or bankacc_number

Lead - Bankacc_No Or Bankacc_Number of the Company created

bsb

Lead - Bsb of the Company created

_category_

Lead - Customer Category

_action_notes_

lead - Large Action Notes field on the front of the lead

_next_action_

Lead - Next action options - a list of values so the list choice must match exactly

terms

Lead - Payment Terms

_term_type_

Lead - Payment terms type

_sales_rep_

Lead - Sales Rep from a list of available sales reps

_trading_

Lead - Trading Name of the Lead created - some companies have different trading names to the company name

_delivery_country_

Lead Address - Delivery Country

_delivery_postcode_

Lead Address - Delivery Postcode

_delivery_state_

Lead Address - Delivery State (NSW, QLD etc)

_delivery_street_

Lead Address - Delivery Street

_delivery_street2_

Lead Address - Delivery Street2

_delivery_suburb_

Lead Address - Delivery Suburb name

_contact_country_

Lead Contact - Country

_contact_email_

Lead Contact - Email

_contact_first_name_

Lead Contact - First Name

_contact_last_name_

Lead Contact - Last_Name

_contact_mobile_

Lead Contact - Mobile

_contact_phone_

Lead Contact - Phone

_contact_postcode_

Lead Contact - Postcode

_contact_role_

Lead Contact - System Role

_contact_state_

Lead Contact - State

_contact_street_

Lead Contact - Street

_contact_street2_

Lead Contact - Street2

_contact_suburb_

Lead Contact - Suburb

_contact_title_

Lead Contact - Title



Person Import - can be person, debtor or creditor

Useful if each company has multiple contacts

Drag the person spreadsheet over the person form

Respond to the popup with yes... The data is now uploaded to the server - but has not yet been imported

Person OR Debtor OR Creditor Profile Import

To import Profiles on the Company (Host Company > Entity and Debtor/Creditor Profiling)

  1. column names start with tilde ~
  2. and match using StartsWith, not full exact match.
  3. Values of Y, 1, true turn the flag on


Ask the system to do the import

When importing Persons - will not be prompted to create debtors or creditors - these are autocreated when transactions are first entered and do not normally require additional information


Note - when importing companies - more information is required than importing person debtors:

  • Cards only will create Company Cards or Person Cards only
  • Suppliers - will also create suppliers (creditors)
  • Customers - will also create customers (Debtors)
    • Delivery name, delivery street, delivery street2, delivery postcode, delivery state, term, term type (invoice, EOM, ROG)



Check if the import worked - search for one of the people.

   string col = c.ToLower();
                if (col.Contains("first")) iFirstName = i;
                else if (col.Contains("last")) iLastName = i;
                else if (col.Contains("company")) iCompany = i;
                else if (col == "title") iTitle = i;
                else if (col == "notes") iNotes = i;
                else if (col == "street") addr1 = i;
                else if (col == "street2") addr2 = i;
                else if (col == "suburb") suburb = i;
                else if (col == "state") state = i;
                else if (col == "country") country = i;
                else if (col == "postcode") postcode = i;
                else if (col == "email") iEmail = i;
                else if (col == "mobile") iMobile = i;
                else if (col == "phone") iPhone = i;
                else if (col == "gender") iGender = i;
                else if (col == "xref") ixref = i;


Importing Companies (Suppliers separately to Customers) - Choose which you are creating.

Drag the company spreadsheet over the company form

Respond to the popup with yes (as above)... The data is now uploaded to the server - but has not yet been imported

Ask the system to do the import (as above)

Check if the import worked - search for one of the companies.

Company OR Debtor OR Creditor Profile Import

To import Profiles on the Company (Host Company > Entity and Debtor/Creditor Profiling)

  1. column names start with tilde ~
  2. and match using StartsWith, not full exact match.
  3. Values of Y, 1, true turn the flag on



 string col = c.ToLower();
                    if (col == "abn") iABN = i;
                    else if (col.Contains("search")) iSearchCode = i;
                    else if (col == "name") iName = i;
                    else if (col == "contact") iContact = i;
                    else if (col == "street") addr1 = i;
                    else if (col == "street2") addr2 = i;
                    else if (col == "suburb") suburb = i;
                    else if (col == "state") state = i;
                    else if (col == "country") country = i;
                    else if (col == "postcode") postcode = i;
                    else if (col.StartsWith("email")) iEmail = i;
                    else if (col == "web") iWWW = i;
                    else if (col.StartsWith("mobile")) iMobile = i;
                    else if (col.StartsWith("phone")) iPhone = i;
                    else if (col.StartsWith("fax")) iFax = i;
                    else if (col == "xref") ixref = i;
                    else if (col == "notes") iNotes = i;
                    else if (col.Contains("trading")) iTrading = i;
                    else if (col.StartsWith("bankacc") && col.Contains("name")) iBankAcctName = i;
                    else if (col.StartsWith("bankacc") && (col.EndsWith("no") || col.EndsWith("number"))) iBankAcctNo = i;
                    else if (col == "bsb") iBSB = i;
                    else if (col == "bti") iBTI = i;
                    else if (col.Contains("currency")) iCurrency = i;
                    else if (col == "terms") iTerms = i;
                    else if (col.Contains("term") && col.Contains("type")) iTermType = i;
                    else if (col.Contains("sales") && col.Contains("rep")) iSalesrep = i;
                    else if (col.StartsWith("bankacc") && col.Contains("name")) iBankAcctName = i;
                    else if (col.StartsWith("bankacc") && (col.EndsWith("no") || col.EndsWith("number"))) iBankAcctNo = i;
                    else if (col == "bsb") iBSB = i;
                    else if (col.Contains("oversea")) ioverseas = i;
                    else if (col.Contains("delivery"))
                    {
                        if (col.Contains("name")) idelname = i;
                        else if (col.Contains("address2")) ideladdr2 = i;
                        else if (col.Contains("address")) ideladdr1 = i;
                        else if (col.Contains("suburb")) idelsuburb = i;
                        else if (col.Contains("state")) idelstate = i;
                        else if (col.Contains("postcode")) idelpostcode = i;
                        else
                        {
                            excel.UnusedColumns.Add(c);
                        }
                    }

Persons with a relationship to a debtor or Creditor

To get a list of persons search codes (or company ones) if you did not define them on import > export from the master list

Drag the relationship XLS over the Debtor Form, respond to the popup to load the file to the session and then Administrator menu to create.

The excel sheet is simple with:

  1. Person - search code
  2. Company - search code
  3. Role - Role name

Related articles

Related articles appear here based on the labels you select. Click to edit the macro and add or change labels.


Related issues