(4 replies, posted in Accounts Receivable)

nailed this a while back, but posting here in case I need it later, and in case it helps others:

                //text_row(_('Invoice notice:'), 'memo', sprintf(_("Recurrent Invoice covers period %s - %s."), $from, add_days($to, -1)),
        $newto = add_months($to, $myrow['monthly']);
        $newto = add_days($newto, $myrow['days']);
        text_row(_('Invoice notice:'), 'memo', sprintf(_("Recurrent Invoice covers period %s - %s."), $to, add_days($newto, -1)),


I have a client that gets two recurring invoices from me, and it was pointed out to me that the invoices I am sending them do not indicate which invoice is for which service.  The comments in the original invoice do note this information, but it is not being included in the subsequent recurring invoices.

I see this topic was covered way back in 2009:


wondering how I can implement this?

I voided the delivery note, that restored inventory.  I could/can not void or cancel the sales order, canceling the order says it cancels undelivered parts, but the whole order is still listed.  But redid the invoice as a direct invoice and it worked this time.  crisis averted.

Today I was creating a direct sales invoice.  I got an error 1406 or something about data too long, so I chased that down and updated the column field in the sales_orders table from tinytext to mediumtext.  when I processed the invoice, it actually came up to the sales order screen saying that the sales order had been processed.  So from there I decided to make a delivery, and got the same error which I fixed by changing the memo_ column in the comments table from tinytext to mediumtext.  After that I was able to process the dispatch, but when I went from there to generate the invoice I got the following error in the red bar:

Undefined index: debtor_no in file: /var/www/fa.computerisms.ca/sales/includes/sales_db.inc at line 282

along with the message

This delivery has all items invoiced. There is nothing to modify.

If I go to print the invoice it doesn't exist, if I go to customer payments, the invoice is not listed to be paid; there is no place I can find the invoice exists, but items have been removed from inventory.  Additionally, if I go to print the delivery, all I get is the comments, none of the items are listed. 

This particular invoice had about a dozen line items, but I sent out about a dozen of them prior that had one or two line items with no problems.

Does anyone have any idea how I can fix this without undoing the whole day's work by restoring from backup?


(16 replies, posted in Accounts Receivable)

thanks for this, seems to have worked for me.

For the next person figuring out how to use it; unpack the zip file so that the merge_customers.php file is in your sales directory, then call it from your browser like so:


Hi Guys, thank you both very much for your reply.

Regarding comment #5, I would suspect this is plaguing me because of the original problem.  given that I have used inventory adjustments before without issue, and it happens to be only this item that is the problem, and this item is having problems elsewhere, then this items seems to be the common denominator.  My intuition tells me it is because I generated a customer invoice on the received items before I entered a supplier invoice to pay for them, but I haven't taken the time to devise a test to prove the hypothesis.

Regarding comment #6; sadly the price of being busier with business is less time to devote to these projects, however, I will endeavour at some point to reproduce this issue if I can.  Also, I did try to void the purchase order, but it doesn't seem to be an option in the list of things I can void.  I am also worried that since I sold those items to a customer already, I might make things worse by voiding the PO of the items I sold.  Again in this case I need to export the existing system to a sandbox so I can mess with it, but right now the big priority is to get invoices out the door and money in the door, so I can go make more money.

Regarding post #7; yes, I had already looked through that table.  As far as date formats go every thing looks to be in order, format is yyyy-mm-dd for all entries, though there are a smattering of 0000-00-00 entries here and there.  I did start tracing the trans_no to see if I could attach those 0000-00-00 numbers to this item in some way, but didn't get far.  I also tried hard-coding dates in the code where I found a $date_ variable in a few different files, in the hopes I could just get the program to accept the supplier invoice.  I was also in the process of getting mysql debugging going so I could try to find the exact query that was failing, but phone keeps ringing and haven't had time to finish that process yet. 

I will see if I can put some more time to this over the weekend or next week...


inventory adjustment gives same error, but one more line:

Undefined property: line_item::$qty in file: /var/www/fa.computerisms.ca/inventory/includes/db/items_adjust_db.inc at line 36
DATABASE ERROR : cannot add journal entry
error code : 1292
error message : Incorrect date value: '' for column `fa_computerisms_ca`.`journal`.`event_date` at row 1

quantity I have entered in the adjustment items section is -4. 

dunno what else to do.  Sorry I broke your program wink

I got it in my head that I can make a new direct invoice, so I have the items received again, and this time paid for.

Now, in trying to undo the already received items; If I try to void the Purchase Order Deliver I used to initially receive the items, I get the same Mysql error:

DATABASE ERROR : cannot add journal entry
error code : 1292
error message : Incorrect date value: '' for column `fa_computerisms_ca`.`journal`.`event_date` at row 1

If I go under supplier invoices and try to remove the items there, I also get the same error.  I can however remove the shipping charges that were on the purchase order from this screen.

If I try to cancel the order, I get an expected error that I cannot do that because the items have already been received.

And there doesn't appear to be a way to void a purchase order.

So thinking I will use an inventory adjustment to remove the items.  Since there is actually only one invoice, the money should still be tracked accurately, I think?

wish I knew what I did to make this happen, so that it can be prevented from happening again...

something else that may be relevant:

after I had received the items, I had to put the wrong date, so I voided the original purchase order delivery and made a new one with the correct date.  Perhaps now that I am trying to invoice, it is referencing the date on the voided delivery, and coming up with an empty date?  I can see two deliveries on the Purchase Order...

Hello Gurus,

I have been spinning my wheels on this for a while and hoping someone can point me in the right direction.

Some time ago I made a Purchase order and received it, then sent a customer invoice for the received goods.  I needed to do this because the customer needed the invoice before I could get an invoice from the supplier.

Today, I am trying to convert the purchase order delivery into a supplier invoice.  I am on the supplier invoice page, have added the inventory items, filled in dates and reference and such, but consistently get this error:

DATABASE ERROR : cannot add journal entry
error code : 1292
error message : Incorrect date value: '' for column `fa_computerisms_ca`.`journal`.`event_date` at row 1

I have tried a variety of dates, from well before everything happened to next year, nothing seems to change the empty date.

I can't really find any thing in the forums that match this problem, but nonetheless I have tried, and failed, to hack the code in a few places based on what similarities I did find.

FWIW, I had another Purchase order that had not been received.  I was able to receive those times and create a supplier invoice for those items.  However, I did not send a sales invoice to a customer for those items prior, so perhaps that is the difference.

Any suggestions how I might get this invoice entered?


(2 replies, posted in Setup)

awesome sauce!  thank you!


(2 replies, posted in Setup)


I am trying to find what each of the settings in the access settings page map to.  There is a page on the wiki, but there isn't really any information on it.

This is not the first time I have been stuck here and just ended up enabling more things than I want to to make something work.  What I am trying to get specifically right now is the sales pricing tab when clicking items and inventory=>items.  I figured out that this page is enabled with access setup=>inventory configuration=>stock items add/edit, but I can't seem to find the right combination of things to get the sales pricing tab enabled for a user role I have created.


(2 replies, posted in Setup)

that would be under setup=>access setup.  But going from full admin to subadmin should not give you more privileges.  the full admin should have every box checked, so shouldn't be any restrictions at all.


(15 replies, posted in Reporting)

I just got this error trying to print a stock check sheet, turned out there was a checkbox under company setup called "use bardoes on stocks".  deselecting this option fixed the problem for me.  Not sure if I am missing out on some feature because I don't know how to use it, but at least my report prints...

I was mulling this over last night in my dreams, and it occurred to me I could maybe just bury the surcharge in the price of the item and modify the description to say that the surcharge is included.  At the end of the month I can run an inventory report and create a vendor invoice based on that instead of using a GL account, and expense the cost that way.  This would avoid any confusion on the invoice, would probably avoid more mistakes in the long run in terms of forgetting to add the additional line item on the invoice, and I can update the individual items as I bill them instead of having to do them all at once, based on whether the description has already been modified or not. 

Only downside I see is that tax would be charged on the surcharge, but it would be a matter of a few cents at most, so not really significant...

then, when/if a fixed charge can ever be coded into the item categories, the process can be reversed in the same way of modifying descriptions and prices on items as required till over time they are all done...

hm, this is an interesting idea.  Certainly seems it would save a bunch of work.  I see the idea, basically another line item on the invoice, but one that would apply tax to a GL account, so easier to track.  Brilliant idea, thank you...

Hi Poncho,

Thanks for the suggestion, I hadn't considered sales kits.  looks like it would work; I have a few thousand items, so if I have to do each item it is going to take a long time no matter what.  Hence the hope for something that can be attached to categories. 

I will consider the data entry load vs the probability of mistakes on separate invoice items and proceed from there...


our area has recently implemented legislation for recycling fees.  The fees are fixed values based on the category of item.

I thought to set it up as a tax, but taxes only seem to have percentage options, no fixed fees.  Ultimately, this would probably be the best way, as it would keep track of all the money collected in a separate GL account.  And even better would be to be able to assign the surcharge to an item category, instead of having to assign it to each item individually.

So I looked through everything I could think of for any kind of "additional fee" field, but I can find nothing like it.

The only way I can think to approach this is to add a separate line item on each invoice, but this is error prone and difficult to reconcile against what was actually sold. 

Wondering if anyone has tackled this before and if so how, and how did it work out for you?


(22 replies, posted in Installation)

okay, thanks to my exceptionally brilliant employee, we figured out the problem is that when you create the dbuser, you must do so with the grant option:

grant all on fa_kmm.* to fakmm@'localhost' identified by 'XXXXXXXXXXXXXXX' with grant option;

This works for me without using RedCone's changes to the function...

If this was in the documentation somewhere, I overlooked it...


(22 replies, posted in Installation)

apmuthu wrote:

It appears that your MySQL server is running on one collation/character set and your client connection is running on another whilst your action FA connection is trying to change the connection characteristics.

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Refer article.

Okay, so this is my first time trying to use MariaDB with FA, so maybe there is something I don't know.  But in the config files, the mysqld server config has:

character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

and the mysql client config has:

default-character-set = utf8mb4

So maybe I am misunderstanding you, but I think I have the same collation/character set on both client and server.  Additionally, this is the 3rd web app I am installing on this box, and the first two show no signs of any discrepancy

apmuthu wrote:

Your connection appears to be anonymous and not from a specific user with password.

I noticed this too and at first though it was significant, however, from this post, I think that it is not.

I am super slow to work my way through code, but from what I can tell, the function that RedCone replaced is where the problem is.  I have yet to figure out exactly what the boggle is, but something in there is not working, for sure...


(5 replies, posted in Setup)

Sage50, at least as I know it, does use mysql as a backend.  but I have never been able to make enough sense of their db structure to pull any usable data from it.  that said, it has been a while since I have tried.

Based on my experience, your best bet is to probably pull reports from sage in csv format, and either enter the data by hand, or possibly if you can organize the data according to FA's db tables, import the csv into FA's mysql tables...


(22 replies, posted in Installation)


I am trying to install 2.4.4 and am running into this same error message at the 2nd page of the installer.  I am using mariadb on debian buster, and I have pre-created the database and user, and I have tested connections to the DB as working, with the php script in this thread and standard shell commands.

I tried changing out the block of code as suggested by RedCone (thanks for providing that solution), but unfortunately, it did not work for me, I still get the error.  Also, when I replace with that block of code, I get an additional error message that access is denied to the database

When I enable logging, it appears the connection is established and the collate command is run:

| 2018-05-17 18:00:27.468765 | [fakmm] @ localhost []      |       132 |         0 | Connect      | fakmm@localhost as anonymous on                                  |
| 2018-05-17 18:00:27.468969 | fakmm[fakmm] @ localhost [] |       132 |         0 | Init DB      | fa_kmm                                                           |
| 2018-05-17 18:00:27.469053 | fakmm[fakmm] @ localhost [] |       132 |         0 | Query        | ALTER DATABASE COLLATE utf8_unicode_ci                       
| 2018-05-17 18:00:27.469137 | fakmm[fakmm] @ localhost [] |       132 |         0 | Quit         |                                                                  |

This log entry happens with the original block of code, and the one that RedCone provided; and this is logged despite the error message on the screen saying access is denied.

I tried downloading 2.4.3 and experience the same symptoms with this as well.

I am out of ideas for the moment, If there are any suggestions they would be graciously received...


(3 replies, posted in Accounts Payable)

Hi, me again...

according to the wiki, in order to void a supplier transaction, one should first void the invoice, then the PO Delivery.  When I void the delivery I get this error:

The entered transaction does not exist or cannot be voided.

Transaction clearly exists, I can click the podelivery order number and see it.  I can also see it in my inventory movements. 

so why can it not be voided?  is there another part to the procedure?


(6 replies, posted in Setup)

okay, I know what you are asking there, but going to have to put that on the pile of things to look at in FA later...
will get back to you in some weeks on this...


(6 replies, posted in Setup)

Hi Apmuthu, thanks for following up.  looks like I have the expected value:

./includes/current_user.inc:define('FLOAT_COMP_DELTA', 0.004);