Sage Line 100 (legacy)
Contents
How do I set to use this interface?
In the accounts centre record, select Sage Line 100 (in the legacy group).
How do I import my customer list?
Sage Set-up Steps
The Sage setup steps consist of installing modified import CSV file layouts for the sales ledger and the purchase ledger. Also, the accounts menu is modified to provide quick access to the import/export functions Match-IT requires and show a help text file that provides ‘how-to’ reminders. Finally, you can transfer the nominal account codes and foreign currency codes from Sage to Match-IT; this is useful to ensure both sides use the same codes. The setup described here is the entire setup within Sage Line 100 for all the import/export functions. The necessary files to be installed can all be found in the Match-IT configs folder (typically c:\match_it\configs). For the purposes of the description here, it is assumed Sage Line 100 is installed in c:\sage\line100. Proceed as follows:
1.Install the modified CSV file layouts for the sales ledger and the purchase ledger.
Copy …\configs\SALCSVI.SKS and …\configs\PURCSVI.SKS and …\configs\SALCSVE.SKP and …\configs\PURCSVE.SKP from configs to c:\sage\svnprogs.
2.Install the ‘how-to’ reminders:
Copy …\configs\sl100hlp.txt to c:\sage\line100
3.Compile the modified layouts:
Log into the Sage Line 100 accounts menu and select “Accounting System|Accounting System Manager|Report Layouts|Compile ALL”. This will (re-)compile all report layouts, including the new ones you just installed.
4.Create a Sage Line 100 user called “Match-IT”:
Log-in to the Sage Line 100 system manager and create a new user with the name “MATCH-IT”, give it a high access level. Note its number for the Match-IT setup steps later.
5.Export the nominal account codes from Sage (optional):
Log into the Sage Line 100 accounts menu and select “Accounting System|Nominal Ledger|Utiltities|File Maintenance|Export Ledger File” to open the export program. Enter ‘H’ for headers and enter a file name to receive the codes (we suggest you use something simple like ‘nc’). Enter and confirm as necessary.
6.Import the nominal account codes into Match-IT (optional):
Log into Match-IT and select “Processes|Go Anywhere”. In the list presented, find the “Import File(s)” process (in the GX group) and execute it. Select the “Filter” radio button, press the “Filter Script” ellipsis, find the “sl100nom.fil” file in the Match-IT configs folder and open it, press the “CSV File” ellipsis, find the ‘nc’ file you created in step 4 above and open it. The “Import” should light up once you’ve done that. Press it, and confirm, to import the nominal codes.
7.Create a dummy foreign currency report in your Sage Line 100 system. The file name for the report must be FC and its descriptive name must be “FOREIGN-CURRENCY”. This is necessary to update the local data dictionary with the new report name. It’s just its existence that’s needed, nothing else. To do this log into the Sage Line 100 accounts menu and select “Report Generator”.
8.Export the foreign currency codes from Sage (optional):
MAKE SURE YOU HAVE DONE STEP 7 FIRST
Copy …\configs\FC.SKS, …\configs\FC.SKI, …\configs\FC.SKO and …\configs\FC.SKP from configs to c:\sage\svnprogs. (NB: 4 files are copied)
Re-compile Sage reports as in item 3 above.
Log into the Sage Line 100 accounts menu and select “Report Generator”.
Select and run the “FOREIGN-CURRENCY” report you see in the list shown.
9.Import the foreign currency codes into Match-IT (optional):
Log into Match-IT and select “Processes|Go Anywhere”. Follow the instructions as in item 6 above, but instead of the “sl100nom.fil” file, use the “sl100fc.fil” file.
10.Modify the Sage accounts menu:
Log into the system manager as the “Manager” user and select the “Amend Numbered Menu” item. Move the selector to the end of the current menu and press the insert key. A new empty item will appear. Fill it in as follows:
Type the item name as “Match-IT:Help”, press enter
Select “Operating System Command” as the program type, press enter
Type “Show Help” in the name field, press enter
Type “notepad” in the program field, press enter
Type “sl100hlp.txt” in the parameters field, press enter
You will be returned to the menu options list with your new option on the end. Press insert again to add another option, and proceed as:
Type the item name as “Match-IT:Import Customers”, press enter
Select “Retrieve 4GL Program” as the program type, press enter
Type “SALCSVI” in the name field, press enter
Type “SALCSVI” in the program field, press enter
Leave the options field blank, press enter
You will be returned to the menu options list with your new option on the end. Proceed in a similar manner to add other “Retrieve 4GL Program” options as in the table below:
Item name 4GL Program
Match-IT:Import Suppliers PURCSVI
Match-IT:Export Customers SALCSVE
Match-IT:Export Suppliers PURCSVE
Match-IT:Export Credit Balances SALDLC
Once these have been defined, press escape to end option entry and accept to update the menu.
Sage Run Steps
Generate the customers CSV file.
Log into the Sage Line 100 accounts menu, select the Match-IT:Help option and follow the instructions in there for exporting customer account headers.
Match-IT Set-up Steps
To setup the Sage Line 100 interface, follow these steps:
1.Tell Match-IT which accounting system you are using.
This can be done from the Accounts Centre form. A way to reach this form using the standard menus is: Functions | Standing Data | Accounts | Accounts Centres. Select the Main account centre then press Detail. You will be presented with a form that allows you to select the accounting system. Select Sage Line 100 then on the Options tab, set in the User Number field the user number of the Sage Line 100 user “Match-IT” you created earlier and Save the form.
2.Tell Match-IT what URN sequence to use when exporting transactions.
All transactions posted to Sage Line 100 are allocated a unique reference number for Sage auditing purposes. These numbers must not clash with similar numbers created within Sage itself. The recommended way of doing that is for Match-IT to start from the largest number possible and work down. It is then very unlikely Match-IT and Sage numbers will meet each other.
The initial URN is set from the Update Ledger Interface form. A way to reach this form using the standard menus is: Functions | Standing Data | Accounts | Ledger Interface. Select the Main account centre then press Detail. On the CSV Export Files tab, set –9,999,999 (NB: minus sign and seven 9 digits) in the Next URN field and Save the form.
3.Tell Match-IT where the customer import CSV file is.
This is done from the Update Ledger Interface form. A way to reach this form using the standard menus is: Functions | Standing Data | Accounts | Ledger Interface.
Match-IT needs to know where your customer csv file is so that it can find it when you ask Match-IT to (re-)import your customer list. You do this by putting the name in the New Cus field in the CSV Import Files tab (or selecting it via the ellipsis button). To minimise typing, and the possibility of errors in the Sage end, we recommend you use a very short file name, just the letter ‘c’ (for Customer) is sufficient. In Sage you’ll just type ‘c’ as the file name and it’ll be created in the Sage Line 100 working folder. In Match-IT, you need to specify the full path name to this file, e.g. c:\sage\line100\c
4.Tell Match-IT how you want invoices posted.
Invoices can be posted line-by-line, or consolidated. Line by line posting provides more detail in your accounts but results in more complex statements. Consolidated posting gives you no detail but simpler statements. Also, with consolidated posting there is a Sage Line 100 limit of 15 nominal codes and 10 VAT codes per invoice. To set consolidated posting change the Accounts Export Sales Inv. Lines and/or Accounts Export Purch. Inv. Lines defaults (in the CS module) from Yes to No.
Match-IT Run Steps
Import the customer CSV file.
This is done from the Import From Ledgers form. A way to reach this form using the standard menus is: Functions | Standing Data | Accounts | Import Accounts. Just check Import Customer Accounts, press the IMPORT button and Match-IT will do the rest.
How do I import my supplier list?
The procedure to import your supplier list is the same as that for your customer list except as follows:
In Sage, follow the instructions for exporting supplier account headers.
The CSV file name is recommended as just ‘S’
The Match-IT New Sup field must be set to the path to your ‘s’ file
Check Import Supplier Accounts on the Import From Ledgers form.
How do I import my customer credit balances?
Sage Set-up Steps
This has already been covered in the description for the setup to import your customer lists.
Match-IT Set-up Steps
1.Tell Match-IT which accounting system you are using.
How to do this is described in the How do I import my customer list section. If you’ve already done it there, you don’t need to do it again here.
2.Tell Match-IT where the debtor CSV files are.
This is done from the Update Ledger Interface form. A way to reach this form using the standard menus is: Functions | Standing Data | Accounts | Ledger Interface.
Match-IT needs to know where your debtor CSV files are so that it can find them when you ask Match-IT to (re-)import your customer credit balances. You do this by putting the name of the first one (Sage produces 4, one for each ageing period) in the Cus Balances field in the CSV Import Files tab (or selecting it via the ellipsis button). Sage has a predefined naming convention for these files, you must enter the name Sage uses. Sage creates 4 CSV files, one for each ageing period, the files have the standard name debtor1.doc (30 days), debtor2.doc (60 days), debtor3.doc (90 days) and debtor4.doc (120+days). You must specify the full path to the first one, e.g. c:\sage\line100\debtor1.doc. NB: The file extension is .DOC and not .CSV.
Match-IT Run Steps
Import the debtor CSV files.
This is done from the Import From Ledgers form. A way to reach this form using the standard menus is: Functions | Standing Data | Accounts | Import Accounts. Just check Import Customer Credit Balances, press the IMPORT button and Match-IT will do the rest.
How do I export new customer accounts?
Match-IT Set-up Steps
1.Tell Match-IT what Sage Line 100 cost centre and department to export to by editing the Sage L100 Cost Centre and Sage L100 Department defaults. The usual settings are SAL and STD respectively. NB: The cost centre and department set in Match-IT must exist in Sage otherwise all transactions will be be posted to the ‘suspense’ account.
2.Tell Match-IT where the new customers CSV file should be put.
This is done from the Update Ledger Interface form. A way to reach this form using the standard menus is: Functions | Standing Data | Accounts | Ledger Interface.
Match-IT needs to know where to put your new customer CSV file so that Sage can find it when you ask it to import your new customer accounts. You do this by putting the full path name in the New Cus field in the CSV Export Files tab. To minimise typing, and the possibility of errors in the Sage end, we recommend you use a very short file name, just the letters ‘SH’ (for Sales Headers) is sufficient. In Sage you’ll just type ‘sh’ as the file name and it’ll be looked for in the Sage Line 100 working folder. In Match-IT, you need to specify the full path name to this file, e.g. c:\sage\line100\sh
Match-IT Run Steps
Export the new customer CSV file.
This is done from the Export To Ledgers form. A way to reach this form using the standard menus is: Functions | Standing Data | Accounts | Export Accounts. Just press the EXPORT button and Match-IT will create the information in the file you have defined.
Sage Set-up Steps
This has already been covered in the description for the setup to import your customer lists.
Sage Run Steps
Log into the Sage Line 100 accounts menu, select the Match-IT:Help option and follow the instructions in there for importing customer account headers.
How do I export new supplier accounts?
The procedure to export your new supplier accounts is the same as that for your new customer accounts except as follows:
The CSV file name is PH
The Match-IT New Sup field must be set to the path for your ph file
In Sage, follow the instructions for importing new supplier account headers.
How do I export new sales invoices?
The procedure to export your new sales invoices is the same as that for your new customer accounts except as follows:
The CSV file name is recommended to be ST
The Match-IT Cus Trans field must be set to the path for your st file
In Sage, follow the instructions for importing sales invoice lines.
Note: Make sure nominal account codes and VAT rate codes you set-up in Match-IT are the same as those known by Sage.
How do I export new purchase invoices?
The procedure to export your new purchase invoices is the same as that for your new customer accounts except as follows:
The CSV file name is recommended to be PT
The Match-IT Sup Trans field must be set to the path for your pt file
In Sage, follow the instructions for importing purchase invoice lines.
Note: Make sure nominal account codes and VAT rate codes you set-up in Match-IT are the same as those known by Sage.
What information is in the CSV files?
The fields expected/produced in the CSV files referenced in this note are listed here for reference purposes:
ACCOUNT HEADER FILES
Sage field Match-IT import Match-IT export Format Note
Account Ref. csh:AccountRef csh:AccounteRef STRING
Account Name csh:Name csh:Name STRING
csh:CusSup
Address 1 csh:Address csh:Address STRING
Address 2 csh:Address csh:Address
Address 3 csh:Address csh:Address
Address 4 csh:Address csh:Address
Address 5 csh:Address csh:Address (1)
Analysis code 1 csh:Industry csh:Industry (2)
Analysis code 2 csh:Area csh:Area (2)
Analysis code 3 csh:Group csh:Group (2)
Contact Name csc:Name csc:Name STRING (3)
cscs:Contact
Country Code csh:CountryCode csh:CountryCode (2)
Credit Limit (ignored) csh:CreditLimit REAL (4)
FAX Number csh:FAX csh:FAX
Telephone Number csh:Telephone csh:Telephone STRING
VAT Reg Number csh:VatRef csh:VatRef
The following are extra to the default L100 layout.
For sales, they are fields from the SAL-HEAD-POST file.
For buys, they are fields from the PUR-HEAD-POST file.
Foreign currency (5) (6) FOREIGN-CURRENCY
Notes
(1) This is the post code field in Sage
(2) On import, codes are created as necessary
(3) The contact used is the default contact for the account.
(4) The currency used is the default currency for the account.
(5) A currency record is created if necessary and set as the native currency for the account.
(6) The native currency for the account is set
INVOICE LINE FILES
Sage field Match-IT sales Match-IT buys Format Note
AccountRef csh:AccountRef csh:AccountRef STRING(8)
TransDate sdc:InvoiceDate pop:Invoicedate DDMMYYYY
DueDate TransDate plus payment terms due days (csp:DueDays) for the
terms set in the associated invoice.
Type 04=invoice, 05=credit note, 06=opening balance
VatGoods1 sdi:LineCost poi:UnitPrice * REAL (1)
poi:Quantity
VATAmt1 sdi:LineVat poi:Vat REAL (2)
Discount always 0
Ddays always 00
OurRef sdc:InvoiceNum pop:InvoiceNum (3)
TheirRef sdi:Line poi:Line (3)
UserNumber csa:SageL100UserNumber “” STRING(4)
URN csl:SageL100NextURN “” INT (4)
Source 07 08 STRING(2) (5)
The following are extra to the default L100 layout.
For sales, they are fields from the SAL-TRAN-POST file.
For buys, they are fields from the PUR-TRAN-POST file.
VatCode1 csv:VatCode csv:VatCode VAT-CODE/1 (6)
Exchange Rate(7) (7) EXCHANGE-RATE
NomCount always 15 (8) always 15 (8) ANALYSIS-ENTRY-COUNT
GROUP,DIM(15) (9) (10)
NomCode sdi:NomCode poi:NomCode ANL-ACCOUNT/1..15
NomVal sdi:LineCost poi:UnitPrice * ANL-AMOUNT/1..15
poi:Quantity
NomDept csSageL100Department ANL-DEPARTMENT/1..15
NomCentre csSageL100CostCentre ANL-COST-CENTRE/1..15
END
GROUP,DIM(9) (11) (12)
VatGoods As VatGoods1 VAT-GOODS/2..10
VatAmt As VatAmt1 VAT-AMOUNT/2..10
VatCode As VatCode1 VAT-CODE/2..10
END
Notes
1.When using consolidated posting, this is the goods amount for the first VAT code in the invoice, otherwise it's the goods amount for one invoice line. The currency used is the default currency for the account.
2.Sign same as goods.
3.When using consolidated posting, this is the VAT amount for the first VAT code in the invoice, otherwise it's the VAT amount for one invoice line.
4.Our ref/their ref together uniquely define the item within Match-IT. Sage Line 100 has no notes or description field available, so detailed information must be looked up in the Match-IT context. The first transaction posted is given the number 9,999,999 then 9,999,998 then 9,999,997 and so on. Sage Line 100 internally allocates increasing numbers starting at 1, so this scheme ensures there is never likely to be a clash. The next number to allocate is stored in the ledger interface record (CSL).
5.These codes are defined by the Sage L100 SI source code and the Sage L100 PI source code defaults.
6.This is the VAT code that corresponds to the VatGoods1 and VatAmt1 fields.
7.This is calculated by dividing the amount in the accounts base currency (csa:AccountsCurrency) by the same amount in the account currency (csNativeCurrency).
8.If, when using consolidated posting, the invoice has more than 15 nominal codes, the export will fail. In this case the invoices must be posted line by line. Analysis fields not used are set to blank.
9.The NomCode..NomCentre fields are repeated 15 times, 1 set for each nominal code in the analysis. When posting line-by-line only the first set is used.
10.Each set is the sum for all invoice lines using the same nominal code when consolidated posting is set.
11.The VatGoods..VatCode fields are repeated 9 times. These in conjunction with the VatGoods1..VatCode1 fields represent the 10 VAT analysis codes available. When posting line-by-line, only the VatGoods1..VatCode1 fields are used.
12.If, when using consolidated posting, the invoice has more than 10 VAT codes, the export will fail. In this case the invoices must be posted line by line..
AGED DEBT FILES (debtor1/2/3/4.doc)
Sage field Match-IT import Format Notes
AccountRef csh:AccountRef STRING(8)
AccountName ignored
Address1 ignored
Address2 ignored
Address3 ignored
Address4 ignored
Contact ignored
CreditBalance csh:Credit##Bal REAL (1) (2)
All ##Bal fields are summed into csh:CreditBalance
The csh:CreditNowBal field is left blank.
Telephone ignored
FAX ignored
Notes
1.The file being processed defines the debt age and the field that is populated by the import. Debtor1.doc populates Credit30Bal, Debtor2.doc populates Credit60Bal, debtor3.doc populates Credit90Bal and debtor4.doc populates Credit120Bal.
2.The aged debt files do not include the outstanding debt for the current period (0-30 days), so this figure is not reflected in the overall credit balance.
Credit balances are assumed to be in the currency appropriate to the default set for the account within Match‑IT.
NOMINAL ACCOUNT HEADERS
Sage field Match-IT import Format Notes
Account Number kdt:Code
Account Name kdt:Name
Other fields ignored
The kdt:Class created is ‘NominalAccount’.
FOREIGN CURRENCY CODES
Sage field Match-IT import Format Notes
Foreign Currency SL-FOREIGN-CURRENCY
Currency name CURRENCY-DESCRIPTION
Exchange Rate EXCHANGE-RATE
Abbreviated name CURRENCY-ABBR-DESC
These records are used to create a currency unit of measure and a conversion to/from sterling. The currency name is the foreign currency code followed by a comma followed by the currency name (e.g “1,French Francs”). The abbreviated name is used in the formatted picture.