EasyPopulate 4.0 Support Thread

[cefyn]

Hi MC , fancy meeting you here . Zen Cart 1.5.4 ,yesterdays EP4 update . SBA 1.5.4 .When I import SBA-Stock-EP2015 whether edited in office libre or excel I get this error on every product.
MySQLi error 1062: Duplicate entry '' for key 'idx_customid'
When executing:
UPDATE products_with_attributes_stock SET
quantity = 1, customid = ''
WHERE (
stock_id = 122 )
Perhaps you could help an inaccurate and unreliable operator to get back on the right track. Sorry, had to laugh at your comment on the SBA thread , I probably have made a stupid mistake somewhere.

[mc12345678]

Hi MC , fancy meeting you here . Zen Cart 1.5.4 ,yesterdays EP4 update . SBA 1.5.4 .When I import SBA-Stock-EP2015 whether edited in office libre or excel I get this error on every product.
MySQLi error 1062: Duplicate entry '' for key 'idx_customid'
When executing:
UPDATE products_with_attributes_stock SET
quantity = 1, customid = ''
WHERE (
stock_id = 122 )
Perhaps you could help an inaccurate and unreliable operator to get back on the right track. Sorry, had to laugh at your comment on the SBA thread , I probably have made a stupid mistake somewhere.

Liked that, huh? :)

So, the obvious solution is to provide a unique customid for each and every product variant... But if that were the only solution then how could the SBA plugin work without having a unique customid for each and every variant?

Couple of things going on here, I just had to figure out a good way to resolve it... See, the customid is designated as a unique key in the table definition for SBA, which means that every customid (that has data) is supposed to be different from every other customid. But unique keys (different than a primary key) are supposed to also be allowed the value NULL...

Okay, so it could be set to null... But then there is the issue that some servers treat '' as null or reject null expecting ''... So sure could omit the customid from the update if it is not provided, but then what if it were being reset to blank/NULL? Still needs to get updated to blank/NULL without losing the other properties of the entry...

Why does it seem that this issue has "snuck in"? When this feature was initially developed, I was advised that there would be a unique customid applied to each and every product (and was provided an example file dataset to match), so.. Naturally when I was testing it I followed suit and had a unique customid applied to each such product... No issues, right? Well, no one till now had pointed out the flaw which was that the case of populating the SBA database with stock quantities failed for product that did not have a customid (same thing will happen if it does have a customid but it is not unique compared to all other customid entries). Not sure I totally agree with the later requirement, but seems there was a need for it so I don't want to go mess that up...

So, I could upload a revision 4.0.31a to fix the issue above... It's not so significant to warrant a complete revision number change I think, just a bug fix... or I could provide the below code fix.. :) The problem is seen in both the stock and the detailed imports, so corrections are provided for both below.

Now, as for sorting by name.... :) If going to add that as a feature, then probably should consider all the methods of sorting, or provide a way/instruction on how to setup the export of the SBA file to be sorted by name... (to make it quicker)... Afterall, the stock export is all about doing an inventory in a store that uses SBA, whereas the detailed SBA export is all about the SBA only aspect of the data... For some both data dumps are relatively the same, but for others that use SBA to "highlight" some product and not on all product, then the detailed SBA data relatively speaking will have far fewer rows than the sba stock dump... (Yes, I know that it is possible to have many many variants for a single product causing this "number of rows" discussion to be way off...)

For the detailed import fix, find in admin/easypopulate_4_import.php approximately line 397:

          $sql = $db->bindVars($sql, ':customid:', $items[$filelayout['v_customid']], 'string');

and replace with:

          $sql = $db->bindVars($sql, ':customid:', (zen_not_null($items[$filelayout['v_customid']]) ? $items[$filelayout['v_customid']] : 'NULL'), (zen_not_null($items[$filelayout['v_customid']]) ? 'string' : 'passthru'));

For the stock import fix, find in admin/easypopulate_4_import.php approximately line 494:

      } elseif ($items[(int)$filelayout['v_SBA_tracked']] == "X") {

Replace:

        } elseif ($items[(int) $filelayout['v_SBA_tracked']] == "X") {
          $sql = "UPDATE " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " SET 
					quantity					    = " . $items[(int) $filelayout['v_products_quantity']] . ($ep_4_SBAEnabled == '2' ? ", customid  = '" . $items[(string) $filelayout['v_customid']] . "' " : "") . ") . "
					WHERE (
					stock_id = " . $items[$filelayout['v_table_tracker']] . " )";
          if ($result = ep_4_query($sql)) {
            zen_record_admin_activity('Updated products with attributes stock ' . (int) $items[$filelayout['v_table_tracker']] . ' via EP4.', 'info');
            $display_output .= sprintf(EASYPOPULATE_4_DISPLAY_RESULT_UPDATE_PRODUCT, $items([int) $filelayout['v_products_model']]) . $items[(int) $filelayout['v_products_quantity']] . ($ep_4_SBAEnabled == '2' ? " " . $items[(string) $filelayout['v_customid']] : "");
            $ep_update_count++;
            if ($sync) {
              $stock->update_parent_products_stock((int) $query[$items[(int) $filelayout['v_products_model']]][(int) $filelayout['v_table_tracker']]);
              //		$messageStack->add_session('Parent Product Quantity Updated', 'success');
              unset($query[$items[(int) $filelayout['v_products_model']]]);
            }
          } else { // error Attribute entry not found - needs work!
            $display_output .= sprintf('<br /><font color="red"><b>SKIPPED! - SBA Tracked Quantity ' . ($ep_4_SBAEnabled == '2' ? 'and CustomID ' : '') . 'on Model: </b>%s - Not Found!</font>', $items[(int) $filelayout['v_products_model']]);
            $ep_error_count++;
          } // if 

        } //end if Standard / SBA stock

With:

        } elseif ($items[(int) $filelayout['v_SBA_tracked']] == "X") {
          $sql = "UPDATE " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " SET 
            quantity              = " . $items[(int) $filelayout['v_products_quantity']] . ($ep_4_SBAEnabled == '2' ? ", customid  = :customid: " : "") . "
                    WHERE (
                    stock_id = " . $items[$filelayout['v_table_tracker']] . " )";
          $sql = $db->bindVars($sql, ':customid:', (zen_not_null($items[$filelayout['v_customid']]) ? $items[ $filelayout['v_customid']] : 'NULL'), (zen_not_null($items[$filelayout['v_customid']]) ? 'string' : 'passthru'));
          if ($result = ep_4_query($sql)) {
            zen_record_admin_activity('Updated products with attributes stock ' . (int) $items[$filelayout['v_table_tracker']] . ' via EP4.', 'info');
            $display_output .= sprintf(EASYPOPULATE_4_DISPLAY_RESULT_UPDATE_PRODUCT, $items[$filelayout['v_products_model']]) . $items[$filelayout['v_products_quantity']] . ($ep_4_SBAEnabled == '2' ? " " . $items[$filelayout['v_customid']] : "");
            $ep_update_count++;
            if ($sync) {
              $stock->update_parent_products_stock((int) $query[$items[(int) $filelayout['v_products_model']]][(int) $filelayout['v_table_tracker']]);
              //        $messageStack->add_session('Parent Product Quantity Updated', 'success');
              unset($query[$items[(int) $filelayout['v_products_model']]]);
            }
          } else { // error Attribute entry not found - needs work!
            $display_output .= sprintf('<br /><font color="red"><b>SKIPPED! - SBA Tracked Quantity ' . ($ep_4_SBAEnabled == '2' ? 'and CustomID ' : '') . 'on Model: </b>%s - Not Found!</font>', $items[(int) $filelayout['v_products_model']]);
            $ep_error_count++;
          } // if 

        } //end if Standard / SBA stock

[cefyn]

That's fantastic MC , the EP4 export/import SBA facility works perfectly with these edits. Thank you so much for the time and trouble you have taken to help me out.

[mc12345678]

That's fantastic MC , the EP4 export/import SBA facility works perfectly with these edits. Thank you so much for the time and trouble you have taken to help me out.

Absolutely no problem, it's a shame that anything had to be done at all as that feature has been available for quite some time now and no one has commented about the issues involved.. Doesn't have to be a big production, and as have seen I only discovered the issue when it was commented about on some other forum... :)

I do recall that at the time of development there was a bit of a clock ticking and only some of the specific functionality was requested (meaning anything extra was a "bonus" but not a development requirement). I do know that a basic import/export will eventually follow; however, I've been wanting to wait until SBA structure and building is settled/finalized... It's expected to be somewhat complicated (meaning lot's of code to put together/write) and I'd rather write it one time and be done not having to revise it significantly because of significant changes in the SBA code/processing/structure.

For anyone reading, hopefully can see how easy it can be to have an issue corrected with proper information, methods of duplicating the issue and posing the question(s)/issue(s) to the proper location...

[Looper]

Gotcha :)
Thanks

[BlackOrchidCouture]

Hi all! So I have ZC v154 and have used Easy Populate 4 for around a year with no issues, and before that another version of EP for many years, so I know the ins and outs. I recently downloaded an EP file using the manufacturer filter so I could just update one category of products. I edited the file and uploaded it, and when I import the file I'm getting the error "No model field in record. This line was not imported". I know for a fact that the v_products_model fields are all filled in, I didn't change anything in that column.

So, I went onto GitHub and saw there was a newer version of EP4, so I installed that. It now looks different in my back end because of the updated version, but it is still doing the same error when I import the files (and I've tried a few files).

Ok, I've done a little more experimenting, and if I create a new EP file in my admin and import that, it imports fine and says the products have been updated. If I take this file and download it to my computer and then upload it again and import it, it also works. So it's somehow in editing my file, or renaming it, this edited file is the one that then says no model field in record. The only column I'm editing is the description, so I'm not sure what's happening.

Any ideas??? Thanks!

[mc12345678]

Flename of revised file?

Looked at upload file with plain text editor to see that quotes are appropriately applied after save?

[BlackOrchidCouture]

Ok, I'm a dork. I've been working on this for days. I just came back to my latest edited file and realized that just ONE of my model id fields had become blank somehow. So I'm not sure what was happening with the previous day's errors, but I think that upgrading to the newest version of EP4 helped, then I somehow deleted on of the model ID fields. Anyway, I just fixed that field, and uploaded/imported, and it worked!!!!!

Yeah, I'm a dork.....

Sorry for any confusion!!

[mc12345678]

No, actually the way that you presented it indicated that it was something in the process on your side, so glad it was something easy to address. :) course thought was that all items in the list were an issue not just a single line. But hopefully there won't be a need to seek help in the future. of course if ever do, know where to swing by for assistance. :)

[BlackOrchidCouture]

Thanks :)