Entering Beginning Balances

Unless PhreeBooks is set up on a new company, there will probalby be a need to transfer beginning balances from your old accounting system. The follwing tables define the data structures required to import inventory valuation and balances as well as Payables and Receivables balances.

Inventory

Before beginning balances can be set up, inventory items need to be imported (or entered) into PhreeBooks. Refer to manual pages on importing/exporting data for the procedure and more information.

NOTE: If any part of the beginning balances needs to be re-entered, the only option at this time is to purge the GL and history records through the General Ledger -> Utilities -> Purge Journal Entries option. Note that purging the journal entries will clean out the journals and history records. It will not affect the customer or vendor accounts.

File format

Type: csv (comma separated variables)
Delimiter: comma (,)
Qualifier: double-quote (")
File extension must be .csv or .txt
Header Lines: 1 (header fields as identifed in the table below)

Column contents

Each entry of the inventory beginning balances should have to following information. Required fields are marked with a *.

Field Name
Description
*sku The item sku as defined on the inventory database. Must match exactly, case sensitive.
*gl_acct The General Ledger account ID to use to offset the inventory value. This account should be an 'equity-doesn't close' type of account.
*inv_gl_acct The General Ledger account ID to store the inventory value. This should be and 'inventory' type of account.
*quantity The quantity of the sku to set as the beginning inventory balance.
*total_amount The total value of the inventory item in the default currency. The unit cost will be calculated by dividing the total cost by the quantity.
 

Serialized items - cannot be imported at this time

Notes:

  1. The beginning balances script will verify the sku is valid, GL accounts are valid. If any of these are found to be true, the script will terminate and no entries will be added to the databases. The script will also verify the the quantity is non-zero. If a zero quantity is found, the script will skip the entry and proceed to the next entry.

Purchase Orders

Importing purchase orders can be performed without first importing the your vendor accounts. The import routine will create the account if it cannot be found. POs with multiple line items must be consecutive to ensure that they are included in the same journal entry.

File format

Type: csv (comma separated variables)
Delimiter: comma (,)
Qualifier: double-quote (")
File extension must be .csv or .txt
Header Lines: 1 (header fields as identifed in the table below)

Column contents

Each entry of the purchase order should have to following information. Required fields are marked with a *.

Field Name
Description
*account_id The Short Name of the vendor that the purchase order should be entered against. This must match exactly to the short name of the vendor existing in the system (or it will be created).
*gl_acct The General Ledger account ID to apply the purchase order. This should be an 'accounts payable' type of account. This account must be the same for all line items of the same PO number.
*inv_gl_acct The General Ledger account ID to use for the inventory line item. This account should be an 'inventory' or 'expense' type of account.
total_amount The total value of the purchase order in the default currency.
freight_total The total freight amount of the purchase order.
tax_total The total sales tax amount of the purchase order.
*order_id Purchase Order number of this entry. Used for account tracking and payment identification.
*post_date Date of the purchase order.
*bill_primary_name The primary name of the accounts mailing address. Can be a company name or inidvidual name.
bill_contact The contact (or attention) line for the account.
*bill_address1

The first mailing address line.

bill_address2 The second mailing address line.
*bill_city_town The mailing city or town.
*bill_state_province The mailing state or province.
bill_postal_code The mailing postal/zip code .
*bill_country_code The ISO 3 character country code. (USA, CAN, AUS, etc.)
telephone1 The account primary telephone number.
telephone2 The account altrernate telephone number.
fax The account fax number.
email The account e-mail address.
website The acount website.
*sku The line item SKU. Only one SKU per line.
*description The line item description of the SKU.
*quantity The quantity ordered.
*total_cost

The line item total price. The unit price will be calculated by (total price / quantity).

Notes:

  1. The billing information will be used as the bill to address for purchase orders.
  2. The script will verify the account (Short Name) is valid (or a new account will be created).
  3. GL accounts will be verified.
  4. If the post date is not within the currently defined fiscal years (including bad format) the record will be placed in accounting period 1.
  5. If any other errors are found, the script will terminate and no entries will be added to the databases.
  6. If the beginning balance file is imported successfully, DO NOT REPEAT the import. The new entries will be added to the prior successful import and duplicate the payables. The import operation will not be posted unless the entire file is read in correctly and the success message is displayed.
  7. The ship to address is assumed to be the company default address.

Accounts Payable

Importing payables can be performed without first importing the your vendor accounts. The import routine will create the account if it cannot be found.

File format

Type: csv (comma separated variables)
Delimiter: comma (,)
Qualifier: double-quote (")
File extension must be .csv or .txt
Header Lines: 1 (header fields as identifed in the table below)

Column contents

Each entry of the accounts payable beginning balances should have to following information. Required fields are marked with a *.

Field Name
Description
*account_id The Short Name of the vendor that the purchase order should be entered against. This must match exactly to the short name of the vendor existing in the system (or it will be created).
*gl_acct The General Ledger account ID to apply the purchase order. This account should be an 'equity-doesn't close' type of account.
*inv_gl_acct The General Ledger account ID to use for the inventory line item. This account should be an 'inventory' or 'expense' type of account.
*total_amount The total value of the payable in the default currency.
freight_total The total freight amount of the purchase order.
tax_total The total sales tax amount of the purchase order.
order_id Invoice number of this entry. Used for account tracking and payment identification. If left blank, the waiting for invoice box will be checked.
*post_date Date of the invoice. Used calculate aging of payables.
*bill_primary_name The primary name of the accounts mailing address. Can be a company name or inidvidual name.
bill_contact The contact (or attention) line for the account.
*bill_address1

The first mailing address line.

bill_address2 The second mailing address line.
*bill_city_town The mailing city or town.
*bill_state_province The mailing state or province.
bill_postal_code The mailing postal/zip code .
*bill_country_code The ISO 3 character country code. (USA, CAN, AUS, etc.)
telephone1 The account primary telephone number.
telephone2 The account alternate telephone number.
fax The account fax number.
email The account e-mail address.
website The acount website.

Notes:

  1. The default company information will be used as the ship to address for payables and only a single line item will be entered identified as 'Beginning Balances Entry'.
  2. The script will verify the account (Short Name) is valid (or a new account will be created).
  3. GL accounts will be verified.
  4. If the invoice is left blank or null, the Waiting For Payment flag will be set.
  5. If the post date is not within the currently defined fiscal years (including bad format) the record will be placed in accounting period 1.
  6. The script will verify the the invoice amount is non-zero. If a zero amount is found, the script will skip the entry and proceed to the next entry. In this case, the skipped records will be identified by row number.
  7. If any other errors are found, the script will terminate and no entries will be added to the databases.
  8. If the beginning balance file is imported successfully, DO NOT REPEAT the import. The new entries will be added to the prior successful import and duplicate the payables. The import operation will not be posted unless the entire file is read in correctly and the success message is displayed.
  9. Vendor history accounts will not be updated by this procedure.
  10. The ship to address is assumed to be the company default address.

Sales Orders

Importing sales orders can be performed without first importing the your custoemr accounts. The import routine will create the account if it cannot be found. SOs with multiple line items must be consecutive to ensure that they are included in the same journal entry.

File format

Type: csv (comma separated variables)
Delimiter: comma (,)
Qualifier: double-quote (")
File extension must be .csv or .txt
Header Lines: 1 (header fields as identifed in the table below)

Column contents

Each entry of the sales order should have to following information. Required fields are marked with a *.

Field Name
Description
*account_id The Short Name of the customer that the purchase order should be entered against. This must match exactly to the short name of the customer existing in the system (or it will be created).
*gl_acct The General Ledger account ID to apply the sales order. This should be an 'accounts receivable type of account. This account must be the same for all line items of the same sales order number.
*inv_gl_acct The General Ledger account ID to use for the inventory line item. This account should be an 'inventory' or 'expense' type of account.
total_amount The total value of the sales order in the default currency.
freight_total The total freight amount of the sales order.
tax_total The total sales tax amount of the sales order.
*order_id Sales Order number of this entry. Used for account tracking and payment identification.
*post_date Date of the sales order.
*bill_primary_name The primary name of the accounts mailing address. Can be a company name or inidvidual name.
bill_contact The contact (or attention) line for the account.
*bill_address1

The first mailing address line.

bill_address2 The second mailing address line.
*bill_city_town The mailing city or town.
*bill_state_province The mailing state or province.
bill_postal_code The mailing postal/zip code .
*bill_country_code The ISO 3 character country code. (USA, CAN, AUS, etc.)
telephone1 The account primary telephone number.
telephone2 The account altrernate telephone number.
fax The account fax number.
email The account e-mail address.
website The acount website.
ship_primary_name The primary name of the accounts shipping address. Can be a company name or inidvidual name.
ship_contact The contact (or attention) line for the account.
ship_address1

The first shipping address line.

ship_address2 The second shipping address line.
ship_city_town The shipping city or town.
ship_state_province The shipping state or province.
ship_postal_code The shipping postal/zip code .
ship_country_code The ISO 3 character country code. (USA, CAN, AUS, etc.)
*sku The line item SKU. Only one SKU per line.
*description The line item description of the SKU.
*quantity The quantity ordered.
*total_cost

The line item total price. The unit price will be calculated by (total price / quantity).

Notes:

  1. The billing information will be used as the bill to address for sales orders.
  2. The script will verify the account (Short Name) is valid (or a new account will be created).
  3. GL accounts will be verified.
  4. If the post date is not within the currently defined fiscal years (including bad format) the record will be placed in accounting period 1.
  5. If the sales order file is imported successfully, DO NOT REPEAT the import. The new entries will be added to the prior successful import and duplicate the sales orders. The import operation will not be posted unless the entire file is read in correctly and the success message is displayed.

Accounts Receivable

Before beginning balances can be set up, customers need to be imported (or entered) into PhreeBooks. Refer to manual pages on importing/exporting data for the procedure and more information.

File format

Type: csv (comma separated variables)
Delimiter: comma (,)
Qualifier: double-quote (")
File extension must be .csv
Header Lines: 1 (header fields as identifed in the table below)

Column contents

Each entry of the account receivables beginning balances should have to following information. Required fields are marked with a *.

Field Name
Description
*account_id The Short Name of the vendor that the invoice should be entered against. This must match exactly to the short name of the vendor existing in the system.
*gl_acct The General Ledger account ID to apply the receivable. This should be an 'accounts receivable' type of account.
*inv_gl_acct The General Ledger account ID to use to offset the payable value. This account should be an 'equity-doesn't close' type of account.
*total_amount The total value of the receivable in the default currency.
freight_total The total freight amount of the invocie.
tax_total The total sales tax amount of the invoice.
order_id Invoice number of this entry. Used for account tracking and payment identification. If left blank, the waiting for invoice box will be checked.
*post_date Date of the invoice. Used calculate aging of payables.
*bill_primary_name The primary name of the accounts mailing address. Can be a company name or inidvidual name.
bill_contact The contact (or attention) line for the account.
*bill_address1

The first mailing address line.

bill_address2 The second mailing address line.
*bill_city_town The mailing city or town.
*bill_state_province The mailing state or province.
bill_postal_code The mailing postal/zip code .
*bill_country_code The ISO 3 character country code. (USA, CAN, AUS, etc.)
telephone1 The account primary telephone number.
telephone2 The account alternate telephone number.
fax The account fax number.
email The account e-mail address.
website The acount website.
ship_primary_name The primary name of the accounts shipping address. Can be a company name or inidvidual name.
ship_contact The contact (or attention) line for the account.
ship_address1

The first shipping address line.

ship_address2 The second shipping address line.
ship_city_town The shipping city or town.
ship_state_province The shipping state or province.
ship_postal_code The shipping postal/zip code .
ship_country_code The ISO 3 character country code. (USA, CAN, AUS, etc.)

Notes:

  1. The beginning balances script will verify the account (Short Name) is valid, GL accounts are valid, invoice number exists, and the date is invalid (including bad format). If any of these are found to not match, the script will terminate and no entries will be added to the databases. The script will also verify the the amount is non-zero. If a zero amount is found, the script will skip the entry and proceed to the next entry.
  2. If the beginning balance file is imported successfully, DO NOT REPEAT the import. The new entries will be added to the prior successful import and duplicate the receivables. The import operation will not be posted unless the entire file is read in correctly and the success message is displayed.
  3. Vendor history accounts will not be updated by this procedure.