Postcode Programming API  PostTrans Live Lookups for Exchequer


 

This has now been replaced by “In-Cell Searching”

Click here for details

 

To help with data entry, this functionality provides a live lookup of Exchequer Account, Stock and sundry codes while within a PostTrans template.

Features

  • Lookup and fill in Account/Stock code, or alt code, postcode, barcode, etc. plus almost any other field from appropriate record
  • GL/Job/Location/Currency/VAT code
  • Fill OrderPad with Telesales History, with days since last bought for easy order taking
  • Lookup Customer based stock price based on Account/Stock discount matrix based on Qty ordered
  • Expand BOM sub components on Quote/Order
  • Raise a Back2Back POR at time of posting
  • Show Free Stock in stock selection, and for location if appropriate
  • Show General notes on Account/Stock selection (Date notes for account too)
  • Expand to allow order from opportunities database
  • + all the other PostTrans functionality, such as transaction printing/email, plus your own customisation using formulas, or vba.

For remote lookups please click here

Download:

Download for new installation and upgrades. Works out of the box with “Electrics R Us”
Learn more >>
Prices:

Pricing for the Basic and Pro version of PostTrans
Learn more >>

Demo Videos:


Please click on one of the videos below to view:

             Introduction to Live Lookups 3:50 mins

             Orderpad using telesales history 6:00 mins

             Orderpad using list defined in account discount matrix 4:28 mins

             How to set up Live Lookups 7:04 mins


Examples:


Several examples of live lookups are included in the PostTrans subdirectory of Exchequer. These are all prefixed with "LIVE LOOKUP". Some of these examples are taken from existing examples but enhanced with the live lookup functionality listed on this page.

Sheets of particular interest are:

LIVE LOOKUP SQU and stock edit.xls

This contains a special sheet which allows the user to edit stock items, and if they are Bill of material stock items allow you to edit the Bill of materials contained in the build. Allow stock items to be copied. Therefore templates can be created bespoke stock items created by copying existing items and easily editing the stock descriptions. Six lines of stock description automatically wordwrap. As quantities entered the stock price will change according to the stock discount matrix within Exchequer.

LIVE LOOKUP Stock from matrix.xls

When the account code is entered in the header of the transaction, in this case and SQU, the sheet is populated with any stock items entered with bespoke pricing in the stock discount matrix. This allows for bespoke OrderPad fridge individual customer to be defined within Exchequer, and PostTrans used to create a sin, saw or and SQU. As quantities entered the stock price will change according to the stock discount matrix within Exchequer.

LIVE LOOKUP Telesales.xls

In the account code is entered in the header of the transaction spreadsheet is populated with stock from telesales history. This does require the stock history/telesales module within Exchequer. As quantities entered the stock price will change according to the stock discount matrix within Exchequer.

Other PostTrans examples


To enable PostTrans Live Lookup:


The Tag in cell A1 must contain “TransLookup”, and be enabled in System Setup under the “Live Lookup” tab, to enable this Live Lookup.

Do not enable if using on remote system, and do not load PostTransLIVELookup.xla and PostTransLookup.xla as they will conflict with each other.

PostTransLIVELookup.xla will, when first triggered, open the Current Exchequer Company as pointed to in PostTrans Setup.

Must have a PostTrans PRO license.


Inhibiting LIVE Lookups:


Due to the way the Live Lookups work, it can be triggered every time bespoke code updates a spread sheet. To stop LIVE Lookups doing this simply set registry using VBA before the update:

SaveSetting AppName:="PostTrans", section:="Update", Key:="LockOut", Setting:=1

And to turn back on when finished:

SaveSetting AppName:="PostTrans", section:="Update", Key:="LockOut", Setting:=0


Setting Focus Chain


You can define the path the user should take while using the template. This can be defined by entering in cell B1 “Chain:x”, where x is the path the focus should take through the sheet.

So if we enter “Chain:d3,d5,d7,d9,e25,f,i,j,e+1” in cell B1, then if the user presses Enter or Tab key in cell d3 then the cursor will move to cell d5, and next to d7 and then d9. Finally jump to stock code entry in cell e25. And whilst the cursor is in the data rows moving from column f will make focus jump to column i, and from column j will cause the cursor to move back to column e and down one row.

Ctrl+Enter to insert a row


Search Functionality


The following will match any chars type user types in field

    • VAT
    • Currency
    • Location
    • Cost Center
    • Department
    • Employee Rate
    • Job Type
    • Analysis Code
    • Job Rate

The following areas offer more specialised searching options

Account

Prefix

Description

None

Account Code

?

Alt Code

\

Name (This is “\” as in Exchequer, as “/” is a hot key in excel)

**

Any word in Name/Phone/Address or Del Address (Scans through all records)

:

Postcode

Stock

Prefix

Description

None

Stock Code

?

Alt Code

\

Name (This is “\” as in Exchequer, as “/” is a hot key in excel)

**

Any word in description (Scans through all records)

<

Barcode

GL

Prefix

Description

None

GL Code, Parent Code or start of description

?

Alt Code

\

Name (This is “\” as in Exchequer, as “/” is a hot key in excel)

**

Any word (Scans through all records)

<

Parent

Job

Prefix

Description

None

Job Code

?

Alt Code

\

Name (This is “\” as in Exchequer, as “/” is a hot key in excel)

**

Any word (Scans through all records)

<

Account Code


How to configure:


Most of the searching works “out of the box” as PostTrans uses the field tags (normally in row 18) and heading mappings to identify where lookup is required.

When searching for stock and accounts, you can now opt to have a multitude of the fields and columns to update, without the need for OLE functions. To enable this functionality, we simply list a series of fieldname and either the cell ref or column letter depending on the type of field, under the system setup “LIVE lookups” tab:

PostTrans Live Lookup Settings

Account example

After search for an account, in header section of transaction, we may want the Company Name and phone number to appear in cells C2 and C3.  To do this we enter:

AcName,C2, AcPhone,c3 in the “Account Mappings” field

We can then make PostTrans Live Lookup do extra things by entering a list of other options in the "Account Options" field.

Show general notes after selction. Simply add ShowGenNotes

Or say we want PostTrans to populate the sheet with all stock defined in the account discount matrix, we simply add FillWithMatrixEntries

And to do both ShowGenNotes, FillWithMatrixEntries more info account mappings and options

Stock example

Say we have the “StockCode” tag in column C. The PostTrans Live Lookup will the automatically popup whenever the start of a stock code is entered in that column below line 20.

So after a lookup we want Column D to contain the Stock Description, F the Price to the customer, and Qty in Column E.

So we enter:

StFullDesc,D, STPrice,F, STSetAccountCode, C4 in the “Stock Mappings” field

This tells the Live Lookup to put stock description in Col F, And the Price to the customer in col F, based on the account code in C4. When the user changes the Qty in column designated by PostTrans Tag “Qty” the price will be refreshed in Col F.

We can then make PostTrans Live Lookup do extra things by entering a list of other options in the "Stock Options" field.

We may want the search window to show free stock, in which ever location is specified on the account record, so we use ShowFree.

And after selection show any general notes which are against thet stock item, so now we have ShowFree, ShowGenNotes

Next we may want PostTrans to expand the stock item to list any sales oportunaties we have defined for that stock item. The list would now become. ShowFree, ShowGenNotes,ExplodeOpp more info stock mappings and options


Account Lookup Options:


Account options switches

These are entered in the "Account Options" field under the "LIVE Lookups" tab

Tag Name

Description

AcFillWithMatrixEntries

Optionally, after account selection fill the sheet with stock items listed in Customer Discount Matrix
use FillWithMatrix

EntriesYN to ask ● ○

AcFillWithSalesHistoryList

Optionally Fill with TeleSales History
use FillWithSalesHistoryListYN to ask ● ○
Requires Stock Analysis module

AcSalesHistorySortByDays

Sort Last bought at top of list

ACShowGenNotes

Show stock general notes if they exist

AcShowDateNotes

Show last 15 dated notes

ACCallClearOnChange

Clear all lines using the [Clear Transaction]

Button before writing and lines to sheet
use CallClearOnChangeYN to ask ●

AcShowNotesIfStatusNotes

If account status is see notes then show Gen and dated (last 15) NOT Implemented

                ● uses mappings from stock to fill in stock          ○ requires StkColRef to be specified to define stock col

Account mapping list

This is a comma seperated list of tags, followed by column Letter, or Cell reference.

Tag Name

Description

Special:

AcTeleSalesListXdays

AcFillWithSalesHistoryList with x days back
cell ref to value of x days

AcSalesListLastBoughtCol

Column to write last bought, used with FillWithSalesHistoryList option

ACStkCodeCol

Specifies the Column to write stock codes in.
Used by FillWithMatrixEntries, FillWithSalesHistoryList, UpdateCustomerPrice

ACDel

Delete the specified cell contents

ACMoveFocus

Cell Ref to move to after search

ACSetFillWithMatrixAcc

Cell Ref to account code to read matrix entries from. This could have been filled in with a user defined field, in which case would be put at end of list.

This allows the matrix to be setup as a template on one account, and multiple accounts use the same matrix list.

Account record mappings:

AcName

Cell ref

AcStatus

Cell ref, Open, Notes, On Hold, Closed

AcAltCode

Cell ref

AcAdd1-5

 

Cell ref

AcPostcode

Cell ref

AcDelAdd1-5

Cell ref

AcUser1-10

Cell ref

AcContact

Cell ref

AcPhone

Cell ref

AcPhone2

Cell ref

AcFax

Cell ref

AcEmail

Cell ref

AcCreditLimit

Cell ref

AcCreditAv

Cell ref

ACBal

Cell ref

AcPayDays

Cell ref

AcAreaCode

Cell ref

AcVATReg

Cell ref

AcCur

Cell ref

AcVatCode

Cell ref

AcVatCodeInc

Cell ref, Inclusive VAT Code

AcEECMember

Cell ref, returns Y/N

AcLocCode

Cell ref

AcCC

Cell ref

AcDep

Cell ref

AcTagNo

Cell ref

AcStatementTo

Cell ref

AcInvoiceTo

Cell ref

AcTradeTerms1-2

Cell ref

AcEECMember

Cell ref Yes/No



Stock Lookup Options:


Stock options switches

These are entered in the "Stock Options" field under the "LIVE Lookups" tab

Tag Name

Description

StExplodeBOM

Causes BOM items to be listed after selection
Used with tags STBOMParent, STBOMParentTopBot and STBOMQty

StExplodeBOMAddBlank

Add a blank line at the bottom of a Exploded BOM

StExplodeOpp

Expand Opportunity
Will write “Opp” into STBOMParentTopBot

StExplodeOppAddBlank

Add a blank line at the bottom of a Exploded Opp

StFlgDesc

Show “DESC” on Description only items

StFlgBOM

Show “BOM” on BOM Items

StShowFree

Show Free Stock

StShowInStock

Show In Stock Qty

StShowOnOrder

Show Qty on Order

StBoldBOM

Puts BOM Stock and StFullDesc Lines in BOLD

StShowGenNotes

Show General note is exists

       

Stock mappings list

This is a comma seperated list of tags, followed by column Letter

Tag Name

Description

Special:

StFullDesc

All of description
Each line of the description is separated by “,”
In order to write back these stock lines to transaction, under “Trans Switches” tab, “Don’t get extra stock lines from stock record” and “and break stock desk on ‘,’” should be ticked

StSetAccountCode

STSetAccountCode sets the account code for use with STPrice.  Also set location for STQtyFree, STQtyInStock and STQtyOnOrder

StSetLocation

Sets location for the STQty… figures
Should be used after SetAccountCode to override location fetched from account

STPrice

This is Customer price based on selected account for Qty 1. Also updated if Qty is changed
STSetAccountCode must be set before
Converted to account Currency

STPriceDiscount

Shows the discount applied due to discount matrix, used with STPrice
This cell should be formatted to Percent

STMoveFocus

Moves focus to column x

Stock record mappings:

STDesc1 – 6

Description line no x

STCostPriceConv

Cost, converted to account Currency

STReOrderPriceConv

Reorder cost, converted to account Currency

STCostPrice

Cost, converted to account Currency

STReOrderPrice

Reorder cost, converted to account Currency

STCostPriceCur

Cost Currency

STReOrderPriceCur

Reorder cost Currency

STAltStockCode

 

Alt Code

STParentStockCode

Parent Stock code in tree

STStockType

PMDXH

STPreferredSupp

Preferred Supplier

STBarCode

 

STBinLocation

 

STLineType

Normal, Freight, Discount, Materials

STJobAnalysisCode

 

STSalesUnits

1

STStockUnits

1

STPurchaseUnits

1

STDescSellingQty

each

STDescStockingUnitQty

each

STDescPuchQty

each

STQtyFree

 

STQtyInStock

 

STQtyOnOrder

 

STPriceBandA-H

 

STPriceBandCurA-H

 

STGenNotes

General Notes

Special BOM Functions: used with option ExplodeBOM

STBOMParent

BOM Component Parent code

STBOMParentTopBot

Mark Top and Bottom of BOM

STBOMQty

Qty of subcomponent