Importing customer csv data in FA v2.3.6 (Page 2 of 3)

ok, I tried the mods but it just splorked so for now I'm going to use the default install from snapshot. At least I got the Canadian CoA to work, and I also deleted company#2 and ran it from default 0_ company because some features weren't available to the admin user and I didn't want to jump back and forth. I'll send what I found re CoA once I'm sure it's working.

Tks, A

Good Morning @apmuthu, yesterday I was able to get our Prestashop connector working well with the new CoA, transferring inventory items and clients. So we'll get our year end inventory figures inputted so now I can start using the inventory control system and issue FA purchase orders. This should be a big help because we have warehouse stock in several different countries, with sales in several different currencies (being in the electronics import/export business).

I think my approach with the mods was too hasty, trying to update everything at once, so now I'm going to see if I can develop a procedure to do them one by one, as it should be much easier to debug. I'm glad that the FA file and db structure is so well organized; this will make the process a lot easier to understand.

I have created a folder structure on my local machine that replicates the FA folder structure. This new replicated structure contains only the modified files from your repository site, and it will make it easier to upload only one change at a time and study the result.

Regards, Adrian

Hi @apmuthu, thanks for the tips and the kind words. Frankly, I would have been completely lost if it was not for your very thorough and prompt assistance. This project is one of the most complex I have ever tackled, and definitely not for the faint hearted.

Speaking about the time zone latency, you seem to be another one that never sleeps, like some others I know!

Regards, Adrian

Hi @apmuthu, I think the wiki IS very good, and I probably would have just continued with that, if I hadn't discovered this problem of field reversal in the inventory items page. It was at that point I felt I needed to talk to somebody in the know, and luckily I got you.

But that all came about because of my assumption that Softaculous would automatically install the latest and greatest version, so my bad on that. This thread would probably not exist if I had just followed the regular install instructions.

Regards, Adrian

Try making it:

  PRIMARY KEY  (`parent`,`loc_code`,`component`,`workcentre_added`),
  KEY `component` (`component`),
  KEY `id` (`id`),
  KEY `loc_code` (`loc_code`),
  KEY `workcentre_added` (`workcentre_added`)

to remove redundant indices but that is not the fix.

The fields parent and component refer to stock_id which is varchar(20) in the other tables and hence these two should be varchar(20) too but that should not generally be the cause of any problems except for type matching and index based joins.

The following sql will achieve the above changes:

ALTER TABLE `0_bom` 
    CHANGE `parent` `parent` VARCHAR(20) DEFAULT '' NOT NULL, 
    CHANGE `component` `component` VARCHAR(20) DEFAULT '' NOT NULL, 
    DROP INDEX `parent`, 
    DROP PRIMARY KEY, ADD PRIMARY KEY (`parent`, `loc_code`, `component`, `workcentre_added`); 

The file / function that is in error is:

includes/db/manufacturing_db.inc => update_bom()

function update_bom($selected_parent, $selected_component, $workcentre_added, $loc_code, $quantity)
{
    $sql = "UPDATE ".TB_PREF."bom SET workcentre_added=".db_escape($workcentre_added)
     . ",loc_code=".db_escape($loc_code) . ",
        quantity= " . $quantity . "
        WHERE parent=".db_escape($selected_parent) . "
        AND id=".db_escape($selected_component);
    check_db_error("Could not update this bom component", $sql);

    db_query($sql,"could not update bom");
}

should be

function update_bom($selected_parent, $selected_component, $workcentre_added, $loc_code, $quantity)
{
    $sql = "UPDATE ".TB_PREF."bom SET workcentre_added=".db_escape($workcentre_added)
     . ",loc_code=".db_escape($loc_code) . ",
        quantity= " . $quantity . "
        WHERE parent=".db_escape($selected_parent) . "
        AND component=".db_escape($selected_component);
    check_db_error("Could not update this bom component", $sql);

    db_query($sql,"could not update bom");
}

The only two functions there that use the id field in the 0_bom table are:

• delete_bom()

• get_component_from_bom()

*** The fix listed above for the file manufacturing_db.inc is not correct ***
It has been superceeded by a later commit in my GitHub Repo. - Thanks @itronics for pointing me in the right direction.

You will nevertheless need the fixes in my previous post as the error in update_bom() will show up when used.

Study the backup file sql and compare it with the original and see where Navicat (that specific version/build) falters. It is possible that your Navicat install expects the keyword "FOREIGN KEY" instead of the plain and possibly ambiguous "KEY" or it is expecting the equivalent "INDEX", in which case it is likely to affect quite a few tables!

The entire fix is now in the FAMods in my GltHub repo.

Spot on! That's the right way for human readability. But... linux has a patch command to auto-magically traverse thru the folder tree and apply such "diffs" (can be generated from WinMerge - Unified Diff and adjusting for CRLF=>LF UnixDos differences in line endings).

Since there was one other key in the table that mimicked the first part of the proposed new re-arranged  Primary Key, we call it a redundant index and have it removed.

Making a new CoA from the en_US-new.sql will be the right way to proceed. The one in my updated FAMods folder has all the changes necessary. You need to change the order of currencies and their default id and rate value to 1 for the home currency making it the first one and the default currency in the users table and in the sys_prefs tables' data. If you add new account heads then space them numerically in steps of 10 or 5 and huddle them together logically for ease of sifting thru later. You may also want to adjust the fiscal_years table to suit your current and future installs.

@joe: In fact a few posts earlier, we found that the thread evolved into a FA Setup Tips topic and I wikied it as such for want of some forum feature to fork a part of it as a separate topic... This new topic can begin with the 13th post in this thread onwards.

For those who partook of my fix in the 35th post in this thread can now update it with the superceeded commit.

Does the official extension "import_transactions" serve the purpose?

You may even update the files in the package after installation but before activation / usage with the ones in my github repo.

In fact I have just completed a few backports and non field db data into reports (Min Order, Prepaid Freight, etc) in my FAMods besides the en_GB-general.sql complete fix in the extensions.

The devs have categorically said they will only provide critical updates to the v2.3.x branch henceforth and I expect a final v2.3 release is hence due.

On the 7th the extensions were queried and an update was expected but all were frozen as of 28th last. I have also done a thorough clean up of all 38 official languages including the yet unaccepted Tamil (ta_IN) even though it has more phrases translated than some of those already there.

The easiest way to do a fresh CoA is to
1. Import the en_US-new.sql into a fresh database
2. Export it from your mysql client schema + data - to say one.sql
3. Make changes as you see fit in the tables that contain data - Currencies, date/time formats, company defaults, sys_prefs, chart_masters, fiscal years, etc.
4. Export it to say two.sql.
5. Use Winmerge to sift the differences and see how they compare with the existing Canadian one.
6. Make changes to two.sql but retain a blank audit_trail table and the standard users table from the US CoA except for some specifics for Canada. That way you'll preserve the default admin access / password and not bother with session based timestamp changes.
7. Remove the CREATE DB and USE DB commands in two.sql.
8. Now you have your CoA - two.sql - rename it to en_CA-general.sql and upload it in the place of your officially installed one so that the config entries remain.

The ones I maintain have the schema without the collations bunched together and the insert statements entered later all in one bunch with those that vary between CoA's alone towards the end. That way when making a new CoA, we just need to copy this and make changes to the data towards the end alone!

In fact my suggestion was to keep the schema and standard data in one sql file and the rest that changes between CoAs alone be used from separate sql files. It remains unaccepted due possibly to accommodate some queer collations and out of turn insertion of data where strict InnoDB and other RDMS constructs will make import fail on cascade dependencies.

Committed the mod for printing the name of the person who generated the sales quotation. Needs to be activated in line 19 of doctext.inc - depending on whether the one in the overriding company/#/reporting/includes folder is used or the native one in reporting/includes folder is used.