Simple way to monitor Rack and/or Bin progress in Excel during a Syspro Stock Take

Generally, I found that it is difficult to answer the question:  “How far are we with the stock take?”

The most commonly used, quick-and-dirty measures are to

  1.   Look at the bottom-line value (or unit) variance on your Syspro or custom report.  (Least accurate method)
  2.   Count the number of StockCodes counted (captured) vs. Total number of StockCodes (in the stock take file)
  3.   Count the number of Bins counted (captured) vs. Total number of Bins in the Stock Take file (or at least, the ones with QtyOnHand > 0)

But none of these measures provide an indication of how much of the physical warehouse (space) has been covered.

The following method might be helpful, assuming you make use of Syspro’s MultiBin functionality:

For the purpose of explanation, let’s assume you have (as shown in Figure 1 below):

  •  2 x Racks, numbered ‘1’ and ‘2’
  •  each Rack is 3 x levels high, numbered (level) ‘1’, ‘2’ and ‘3’
  •  each Rack is 2 x Bins ‘deep’, numbered ‘A’ and ‘B’
Example of Warehouse Rack and Bin layout

Figure 1:  Example of Warehouse Rack and Bin layout











So you have 12 x Bins in total, numbered (say) <Rack number><Level number><Depth>:  11A, 12A, 13A, 11B, 12B, 13B and 21A, 22A, 23A, 21B, 22B, 23B

Import data from Syspro into Excel using the following Query:

SELECT Bin, MAX(NumTimesCaptured) AS [Count] FROM dbo.InvStockTake

(one can add filters for specific Warehouses, etc.)

Use Excel’s VLOOKUP function – and Conditional Formatting – to set up the simple visualization below (Figure 2, query data in column A and B).

Excel example of monitoring Racks /Bins

Figure 2:  Excel example of monitoring Racks /Bins











The formula in (say) Cell F3 – which can be copied to the rest of the ‘Bin’ cells (columns F to H):


The visualization tells us the following:

  •  (At least one) StockCode in Bin ’11A’ has been counted once.  I used Conditional formatting to show these Bins as ‘green’ or ‘ok’.
  • Level 2 of Rack 1 has not been counted yet – or these bins are empty.
  • (At least one) StockCode in Bin ’13A’ has been counted twice.  This is handy if you’d like to see if a Bin was ‘double checked’ or if everything has to be double counted (e.g. two separate teams).
  • ‘Column’ or Depth ‘B’ of Rack 2 has not been counted – or the whole ‘Column’ in Rack 2 is empty.
  • Additionally, the Excel formula also caters for instances where the Bin does not exist in Syspro – these calls will be empty (blank). (Not shown in this example)

Hope it helps!

How to age stock value (Aged Inventory Valuation) in Syspro with Costing method = Average

I recently wrote a report for a customer with Average costing switched ON, so there were no dated lots/batches to use in the aging.

I made the assumption that FIFO was used.

I first calculated the receipts (POs, Jobs, Adjustments and Stock Receipts) quantities and values for the last 3, 6, 9, 12 (etc.) months, at Actual Costs (from InvMovements).
I then systematically calculated backward from the InvWarehouse.QtyOnHand, by deducting each period’s receipts, until InvWarehouse.QtyOnHand was ‘depleted’. The remaining periods’ receipts were then cleared/zeroed.

Let me know if you need assistance with something similar.

Pastel Partner Table names, Descriptions and File names

Herewith a list of the most commonly used Pastel Partner Table names, a short description of each and the corresponding file name.

Table Name Description  Filename
AccountUser Passwords, User Settings accuser.dat
ActivityMaster Time & Billing Activities accactiv.dat
AnalysisCodes Receipt Analysis Codes accranal.dat
BankDetails Receipting Bank Details accbnk.dat
BICUsers BI Centre users ACCBIC.dat
BillingParameters Time & Billing Parameters accprmts.dat
BOMHeader Bill Of Material Header accbomh.dat
BOMLines Bill Of Material Lines accboml.dat
CashBookCurrency Foreign Cash Books / Currency Balances acccbcur.dat
CostCodes Cost Codes accjcode.dat
CurrencyFile Foreign Currency accfcurr.dat
CustomerCategories Customer Categories accdcat.dat
CustomerDiscount Volume Based prices accdisc.dat
CustomerMaster Customer Master AccMasD.dat
CustSuppParameters Customer / Supplier Parameters accprmdc.dat
DeliveryAddresses Customer Delivery Address accdeliv.dat
EmployeeActivities Time & Billing Employee Activities accempac.dat
EmployeeMaster Time & Billing Employees accemp.dat
Euro Euro Code acceuro.dat
FinancialCategories Financial Categories accfcat.dat
Fonts Printer / Font Setup accfont.dat
HistoryHeader History of Document (e.g. Inv/Crn) Headers acchisth.dat
HistoryLines Document Lines, Inventory History acchistl.dat
HistoryTax Tax History acchtax.dat
HistoryUserDesc User (Transaction) History Descriptions acchistu.dat
HistoryUserSetup User History Settings acchistd.dat
InternetBank Internet Banking Cashbook accbank.dat
InternetBenf Internet Banking Beneficiaries accbenf.dat
Inventory Inventory Master accprd.dat
InventoryCategory Inventory Categories accicat.dat
InventoryGroups Inventory Group accgrp.dat
InventoryLink Store Linking accstklk.dat
InventoryParameters Inventory Parameters accprmst.dat
JumpUserAccess JumpUserAccess ACCUSERACCESS.DAT
LedgerBalances General Ledger Balances ACCMASB.DAT
LedgerMaster General Ledger Master accmas.dat
LedgerParameters General Ledger Parameters accprmgl.dat
LedgerTransactions GL, Customer, Supplier Transactions acctrn.dat
LinkHeader Link Header acclinkh.dat
LinkLines Link Lines acclinkl.dat
MenuAccess Menu Access ACCMACCESS.dat
MultiStore Multi Store accstore.dat
MultiStoreTrn Multi Store Transactions accstkst.dat
Notes Notes accnote.dat
OpenItem Open Items accoi.dat
POSAudit Point of Sale Audit posaudit.dat
POSCashUp Point of Sale Cashup poscash.dat
POSPaidOuts Point of Sale Paid Outs pospo.dat
POSPayments Point of Sale Payments pospaymt.dat
POSSetup Point of Sale Setup accprmps.dat
PreferredSupplier Preffered Supplier accstkps.dat
ProcessOptions Process Options ACCUSROPT.DAT
ReceiptTransactions Receipt Transactions accrecpt.dat
ReportCategories Report Categories accrcat.dat
ReportExport Report Export accrwexp.dat
ReportWriter Report Writer accrepw.dat
SalesmanMaster Salesman Master accsale.dat
SerialMaster Serial Master accmassn.dat
SerialTransaction Serial Transactions acctrnsn.dat
SupplierCategories Supplier Categories ACCCCAT.DAT
SupplierMaster Supplier Master accmasc.dat
SystemParameters System Parameters accprmsy.dat
TaxBox Tax Box acctbox.dat
TaxDescription Tax Description acctdesc.dat
TaxParameters Tax Parameters accprmtx.dat
TimesheetHead Timesheet Header acctimeh.dat
TimesheetLines Timesheet Lines acctimel.dat
Unposted Unposted accprdq.dat
UserGroups User Groups ACCUSRGRP.DAT
UserStore User Store accusstr.dat

Let me know if you need to link these or do a custom Pastel report from them!

Pastel custom reports – easy!

An accountant friend of mine asked me to do a ‘Monthly Product Sales’ for his client working on Pastel Partner.

I’ve never seen Pastel’s database layout before – but picked it up very quickly using the ‘Pervasive SQL Control Centre’.

Within an hour I could show the (customer) their monthly sales (by units and/or values – for the last two years) in an Excel Pivot table – with the customer and/or product in the filters.

The nice thing about using Excel’s Pivot tables – is that one can easily ‘drill down’ to invoice line level.  We could immediately pick up where/when exchange rate calculations were skewd by misplaced decimal points..

The accountant was quite delighted to find out that the same report can be used on any of his clients’ Pastel databases.

Let me know if you require any Pastel custom reports (in Excel or on the web).



Regular master data in Excel – penny wise, pound foolish

Are you regularly getting ‘master data’ in Excel – and then capturing it in your accounting/ERP system – or even worse:  keeping it in Excel?  Please stop!

I’ve done some work for a debt collection company the past few months.  They receive their ‘master data’ every month in Excel.  New/old accounts, new balances, new client info, etc.  I then had to report on changes in balances (graphs) and business ‘growth’/’decline’.

The ‘quick fix’ was to handle everything in Excel:  copy-and-paste new data into a ‘summary’ workbook and then run multiple vlookups.  This method was ok at the time – since the sustainability of the business was unsure.

But this has been going on for almost a year now..  Plus, any ad hoc queries would take time (money).

If I had to do it all over – I would’ve immediately designed an integration app that would (auto) load new data into a database and run a series of scripts to (auto) generate reports.  It would’ve saved myself some time and the customer some money.

If you’re getting Excel sheets (or CSV or ‘flat’ text files) from your customers, suppliers, transporters, etc and would like to integrate it with your account/ERP system – or run an independant system:  please contact me – I’ve learnt a few lessons..