26 Reply by oakstreet1

(4 replies, posted in Banking and General Ledger)

Also, I am noticing a customer prepayment is simply going to the Accounts Receivable/Asset account. I don't find a Customer Deposit/Liability account. Is this by design? It would seem that a customer pre-payment should be recorded to a liability account since it isn't yet an asset.

The entries are recorded as negative amounts so they reduce your AR asset account. I guess a negative AR is a positive liability, but it works out the same.
Balances on customer accounts are determined independent of the GL and all customer charges and credits are rolled into AR in the general ledger.
I'm not saying it's the best or most correct way of doing it, but a negative asset has the same net result as a liability, so the balance sheet is correct.

Go to forum: Banking and General Ledger

27 Reply by oakstreet1

(6 replies, posted in Accounts Receivable)

Not sure where all the previous discussion went, but I don't remember anyone saying anything about making journal entries to pay invoices.

Go to forum: Accounts Receivable

28 Reply by oakstreet1

(25 replies, posted in Accounts Receivable)

All of my testing has been with decimals set to 2, but the error only happens if you add sales tax on freight (AFAIK). However, if you do that, you are guaranteed to get some invalid numbers.

I reported a bug. It may be something fairly easy to fix, since it is pretty predictable.

Just be sure your Accounts Receivable balance is correct. That is probably where the errors are the most noticeable ... that and when an invoice shows an incorrect total.

Go to forum: Accounts Receivable

29 Reply by oakstreet1

(25 replies, posted in Accounts Receivable)

Also, you only get that block on your invoice if you use the prepaid invoice feature, which is causing some strange errors. When I did it, it applied sales tax on shipping for a tax exempt customer, but applied it inconsistently, discounted the shipping by $0.16 (about 2%), and calculated the sales tax amount on the incorrect shipping in 2 places where it was calculated on the full shipping price in other places.

Finally, the prepaid invoice I generated showed the wrong sales tax amount, but the correct total, so if you add up the numbers, the computed total doesn't agree with the invoice total.

Until the prepaid order is fixed, I wouldn't use it. I'd be afraid my GL reports were going to get totally screwed up.

Go to forum: Accounts Receivable

30 Reply by oakstreet1

(25 replies, posted in Accounts Receivable)

In that case you probably want to just enter a direct invoice. That will create a sales order, mark all items as delivered, and generate the invoice for billing.

If you find rep107.php under reports, you can add some lines like the following (search for $DisplayTotal) to find where to add these lines. Note that I created an invoice total variable and edited the display total line to use it. Otherwise, I just added 6 new lines ...
(if you want a custom invoice form, it's best to make a copy of the original file in company/X/reporting where "X" is the company number. FA should use the custom form in the company/X/reporting folder in place of the original one in reporting/)

            $rep->NewLine();
        $invTotal = $myrow["ov_freight"] + $myrow["ov_gst"] + $myrow["ov_amount"]+$myrow["ov_freight_tax"];
        $DisplayTotal = number_format2($sign*$invTotal,$dec);
        $rep->Font('bold');
        if (!$myrow['prepaid']) $rep->Font('bold');
        $rep->TextCol(3, 6, $rep->formData['prepaid'] ? _("TOTAL ORDER VAT INCL.") : _("TOTAL INVOICE"), - 2);
        $rep->TextCol(6, 7, $DisplayTotal, -2);

        $rep->NewLine();
        $rep->TextCol(3, 6, "AMOUNT PAID", - 2);
        $rep->TextCol(6, 7, number_format2($myrow['alloc'],$dec), -2);

        $rep->NewLine();
        $rep->TextCol(3, 6, "AMOUNT DUE", - 2);
        $rep->TextCol(6, 7, number_format2($invTotal - $myrow['alloc'],$dec), -2);

Go to forum: Accounts Receivable

31 Reply by oakstreet1

(25 replies, posted in Accounts Receivable)

The only purpose of pre-payment required is to enforce a certain amount of payment, up to the order total, before delivery is enabled.
So, if you have one person or group of people entering sales orders, and someone else handling deliveries, the people handling deliveries will not be able to make a delivery until a payment is entered against the SO that is at least equal to the prepayment amount.
So, the pre-payment amount cannot exceed the order total, but may be less.

Go to forum: Accounts Receivable

32 Reply by oakstreet1

(6 replies, posted in Accounts Receivable)

gnurob wrote:

Likewise, invoicing paid in advance order generate a Pre-Payment invoice that shows the payment terms. It's a little confusing to appear on a final invoice document.

I have a client that requires that her customers keep a credit balance because there are many small charges against the account over time, and she generates an invoice once the customer gets a debit balance.

So, the invoice would be very confusing since most of it is already paid from the credit balance and the amount left to be paid might be as little as $1 (although the customer is asked to pay $1 + an amount to keep as a credit and prevent invoicing over and over for small amounts).

What I did was I modified the invoice report (rep107.php) slightly to include an amount paid and the net amount payable.

That allows you do skip a bunch of steps and just create a direct invoice, apply the customer payment to the invoice, then generate an invoice that shows it is paid or partially paid with a net payable amount.

Go to forum: Accounts Receivable

33 Reply by oakstreet1

(6 replies, posted in Accounts Receivable)

Actually, I missed a step. If you said the order required prepayment. The first thing you have to do, before you can do a delivery is enter a customer payment that is at least as much as the prepayment amount.

After that, you can make the delivery, invoice on the delivery, and then you can print the invoice.

Go to forum: Accounts Receivable

34 Reply by oakstreet1

(6 replies, posted in Accounts Receivable)

When you create a sales order, the first thing you need to do is create a delivery on the order.
After you do the delivery, then you can invoice the order and print the invoice for the customer.

Go to forum: Accounts Receivable

35 Reply by oakstreet1

(25 replies, posted in Accounts Receivable)

One more note: I just did an identical sales order without using prepaid terms and the GL amounts and tax entries are correct.

Go to forum: Accounts Receivable

36 Reply by oakstreet1

(25 replies, posted in Accounts Receivable)

I just noticed that the deferred income amounts appear to be wrong, also.

The only strange thing I did in producing these results was to adjust the prepaid amount on the sales order entry to match the correct order total from the incorrect amount that was displayed.

Go to forum: Accounts Receivable

37 Reply by oakstreet1

(25 replies, posted in Accounts Receivable)

The 0.16 discrepancy in the S&H amount (GL = 8.44, DEL=8.60) accounts for the difference in tax amount.
So, if it's possible to figure out how the S&H got reduced to $8.44, the tax amounts should be consistent.

Go to forum: Accounts Receivable

38 Reply by oakstreet1

(25 replies, posted in Accounts Receivable)

This is the data review of my test. There are some serious red flags. The total amount in the sales order is wrong and at least 2 of the GL entries are wrong. Also, one entry in the tax table is rounded and another is not.

sales order record for order_no=34

| ord_date   | order_type | freight_cost | delivery_date | payment_terms | total | prep_amount | alloc |
+------------+------------+----------+---------------------------------+---------------+---------------+
| 2019-08-29 |          1 |          8.6 | 2019-09-28    |             5 | 32.79 |       32.19 | 32.19 |

Note: The sales order total is 0.6 greater than the total of item, shipping and freight tax.

sales order details records for order_no=34

| id  | order_no | trans_type | stk_code | description       | qty_sent | unit_price | quantity | invoiced | discount_percent |
+-----+----------+------------+----------+-------------------+----------+------------+----------+----------+------------------+
| 117 |       34 |         30 | 103      | iPhone Cover Case |        1 |      22.99 |        1 |        0 |                0 |

debtor trans records for order_ = 34

Delivery Transaction:
| trans_no | type | tran_date  | due_date   | reference | tpe | order_ | ov_amount | ov_gst 
+----------+------+------------+------------+-----------+-----+--------+-----------+-------
|       33 |   13 | 2019-08-29 | 2019-09-28 | 001/2019  |   1 |     34 |     22.99 |      0 

| ov_freight | ov_freight_tax | ov_discount | alloc | prep_amount | rate | ship_via | payment_terms | tax_included |
+------------+----------------+-------------+-------+-------------+------+----------+---------------+--------------+
|        8.6 |            0.6 |           0 |     0 |       32.19 |    1 |        1 |             5 |            0 |

Invoice Transaction:
| trans_no | type | tran_date  | due_date   | reference | tpe | order_ | ov_amount | ov_gst 
+----------+------+------------+------------+-----------+-----+--------+-----------+-------
|       33 |   10 | 2019-08-29 | 2019-08-29 | 02595     |   1 |     34 |     22.99 |      0

| ov_freight | ov_freight_tax | ov_discount | alloc | prep_amount | rate | ship_via | payment_terms | tax_included |
+------------+----------------+-------------+-------+-------------+------+----------+---------------+--------------+
         8.6 |            0.6 |           0 | 32.19 |       32.19 |    1 |        1 |             5 |            0 |

Note: The delivery and invoice transaction details have the correct amounts.

id |debtor_trans_no|debtor_trans_type|stock_id|description      |unit_price|unit_tax|quantity|discount_percent|standard_cost|qty_done|src_id
---+---------------+-----------------+--------+-----------------+----------+--------+--------+----------------+-------------+--------+------
202|            33 |              13 | 103    |iPhone Cover Case|    22.99 |      0 |      1 |              0 |          10 |      0 |   117

Customer allocations:

| id | person_id | amt   | date_alloc | trans_no_from | trans_type_from | trans_no_to | trans_type_to |
+----+-----------+-------+------------+---------------+-----------------+-------------+---------------+
| 31 |         9 | 32.19 | 2019-08-29 |            15 |              12 |          33 |            10 |


Trans Tax Details (this may be the floating point error):

| id | trans_type | trans_no | tran_date  | tax_type_id | rate | ex_rate | net_amount | amount           | memo     | reg_type |
+----+------------+----------+------------+-------------+------+---------+------------+------------------+----------+----------+
| 70 |         10 |       33 | 2019-08-29 |           1 |    7 |       1 |        8.6 | 0.58902104300091 | 02595    |        0 |
| 71 |         10 |       33 | 2019-08-29 |           0 |    0 |       1 |      22.99 |                0 | 02595    |        0 |
| 68 |         13 |       33 | 2019-08-29 |           1 |    7 |       1 |        8.6 |              0.6 | 001/2019 |     NULL |
| 69 |         13 |       33 | 2019-08-29 |           0 |    0 |       1 |      22.99 |                0 | 001/2019 |     NULL |


General ledger entries:
| counter | type | type_no | tran_date  | account | memo_ | amount   | person_type_id | person_id |
+---------+------+---------+------------+---------+-------+----------+----------------+-----------+
|     199 |   12 |      15 | 2019-08-29 | 1060(CHECKING)  |    32.19 |           NULL | NULL      |
|     200 |   12 |      15 | 2019-08-29 | 1200(AR)|       |   -32.19 |              2 | 9         |
|     201 |   13 |      33 | 2019-08-29 | 4020(TAXABLE SALES) -22.99 |           NULL | NULL      |
|     202 |   13 |      33 | 2019-08-29 | 5010(COGS)      |       10 |           NULL | NULL      |
|     203 |   13 |      33 | 2019-08-29 | 1510(INVENTORY) |      -10 |           NULL | NULL      |
|     204 |   13 |      33 | 2019-08-29 | 2390(DEFER INCOME)   22.99 |           NULL | NULL      |
|     205 |   10 |      33 | 2019-08-29 | 2390(DEFER INCOME)  -22.57 |           NULL | NULL      |
|     206 |   10 |      33 | 2019-08-29 | 1200(AR)|       |     31.6 |              2 | 9         |
|     207 |   10 |      33 | 2019-08-29 | 4430(S&H)       |    -8.44 |           NULL | NULL      |
|     208 |   10 |      33 | 2019-08-29 | 2150(TAX)       |    -0.59 |           NULL | NULL      |

Note that the tax entry is off by 1 penny from the invoice and I don't know where the S&H amount of $8.44 came from.

If you copy my data into a wider display, it will look better. I tried to remove unneeded fields to prevent the display from wrapping in a confusing way.

Go to forum: Accounts Receivable

39 Reply by oakstreet1

(25 replies, posted in Accounts Receivable)

In my quick test setup, sales tax was added, which actually made the prepayment amount incorrect. The amount displayed was the full price of the invoice (including tax) plus the tax (a second time). So the tax was $0.60 and the prepaid amount was $0.60 more than the invoice total.

It did let me enter the sales order with the manually corrected prepaid amount. I've never done this before, so I am not sure what to expect. It appears I must now enter payment from the customer before I can make a delivery and invoice the customer ...

However, the sales order amount shows incorrectly, in the same way the prepaid amount was incorrectly computed. I entered the correct amount, rather than paying the full allocation and then I was able to make a delivery on the sales order which had the correct total, so I assumed the invoice would get the correct amount.
However, when I went to generate an invoice on the prepaid, and now delivered sales order, the invoice doesn't include the shipping charge so the invoice amount is wrong.

As I mentioned, I've never used prepaid sales orders, so I'm not totally clear on how this should work, but it looks to me like there are some serious discrepancies.

I will post database entries next for review ...

Go to forum: Accounts Receivable

40 Reply by oakstreet1

(25 replies, posted in Accounts Receivable)

paul wrote:

... have now also tried PHP 7.0 and 5.6. These attempts did not help my situation.

That's actually good, because it will be quicker for me to install 2.4.7 on PHP 5.6 for testing.

Go to forum: Accounts Receivable

41 Reply by oakstreet1

(25 replies, posted in Accounts Receivable)

Floating point values are notorious for interjecting small errors. FA should be rounding all of these numbers to avoid tiny errors that creep in. The database fields should probably be fixed precision decimal fields, rather than double precision floating point, but that's another matter.

In any case, you shouldn't need to work this hard to enter a prepayment. I don't yet have 2.4.7 installed anywhere to test, but I'm pretty sure if the code that is detecting the error was correctly rounding the values to the specified precision before comparing them, your prepayment would pass.

If this is new to PHP 7, I may not be able to reproduce it in the short term. I only have one PHP 7 installation (for testing) and that is on a VM that is shutdown at this time.

If nobody else gets to it first, I'll give this a look as soon as I have some time.

Go to forum: Accounts Receivable

42 Reply by oakstreet1

(1 replies, posted in Report Bugs here)

What version of FA are you using?

Go to forum: Report Bugs here

43 Reply by oakstreet1

(4 replies, posted in Report Bugs here)

I understand why we can't just change it, now. smile

I guess I'm just ranting, because I have to join keys from another database and I just realized that this is going to be a real problem. It's not a marginal case for this client, because they manage an association of emergency medical services, paramedics, and rescue workers. Almost all of the department names have an ampersand in the name. e.g. "Fire & Rescue" or "Rescue & EMS".  Matching records from one database to the other cannot be done in an SQL query in a JOIN.

When I finish this project and get paid, I will be making another cash donation to FA development, and when I get caught up on my deadlines, I will happily donate my time to help out. I have been using FA in my business for almost 2 years, now, and I'm pretty happy with it. From a developer perspective, it has presented some challenges. smile

Go to forum: Report Bugs here

44 Reply by oakstreet1

(4 replies, posted in Report Bugs here)

What's done is done, I guess, but this is a bad design choice, in my experience.

1) using a normal query tool, a user query may fail because search strings will not match the HTML entities
2) A string that should fit in a field will fail to fit because HTML entities expand the string by many chars (e.g. &quote;)
3) There are other ways to deal with escaping strings in the database. Using parameters in queries is the current best practice.
4) Query results look wrong, unless the query tool converts the HTML entities for display.
5) Joining with keys from other databases will fail because FA uses a unique encoding for strings

If your concern was SQL injection, using SQL escaping would not corrupt the stored data.
If your concern was PHP execution, you would escape/convert values *after* selecting them from the database.
There should never be a case where PHP code executes a string from the database. That would just be bad coding. Corrupting the data and interjecting strange, random problems like "Input value is too long", when the input value is no longer than other values that were saved in the same field, is not the answer to bad coding practices.

Even worse, in FA, if you overflow the branch_ref field by adding some special chars, the update silently fails. How do I explain to a client that they can use special chars, as long as their expanded versions don't push the length of the string past the character limit imposed by the database, and, if they do, their update will fail without any indication that it did fail, or why?

I'm too far into my project to give up on FA, but this is a serious disappointment. I started my career in database applications in 1987. I've worked with almost every major DBMS and I've written countless database applications.

Go to forum: Report Bugs here

45 Topic by oakstreet1

(4 replies, posted in Report Bugs here)

Using version 2.4.6
I am seeing things like "&" in my test database while working on this extension and I was wondering if I accidentally pasted that into a name field, but when I create a new customer by entering "Testing Adam & Willy", this is what I get in the database:

[7] => Array
        (
            [branch_ref] => testaw
            [debtor_ref] => testaw
            [name] => Testing Adam &amp; Willy     <-- not the "&amp;" in place of an ampersand.
            [br_name] => Testing Adam &amp; Willy
            [group_no] => 0
        )

Maybe this is just an opinion I've formed after decades of writing business applications, but in my experience, you don't alter the values that are stored in the database. You alter them to suit your display method when they are displayed.

Should I write all of my code to convert HTML entities into their actual values for display in things that don't use HTML, or is this a bug that may someday be fixed?

Forgive me for having a strong opinion on this subject, but I really hope this was not intentional.

Go to forum: Report Bugs here

46 Reply by oakstreet1

(4 replies, posted in Setup)

I agree that having prefixes does protect you from overwriting your company data with a different company's backup. However, if you are writing general report queries that you run outside FA, it would be simpler to have all the table names the same and just connect to the correct database, based on the company being queried.

I know, from a developer perspective, constantly entering queries at the command line and having to be sure to add the correct prefix to table names can be painful, but I have adapted to it. The real problem, as I stated earlier, is with using a generic reporting tool. AFAIK, there are no tools that provide macro replacement support to adjust table names before executing a query.

Go to forum: Setup

47 Reply by oakstreet1

(4 replies, posted in FA Modifications)

Thank you, Braath Waate.

Go to forum: FA Modifications

48 Topic by oakstreet1

(4 replies, posted in FA Modifications)

First, I'm not understanding why the NULL, 0, 0 entries exist in the GL for each invoice:

| counter | type | type_no | tran_date  | account | memo_ | amount | person_type_id | person_id |
+---------+------+---------+------------+---------+-------+--------+----------------+-----------+
|     171 |   10 |      28 | 2019-08-19 | 4010(sales)     |     -5 |           NULL | NULL      |
|     172 |   10 |      28 | 2019-08-19 | 1200(AR)|       |      5 |              2 | 6         |
|     173 |   10 |      28 | 2019-08-19 |         |       |      0 |           NULL | NULL      | <-- this entry
|     174 |   10 |      29 | 2019-08-19 | 4010    |       |     -1 |           NULL | NULL      |
|     175 |   10 |      29 | 2019-08-19 | 4010    |       |     -1 |           NULL | NULL      |
|     176 |   10 |      29 | 2019-08-19 | 1200    |       |      2 |              2 | 6         |
|     177 |   10 |      29 | 2019-08-19 |         |       |      0 |           NULL | NULL      | <-- and this entry
|     178 |   12 |      12 | 2019-08-19 | 1065(Petty Cash)|     50 |           NULL | NULL      |
|     179 |   12 |      12 | 2019-08-19 | 1200    |       |    -50 |              2 | 7         |
|     180 |   12 |      13 | 2019-08-19 | 1060(Checking)  |   1500 |           NULL | NULL      |
|     181 |   12 |      13 | 2019-08-19 | 1200    |       |  -1500 |              2 | 5         |

And my second question is if it will cause any problems if I don't enter tax transactions where there is no tax collected and stock moves where stock levels are not applicable?

I can make these NULL entries, but it just seems like I'm polluting the database with rows that cannot affect any report or function, because their net effect is to add zero.

Go to forum: FA Modifications

49 Reply by oakstreet1

(6 replies, posted in Banking and General Ledger)

I suppose I should add that I am still using FA version 2.4.6

Go to forum: Banking and General Ledger

50 Reply by oakstreet1

(6 replies, posted in Banking and General Ledger)

I reconcile 4 different accounts every month and I have never noticed any missing transactions ???

Go to forum: Banking and General Ledger