Category: Using SQL-Ledger

SQL-Ledger Forum Posts, Using SQL-Ledger

Incorrect COGS after editing invoices

The issue with the Cost of Goods Sold (COGS) going out of sync in SQL-Ledger is a known problem, especially when editing past invoices. Since SQL-Ledger uses the FIFO (First-In, First-Out) method for inventory costing, modifying historical transactions can disrupt the accuracy of your COGS calculations.

Possible Solutions:

  1. Purchase the COGS Reposting Script:
    • The developers of SQL-Ledger offer a script that recalculates and fixes COGS across all invoices.
    • You can purchase the script directly from their website:
    • After purchasing, you’ll receive instructions on how to run the script to correct your COGS entries.
  2. Manual Reconciliation:
    • If purchasing the script isn’t an option, you can manually adjust the affected transactions.
    • Steps to follow:
      • Identify all invoices that have been edited and have incorrect COGS entries.
      • Adjust the inventory and COGS accounts through general ledger entries.
      • Note: This method can be time-consuming and may introduce errors if not done carefully.
  3. Avoid Editing Past Invoices:
    • To prevent this issue in the future, try to avoid making changes to invoices that have already been posted.
    • If adjustments are necessary, consider creating adjustment entries or credit notes rather than editing the original invoice.
  4. Update SQL-Ledger Version:
    • Ensure you’re using the latest version of SQL-Ledger, as updates may include fixes or improvements to inventory and COGS handling.
    • Always back up your data before performing an upgrade.
  5. Consult the Community:
    • Check the SQL-Ledger forums or user groups for any shared scripts or alternative solutions provided by other users.
    • Sometimes, community members develop their own tools to address common issues.

Important Considerations:

  • Backup Your Data: Before running any scripts or making significant changes, make sure to back up your database to prevent data loss.
  • Test in a Safe Environment: If possible, test any scripts or adjustments in a non-production environment first.
  • Seek Professional Advice: If you’re unsure about the process, consider consulting with an accountant or a professional familiar with SQL-Ledger.

(From SQL-Ledger Forum User Post)

SQL-Ledger Forum Posts, Using SQL-Ledger

Issue with Editing Assemblies in SQL-Ledger

Original Question by arblake (November 9, 2009):

I have many assemblies and sub-assemblies. I created a new assembly, added items, and saved it. Now I wish to add more items. After adding, updating, and saving, the assembly shows the correct dollar value, but when I open the assembly, the added parts are not listed. Why is this happening, and how do I resolve the problem?

Response by dws (November 17, 2009):

You cannot change an assembly which has been sold or stocked by simply editing it. You have to use the Changeup function to make the changes.

Summary and Solution

Understanding the Issue

  • Problem: After adding new items to an existing assembly and saving it, the assembly’s dollar value updates correctly, but the newly added parts do not appear when reopening the assembly.
  • Cause: This issue occurs because the assembly has already been sold or stocked, and direct edits to such assemblies are not permitted in SQL-Ledger’s standard editing mode.

Solution: Use the Changeup Function

  • Changeup Function: SQL-Ledger provides a Changeup function specifically designed to modify assemblies that have already been sold or stocked.
  • Steps to Resolve:
    1. Access the Changeup Function:
      • Navigate to the assembly you wish to modify.
      • Look for the Changeup option, which may be located in the assembly’s menu or toolbar.
    2. Modify the Assembly:
      • Use the Changeup interface to add or remove items from the assembly.
      • Update quantities, descriptions, or other relevant details as needed.
    3. Save Changes:
      • After making the necessary adjustments, save the changes through the Changeup function.
      • This ensures that all changes are properly recorded and reflected in the system.

Why Direct Editing Doesn’t Work

  • Integrity of Historical Data: SQL-Ledger prevents direct edits to sold or stocked assemblies to maintain the integrity of historical transaction data.
  • Proper Tracking: Using the Changeup function allows the system to track changes over time, ensuring accurate inventory management and accounting.
SQL-Ledger Forum Posts, Using SQL-Ledger

How to Create a Recurring Transaction in SQL-Ledger

Original Question:

Posted by Dan on Friday, October 23, 2009

OK I’ve been trying to figure out how to create a recurring transaction from a new invoice, not an already created invoice.

I click on Sales Invoice, enter product and amount and date on the invoice. I also enter a new description and change the invoice date to 11-01-2009. Next, I clicked on Schedule and entered another description here, set start date to 11-01-2009, checked off Email Invoice, and then clicked Save. The recurring transaction brings me back to the invoice and does not save anything in the recurring transaction list.

What am I missing?

Thanks,

Dan

Response from Marcus (localguru), Germany, on Saturday, October 24, 2009

Hi Dan,

If I understand the recurring concept of SQL-Ledger correctly, the first invoice is never a recurring invoice or transaction. In your example, you need to save the invoice itself first to create a recurring transaction.

However, if you set the date of the invoice and the first recurring transaction to the same date (e.g., 11-01-2009), you will create an invoice with a future date (11-01-2009) and not a recurring transaction starting on that date.

Important Notes

  • Initial Invoice Date: The date of the initial invoice should reflect when the first transaction occurred, not necessarily the start date of the recurring series.
  • Future Dated Invoices: Creating an invoice with a future date may not generate the recurring transaction as expected and can cause accounting inconsistencies.
  • Recurring Transaction Creation: Recurring transactions are typically based on existing, saved transactions. Saving the initial invoice provides a template for the recurring entries.

Summary

To successfully create a recurring transaction in SQL-Ledger:

  • First, create and save the initial invoice with the correct date and details.
  • Then, set up the recurring schedule from the saved invoice, specifying the start date and frequency.
  • Finally, verify that the recurring transaction has been saved and is listed correctly.

By following these steps, you should be able to generate recurring invoices that will automatically be created and, if configured, emailed to your customers at the specified intervals.

SQL-Ledger Forum Posts, Using SQL-Ledger

How to Raise a VAT-Only Invoice in SQL-Ledger

In August 2009, a user named Dominic Raywood (username “RubiconCSL“) posted on the SQL-Ledger User Forum seeking assistance on how to raise a VAT-only invoice using SQL-Ledger version 2.0.8.

Issue

Dominic was working with a client who used self-invoicing, meaning the client raised invoices on his behalf. The initial invoice issued did not include VAT because they hadn’t received his VAT registration details. As a result, Dominic needed to raise an invoice solely for the VAT amount corresponding to that initial invoice.

Question

How can I raise my own invoice just for the VAT for that initial invoice in SQL-Ledger?

Solution Provided

A forum member, “dws,” offered the following solution:

  1. Add an Accounts Receivable (AR) Transaction:
  • Navigate to the AR Transaction module in SQL-Ledger.
  1. Uncheck the Tax Calculation Option:
  • When entering the transaction, uncheck the tax option so that tax is not automatically calculated on the amount.
  1. Enter the VAT Amount Manually:
  • Input the VAT amount as the transaction amount.
  1. Ensure Proper Tax Reporting:
  • Using an AR transaction without a base amount but with a tax component creates a tax-only transaction or invoice.
  • This method ensures that the VAT amount is included in tax reports. If a General Ledger (GL) transaction were used instead, the tax would not be included in the tax reports.

Dominic’s Feedback

After implementing the suggested steps, Dominic confirmed that the solution worked:

“Brilliant, thanks. Just what I needed—I had overlooked your ‘uncheck the tax’ bit. All working now.”

Summary

To raise a VAT-only invoice in SQL-Ledger:

  • Create an AR Transaction without a base amount.
  • Uncheck the Tax Calculation to prevent automatic tax computation.
  • Manually enter the VAT amount as the transaction amount.
  • This approach generates a tax-only invoice, ensuring the VAT is correctly recorded and appears in tax reports.

Notes

  • This method is useful for situations where VAT needs to be invoiced separately due to initial omissions.
  • Always verify that this process complies with your local tax regulations and accounting standards.

By following these steps, you can effectively issue a VAT-only invoice in SQL-Ledger, ensuring accurate accounting and compliance with tax reporting requirements.