1,851

(20 replies, posted in Banking and General Ledger)

First off, I assume that you have no extensions installed that need db modifications.

A similar DB schema check will need to be done for all tables and for all records in lookup tables.
Note that the default document numbering system now has a pattern template in FA 2.4.x.

Some Chart of Accounts in FA 2.3.x and even in FA 2.4.x have field order differences with the standard sql/en_US-new.sql chart.

After synch-ing the chart, then see if an upgrade withing FA is necessary (Setup=>Software Upgrade). In each instance backup before and after such interventions.

1,852

(20 replies, posted in Banking and General Ledger)

Which Chart of Accounts did you start out in FA 2.3?
Do you have a backup before upgrade?
Can you check out the differences between the pre-upgrade sql schema and that of the last FA 2.3.26 version's sql/en_US-new.sql?

You should have done all this and then upgraded to FA v2.3.26 and then if all was well, attempt to do an upgrade to FA 2.4.4 and then verify that the resultant schema is in consonance with the tables and field orders and the new config.php settings and new sys_prefs table records - above all, the schema being in synch with the latest sql/en_US-new.sql.

1,853

(20 replies, posted in Banking and General Ledger)

The table definition of the 0_budget_trans table in FA 2.4.x is:

CREATE TABLE `0_budget_trans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tran_date` date NOT NULL DEFAULT '0000-00-00',
  `account` varchar(15) NOT NULL DEFAULT '',
  `memo_` tinytext COLLATE utf8_unicode_ci NOT NULL,
  `amount` double NOT NULL DEFAULT '0',
  `dimension_id` int(11) DEFAULT '0',
  `dimension2_id` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `Account` (`account`,`tran_date`,`dimension_id`,`dimension2_id`)
) ENGINE=InnoDB;

It has just 7 fields and the memo_ field is the 4th one.

Which Chart of Accounts did you start out with?

1,854

(0 replies, posted in FA Modifications)

When any code that has been specifically written for FA 5.4+ needs to be backported / integrated with FA in a backwards compatible manner, refer the GitHub project php-code-downgrade.

1,855

(26 replies, posted in Report Bugs here)

At Line 52 in reporting/rep101.php, the value of the $sql variable for the first available debtor_no in the second Date Range (2018) in the function get_open_balance() is:

SELECT 
    SUM(IF(t.type = 10 OR (t.type IN (0 , 1) AND t.ov_amount>0), -ABS(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges
   ,SUM(IF(t.type != 10 AND NOT(t.type IN (0 , 1) AND t.ov_amount>0), ABS(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits
   ,SUM(IF(t.type != 10 AND NOT(t.type IN (0 , 1)), t.alloc * -1, t.alloc)) AS Allocated
   ,SUM(IF(t.type = 10, 1, -1) * (ABS(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) - ABS(t.alloc))) AS OutStanding
FROM 1_debtor_trans t
WHERE t.debtor_no = '1'
  AND t.type <> 13
  AND t.tran_date < '2018-01-01'
GROUP BY debtor_no;

The output corresponding to the statements attached is:

charges    credits    Allocated    OutStanding
-6540        -6540            0            0

Do you want to zero out the Charges and Credits in this instance?

1,856

(9 replies, posted in Setup)

Wiki-ed it.

On the 4th day of the Official Release of FA 2.4.4, the SF Download stats are here.

Yes. Refer this commit.

Does this mean that all standard Chart of Accounts are made with Default Company permissions and that on creating a non default company with it does not automatically remove such Super Admin privileges for it?

1,860

(4 replies, posted in Announcements)

Pending Issues:

1. Accounting for expenses as part of Cost of Goods Purchased
2. Update Language Packs for Hindi and Tamil
3. Account and Type Ordering
4. Merge Customers
5. Unable to Edit or Void a Credit Note - Was postponed to later minor release earlier
6. Multiple Sales Orders into Single Delivery - Feature Request - Currently keep editing a Single Sales Order till delivery. Was postponed to later minor release earlier
7. Move add_access_extensions(); to header.inc from all themes and extensions - being studied for Ajax and related security issues.
8. Remember Inquiry forms date / date ranges
9. Clone and Edit Sales Quote
10. Zero price Supplier Invoice Item Entry
11. Voided Transaction Number re-used for new one errors out on old records present.
12. Security Role Permissions change on updation.
13. rep101.php opening balance issue.
14. Void Entries in Fiscal Year Closure - needed only for special cases.
15. Duplicate Short name of Supplier Fix - Milestone FA v2.5 but can fix manually if needed in FA 2.4.x itself.
16. Voided Entries in Sales Invoices Display error fix
17. Customer Credit Note Undefined Index Fix - Committed
18. Currency Conversion Provider CCA inclusion
19. Inventory Valuation
20. Speedup Indices postponed to 2.5+

It is pertinent to have such a message to accommodate records prior to inducting this table in FA and for records that straddled fiscal years when the older ones have been purged.

1,862

(15 replies, posted in Modules Add-on's)

This module was authored by @sticmann and his contact details are in it's config file. If you find a demo link or current development repo, please share it here. The code is provided for anyone to use it as a skeleton to develop it further.

1,863

(15 replies, posted in Modules Add-on's)

Yes. It is compatible with FA 2.3.x.
You will need to manually add it into your installed_extensions.php file (since it is a third part extension) and then activate it for your target company and then in the latter, enable permissions for the extension for the role of the user.

Unless you edit /delete any transaction, an audit trail will generally not occur.

For a brand new sql/en_US-new.sql based company, on entering 1 customer, 1 supplier and 1 item, there are no audit_trail entries at all. GL Transactions will certainly have records generated in the audit_trail table.

All audit_trail records except latest one should have the field gl_seq set to NULL to avoid need for subqueries (absent in MySQL 3) all over the code - see code and comments in function add_audit_trail() in includes/db/audit_trail_db.inc file.

Read the Wiki.

1,865

(9 replies, posted in Setup)

@poncho1234: It is not everyday we have the tax rates being changed. Hence your experience is valuable. Thanks for the feedback.

1,866

(9 replies, posted in Setup)

@poncho1234: Must we then create all new items for the new tax rate so as to preserve the old items with the old tax rate?

1,867

(9 replies, posted in Setup)

Make a credit note in the new fiscal with the new 15%. Pass an adjustment Journal Entry for the difference.

@Joe: What is the best practice here - an editable Tax Rate?

1,868

(9 replies, posted in Installation)

@oakstreet1: Thanks for the feedback as it will avoid sidetracking users especially newbies.

1,869

(4 replies, posted in Announcements)

Attached is the changed files list that can be used to upgrade FA from FA v2.4.3 to FA 2.4.4.

Take care to compare the config.default.php with your existing config.php file and make changes in the latter as needed for the new VARLOG/VARLIB constants introduced in this version.

Also verify that the data in the sys_prefs table in all your company's databases have the necessary records to match those in the new sql/en_US-new.sql file now. Refer this commit for an example.

1,870

(9 replies, posted in Installation)

Keeping your MySQL version to be <= 5.5 will avoid needing to enable date strict mode to allow for zero dates and times to be acceptable.

zLib compression - HowTo.

1,871

(9 replies, posted in Installation)

Are you working a Windows platform? If so, you may have to disable gettext in your php.ini and you will then get array based translation.

Use Manufacturing in FA.

I am yet to get a satisfactory answer from @itronics on the need for the number change on edition of a Journal Entry and as to how and where the code changed from FA 2.3.x to 2.4.x.

In one way, such editions will now clearly standout of sequence of other entries to indicate that some change has occurred - a sort of answerable audit trail exposure.

Taking the sql backup diffs between before and after adding a single Inventory Item, the following sequence of SQL statements are observed (Assumed TB_PREF=3_):

SET @StockID      := 'MT501';
SET @ItemDesc     := 'Camera 5 MP';
SET @ItemLongDesc := 'Digital Camera of 5 MP resolution';
SET @ReOrderLevel := '0';
SET @MBFlag       := 'B';    -- BoughtOut, Manufactured, D: Charges/Services
SET @CatID        := '1';    -- stock_category.component_id -- Components
SET @LocationCode := 'DEF';  -- locations.loc_code
SET @Units        := 'each'; -- item_units.abbr 
SET @TaxTypeID    := '2';    -- item_tax_types.id
SET @SalesAc      := (SELECT `value` FROM 3_sys_prefs WHERE `category` = "glsetup.items" AND `name` = "default_inv_sales_act");
SET @COGSAc       := (SELECT `value` FROM 3_sys_prefs WHERE `category` = "glsetup.items" AND `name` = "default_cogs_act");
SET @InventoryAc  := (SELECT `value` FROM 3_sys_prefs WHERE `category` = "glsetup.items" AND `name` = "default_inventory_act");
SET @AdjAc        := (SELECT `value` FROM 3_sys_prefs WHERE `category` = "glsetup.items" AND `name` = "default_adj_act");
SET @WIPAc        := (SELECT `value` FROM 3_sys_prefs WHERE `category` = "glsetup.items" AND `name` = "default_wip_act");

INSERT INTO `3_stock_master` VALUES
(@StockID, @CatID, @TaxTypeID, @ItemDesc, @ItemLongDesc, @Units, @MBFlag, @SalesAc, @COGSAc, @InventoryAc, @AdjAc, @WIPAc, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '', '0', '0', '0000-00-00', '0000-00-00', '');

INSERT INTO `3_item_codes` VALUES
(NULL, @StockID, @StockID, @ItemDesc, @CatID, '1', '0', '0');

INSERT INTO `3_loc_stock` VALUES
(@LocationCode, @StockID, @ReOrderLevel);

-- If a jpg image of the item was added to company #, then it would be named company/#/images/<StockID>.jpg
-- Here it would be: company/3/images/MT501.jpg
-- item_codes.id has no relevance in FA except for uniquely choosing the item and not even for sorting
-- Foreign Items can have different item_code and stock_id for the same item

Here, first a stock item is added to the stock_master..
Then one item_code is allotted for it which may be different from the stock_id if the item is a foreign one.
The stock_id is assigned to a location and a Re-Order level fixed for it.

The above can be useful for bulk importing of inventory items from disparate systems.

1,875

(13 replies, posted in Reporting)

@joe: What are the consequences of this pull request. @rafat seems satisfied.