1 Reply by ephraim

(14 replies, posted in Accounts Receivable)

OK, first off, I have to agree with Janusz. It's not really the way to go by accessing the database directly, especially if I have to redo all the work every time a major new upgrade comes out.

We have used a variety of windows/unix/web accounting programs over the last 14 years and frontaccounting is by far the best we have used for our purposes. I keep thinking that gnue will go somewhere, but it been 14 years and counting since I started following that. Whatever we do, it has to work with frontaccounting.

Here is what we are trying to do...

We are running two business under 1 set of books. We have two DBA names because the businesses are so different and unrelated (Bookbinding and Wireless High Speed Internet).

Frontaccounting is set up under the Bookbinding business because that is the main one. The Wireless is a side effort but uses a different name because, lets face it, who would by Internet access from a old book guy :-)

Anyway, we have the bandwidth and threshold usage stored in an autogenerated rrd database. We also have a list of customers stored in a mysql database with what services they get billed for very month.

What I wanted was a script that would access the autobill information from mysql, then access the rrd database and determine if any additional bandwidth should be billed to the customer, generate an email invoice/statement combination and then insert the invoice information into frontaccounting as if I had manually inserted a direct invoice. This script would run just after midnight on the first of every month.

I wrote a first pass prototype of the script in bash (what an ugly kludge of a script!) It's 500 lines of database calls, and string manipulation. It was relatively easy to figure out what frontaccounting was doing by looking at the log, but I really would hate to do that again. If nothing else, the process was very informative of what frontaccounting was doing. (rather more complicated than some of the other accounting programs have been)

I wouldn't even bother posting the kludge. It's obviously not the way to go.

I've never really done much php scripting, but I guess now is as good a time as any. Can you run a php script from a cron job?

Go to forum: Accounts Receivable

2 Reply by ephraim

(14 replies, posted in Accounts Receivable)

having looked at the cart_class.php file, it looks pretty simple. If I was doing something from php, that's certainly the way I would go.

However, the program I have to integrate is not php. what I have to do is write a glue script that will move data from it (stored in an rrd database and a MySQL database) to the frontaccounting database and make it look like I created a direct invoice from within frontaccounting.

It turns out the easiest way to see what was going on was to log the MySQL calls that frontaccounting made during the generation of a direct invoice. By manipulating the log it's pretty clear what is happening. There are lots of SELECT queries (which I expected to find) and three distinct transactions. One for the order, one for the delivery and one for the invoice. You really only have to pay attention to the UPDATE and INSERT statements to clearly understand it.

Given this information, I think I can write that glue script to run under a cron job. I still have a bit more digging to do before I try though. Frontaccounting uses a quite a few numerical symbols (such as "type" for transaction, etc..) that I need to get a handle on.

Go to forum: Accounts Receivable

3 Topic by ephraim

(14 replies, posted in Accounts Receivable)

I was wondering if anyone could inform me of the flow of the database access and updating when making a direct invoice (sans payment)

I want to integrate a separate program that will auto-generate invoices based on some dynamic information contained in another database, then insert the appropriate data in frontaccounting as if frontaccounting had done it itself.

I hav no problme with database manipulation, but cannot find documentation on what tables are changed in frontaccounting with a direct invoice.

Thanks

Go to forum: Accounts Receivable

4 Reply by ephraim

(5 replies, posted in Accounts Receivable)

Doesn't happen too often, but it does happen. I do bookbinding and I don't take payment until the job is complete. Sometimes the customer decides (probably after a tongue lashing from the spouse) that they can buy a new book cheaper than fixing the old one (which is true). I can generally sell the old book  and reduce the loss, but it is a danger of doing business this way.

Go to forum: Accounts Receivable

5 Topic by ephraim

(5 replies, posted in Accounts Receivable)

I was wondering if anyone had a procedure for writing off a bad debt from a customer under FrontAccounting. I know I can make a journal entry to a bad debt expense account from Accounts receivable, but that doesn't take care of the customer balance in the reports.

Any ideas?

Thanks

Go to forum: Accounts Receivable

6 Reply by ephraim

(2 replies, posted in Installation)

If world writeable bothers you, you can change the owner of the directories to the owner and group of whatever your web server is running as and then set them user writeable with world write permissions set to off.

Go to forum: Installation

7 Reply by ephraim

(27 replies, posted in Banking and General Ledger)

Hmm, I'm trying to understand how FrontAccounting actually closes the books and I'm still struggling.

Here is what I normally do on Dec 31:

1) A credit entry to every expense account with the total expense amount debited to a temporary account.
2) A debit entry to every income account with the total income amount credited to the temporary account.
3) The profit (or loss) debited (or credited) to the temporary account and the Retained Earnings credited (or debited) to zero out the temporary account.

I see that I can still do that in FrontAccounting and when the books are closed, no automatic magic happens. However, after reading this thread, I am confused as to what exactly FrontAccounting tries to do on its own if I let it close the books without manually making these entries.

For instance, I have two long term liability accounts -> Startup costs (which is the capitalized start up losses) and Profit/Loss which is the cumulative retained earnings. I told the system to use my temporary account as 'Profit/Loss year' account and the Profit/Loss account as the 'Retained Earnings' account.

In 2009, I had a total loss of $269.31. I see $269.31 Debited to the 'Profit & Loss' Account and 269.31 Credited to the temporary account.

So the total amount debited to the Retained earnings is correct, but none of the income or expenses are zeroed out and I now have this temporary account carrying a credit of $269.31.

What exactly is FrontAccounting doing here?
What are the benefits of doing it that way vs what I have been doing?

Thanks

Go to forum: Banking and General Ledger

8 Reply by ephraim

(8 replies, posted in Accounts Receivable)

itronics wrote:

I think there is some misunderstanding of the process. When you have some payment to supplier and some part of it (but not whole amount) is already allocated to invoice, when you select again the payment you have all allocated invoice displayed with 'This allocation' input filled. This is presented here to enable reallocation of the payment, however it was previously allocated.
.
Janusz

I wouldn't doubt that I am misunderstanding. I am learning accounting at the same time I am running this program. :-)

I'm not sure I understand your last statement though. perhaps If someone walked me through the expected process?

Here is what I am doing now...

Business is a bookbinding setup. Customer gives me a book to rebind and I fill out a paper slip with the customers desired options on it. This slip stays with the book as it goes through the whole process. (tried a workorder process at some point and it just didn't work. I need a paper slip with the book). When I'm finished with the book:

1) Create direct invoice with delayed payment.
2) Print invoice and place it in the book (call customer to pick up the book)
3) When customer arrives, I accept payment through the customer payment process.
4) Customers invoice pops up on the customer payment screen and I select the 'All' link which then fills in the 'This Allocation' with the whole payment amount as well as the total amount.
5) select the 'Add Payment' button

Now the next time that customer brings me a book, I go through the same process except at step '4' I now see the new invoice as well as the original invoice with both invoices showing the full amount under 'Left to Allocate'

If I look at Aged Customers, I see a whole bunch of customers with '0' balances. If I inspect the database, I see the 'alloc' field contains '0' in the transactions table for the customer.

I can then go to the payment allocation field and manually allocate that payment to the customer which then fills in the proper amount in the 'alloc' field.

If, instead of selecting the 'All' link in the original process, I instead manually fill in the 'This Allocation' field, it appears the same on the screen, but the database actually gets updated with the proper amount in the 'alloc' field of the table and I do not have to manually allocate the payment.

It's not a big deal, just doesn't seem to work the way I thought it would.

Go to forum: Accounts Receivable

9 Reply by ephraim

(8 replies, posted in Accounts Receivable)

ok. I looked into the allocation process. this is definitely different than anything I have done before...

The unallocated payments can be allocated to the invoices through that process and then those invoices cease to show up in the customer payment or vendor payment screens and the fields are filled out properly in the database.

However, i now see why some show and some do not... if I manually add the value to the allocate field in the payment screen, the payment is allocated properly. if i click on the 'All' link and allow the screen to automatically fill in the value, the program does so on the screen, but leaves the allocation value at '0' when it updates the database. that's definitely odd behavior, but workable if I just remember not to use the 'All' link.

Go to forum: Accounts Receivable

10 Reply by ephraim

(8 replies, posted in Accounts Receivable)

hmm, ok - same problem on the supplier invoices and payments. while inspecting the "0_supp_trans" table, I see where on the supplier invoices that repeatedly show up the field 'ov_amount' has the amount of invoice or payment (appears to be 'type' = 20 for invoice and 'type' = 22 for payment.

On the invoice that does not show up, the field 'alloc' has the same amount in it as 'ov_amount'.

On the invoice that does show up repeatedly, the field 'alloc' is empty in both types of entries.

it appears that the program is not updating these fields unless a partial payment is made.

On the plus side, the reports still show the correct balance and payment information.

Go to forum: Accounts Receivable

11 Reply by ephraim

(8 replies, posted in Accounts Receivable)

An update on the first problem...

If an invoice is partially paid, then fully paid, the program behaves as I expect it to. If the invoice is fully paid right away, it does not.

However, when you fully pay an invoice, the problem remains. I think its a bug. (running 2.2.6)

Randy

Go to forum: Accounts Receivable

12 Topic by ephraim

(8 replies, posted in Accounts Receivable)

Perhaps I'm misunderstanding how this work, but when I enter a customer invoice and then make a payment on that invoice, then enter another invoice to the same customer (perhaps for another item) and enter the payment screen, the original invoice still shows up even if it was paid in full. There is no indication that it was paid in full either. Am I doing something wrong?

It seems to me that the only invoices that should show up in the customer payment screen are thos that are unpaid or partially unpaid.

Also, how do you handle a customer that pays too much? (yeah, I know, it's a dream problem, but it happens) What I would like is an option to apply the excess payment to a GL account from within the customer payment screen.

Any ideas?

Thanks

Go to forum: Accounts Receivable