2,101 Reply by apmuthu

(10 replies, posted in Setup)

Once you get the CSV of the customers and assuming you are using the latest FA v2.4.3 and the sql/en_US-new.sql Chart of Accounts, manually enter one typical ustomer and see the diffs in the before and after backups and construct your sqls accordingly. This will need to be carefully done in a cloned container and then migrated to the production install. The same can be done for vendors (suppliers), items (inventory), etc.

Try to revert to an earlier version of DacEasy before Sage took over. You will most probably find a non expiring version.....MBAs tend to enforce migration by such blackmail tactics - they probably cannot survive in a non-monopolistic environment - they will however wax eloquent on the virtues of their idea of a free "market economy".

Go to forum: Setup

2,102 Reply by apmuthu

(5 replies, posted in Report Bugs here)

There is a problem with the new pattern for references if it straddles fiscal years. See here.

Go to forum: Report Bugs here

2,103 Reply by apmuthu

(13 replies, posted in Reporting)

@trafficpest: The "add this above ... below", etc are not discernible. Kindly post the whole modified rep107.php file so that a diff file can be prepared. Thanks for the "money" offer - don't bother.

@joe: there is an error in the get_invoice_range() if it straddles fiscal years with the default "pattern" of references. The year comes last in the default pattern ({001}/{YEAR})and the actual invoice number comes in first so if we choose 001/2016 to 001/2018, we miss out on 002/2016, 002/2017, etc.

The $sql in get_invoice_range() we get will be:

SELECT trans.trans_no, trans.reference
        FROM &TB_PREF&debtor_trans trans 
            LEFT JOIN &TB_PREF&voided voided ON trans.type=voided.type AND trans.trans_no=voided.id
        WHERE trans.type=10 AND ISNULL(voided.id) 
        AND trans.reference>='001/2016' 
        AND trans.reference<='001/2018' 
        ORDER BY trans.tran_date, trans.reference

We inspect the above by temporarily putting in:
file_put_contents('D:/WebServers/XAMPP/htdocs/frontac24/tmp/log_file.txt', $sql, FILE_APPEND);
in line 42 of reporting/rep107.php

To alleviate this, we can change the default pattern to be {YEAR}/{001} but will that lead to other problems?

Go to forum: Reporting

2,104 Reply by apmuthu

(13 replies, posted in Setup)

The said function just saves the reflines.pattern field during setting of the Setup => Transaction Preferences.

Go to forum: Setup

2,105 Reply by apmuthu

(13 replies, posted in Setup)

What happens if last 2 got voided?

hence:

search refs, find highest value for that type (and year if new templated numbering is used)
search voided for highest value for that type (and year if new templated numbering is used)
Take the larger of the two and increment by 1

Go to forum: Setup

2,106 Reply by apmuthu

(1 replies, posted in Reporting)

Most browsers have native PDF Viewers that can be used to view in it and print from there. Page size and absence of styling css will make for very unwieldy html pages.

Go to forum: Reporting

2,107 Reply by apmuthu

(1 replies, posted in Fixed Assets)

If it is a very small value in consideration of you total assets / turnover / expenses, it may be claimed as 100 % depreciation or expenses - consult your local auditor. Depreciation is so that the asset does not get taken away by the staff or others. Good practice is to depreciate it in the books even if it is 100%.

Go to forum: Fixed Assets

2,108 Reply by apmuthu

(13 replies, posted in Setup)

If the voided transactions too are taken into account when the greatest customer transaction is considered, it might alleviate this situation.

Go to forum: Setup

2,109 Reply by apmuthu

(4 replies, posted in Dimensions)

If there are payments or deposits whether voided or not - pertaining to any value of the dimension to be deleted, you will not be able to delete the dimension.

Go to forum: Dimensions

2,110 Reply by apmuthu

(1 replies, posted in Dimensions)

Sales => Direct Invoice will make for cash Invoice by default. Try to locate the script and make the default value for Dimension 1 as what you want. Dimensions are optional in the Invoice Entry forms.

Go to forum: Dimensions

2,111 Reply by apmuthu

(30 replies, posted in Fixed Assets)

It is there - see this link - maybe you are using some version other than v2.4.3+.

Go to forum: Fixed Assets

2,112 Reply by apmuthu

(12 replies, posted in Reporting)

It is possible that your earlier attempt using the erroneous CoA would have some remnants that would need to have been purged before attempting a fresh install.

Go to forum: Reporting

2,113 Reply by apmuthu

(13 replies, posted in Reporting)

The function needs 2 arguments and the second one $to is missing. It should be computed to be the current date/time within the function if the argument is not given.

Go to forum: Reporting

2,114 Reply by apmuthu

(1 replies, posted in FA Modifications)

Create a View of the XML format fields.
Now use any of:
http://nst.sourceforge.net/nst/docs/scripts/mysql2xml.html
http://www.phpbuilder.com/snippet/detail.php?type=snippet&id=296
or hard code them like this.

Another library is archived here.

Go to forum: FA Modifications

2,115 Reply by apmuthu

(4 replies, posted in Dimensions)

Only that Dimension will be filtered.

Go to forum: Dimensions

2,116 Reply by apmuthu

(1 replies, posted in Reporting)

gl_trans.type_no is retail (1) or wholesale (2) taken from sales_types table.

gl_trans.type = stock_moves.type which is the Transaction Type.

stock_moves.trans_no is the actual transaction number that does not have an equivalent directly in the gl_trans table.


The gl_trans table manages all types of transactions.

The supp_trans and debtor_trans tables handle purchase and sale transactions respectively and have trans_no and type fields in them.

The supp_invoice_items and debtor_trans_details tables have the item wise records for linking with the stock_moves table on both trans_no and type fields.

Go to forum: Reporting

2,117 Reply by apmuthu

(6 replies, posted in Banking and General Ledger)

Make the Journal Date alone mandatory and use it for the other two dates if empty.....

Go to forum: Banking and General Ledger

2,118 Reply by apmuthu

(10 replies, posted in Items and Inventory)

Line 146 in reporting/rep107.php calls the function get_customer_trans_details() defined at the top of includes/db/cust_trans_details_db.inc. In the said function definition, change line 21:

        line.description As StockDescription,

to be:

        line.description AS StockDescription, item.long_description AS FullDescription,

and now change the line 166 in reporting/rep107.php:

                $rep->TextColLines($c++, $c, $myrow2['StockDescription'], -2);

to be:
[

                $rep->TextColLines($c++, $c, (($myrow2['FullDescription'] != '') ? $myrow2['FullDescription'] : $myrow2['StockDescription']), -2);

The field long_description may be available in multiple tables denoting other contexts. The existing StockDescription is retained as a fallback.

Caveat: Note that as FullDescription is taken from the stock_master table's long_description field, it may not be consistently available especially if changed or deleted in it later, whereas, the StockDescription will be available even without it from the stock_master table. It may be prudent to use both when the FullDescription is available.

@joe: would you like to commit the first code change in cust_trans_details_db.inc making the long_description for those who want it as well?

Go to forum: Items and Inventory

2,119 Topic by apmuthu

(0 replies, posted in Accounts Receivable)

When a customer payment is allocated, the date of allocation is entered as '0000-00-00' but it is correctly entered for direct cash invoices. This happens in the cust_allocations and debtor_trans tables. The $_POST['DateBanked'] or it's processed value like end of current fiscal_year should have been used.

The set of payment and full allocation transaction sqls are:

SET @alloc=400;
SET @bankcharge=5;

INSERT INTO `1_refs` VALUES
('4', '12', '001/2018');

INSERT INTO `1_bank_trans` VALUES
(NULL, '12', '4', '1', '001/2018', '2018-01-07', @alloc-@bankcharge, '0', '0', '2', '1', NULL);
INSERT INTO `1_comments` VALUES
('12', '4', '2018-01-07', 'paid by outstation cheque');
INSERT INTO `1_cust_allocations` VALUES
(NULL, '1', @alloc, '0000-00-00', '4', '12', '5', '10');
INSERT INTO `1_debtor_trans` VALUES
('4', '12', '0', '1', '1', '2018-01-07', '0000-00-00', '001/2018', '0', '0', '400', '0', '0', '0', '0', @alloc, '0', '1', '0', '0', '0', NULL, '0'),
UPDATE `1_debtor_trans` SET `alloc`=`ov_amount`-@alloc WHERE `type`='10' AND `trans_no`='5' AND `debtor_no`='4'
INSERT INTO `1_gl_trans` VALUES
(NULL, '12', '4', '2018-01-07', '1060', '', @alloc-@bankcharge, '0', '0', NULL, NULL),
(NULL, '12', '4', '2018-01-07', '1200', '', -@alloc, '0', '0', '2', '1'),
(NULL, '12', '4', '2018-01-07', '5690', '', @bankcharge, '0', '0', NULL, NULL);
INSERT INTO `1_audit_trail` VALUES
(NULL, '12', '4', '1', '2018-01-07 10:13:49', NULL, '3', '2018-01-07', '0');

Go to forum: Accounts Receivable

2,120 Reply by apmuthu

(4 replies, posted in Reporting)

The standard sql/en_US-new.sql chart already has the Profit & Loss Account defined at Line 323 and set in the SysPrefs at line 1853.

Make sure your tables have these records.

Go to forum: Reporting

2,121 Reply by apmuthu

(12 replies, posted in Reporting)

Thanks @Foxyhimself and @bazza.
Fix committed.

Go to forum: Reporting

2,122 Reply by apmuthu

(13 replies, posted in Setup)

For now, create the new invoice and then void the previous one so that you do not void the last one.

@joe / @itronics: if this is a code bug, we need to fix it forthwith.

Go to forum: Setup

2,123 Reply by apmuthu

(1 replies, posted in Accounts Receivable)

Allocation may have been made so you should not change the Customer name/branch. Void it and recreate it anew. Database intervention is possible but not advised.

Go to forum: Accounts Receivable

2,124 Reply by apmuthu

(19 replies, posted in Reporting)

This report is new and was not there in the release. Also the original inclusion had another file that is necessary.

Go to forum: Reporting

2,125 Reply by apmuthu

(14 replies, posted in Setup)

You need to close the fiscal years in sequence from the earliest onwards.

Go to forum: Setup