Purpose

Here you can add new companies or change connection data for companies already in use.

Procedure

  • From the default company, navigate to Setup => Create/Update Companies
  • Note that the default company's database and other credentials will be shown - alter them to suit your needs.
  • The next available table prefix will be automatically shown.

Deleting FA Tables for a single company

There are times when we either wrongly create a company with it's tables in another database than the intended one. We then manually create another database (if it did not exist) and then copy over the tables from the old one. Now we need to delete the tables in the old database pertaining to this new company. MySQL does not have a means to delete tables using wildcards for it's names. The following will generate the necessary SQL (replace 1_ with your prefix and frontac000 with your target database name) to get it done:

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_name LIKE '1_%' AND table_schema = 'frontac000';

The above statement may not fit in all the 78/79 tables that FA has since the heap buffer may have overflowed and the result may have to be repeatedly executed for the remaining tables to appear or increase the heap size. Alternatively, execute the following to get a list of individual drop statements:

SELECT CONCAT( 'DROP TABLE ', CONCAT(table_name), ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_name LIKE '1_%' AND table_schema = 'frontac000';

For a standard FA v2.3.x install, the following will remove all such tables (replace 1_ with your prefix):

DROP TABLE 1_areas,1_attachments,1_audit_trail,1_bank_accounts,1_bank_trans,1_bom,1_budget_trans,1_chart_class;
DROP TABLE 1_chart_master,1_chart_types,1_comments,1_credit_status,1_crm_categories,1_crm_contacts,1_crm_persons;
DROP TABLE 1_currencies,1_cust_allocations,1_cust_branch,1_debtor_trans,1_debtor_trans_details,1_debtors_master,1_dimensions;
DROP TABLE 1_exchange_rates,1_fiscal_year,1_gl_trans,1_grn_batch,1_grn_items,1_groups,1_item_codes,1_item_tax_type_exemptions;
DROP TABLE 1_item_tax_types,1_item_units,1_loc_stock,1_locations,1_movement_types,1_payment_terms,1_prices,1_print_profiles;
DROP TABLE 1_printers,1_purch_data,1_purch_order_details,1_purch_orders,1_quick_entries,1_quick_entry_lines,1_recurrent_invoices,1_refs;
DROP TABLE 1_sales_order_details,1_sales_orders,1_sales_pos,1_sales_types,1_salesman,1_security_roles,1_shippers,1_sql_trail;
DROP TABLE 1_stock_category,1_stock_master,1_stock_moves,1_supp_allocations,1_supp_invoice_items,1_supp_trans,1_suppliers,1_sys_prefs;
DROP TABLE 1_sys_types,1_tag_associations,1_tags,1_tax_group_items,1_tax_groups,1_tax_types,1_trans_tax_details,1_useronline;
DROP TABLE 1_users,1_voided,1_wo_issue_items,1_wo_issues,1_wo_manufacture,1_wo_requirements,1_workcentres,1_workorders;

Tips and Tricks

  • Never use the same database for two or more companies unless you really trust every admin on all companies or they all share a common admin. As Backup and Restore area is not reserved for site admin, but available for any single company admin, tables which belong to other companies can be overwritten accidentally or intentionally without warning.
  • FA caches javascript code under company/#/js_cache (# is the company number), but the cache is cleared automatically on user login. When migrating / copying companies where users are logged in, care should be taken to manually purge it.