Adventures in sorting

A legacy report created in 2004 in the Advanced Accounting 5.1 version by another programmer that was later ported by us into the Advanced Accounting 7i and eventually to the  Adv 8 version, suddenly started to fail today after not having been changed since 2018.  Instead of producing a three page output, it was producing 85 pages of output in a report preview.  The problem was not the data nor had the program been recently changed.  So what happened?

The SORTA command has its limitations (that can be overcome):

This program was using a sort array (SORTA) command to sort items placed into array because an index or key field was not available for the inventory category field and the report needed to sort by that field.

While users can have a virtually unlimited number of inventory items in the Advanced Accounting system, this particular custom program was reading each inventory record into a memory array and then sorting on that memory array to produce the desired results.

The SORTA command has been available since the 16-bit TAS Professional 4.0 version on which Advanced Accounting 4.0 was based (and even older versions, for example TAS Professional 3.0, had a SORT command that worked differently).  In the 32-bit Windows versions of Advanced Accounting starting with version 6 (TAS Professional 6 and TAS Premier 7i and 7ix), the SORTA command behind the scenes uses a  Delphi TLIST object to accomplish the sort.  This command relieves the programmer from having to develop their own algorithm to sort memory values.

The TAS programming language SORTA command used to sort memory arrays has a limitation of 65,535 values.  This original custom program had been designed to read each record, one at a time, into memory and then sort and perform other logic from there.

But the end user now has 65,649 inventory items. That isn't a problem for the system in general to handle; however, it is problem for the way this custom program was sorting the information and it now created havoc based on the program design as the "read"array values were now being misinterpreted.

The solution was to condense the number of arrays to just a mere 450 internal arrays (instead of over 65,000) as that was how many different category values existed in the end user's inventory file.  The revised program creates those dynamically when each inventory record is read by using a second corresponding array (rather than combining them later after being read, which is how the program was originally designed).

Other solutions would of course be possible including defining the inventory category field as as index (but this would require changes to a number of other programs and would involve restructuring the data, and would also make the system harder to upgrade in the future) or by creating a new data structure with the appropriate keys that each record is saved to and updated in a manner similar to the revised array approach (but this creates multi-user issues that have to be addressed, and the program is going to run much slower as this is fairly hard drive intensive alternative) and then changing the program to report on that new temporary file, or a third solution would be to access the data via ODBC and query the data via SQL (but that would require using an outside process or a separate Delphi or other program or our SQLQUERY.EXE or the Pervasive/Zen equivalent, assuming the user is utilizing the newer database engine).

What is the last cost of an inventory item?

As with most other accounting software packages that provide inventory management capabilities, Advanced Accounting keeps track of the "last cost" of inventory items yet what really means is not as simple as it sounds.

As an overall costing method, Advanced Accounting uses moving average cost.   Other systems may offer additional options in terms of how inventory value and cost of good sold are calculated.  But often users want to use "last cost" as a metric to help determine future pricing especially in a world of constantly changing costs.

The last cost however as tracked by Advanced Accounting and other accounting packages may not necessarily inform managers as to how the absolute latest costs should impact the setting of sales prices and/or at the very least can be misunderstood.  

Inventory cost information provided by Advanced Accounting includes:

• Average cost  (both by location as well as for all units i.e. total blended average cost)
• Last cost (last saved or processed cost by location, by vendor and for all locations)
• Supplier cost (also referred as "catalog cost" i.e. the next cost based on pricing information provided by the supplier)
• Inventory movements (chronological detail of all costs processed against a part or part-location)

Costs, including last costs, are updated as each incoming transaction occurs based on  primarily purchases and any adjustments.  When inventory is sold, it is the item's average cost that is used to calculate the cost of goods sold and the reduction of the item's inventory asset account.  Average cost is used also in connection with inventory transfer situations (one location to another or to a different part code) and for bills of material calculations.  Last cost also is tracked not for accounting transaction posting but rather for general reference.

Some systems refer to the "most recent" cost which includes additional costs and taxes processed against that receipt.  This however will still not necessarily tell end users what they want to know as the word "recent" (meaning happening a short time ago) often may not actually be the most recent chronologically processed cost.

In Advanced Accounting, last cost (at the overall item, location, or vendor level) means the last time that a transaction was processed in the system against that item (i.e. product code or SKU) but it does not mean that cost occurred necessarily on the most recent date nor that it happened just a short time ago.  Timing is everything:  both in terms of when items are received, when vendors provide final invoices which often involve transactions dated prior to the last receipt date, and when transactions are physically entered into the accounting system.  If the person who normally processes purchase order receipts is not available due to illness or some other reason, last cost will be dependent on when those purchase orders are ultimately processed, and in what order.  So, last cost may not for a number of reasons  be the most recent calendar date transaction.  It might instead relate to costs on a vendor invoice dated several weeks prior or much longer than when the items were physically received.

So managers can be easily misled by just looking at last cost values.

Last cost (and average cost) can also be temporarily moderately to greatly skewed based on "best guess costs" processed on partially received items (when you don't necessarily know what the vendor will be charging for an item or in the event of a sudden price increase or a special one-time discount that lowers the price, but the stock has been received and needs to get into inventory as quickly as possible) and that could then also change later (but might then relate to an older dated vendor invoice when the vendor shipped the goods prior to their being received) based on the vendor's actual invoiced cost. 

So "last cost" does not mean the very latest "most recent" cost but rather the last cost as processed in the program that could involve older dated invoices or based on when staff is available to process transactions, etc.  

This is also why we are always concerned about sales prices being solely based on "cost" that can be quite plastic absent very close monitoring.  While it may be appropriate in some industry environments, "cost plus" pricing is fraught with perils and is also why we provide a "floor price" in  Advanced Accounting to help ensure that an item can't be sold for less than a certain amount.  But this requires a high degree of oversight to provide the type of controls that inventory management requires.  You may be given a large discount on a purchase due to a vendor promotion or because of your buying history and/or loyalty to a particular vendor.  Does that mean your customer pricing should be immediately lowered?  You may also have a temporary increase in prices that you could decide to "hold the line" to retain your long term customers. 

So there can be factors that are much more important than just the most recent or "last" costs.  Using the vendor's most recently published "catalog" pricing is a better solution or when that isn't available, diving into the inventory movements of an item to determine trends based on what the actual latest (which are not necessarily the "last") costs processed have been for a given product.

Last cost and average cost
(via IC-A's Cost Information button)

No More Pennies: cash back implications for point of sale systems

The US one-cent coin, i.e. the penny, is the fundamental unit of U.S. currency.  Children learning about money are often taught how to count starting with the penny.  An estimated 18 million cash only (or primarily so) retail businesses in the United States plan to continue to operate in that same way, and therefore have an ongoing need to make exact change (or in some cases to provide cash refunds).  Without a penny supply, however, those businesses will have to adapt with either different pricing strategies, increased reliance on other payment types, or implement updated point of sale software.  

But the implications of the cessation of the production of the US one-cent coin initially received relatively little fanfare.  With some 250 billion pennies still in circulation when the last one-cent coin was minted in November of 2025, businesses processing cash transactions were not terribly worried.

But now, mere months later, the situation has changed.

Recently, we were contacted by a point of sale user in Washington state who indicated that their bank can now no longer supply them with one-cent coins.

So what are retailers (including food service providers) that need to "make change" for cash payments supposed to do?

Round. But "rounding" leads to more questions.  Round up to the next (higher) five cents?  That approach may be available in some states but is generally frowned upon.  A number of states have already passed laws, including Washington state, that requires symmetrical rounding.  While there may also ultimately be federal legislation, a common principle that has evolved (for the 45 states plus the District of Columbia that have statewide sales taxes) is that you cannot round off sales tax, and that  the exact amount of sales tax based on the original sales price must still be calculated and collected.

There is also a bit of a catch-22 problem since in point of sale (POS) cash transaction handling, you don't know whether a cash payment is going to be involved (and hence potential cash back) until towards the end of the transaction when the customer tenders payment.

In eliminating production of the penny, Congress was not consulted, and while the decision ultimately might have been the same, this was more of a unilateral decision by the U.S. Mint following a directive from the White House due to the high cost of producing the penny. But that analysis failed to consider consumer and business impacts of this decision which now will require somewhat unintuitive software logic changes (and related upgrade expenses by businesses), and also because different states could end up having different rounding requirements that software publishers will have to provide within their POS software.  This then leads to higher consumer prices.

In solving the issue for POS applications in our accounting software program, we have spent an unfortunate amount of time on what seems like a very simple problem.  The total, after tax, has to be rounded either up to the next five cents or to the closest five cents (depending on what is allowed) but without making any change to the taxable sale amount since sales tax has to remain unchanged.

To avoid making more invasive changes to the system (which in the case of our system could also change the sales order module  programs because POS transactions when posted in our software become part of sales invoice history) and to avoid adding any changed data structures (such as a new "total only" adjustment field which could operate sort of like the highly unpopular gas fuel surcharge for sales invoices which have had a resurgence), we decided to automatically add a line item adjustment (marked as non-taxable to avoid any sales tax changes) that represents the difference between the actual total and the rounded total.  This allows us to maintain the normal internal logic whereby the total of line item extensions equal the POS ticket's subtotal plus tax (and in the case of sales orders, freight).  By making the penny adjustment of +- 0.01 to +-0.04 as a line item, the taxable basis for sales tax remains unchanged and allows any cash back (which is where the real problem is) to be calculated based on the "five-cent" rounded amount (and hence no pennies required). 

Unless cash back (or a cash refund) is involved, then nothing changes; the forced line item and rounded total only comes into play if cash tendered is greater than the sale amount (or the transaction involves a cash refund). Some technical details as to the conditional rounding calculation is contained below (although that doesn't get into most of the in-line logic required within the POS application itself, but rather just the "rounding" calculation).  The end result is that:

$10.01 or 10.02 rounds down to $10.00
$10.03 or 10.04 rounds up to $10.05
$10.06 or 10.07 rounds down to $10.05
$10.08 or 10.09 rounds up to $11.00

If the sale involves a return and the refund is made using cash, then the retailer may want to also round the negative sale to avoid having to use any one-cent coins.  In that case, there is  cash "out" but not necessarily "cash back" as it may be classified in some POS software (including Advanced Accounting) even though cash is being given to the customer in both cases.  This may then result in some slightly different conditional logic that then comes into play when there is a cash refund as a result of a return, pointing again to the fact that eliminating the penny has more potentially complex consequences than might have been fully recognized.

This new logic will now be incorporated as standard in Advanced Accounting's POS-A  ("Run POS Register") option going forward.  The end user will need to input a new non-inventory item called PENNYADJUST under "Enter/Chg Inventory" and use a general ledger (GL) sales code linked to either the over/short GL code specified in POS-J (POS configuration setup) or  any desired income or expense GL code. Older users that utilize sales orders for point of sale activity will have to manually enter the adjustment.

Technical details concerning five-cent rounding (included here mainly for programmers or for those who are curious to know more about a fairly boring topic):

As indicated above, the trigger for rounding happens only if (a) there is a cash transaction and (b) cash back is required due to an amount tendered that is more than the sale.  Nothing changes with a credit/debit card, ACH, or check payment.

When the initial request we received (in connection with the penny shortage) was to always round up to the next "nickel" (something, that as it turns, out isn't evolving as the standard approach), we first turned to the CEIL function, which has existed in the TAS Professional (and newer) programming language for a very long time.  In version 4.0 (1992) it was called CEILING.  Then, in TAS 5.0 through TAS 5.1 (1995 to 1996 releases) it was changed to simply CEIL.  Those were all 16-bit versions.  In the newer 32-bit version, we continue to have a CEIL function (which is also a Delphi/PASCAL function).  The rarely used CEIL function returns a whole number that is the closest value equal, to or greater than, the field value of interest. Because 1/20 = 0.05, the "trick" is to multiply the CEIL() result of the desired field by 20 and then divide that result by 20.  In the TAS language, this is represented by:

    define newtotal, oldtotal type N size 14 dec 2
    newtotal = CEIL(oldtotal * 20)/20

The above approach however, does not satisfy the symmetrical rounding concept which is the more equitable approach and is favored by states and other authorities (and in some cases required legislatively).

A Dephi function that does this is:

function RoundToNearestNickel(const AValue: Currency): Currency;

var
  TotalPennies, Remainder: Integer;

begin

  // Convert currency to total integer pennies
  TotalPennies := Round(AValue * 100);

  // Get the modulo 5 to identify the trailing digit
  Remainder := TotalPennies mod 5;

  //Apply symmetrical rounding rules
  case Remainder of

    1, 2:       // Round down to the previous nickel

          Result := (TotalPennies - Remainder) / 100;

    3, 4:       // Round up to the next nickel

          Result := (TotalPennies + (5 - Remainder)) / 100;

    else      // Already ends in 0 or 5 (no rounding needed)

      Result := AValue;

  end;

end;

An equivalent TAS Professional/Premier function follows:

define TotalPennies, Remainder type I size 5
define newtotal, oldtotal type N size 14 dec 2

func RoundNearest5 oldtotal

  TotalPennies = Round(oldtotal * 100)

  // Get the modulo 5 to identify the trailing digit
  Remainder = mod(TotalPennies, 5)

  // Apply symmetrical rounding rules
  //(verbose approach to match the Delphi version)

  select Remainder

     case 1   // Round down to the previous nickel

           newtotal = oldtotal-(Remainder*0.01)

     case 2
           newtotal = oldtotal-(Remainder*0.01)

     case 3  // Round up to the next nickel                          

           newtotal = oldtotal+(0.05-Remainder*0.01)

      case 4
           newtotal = oldtotal+(0.05-Remainder*0.01)

     otherwise      // Already ends in 0 or 5 (no rounding needed)

           newtotal = oldtotal

  endc

  ret newtotal

//A more concise way to write the above select/case/endc section 
//inasmuch as the TAS case statement does not allow more than one //parameter follows:

  if remainder=1 .o. remainder=2
     newtotal = oldtotal-(Remainder*0.01)

  else_if remainder=3 .o. remainder=4
     newtotal = oldtotal+(0.05-Remainder*0.01)

  else
     newtotal = oldtotal
  endif

In the TAS 7ix samples folder, we have added a program that performs either of the foregoing calculations:

 

File system error 65535

 

Recently one of our users experienced the error above.

This error is not an internal error generated by our software nor is it a Btrieve nor PSQL/Actian Zen error message (even though the file name referred to above to is in fact a PSQL data file).

It is instead a Microsoft file system error that is discussed here:

https://learn.microsoft.com/en-us/answers/questions/2434660/unable-to-create-file-with-file-system-error-(6553

Should you receive this message, IT support should be immediately engaged to investigate potential causes.

Zen 14 error 27555 when installing to Windows 11

 

Recently a customer experienced the above error message when trying to install a Zen 14 client on a new Windows 11 client PC.

The solution is described here:

https://communities.actian.com/s/question/0D5f300005ytV6yCAE/internal-error-27555

Creating a local admin account and then logging in using those credentials led to a successful install.

This issue could occur when installing to a Windows 10 client as well.

Make an image-level backup to protect your Actian (Zen, Pervasive, PSQL) license

The newer versions of what was once called simply Btrieve (BSERVER on client-server Novell systems) later became Pervasive, with newer engines also referred to as PSQL and more recently as Actian Zen.  These more modern engines will be collectively referred to here as PSQL.

After the 16-bit Btrieve 5.10a, Windows versions first in 16-bit and then 32-bit versions were released by Btrieve Technologies, Inc. over the 1982 to 1995 time frame.  With a corporate name change to Pervasive Software, Inc. there were versions 7, 8, and by 2006, version 9. Unlike with older pre-Pervasive versions, these newer versions required unique licenses.  The PSQL licenses were eventually linked to CPU or other installed hardware in versions 10 and 11. But that was changed starting in version 12 to be tied to solely the computer (server) name and that continues to be the case through the latest version, Actian Zen v16.

So these newer PSQL licenses which since v12 are solely tied a computer name can only be active on one computer at a time.  Both workgroup for smaller installations and server class licenses are available (more recently both "enterprise" and "cloud" versions are options in newer versions).  It is mainly server type licenses that are being referenced below but could also relate to workgroup licenses that can  be configured to support small client-server networks.

In a normal situation where the production server is operating, when migrating to a new server (or setting up a backup PC for a "just in case" scenario), you would want to install from your original downloaded media and leave the PSQL software in an unlicensed state (a temporary license will automatically be installed and its expiration date can be viewed in the PSQL License Administrator) on that new server.  It will then operate in a temporary license mode (the periods have changed over time, with older versions it normally has been 30 days but could be shorter such as two weeks; the trial version for v16, the latest version, is apparently for just one day) that gives you time to test the new server and verify that everything is working.  Then when finally migrating you would deauthorize the license on the production server (via the installed PSQL License Administrator which can be accessed either separately or via the PSQL Control Center; take a screen shot before deauthorizing) and apply the license on the new server.   Internet access is required when deauthorizing and then also when authorizing on the new server.  There used to be a limit as to how many times you could authorize/deauthorize but currently that appears to no longer be the case.

But, as previously mentioned, only one server can be authorized by any one PSQL key at any one time. (The only away around that would be to buy a second license and they are not inexpensive; inexpensive compared to Oracle and perhaps Microsoft SQL, but not cheap.)

In case of the situation where the production server goes down and cannot be brought back to life, that is when it is critical to have an image-level backup for the Zen/Pervasive install (which is not installed in the same folder as the Advanced Accounting or other TAS-based  system and is completely separate; the image-level backup for the accounting software would have to then be supplemented by a recent file level backup as well as any programs that have since been changed via ongoing updates as well as for any custom modified programs).  Without the image-level backup with which you could restore the PSQL license in the event of total failure of the production server, in the past you would simply contact Actian (formerly Pervasive) to get around the problem of not being able to deauthorize the license.  In the last few years however Actian has taken a reversal in their approach and now requires an expensive tech support contract instead of a free, simple support ticket.  

See for example this warning on the pricing pages at Goldstar:

https://www.goldstarsoftware.com/prices16.asp

Goldstar now includes that  cautionary language on versions going back to v11 (although the image-level backup would only help starting with v12).

See also:

https://communities.actian.com/s/supportservices/actian-licensing/actian-zen-licensing

We've already had two users who were forced into buying new licenses in light of this draconian policy that we can only hope they will reverse in the future (we and others have complained; there is also talk of a class action suit).

Even if you have an image-level backup, that will only work if the replacement server has the exact same computer name as the prior production server.  Otherwise you will receive a  key validation failure error message indicating that there has been a change to the machine signature and an indication that it relates to the different computer name.

Note that the foregoing does not apply to the many users we still support that are running valid legacy Btrieve 6.15 32-bit engines nor to the even older Btrieve.exe that loaded as a TSR from 16-bit ("DOS") versions although an image-level backup for those systems could still be useful.

Passwordless email delayed for now but Oauth2 is coming

Originally SMTP AUTH basic authentication (see our February 2017 GMAIL blog) was going to be phased out by Microsoft as early as March of 2026 (see this April 2024 Exchange Online blog).  That has since been rescheduled for Dec 31, 2026 (January 2026 Exchange Online blog).

Microsoft 365 (formerly Office 365, which is Microsoft's cloud-based email, calendar, and contacts service) uses Exchange Online.

Microsoft 365 will still allow for app specific passwords (see our February 2017 blog referenced above which discusses app specific passwords in the context of using GMAIL) as long as multi-factor authentication (MFA) is enabled. The intention is however to ultimately eliminate that option.

So what is behind this change?  OAuth: an abbreviation of "open authorization" that provides a way to authorize permissions between applications.  Essentially it eliminates the use of passwords and therefore is considered to be more secure.  (More information: What is Oauth?)

OAuth has been in use since late 2006, starting with its development for Twitter's API, with the first public version (OAuth 1.0) released in December of 2007 followed by the now more common OAuth 2.0 in 2012, which significantly expanded its use for apps and devices beyond web browsers. Oauth 2.0 is a redesign of Oauth 1.0 which was designed for websites. OAuth 2.0 is also referred to as OAuth2 (there is also an OAuth 2.1 on the horizon which is a different product).

Google Workspace apps including GMAIL have required OAuth2 since March of 2025 but as noted also still allows app specific passwords as the only allowed password method for sending emails. 

The most popular email providers currently are GMAIL, MS Outlook (formerly Hotmail and including Office 365, now Microsoft 365)  and Yahoo.  These providers have been implementing OAuth2 on different schedules.

Yahoo for example hasn't announced a specific date regarding the elimination of app passwords but may no longer be using it for new accounts; instead, they are pushing users towards newer, more secure methods such as account key (passwordless sign-in) for their own apps, while app passwords remain necessary for many older third-party email clients that don't support newer security methods, with some reports suggesting that Yahoo removed them for new accounts but still supports them for legacy users. App password generation may become available for individual new accounts after a period of regular use.

Local IT providers will likely follow suit but may continue to offer traditional SMTP (Simple Mail Transfer Protocol) services.

Note that you do not choose between SMTP and OAuth 2.0; you use OAuth 2.0 for authentication within the SMTP connection for secure email delivery, especially as providers phase out older, less secure methods. 

So why do we care?  Desktop applications that send emails will have to support OAuth2 if their end users rely on utilizing Microsoft 365 after December of 2026 (unless delayed again) and in the future with other major email providers.

Users though will still have other options that may be available through their website provider or via SMTP relay services such as https://www.smtp2go.com which already provides OAuth2 support.

So there is currently no reason currently to panic. We have been anticipating this change and have been closely monitoring the likely need to add OAuth2 support to Advanced Accounting's current email capability and have already identified several options.  Meanwhile we do plan to stay with our current email solution which for now works with all email providers.