The Custom Sapphire Tools screen provides additional tools for the maintenance of your data file. Some of these tools have been designed with a specific function or problem in mind and most of them should only be used under the direct instructions on SapphireOne support staff.
These tools if used incorrectly may cause irreparable damage to any data file.
There are two warning screens and as seen in the First one there must be only ONE user online when these functions are being run. Since these functions have the potential to damage the database, it is Critical that you have a current backup saved. This backup should be stored safely, and preferably not on the same drive that the database is located on.
Following there is a brief description of each of the functions listed in the Select Procedure menu. Many of them will have as a description as follows. Not to be used. Please do not ignore the warning.
Four tools have additional documentation. There will be a brief comment within the table and extended documentation will be provided at the end of the article for:
These tools are, Bay Fix, Bay Import, Change ID, and Fix100%Alloc.
Sapphire Tools…
| Allocation RebuildA: | This is a special tool to fix allocation problems that may occur regarding Allocation Lines. |
| Archive Invoice Changes: | If you keep track of changes to Invoices (set in System Defaults) this archives and deletes the changes if the file has grown to an excessive size. Archive files are created as text files. |
| Archive_Log: | This tool allows the archiving of selected information to a separate file and removal of this information from SapphireOne’s data file. |
| Authorise: | This tool is required when running more than one data file. |
| Backup System: | This tool creates a file which is a backup of SapphireOne Values file. |
| Bay Fix: | This tool will present a screen enabling a number of checks to be done for the selected inventory bay item. They are documented in further detail after the end of this table. |
| Bay Import: | This tool imports details for a bay items and is the same as a Gateway import and there are five import options. Since they are complex they are documented in further detail after the end of this table. |
| BlindClient Allocate: | This tool will blindly allocate all un-allocated transactions to their respective clients and may cause allocation errors. |
| CGLBalUpdate: | This tool will fix any errors found when running Foreign Currency General Ledger. |
| Change ID: | This function allows the changing, consolidation or correction of ID’s. Note that you have to tab forward after entering the new ID before the ACTION button will become active! Details at the end of this article. |
| CheckDupKey: | Not to be used unless under explicit instructions from SapphireOne support staff |
| Check Inventory: | Not to be used unless under explicit instructions from SapphireOne support staff |
| Clean User Access: | If your User Access files have developed duplicate entries this function will consolidate the files accessible back to one entry. |
| CleanAudit LineStatus: | Not to be used unless under explicit instructions from SapphireOne support staff |
| Company Fix: | This function will fix any fields that are incorrectly related to companies, as well as assigning the correct Company ID’s as required where they have not been assigned |
| CustomReport List: | The system will look for any custom reports with data and display them in a list. They may be edited from here as normal for a custom report. | |
| DeleteHistory Lines: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| ExportHtran: | This function will export all history transactions to a text file. | |
| Export Htranline: | This function will export all history journal lines to a text file. | |
| Exporttranseq: | This function exports all transaction sequence numbers to a text file. | |
| Fix100Alloc: | It is not recommended that you use this function without first consulting SapphireOne support staff. Additional documentation is at the end of this article. | |
| FixErrSuperTran: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| FixTransactions PeriodbyDate: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| Fixundo SIGSECU: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| FixVRS: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| Full Backup: | This function backs up all the data files to a text file for storage purposes. | |
| FX_ChangeFormat: | If you change the formatting of an FX currency in Management \ General Ledger \ General Ledger Types you will be told to run this utility. It will update all the Invoices, Transactions etc with the new currency format. | |
| FX_Fix: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| GLBal_Import: | This function allows importing of General Ledger balances not in the current year from a Text file. The Text file must be in the format of Account ID, Period, Debit Amount and Credit Amount and stored in a tab delimited file. | |
| GLBalUpdate: | This function will fix any errors found when running the General Ledger Audit in SapphireOne Functions. Please make sure that a TranAudit has been run and has verified that there are no errors in the data file before running this procedure. Failure to do so may result in balancing errors within your General Ledger. | |
| GLBud_Import: | This function allows importing of General Ledger budgets from a Text file. The Text file must be in the format of Account ID, Period, Debit Amount and Credit Amount and stored in a tab delimited file. | |
| GLPRBud_Import: | This function allows the importing of Project General Ledger budgets from a Text file. The Text file must be in the format of Account ID, Project ID, Period, Debit Amount and Credit Amount and stored in a tab delimited file. | |
| GST Conversion: | Not to be used unless under instructions from SapphireOne support staff | |
| ImpHline: | This function imports all history journal lines from a text file. This reads the file produced when Export HTranLine is run. | |
| Import_Invoices: | This function imports Invoices in 4D format from a text file | |
| ImportHTran: | This function imports all history transactions from a text file. This reads the file produces when Export HTran is run. | |
| InterCompany: | Department Balancing and Intercompany must be activated under Master Defaults plus the Department must have the Intercompany loan account specified to utilise this function. This may be run as frequently as reports are required however this routine may take time. It is suggested this routine be performed as part of the period end function. | |
| InventoryCCR: | Not to be used unless under instructions from SapphireOne support staff | |
| Key Unlock: | This is where data file authorisation codes are entered. | |
| Key Unlock Ext: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| LocationBal Update: | This function checks the number of each item held in the Base Inventory with the sum of the same item in ALL Locations. | |
| Multi Company: | This function will display the staging window for the multi company conversion process. It is used when you are ready to run the next stage but the system does not automatically display the window. | |
| NetPeriod0: | This function nets out the Period 0 debits and credits. | |
| Parent: | Not to be used unless under instructions from SapphireOne support staff | |
| ProcUpdate: | This function allows reading of specialist procedures saved on disc by SapphireOne | |
| ProjectTaskUpdateGLBudget: | This will update all General Ledger Budgets by your Project Inventory Budgets. Please not that all existing budgets will be overwritten | |
| RecalAllocTotals | This function will force the system to recalculate allocation totals. | |
| RecalSPAnalysis: | This function recalculates the sales people period from history Invoices | |
| RecalcYTD Balance: | This function will force the system to recheck the YTD balance: | |
| Recreatetran: | Copies transactions to Current for all sequence numbers specified in a text file. | |
| ReflexUpgrade Table: | Not to be used! Custom programming! | |
| ReindexAll: | This function rebuilds the indexes for the entire database. | |
| RemoveChar13 | This function removes ASCII character (13 – Carriage Return) from all Alpha-numeric fields but not including text fields. | |
| RemoveJGJ: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| RetreiveInvoices Lines: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| Reverse special: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| RunFRF: | This function executes a Foot Runner file. | |
| Serial Update: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| UnpostedSales Lines: | This function looks for Invoices and Purchases with a post date set but not marked as posted and marks them as posted. | |
| UpdateDelivery Date: | Not normally used! | |
| UpdateInventory Unposted: | Not to be used unless under explicit instructions from SapphireOne support staff | |
| YearEndMaster: | Not to be used unless under explicit instructions from SapphireOne support staff |
Bay Fix
Bondi Blue Bay Details
In setting up Bays the first task is to perform a Location Inquiry and create the Bays. You cannot assign Inventory items to Bays that do not exist. So to examine the current bay details in Bondi Blue go to: Inventory \ Inventory \ Location Inquiry. Select location 0 , zero in the Bondi Blue data file.
Note that it is a Location Inquiry and not an Inventory Locations Inquiry.
And when we select the Bay item in Bondi Blue BAY~0 the following details are displayed in the Bay Info Page in an Inventory Location Inquiry. Bays 1 and 6 contain stock.
Bay Fix Procedure
During the course of normal operations stock levels in bays may become incorrect for various reasons. This function will display the stock levels as determined from the Inventory items and these totals are displayed in the area above the bay list area. The stock levels in the bays themselves are displayed in the bay list area.
This is the screen displayed when the Bay Fix function is select from the Custom Tools menu:
To check the Bay stock levels, select the 
button. This will cause SapphireOne to display the pop up as seen to the right. It is asking the user to select a name and a location to save a file, as a tab delineated text file containing the bay details as seen below when opened using a spreadsheet program.
In the exported text file each inventory item that has bays is grouped as seen in the screen shot below. In each group the second last row contains the Bay totals and the last row has the totals for the Inventory item. Note that if SapphireOne determines that there are no errors the file will only contain the column headings.
If the numbers do not agree the user may select the 
button. SapphireOne will then display the pop up as seen below and note the warning!
The user also has the ability to manually adjust Bay stock levels place the cursor over the number, highlight it then enter the new number that you want for the adjustment.
Then enter in the new stock levels for each bay as required, then select the 
button to permanently write the changes into the data file.
Once completed you may re-run the function to check that all is correct.
The 
button works in the same way as the Verify Bay stock function. SapphireOne will ask what the file name is to be and where to save the text file.
Only in this case SapphireOne has determined that the Invoices affecting the bays have no errors, and is displaying the column headings in the text file only as seen to the left.
To correct any errors found when this function is run, the Invoices that have bays involved will have checked and any errors found in them will have to be fixed.
The function must only be used when there is a single Bay containing all of the Inventory items.
This will cause the bays that contain all of the inventory items to align its stock levels with the actual Inventory stock levels as reported by the Inventory Item.
Then select the button to save the changes.
If you use this option and if you find that some bays may be incorrect, it will cause major problems in your stock levels. Always check the result and if it is not correct immediately select the 
button to cancel any changes made.
Bay Management
When the 
button is selected SapphireOne will display the pop up as seen below, all bays that have no stock will be removed.
When the 
button is selected SapphireOne will display the pop up as seen below. And stock held in any duplicated bays will be included as well. Please check the results of this procedure when completed.
When the 
button is selected SapphireOne will display the standard pop up for the saving of a file. This is a text file that contains ID, Bay, Order and Reference. If there are no duplicates the file will only contain the headings as listed.
When the 
button is selected SapphireOne will remove all duplicate distances.
When the 
button is selected, SapphireOne asks for Inventory Location Inquiry and Bay name in two columns. SapphireOne will then proceed to move the item as entered to the as entered default bay
When the 
button is selected SapphireOne will look for a tab delineated text file with just two columns. A valid Inventory Location ID and a valid Bay Name. All items will be imported to the bays as entered. It will then import these items into the default bay which will have the lowest order number!
Bay Import
These five functions are very specific to Bays and provides the user with a means of importing bulk data into your data file when setting up Bays.
The import file must be a Tab delineated text file as for other Gateway import file.
Since this import function is very specific to Bays, the #??? syntax in column A is not required as in other Gateway Import files.
Column A must contain unique data so that SapphireOne recognises the data to be imported correctly. SapphireOne if it finds a record that is the same, it will overwrite the record with the data that is in the import file.
As in other gateway import files, the last row in column A must contain 4 %%%% symbols to indicate end of the import file to SapphireOne.
There are currently 5 Import procedures available.
| Import Bay Location: | Import Bay Location Distance: | |||
| A | Location ID (Must be valid Location ID) | A | From Bay Name | |
| B | Bay Location Name | B | To Bay Name | |
| C | Bay Order number (0->?) | C | Location ID (Must be a valid Location ID) | |
| D | Bay Level. | D | Bay Level. | |
| E | Start Distance | E | Distance |
| Import Bay Inventory: | |
| A | Inventory Location ID (MUST be a valid Inventory Location ID) |
| B | Bay Name (Exact) |
| C | Current Stock |
| D | Un-posted stock |
| E | Allocated Stock |
| F | Priority (0 ->? Determines which bay to first use when creating an invoice) |
| Import Bay Invoice Line: | |
| A | Location ID (MUST be a valid location ID) |
| B | Inventory Sequence number |
| C | Bay Name (Exact) |
| D | $ Order Value |
| E | $ Back Order Value |
| F | Quantity (Known as Delivery or Arrival as well) |
| G | Type (E.g. OVI PMP) |
| H | Date (format dd/mm/yyyy) |
Change ID
This function allows the changing, consolidation or correction of ID’s. Note that you have to tab forward after entering the new ID before the ACTION button will become active! This function has provision for the changing of ID’s in a number of tables. To check if the table you want is supported select the function and examine the drop-down list to see for what tables are currently supported. General Ledger, Inventory, Inventory Location, Clients, Vendors, Job Projects, Project Parent, Resources, General Ledger Class, Client Class, Vendor Class, Projects Class, Employee Class, Employee Department, Employee Allowance, Employee Award, Asset, Asset Class, Asset Location, Asset Location Class, Location, Sales Rep, Contacts, Manager and Users.
Change ID has the ability to import replacement IDs for any of the above tables. For example, if you had been using a 2 Alpha ID system for Employee IDs and you were elected at some time in the future to change that to an alpha numeric ID as your workforce had increased dramatically and you had a lot of repetition in the 2 Alpha IDs, you could then do a complete replacement for all the IDs with an Alpha Numeric ID system. The Import button allows the user to mass change IDs from a csv. file. The format of the file is the file, old ID, new ID, carriage return. The files are limited to the names used above i.e. Employee, Users, etc.
How to Use Import on Change ID
Column A should be the name of the table. This needs to match the wording on the Change ID screen. It only handles the tables listed in the screen e.g. employees, users. Column B is the old ID. Column C is the new ID. It should end with 4 percentage marks and be saved as a CSV.
Fix100%Alloc
It is not recommended that you use this function without first consulting SapphireOne support staff. It is mainly used to clear transactions that have a very small percentage either under or over allocated, e.g., 99.99% or 100.01%. One of the main sources of these transactions is from FX transactions where the exchange rate has altered. Another source is when rounding has occurred.
This tool is mainly used to clear transactions that have a very small percentage either under or over allocated, e.g., 99.99% or 100.01%. One of the main sources of these transactions is from FX transactions where the exchange rate has altered. Another source is when rounding has occurred.
You can review our Blog and YouTube channel for additional information and resources on SapphireOne ERP, CRM and Business Accounting software.