Sales Transactions Permissions:
Sales orders edition
Sales deliveries edition
Sales invoices edition2,051 01/18/2018 05:31:24 am
Re: Access setup: Edit one's own transactions (6 replies, posted in Setup)
2,052 01/18/2018 05:27:01 am
Re: Print Customer Balance On Invoice (13 replies, posted in Reporting)
@trafficpest: If the Balance is 0 (zero) then it is best suppressed.
@joe: Can it be an option in the existing one?.
2,053 01/17/2018 07:23:46 am
Re: Print Customer Balance On Invoice (13 replies, posted in Reporting)
You will need to incorporate this fix as well.
Attachment has it done.
@joe: Want to include it in the core?
2,054 01/17/2018 07:14:09 am
Re: Workflow adjustment to sales_order_ui.inc (2 replies, posted in FA Modifications)
It actually looks nice in just 2 lines instead of 3 line header table.
@joe: Want to add it into the core or does it go against intuition (needing to be first seen) and / or familiarity hitherto?
2,055 01/17/2018 07:03:31 am
Re: Customer Payment (3 replies, posted in Accounts Receivable)
After a Customer Payment is made, it needs to be Allocated to one or more invoices.
2,056 01/16/2018 06:13:55 pm
Re: Updating Standard Charts (1 replies, posted in Misc. Charts of Accounts)
@itronics: Thanks. Committed.
2,057 01/16/2018 04:32:14 am
Topic: Updating Standard Charts (1 replies, posted in Misc. Charts of Accounts)
The recent commit to the standard Charts missed out the Deferred Income Account in the chart_master table and left the corresponding SysPrefs entry blank. Correct it with (if not already done) substituting appropriate table prefix:
INSERT INTO `0_chart_master` (`account_code`,`account_code2`,`account_name`,`account_type`,`inactive`) VALUES ('2105', '', 'Deferred Income Account', '4', '0');
INSERT INTO `0_payment_terms` (`terms_indicator`,`terms`,`days_before_due`,`day_in_following_month`,`inactive`) VALUES ('5', 'Prepaid Sale', '-1', '0', '0');
UPDATE `0_sys_prefs` SET `value`='2105' WHERE `name`='deferred_income_act'; If the entry is not there in the sys_prefs table, add it in with:
INSERT INTO `0_sys_prefs` (`name`,`category`,`type`,`length`,`value`) VALUES ('deferred_income_act', 'glsetup.sales', 'varchar', '15', '2105');Note: This is only for those who use the standard en_US-new.sql and en_US-demo.sql and their translations.
2,058 01/16/2018 03:51:11 am
Re: Change certain tags languages (3 replies, posted in Translations)
Assuming you are using FA 2.4.3+ and the official es_AR translation, update your file at lang/es_AR/LC_MESSAGES/es_AR-2.4.1-2.mo.
2,059 01/15/2018 05:21:05 pm
Topic: Anatomy of a Sales Transaction (0 replies, posted in Accounts Receivable)
In the making of a New Invoice and receiving payment for it, the following SQLs by operation occur:
1. Make a Sales Order, effect a Customer Delivery and then raise an invoice for it (A Non Cash Direct Invoice):
-- Table Prefix _1
-- Order No. 8
-- Sales Delivery No. 5
-- Sales Invoice No. 5
-- debtor_no 4
-- ST_SALESORDER
INSERT INTO `1_sales_orders` VALUES
('8', '30', '1', '0', '1', '1', 'auto', '', NULL, '2018-01-07', '1', '1', 'N/A', NULL, NULL, 'Donald Easter LLC', '0', 'DEF', '2018-01-17', '3', '400', '0', '0');
INSERT INTO `1_sales_order_details` VALUES
(NULL, '8', '30', '103', 'iPhone Cover Case', '2', '50', '2', '0', '0'),
(NULL, '8', '30', '101', 'iPad Air 2 16GB', '1', '300', '1', '0', '0');
INSERT INTO `1_audit_trail` VALUES
(NULL, '30', '8', '1', '2018-01-07 10:09:06', NULL, '3', '2018-01-07', '0');
-- ST_CUSTDELIVERY
INSERT INTO `1_stock_moves` VALUES
(NULL, '5', '103', '13', 'DEF', '2018-01-07', '50', 'auto', '-2', '10'),
(NULL, '5', '101', '13', 'DEF', '2018-01-07', '300', 'auto', '-1', '200');
INSERT INTO `1_debtor_trans` VALUES
('5', '13', '1', '1', '1', '2018-01-07', '2018-01-17', 'auto', '1', '8', '400', '0', '0', '0', '0', '0', '0', '1', '1', '0', '0', '3', '1');
INSERT INTO `1_debtor_trans_details` VALUES
(NULL, '5', '13', '103', 'iPhone Cover Case', '50', '2.38', '2', '0', '10', '2', '11'),
(NULL, '5', '13', '101', 'iPad Air 2 16GB', '300', '14.29', '1', '0', '200', '1', '12');
INSERT INTO `1_gl_trans` VALUES
(NULL, '13', '5', '2018-01-07', '5010', '', '20', '0', '0', NULL, NULL),
(NULL, '13', '5', '2018-01-07', '1510', '', '-20', '0', '0', NULL, NULL),
(NULL, '13', '5', '2018-01-07', '5010', '', '200', '0', '0', NULL, NULL),
(NULL, '13', '5', '2018-01-07', '1510', '', '-200', '0', '0', NULL, NULL);
INSERT INTO `1_trans_tax_details` VALUES (
(NULL, '13', '5', '2018-01-07', '1', '5', '1', '1', '380.95', '19.05', 'auto', NULL);
INSERT INTO `1_audit_trail` VALUES
(NULL, '13', '5', '1', '2018-01-07 10:09:06', NULL, '3', '2018-01-07', '0');
ST_SALESINVOICE
INSERT INTO `1_refs` VALUES
('5', '10', '001/2018');
INSERT INTO `1_debtor_trans` VALUES
('5', '10', '0', '1', '1', '2018-01-07', '2018-01-17', '001/2018', '1', '8', '400', '0', '0', '0', '0', '0', '0', '1', '1', '0', '0', '3', '1'),
INSERT INTO `1_debtor_trans_details` VALUES
(NULL, '5', '10', '103', 'iPhone Cover Case', '50', '2.38', '2', '0', '10', '0', '13'),
(NULL, '5', '10', '101', 'iPad Air 2 16GB', '300', '14.29', '1', '0', '200', '0', '14');
INSERT INTO `1_gl_trans` VALUES
(NULL, '10', '5', '2018-01-07', '4010', '', '-95.24', '0', '0', NULL, NULL),
(NULL, '10', '5', '2018-01-07', '4010', '', '-285.71', '0', '0', NULL, NULL),
(NULL, '10', '5', '2018-01-07', '1200', '', '400', '0', '0', '2', '1'),
(NULL, '10', '5', '2018-01-07', '2150', '', '-19.05', '0', '0', NULL, NULL);
INSERT INTO `1_trans_tax_details` VALUES (
(NULL, '10', '5', '2018-01-07', '1', '5', '1', '1', '380.95', '19.05', '001/2018', '0');
INSERT INTO `1_audit_trail` VALUES
(NULL, '10', '5', '1', '2018-01-07 10:09:06', NULL, '3', '2018-01-07', '0');2. Receive a payment for the customer and allocate it entireely to the above invoice:
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', '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');Note that the date of allocation is "0000-00-00" in 2 places above.
The actual date of payment is entered in the case of a Direct Invoice in Cash mode.
An audit trail entry is made after each transaction.
This should be useful for anyone trying to directly insert transactions..
All Account codes and rates are as per the demo chart of accounts - en_US-demo.sql.
2,060 01/15/2018 09:07:07 am
Re: Thai font shows ????? after upgrade software from 3.2.25 to 2.4.3 (1 replies, posted in Setup)
The db functions have now been moved to includes/db/connect_db_mysql.inc and includes/db/connect_db_mysqli.inc where the latter is the default preferred choice.
2,061 01/15/2018 05:53:46 am
Re: Void a Transaction Functionality (13 replies, posted in Setup)
There is no guarantee that the the $ref will have the $ref of the trans_no as the first part. It is better to rely on the pattern to extract it or rely on the trans_no directly. Consider the case of straddling fiscal years.
2,062 01/15/2018 05:51:03 am
Re: Importing from DacEasy (10 replies, posted in Setup)
Try the SQLyog community edition for a start - all Windows GUI for the MySQL Database.
Direct import from CSV and table creation / altering too in the GUI.
The "History" tab will show the SQLs used.
https://github.com/webyog/sqlyog-commun … /Downloads
Everyone starts off not knowing MySQL and assuming FA not to be a good fit but in time you will find is as user friendly as possible as any web app can be. Scout around for anything more user friendly and let us know so that FA can catchup as well.
2,063 01/14/2018 04:11:22 am
Re: Importing from DacEasy (10 replies, posted in Setup)
Making an Excel / Access table with the above data is trivial. It then will not need the BTrieve ODBC Driver and use the native MS Jet ODBC Driver for Excel / Access.
FICA (Employee and Employer Witholding Tax) - US - Federal Insurance Contributions Act
FUTA (Employer Only) - US - Federal Unemployment Tax Act
SICA (Employer Only) - US - State Unemployment Tax Act
So much for ease of doing business in the USA! No wonder they find it attractive to import from the rest of the world! All they need to do is print more money and run up trillions of dollars of debt.... Every country should follow suit - then there would be no one willing to export to anyone....in others currency! Then no more multi-currency in FA or no FA at all!
Wall Street = Legalised Casino.
Monetary Policy = Con Game.
The Code Book from The Bangladesh Mathematical Foundation.
2,064 01/13/2018 06:55:07 pm
Re: Invoice Editable (23 replies, posted in FA Modifications)
FA has just 1 series for each Voucher Type. A default Prefix can be used. The same number of characters must be in the final invoice number and it is editable. It will be preferable to have the same running number but with different prefixes to identify the Client/Location pair. Since both Client and Location are available in the transaction detail, a well crafted sql will be able to report when grouped by accordingly so as to remain within FA's single numbering series for all sales.
2,065 01/13/2018 06:50:05 pm
Re: Default currency account (5 replies, posted in Report Bugs here)
@itronics committed fix.
2,066 01/13/2018 02:30:43 pm
Re: problem at creating a pdf report in Arabic (3 replies, posted in Report Bugs here)
Ask someone else to view the page and see if they can see it or compare with their installs. We can all generate Arabic PDFs. Hope you have the necessary fonts installed in FA.
2,067 01/13/2018 10:59:07 am
Re: Print Sales Orders (5 replies, posted in Report Bugs here)
Follow @joe's instructions by choosing the smaller number in "From" and larger Number in "To".
If the problem still occurs in your installation, then troubleshoot as below.
The function get_sales_order_header() in sales/includes/db/sales_order_db.inc is where this error emanates.
It is possible that the database may be missing some records.
Try this set of SQLs (with your table prefixes - set as 1_ here in 9 places) for each of the error pairs of type and order number in phpMyAdmin or any MySQL client:
SET @typ = 30;
SET @ono = 28; -- test with 29, 38, 71, 82 and 462 where the error occurs as well
SELECT sorder.*,
cust.name,
cust.curr_code,
cust.address,
loc.location_name,
cust.discount,
stype.sales_type,
stype.id AS sales_type_id,
stype.tax_included,
stype.factor,
ship.shipper_name,
tax_group.name AS tax_group_name,
tax_group.id AS tax_group_id,
cust.tax_id,
sorder.alloc,
IFNULL(allocs.ord_allocs, 0)+IFNULL(inv.inv_allocs ,0) AS sum_paid,
sorder.prep_amount>0 AS prepaid
FROM 1_sales_orders sorder
LEFT JOIN (SELECT trans_no_to, SUM(amt) ord_allocs FROM 1_cust_allocations
WHERE trans_type_to=30 AND trans_no_to=@ono GROUP BY trans_no_to)
allocs ON sorder.trans_type=30 AND allocs.trans_no_to=sorder.order_no
LEFT JOIN (SELECT order_, SUM(alloc) inv_allocs FROM 1_debtor_trans
WHERE TYPE=10 AND order_=@ono GROUP BY order_)
inv ON sorder.trans_type=30 AND inv.order_=sorder.order_no
LEFT JOIN 1_shippers ship ON ship.shipper_id = sorder.ship_via,
1_debtors_master cust,
1_sales_types stype,
1_tax_groups tax_group,
1_cust_branch branch,
1_locations loc
WHERE sorder.order_type=stype.id
AND branch.branch_code = sorder.branch_code
AND branch.tax_group_id = tax_group.id
AND sorder.debtor_no = cust.debtor_no
AND loc.loc_code = sorder.from_stk_loc
AND sorder.trans_type = @typ
AND sorder.order_no = @ono;2,068 01/13/2018 07:50:07 am
Re: Print Customer Balance On Invoice (13 replies, posted in Reporting)
The fix for the invoice range is not to rely on any pattern but to rely on the transaction number and type directly. Hence, replace lines 39-40 of reporting/rep107.php:
." AND trans.reference>=".db_escape(get_reference(ST_SALESINVOICE, $from))
." AND trans.reference<=".db_escape(get_reference(ST_SALESINVOICE, $to))with:
." AND trans.trans_no BETWEEN ".db_escape($from)." AND ".db_escape($to)This will result in the $sql in the get range function to be like:
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.trans_no BETWEEN '1' AND '5'
ORDER BY trans.tran_date, trans.reference@joe: see if fit to commit.
2,069 01/13/2018 07:08:11 am
Re: Importing from DacEasy (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".
2,070 01/13/2018 07:04:01 am
Re: Print Sales Orders (5 replies, posted in Report Bugs here)
There is a problem with the new pattern for references if it straddles fiscal years. See here.
2,071 01/13/2018 06:56:39 am
Re: Print Customer Balance On Invoice (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.referenceWe 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?
2,072 01/13/2018 06:20:33 am
Re: Void a Transaction Functionality (13 replies, posted in Setup)
The said function just saves the reflines.pattern field during setting of the Setup => Transaction Preferences.
2,073 01/12/2018 03:49:34 pm
Re: Void a Transaction Functionality (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
2,074 01/12/2018 03:28:36 pm
Re: Can print without loading PDF files or without loading Excel (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.
2,075 01/12/2018 04:53:02 am
Re: is funiture fixed assets? (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%.