Aging Shows Differently After Database Rebuild

Q - After the database rebuild on A/R Open Item File (AROPNFIL.BTR), the A/R aging shows differently than before. For some reason, the payments are showing before the invoices. Also, they are not in any logical order that we can see. Here are the sample screens.  The first one shows the original sequence in A/R Customer Account Inquiry.  The second one shows what it looks like after using the Pervasive rebuild utility:

A - Here is the reason why this happens and how you can fix this problem:

Tables That You Should Avoid Trying to Rebuild or Defragment

There are four tables in the Elliott database that are known for this type of problem. You should try to avoid using Pervasive's Rebuild or Defragmenter on these tables:
  • AROPNFIL.BTR - A/R Open Item File
  • ARHISFIL.BTR - A/R History File
  • APOPNFIL.BTR - A/P Open Item File
  • APHISFIL.BTR - A/P History File

How to Fix Them Once It Happens

If you happen to rebuild and defragment these tables and notice this kind of issue, then you can take the following procedure to correct it.  Below, we will give you an example of how to fix it for the AROPNFIL.BTR file.  You can use the same principle for other tables.

(1) Rename AROPNFIL.BTR File
Go to the command prompt and change your folder to our Elliott data directory, such as "M:\Elliott7\Data."  Rename the BTR file to an OTR file:
You will not be able to rename the file if someone currently has this table open.  If this should happen, you can ask the user to exit and try again.  It is important that your users do not access this file when you are performing this procedure, so it may be better to perform this action during an evening or on a weekend.

(2) Export AROPNFIL.OTR File
At the same command prompt, export this file by using BUTIL:

(3) Create AROPNFIL.BTR File
At the same command prompt, use BUTIL to clone an empty AROPNFIL.BTR file:

At the same command prompt, use BUTIL to load data back to AROPNFIL.BTR file:

Why Does the Above Procedure Work?

All four of these tables have an "Apply-To" key.  The Apply-To key consists of Customer# + Apply-To# or Vendor# + Apply-To#.  The Apply-To key can have duplicate values.  In the above example, Customer #406903 has four records that are the same as 558043.  In order for the key to allow duplicate values, Pervasive implements them as a link list by chronological sequence.  That is to say, when the invoice record, doc-no 558043, was written to the database, it was the first record in the link list.  Then when the payment record, doc-no 367414, was written to the database, it was the second record in the link list, and so on and so forth.  When the system performs a customer account inquiry, when it comes to doc-no 558043, the database engine returns the record by the link list sequence (first-in first-out) and everything looks fine.

The problem starts to happen when you use the Pervasive Rebuild or Defragment utility, which does not necessarily honor the first-in first-out sequence for keys that contain duplicate values.  Hence, you see the wrong sequence in A/R Account Inquiry.

When you use the command BUTIL -SAVE AROPNFIL.OTR AROPNFIL.TXT, it exports the AROPNFIL.OTR file by using the primary key sequence.  The primary key does not contain a duplicate value and it is the same sequence as first-in first-out.  Therefore, when you use BUTIL -LOAD AROPNFIL.TXT AROPNFIL.BTR, it loads the new table by using the first-in first-out sequence to re-establish the Apply-To key.

When Payment Date Is the Same As or Before the Invoice Date

In some rare situations, when the payment date is the same as or before the invoice date, the A/R Account Inquiry screen still may not look correct.  The following is an example after going through the above manual rebuild procedure:

When you do a manual rebuild by using the above procedure, the loading sequence is by the primary key of the AROPNFIL.BTR file, which is Customer# + Date + Doc-No. In this case, the payment record and invoice have the same customer# + date. Since the payment document no. is smaller than the invoice no., the payment record is loaded to the table before the invoice when you do the manual rebuild. So that is what you see on the Inquiry Screen. This is a rare case. In addition, this is an aesthetic issue and does not affect Elliott functionality. If this is something that bothers you, you could try the following procedure:
  1. Go to re-apply the payment record and make the re-apply = 0
  2. Go to re-apply the payment record again and make the re-apply = 618656

Systems Manager

  1. Using Raw Data Pass Through for Dot Matrix Printer on Windows 10 Does Not Work
  2. How Do I Find The Workstation That's Infected with The Crypto Ransom Ware?
  3. How to Reset the Counter in *.DAT Files After Recovery from Crypto Ransom Ware Attack
  4. Aging Shows Differently After Database Rebuild
  5. What Do I Do When My Anti-virus Software Reports Elliott EXE File as a Virus?
  6. High CPU Usage and Performance Issues After Implementing NTFS Security
  7. "We can't verify who created this file. Are you sure you want to run this file?"
  8. Problem with Printer Configuration If Running Elliott from Multiple Workstations
  9. Inconsistent Elliott Error on Terminal Server
  10. How to Restore Elliott from CryptoWall Ransomware Attack
  11. Clarify Laser Forms Line Item Level Barcode Printing
  12. Is Dot Matrix Printer Still Supported in Elliott with Windows 10?
  13. Should I Install the 32-bit or 64-bit Outlook/Office?
  14. I Receive Error 114 When I Start Up Elliott on the Server
  15. Manage Data Execution Prevention (DEP) Through Registry Editing
  16. Windows Defender May Cause Elliott Start-Up Difficulties
  17. Why Do I Exceed Elliott User License Count When There are No or Very Few Users in Elliott?
  18. What Causes Load Error 198?
  19. How the Windows Scheme You Choose May Affect the Elliott Screen Display
  20. Still Get Error 114 After Adding All Elliott EXE Files to the DEP List
  21. How to Copy Laser Form Templates from Company to Company
  22. Weird Character Displayed for Backslash (\) Character
  23. Does Elliott Support Desktop Virtualization?
  24. Access Is Denied When Launching Elliott V8 Report Viewer
  25. User Cannot See Some Spooled Reports in Elliott V8
  26. Why Does the User List Function Take Forever to Bring Up?
  27. I Am Running Out of Invoice Numbers -- I Need Direction to Archive Invoices
  28. Algorithm to Speed Up eContacts List in Elliott V8
  29. Elliott Requires Volume Supporting 8dot3name
  30. Elliott Running Extremely Slow on Windows 8 with Trend Micro Anti Virus Software Installed
  31. What Causes "File Table Exceed Limit" Error?
  32. Mass Email Time Out
  33. Report Incomplete After 4 Hours: Event Stops Working After Upgrade to Elliott V8
  34. File Created in C:\ Root Directory Disappears
  35. Is There a Way to Create and Distribute Elliott Printer Configurations?
  36. What Are "COBOL Only" or "User Defined" Event Actions? Can We Use Them in Any Way?
  37. How to Purge WSORDHDR.BTR and WSORDLIN.BTR files?
  38. CPORDLS (Order Serial/Lot File) Btrieve Page Size
  39. Create PO Receiving Event to Trap Negative Qty on Hand Problem
  40. Reducing File Sizes for INITLFILE.LOG & SYACTLOG.BTR
  41. Windows 10 and Trend Micro Anti-Virus Software
  42. The Remote Certificate Is Invalid According to the Validation Procedure
  43. Customer Has Multiple Primary Contacts or No Primary Contact
  44. Btrieve Error 46 on S/M Activity Log File
  45. Multi-Currency Workarounds in Elliott
  46. Using the Export Processor to Export Data
  47. Instructions for Importing Laser Form Template
  48. I Am Unable to Post or Enter a Date for the New Year
  49. Does Elliott Work Under Virtualized Desktops Infrastructure (VDI)
  50. Btrieve Error 80 During Defer Processing
  51. Lauch CSV File -- There Was a Problem Sending the Command to the Program
  52. Feature - System Users File Integrity Check
  53. Receive Error in NWSMSCRN for Function Pf-Map-Coordinate-Space
  54. Not Able to Receive Event Emails from Certain Users
  55. Feature - Events for Add Note and Delete Note
  56. When and How to Use DDF2BTR.EXE Utility
  57. Elliott Last Printing Job Overrides Windows Default Printer on Windows 10
  58. Not Able to Change Attributes Added by Other Users
  59. Feature - New Context Menus in v8.0 Spooled Reports Manager
  60. Feature - Automatic Archiving of Spooled Reports in Elliott v8.0
  61. Feature - Enhanced Security for Attributes
  62. Feature - Deferred Processing Multiple Times Per Day
  63. Feature - Ability to Specify User ID in User Search
  64. Feature - Utility to Re-Calculate Item First Received and Last Received Date
  65. NSCTLMN1 Global Setup Time Clock Global Setup
  66. How to Set Up One-to-One Restriction of Customer and Item
  67. Recursion Error When Drill Down to Item File Inquiry
  68. Error Invoking Macro - Cannot Install Hook
  69. Feature - Record Navigation in Attribute Window
  70. Error Connecting VPN from Windows 10 to Windows 2008 Server
  71. How to Stop Users from Sending Elliott Reports through Email
  72. Does Elliott Support Windows 10?
  73. It Is Extremely Slow to Run Elliott over VPN Connection
  74. SYTIMCLK Systems Manager General Time Clock
  75. Feature - Recalculate AP Vendor YTD and Last Year Amount
  76. XCO0100 Systems Manager Design Your Own Order
  77. SYMENU Systems Manager - Index
  78. SYMENU Systems Manager - Installation
  79. SYMENU Systems Manager - Introduction
  80. SYMENU Systems Manager - Getting Started With Elliott
  81. SYMENU Systems Manager - Screen User Interface
  82. Feature - Restrict Recurring Event for Number of Times for a Specific Reference
  83. How Do I Limit the Companies for Users?
  84. Feature - Enforced Report Destinations
  85. I Am Unable to Create a New User in Elliott
  86. Feature - PDF Printing in Elliott V8.2
  87. Feature: PDF PostOffice in Elliott V8.2, Introduction
  88. Feature: PDF PostOffice in Elliott V8.2, Part 1
  89. Feature: PDF PostOffice in Elliott V8.2, Part 2
  90. Feature: PDF PostOffice in Elliott V8.2, Part 3
  91. Feature - Added Checkbox and Description to Attribute List and the SPS Commerce Create Attribute Register
  92. Feature - Performance Options for eContact Activity Tab
  93. Feature - Mass Change Salesman Utility
  94. Elliott Installer File-In-Use Warning During Installation
  95. Unable to Run Elliott from UNC Path
  96. V8 Program Desktop Startup Shortcuts - Internal Macro
  97. Event When Order Put On Hold at Invoice Printing
  98. Feature - Time Clock Logoff Type
  99. Feature - Added Distribution to G/L History Files
  100. Feature - Utility to Update eContact PDF PostOffice Flags
  101. Can I Use Elliott for Sales, Telemarketing and CRM Purposes?
  102. Emailing PDF with SSPI Failed After Online Charging Using Credit Card
  103. Can You Explain How Elliott Stores Serial Numbers in Its Database?
  104. Converting Elliott Internal Date to Conventional Date Format in Excel
  105. All of a Sudden, My Terminal Server's Elliott Default Changed to Someone Else's Settings
  106. WannaCry Ransomware Security Recommendation
  107. Symantec Endpoint Protection Versions 12 and 14 Cause Elliott Startup Error
  108. Notes Security - From Global Setup, Note Type to Supervisory Relationship
  109. Remote Desktop Workstation Name
  110. Which Anti-Virus Software Do You Recommend to Installing on the Elliott ERP Server?
  111. Menu Access Error - You Do Not Have Access to CP,INQ, Menu Item 01
  112. Ping Test to Isolate Inconsistent Load Error 198
  113. The Definitions of Extra Flags in System Period Control Setup
  114. How to Use Procdump.exe to Create a Memory Dump for PSQL Engine
  115. Feature - Group Location Security
  116. NSCTLMN4 Global Setup Bill of Lading 1
  117. NSCTLMN4 Global Setup Bill of Lading 2
  118. NSCTLMN4 Global Setup Bill of Lading 3
  119. NSCTLMN4 Global Setup Bill of Lading 4
  120. NSCTLMN4 Global Setup Bill of Lading 5
  121. NSCTLMN4 Global Setup Bill of Lading 6
  122. NSCTLMN4 Global Setup Bill of Lading 7
  123. NSCTLMN4 Global Setup Bill of Lading 8
  124. NSCTLMN4 Global Setup Bill of Lading 9
  125. NSCTLMN4 Global Setup Bill of Lading 10
  126. NSCTLMN4 Global Setup Bill of Lading: Index
  127. Feature - Support 12 Months History in Export Processor
  128. NSCTLMN3 Inventory Management Global Control Setup 1
  129. NSCTLMN3 Inventory Management Global Control Setup 2
  130. NSCTLMN3 Purchase Order Global Control Setup 1
  131. NSCTLMN3 Purchase Order Global Control Setup 2
  132. NSCTLMN3 Bill of Material and Production Order Global Control Setup
  133. NSCTLMN3 Distribution Modules Global Setup: Index
  134. NSCTLMN5 Global Setup Change Quote to Order 1
  135. NSCTLMN5 Global Setup Change Quote to Order 2
  136. NSCTLMN5 Global Setup Change Quote to Order 3
  137. NSCTLMN5 Global Setup Change Quote to Order 4
  138. NSCTLMN5 Global Setup Change Quote to Order 5
  139. NSCTLMN5 Global Setup Change Quote to Order 6
  140. NSCTLMN5 Global Setup Change Quote to Order: Index
  141. I Can Not See The Spooled Reports Even Though I Am An Administrator Equivalent User

Feedback and Knowledge Base