Stock by Attributes 5.0 Beta (for v1.5.0)

[mc12345678]

EP4's Attrib-Basic-EP exports and can re-import the following fields:
v_products_model
v_products_options_type
v_products_options_name_1
v_products_options_values_name_1

I thought it might help!

Okay, so I don't know which version you have installed, but Version 4.0.22 - Beta 6-10-2012 includes Detailed Products Attributes (detailed multi-line) option which downloads the data I was referring to: v_products_attributes_id, v_products_id, v_options_id, v_options_values_price

The version of Stock by Attributes that I ended up installing (still not sure if it was a Creinold version or more recent, has a data table called:
products_with_attributes_stock (of course need to consider any prefix when addressing table names).

This table has 5 columns to include data:
stock_id
products_id
stock_attributes
quantity , and
sort

stock_id is autogenerated and is the key for the data table. That means that if an item is not already tracked by products_with_attributes_stock then adding a new item to the list will give it a new stock_id. If the item and its intended attributes is already tracked than the stock_id would be needed to be able to revise the stock for that set of attributes. (I would suggest for your situation that the new stock of items not have any existing stock quantities assigned through products_with_attributes to simplify the following process.)

Now regarding the import of all of your items, here is how I would suggest the quickest (also dirtiest) method of populating it.

I believe you could use easy populate to add all possible/anticipated attributes to the items you are adding. (ie color and size if those are the two attributes in question), it also may be easiest when adding those attributes to incorporate the price associated with the different sizes, and if "color" (aluminum, wood, painted, etc...) have different prices, then when adding those attributes to include those costs as part of populating them.

Then, use easypopulate to export the Detailed Products Attributes. Take this data and place it into your preferred spreadsheet (essentially removing the delimiters throughout). Then to populate the products_with_attributes, you will need to pull out the attributes numbers (v_products_attributes_id) that when grouped together identify the attributes applicable to a single item and concatenate them with a comma. Place that information into a single row of data such that you have product_id and stock_attributes adjacent to each other, then the next column would be your stock (quantity), and if desired a sort order. (I'm not entirely sure that the sort order is respected, but I have made efforts to assign one as desired.

Then you can create a generic SQL statement that concatenates with the data to the left of it. This sql statement would be to populate the products_with_attributes_stock data table.

Next would be to either export just that column or copy the column to a new sheet and export it so that the delimiter is a return/carriage return/enter.

Then import/paste that file into either your mySQL admin panel or Zen-Cart's admin panel->Tools->Install SQL Patches and voila, your data table is populated.

A similar process could likely be used to plus up any quantity, with the appropriate SQL statement to add to instead of replace or create a new value. The only thing there, is that I think the stock_id would need to be known/obtained so that there is only one line item of stock by attributes for each set of attributes for the stock.

So, regarding the concatenation of the applicable attributes: if there were two products having two attribute types, each with two attributes:
(X,Y), (A, B) and (1, 2) respectively, then it would make sense to have stock in X-A1B1, X-A2B1, X-A1B2, X-A2B2, Y-A1B1, Y-A2B1, Y-A1B2, and Y-A2B2; however, not X-A1A2, X-B1B2, Y-A1A2, Y-B1B2, etc...

The attributes data dump shows each attribute type that is assigned to each product. So the data dump would contain something similar to:

X A 1
X A 2
X B 1
X B 2
Y A 1
Y A 2
Y B 1
Y B 2

By creating a new column that would associate the line to attribute 1, and attribute 2, it may be easier to identify the rows that contain attributes to be concatenated and then iterate through all products and combinations of ids that would identify a line item that would support import into the products_with_attributes table.

That's essentially what I was referring to before about building on other data.

In converse, if the products with attributes table is populated with all of the combinations of attributes after the attributes have been added to all of the products, then the products_with_attributes table could be exported, populated with the quantities, then reimported either by sql statement or straight table update with the new data (stock_id would be exported and therefore the quantities could be replaced.) That would address modification of quantities of items "in bulk".

Several ways to approach, depends on where you are at, how much work is involved to get you where you want to go, experience, etc...

Some things discussed above may be different depending on the implementation of stock by attributes that is used. For example, I seem to recall that the sequence of numbers associated with stock_attributes can be important in that the first attribute(s) are such that they are the ones that offer choices, while the remaining attributes may be just input blocks.

[Kevin205]

Okay, so I don't know which version you have installed, but Version 4.0.22 - Beta 6-10-2012 includes Detailed Products Attributes (detailed multi-line) option which downloads the data I was referring to: v_products_attributes_id, v_products_id, v_options_id, v_options_values_price

The version of Stock by Attributes that I ended up installing (still not sure if it was a Creinold version or more recent, has a data table called:
products_with_attributes_stock (of course need to consider any prefix when addressing table names).

FYI - I do use Detailed Products Attributes (detailed multi-line)from EasyPopulate 4.0, downloadable from Github https://github.com/chaddro/EasyPopulate-4.0. Support thread is http://www.zen-cart.com/showthread.p...Support-Thread. (BB LINK function does not work here)

Then, use easypopulate to export the Detailed Products Attributes. Take this data and place it into your preferred spreadsheet (essentially removing the delimiters throughout). Then to populate the products_with_attributes, you will need to pull out the attributes numbers (v_products_attributes_id) that when grouped together identify the attributes applicable to a single item and concatenate them with a comma. Place that information into a single row of data such that you have product_id and stock_attributes adjacent to each other, then the next column would be your stock (quantity), and if desired a sort order. (I'm not entirely sure that the sort order is respected, but I have made efforts to assign one as desired.

Then you can create a generic SQL statement that concatenates with the data to the left of it. This sql statement would be to populate the products_with_attributes_stock data table.

Next would be to either export just that column or copy the column to a new sheet and export it so that the delimiter is a return/carriage return/enter.

Then import/paste that file into either your mySQL admin panel or Zen-Cart's admin panel->Tools->Install SQL Patches and voila, your data table is populated.

My problem is that, I do not know any thing about SQL statements!

So, regarding the concatenation of the applicable attributes: if there were two products having two attribute types, each with two attributes:
(X,Y), (A, B) and (1, 2) respectively, then it would make sense to have stock in X-A1B1, X-A2B1, X-A1B2, X-A2B2, Y-A1B1, Y-A2B1, Y-A1B2, and Y-A2B2; however, not X-A1A2, X-B1B2, Y-A1A2, Y-B1B2, etc...

98% of time any given product have 2-6 size attribute values with different prices and quantities.

Several ways to approach, depends on where you are at, how much work is involved to get you where you want to go, experience, etc...

I can get around code (an old programmer) with no knowledge SQL and SQL statements!

What you have stated makes sense, but I need a little more clear direction. Thank you.

[mc12345678]

FYI - I do use Detailed Products Attributes (detailed multi-line)from EasyPopulate 4.0, downloadable from Github https://github.com/chaddro/EasyPopulate-4.0. Support thread is http://www.zen-cart.com/showthread.p...Support-Thread. (BB LINK function does not work here)



My problem is that, I do not know any thing about SQL statements!


98% of time any given product have 2-6 size attribute values with different prices and quantities.


I can get around code (an old programmer) with no knowledge SQL and SQL statements!

What you have stated makes sense, but I need a little more clear direction. Thank you.

So, was thinking about this some more... Based on having only a single attribute (size) for the product that has multiple selections within, the first question is, if you assigned three sizes to a product, and then populated the stock such that there was one that indicated a zero quantity, would the customer see the third option on your site, or would they only see two options available? If the answer is yes they only see two options, then the following will probably be the quickest easiest way to accomplish your add in.

Look at your total number of sizes available, regardless of to what product they are to be assigned.

Use EP to add that sized attribute to every product. (ultimately this should not be difficult/time consuming as if there are 7 sizes in the entire grouping, then every item will have 7 records to be created and through a spreadsheet program you can use relational equations to produce the results repetatively.)
Ideally all items that are of size 1 are the same price, all items of size 2 are the same price (but different than size 1), etc... If not, then would recommend populating that field in some methodic manner. Perhaps 80% of them are the same, so populate 100% with that price and then go fix the 20% that are different.

Download the attribute information as performed before focusing on just the items that are to be populated. Again, because you only have a single attribute to address, the remaining portion of this will be simpler than previously described.

Create a new column to populate the quantity for that item's attribute. (This part may take a while depending on what you have to do to populate it.) Again assuming that if an attribute has a zero quantity that the attribute will not be shown to the customer, then assign 0 for values you do not have, and assign positive numbers for those you do have.

Access your SQL database, open the products_with_attributes table, identify the exact name of that table (remembering there may be a prefix to that table name and all table names in your database) this will be needed for your SQL statement that is to be generated next. Also identify the fields that makeup your table, noting the entire name including any underscores etc...

If I am correct the general structure of the table has remained the same, it has been all about how it is populated and operated on that has changed throughout the various versions of stock by attribute. If they are different than I previously posted, may need to modify the following approach.

Assuming that no data exists in your products_with_attributes table that include the product numbers (product_id) for the items/attributes just added, then the following SQL statement can be generated concatenating the data that is in the sheet produced from the dump before.

Your SQL statement will be to Insert a new row of data to the (prefix_)products_with_attributes table assigning the row's column data equal to a value in the row of your spreadsheet. Future SQL statements using essentially this process would be Update or Replace statements to increase the quantities of the items in the (prefix_)products_with_attributes table.

INSERT INTO (prefix_)products_with_attributes (products_id, stock_attributes, quantity, sort) VALUES (`column1`, `column2`, `column3`, `column4`);

Note: (prefix_) would be replaced by whatever the prefix for the table is. column1 would be the value associated with products_id (v_products_id) which would be in your table, column2 would be the stock_attributes (v_products_attributes_id), column3 the quantity in the column created above, and column4 would be the sort order that possibly also should be added, but could be set as 10 for the first attribute, 20 for the second, etc..)

In Excel, if I had v_products_id in column A, v_products_attributes_id in column B, the quantity in column C, and a sort order in Column D, with the first row of data in row 2 (Titles in row 1), then in E2 I would put the following:

="INSERT INTO products_with_attributes (products_id, stock_attributes, quantity, sort) VALUES (`"&A2&"`, `"&B2&"`, `"&C2&"`, `"&D2&"`);"

Then I would copy this down to the end of the list of items/attributes to be added.

I would then copy and paste column E to a new worksheet and export the data to a CSV file (to support text capturing the data to feed back into the database). with the delimiter being an enter or carriage return or similar.

Then as said before either copy all of that into the applicable space to populate the tables (admin panel or mySQL admin area).

As a side note and depending on how you get your data together, it is possible to have a single SQL statement that adds multiple rows at once, the SQL statement could be:

="INSERT INTO products_with_attributes (products_id, stock_attributes, quantity, sort) VALUES (`"&A2&"`, `"&B2&"`, `"&C2&"`, `"&D2&"`), (`"&A3&"`, `"&B3&"`, `"&C3&"`, `"&D3&"`);"

This statement would add two rows, each additional entry has to be separated by a comma and have the same sized groupings throughout.

There are alternate ways to formulate the statement as well if that doesn't work on your server. (Ie. do the above work for a row or two, try it out and then get into a groove of updating... :) )

[mc12345678]

I would then copy and paste column E to a new worksheet and export the data to a CSV file (to support text capturing the data to feed back into the database). with the delimiter being an enter or carriage return or similar.

Unfortunately the time had elapsed for me to modify the message again; however, the above statement was incorrect when performed by the majority of people using Excel. More correctly:

I would then copy column E and paste (paste special->values) in a new worksheet and then export the worksheet to a CSV file (to support text capturing the data to feed back into the database). (At least with Excel (again there is a more preferred/consistent application recommended for CSV export) this will place each row (SQL Statement) on its own line and the contents of the file can then be copied/imported into the SQL processor of choice.)

[mc12345678]

Lastly, if the above is successful, the next issue would be managing the quantities of stock available. I do not recall where in this thread or perhaps it is in a similar thread related to stocking by attributes, but there have been ways posted about how to obtain stock data for existing quantities. If that data can be obtained with the stock_id, then a similar process could be used to populate the additional stock through a SQL statement.

UPDATE `(prefix_)products_with_attributes` SET `quantity` = `quantity` + `(new quantity obtained from spreadsheet or other source)` WHERE `stock_id` = `(the stock_id value for the row in question)`;

Note: Above, the use of parantheses () is to indicate that the value is calculated/obtained from somewhere such as a spreadsheet. The parentheses are not to be included in the actual statement. Also the use of single quotes around a number is not mandatory, but seems to be recommended.

I would recommend also that if the above is successful that questions related to development of an export option of the products_with_attributes table be addressed on the easypopulate thread. A response may be to self develop which may be possible using the existing code/instruction to handle this situation, but that aspect is more about EP than it is about stock by attributes... It just happens that in this case EP became a tool to support stock by attributes.

Good luck!

[bubbadan]

Is it just me? I haven't seen anything about making the actual input page collapsible for each product?
There just seems to behind having all the product open to one huge scrolling page.
Seems to make more sense if each product would be collapsible so that you click it to open it?


And why doesn't it sort by input?
This seems to be more trouble than it is worth!

Very difficult to enter all 24 attributes for one product when it doesn't display them in the input page with any semblance of order?
Right now it comes up (For example)

Stock ID Variant Quantity in Stock Edit Delete
20 Color: Black 30 Edit Quantity Delete Variant
Size:: Small
32 Color: Red 30 Edit Quantity Delete Variant
Size:: X- Small
21 Color: Black 30 Edit Quantity Delete Variant
Size:: Medium
36 Color: Red 30 Edit Quantity Delete Variant
Size:: X-Large
26 Color: Black 30 Edit Quantity Delete Variant
Size:: 2X-Large

I enter them in order - per size color etc.
What am I doing wrong? ...if I am that is.

Thanks

[mc12345678]

Is it just me? I haven't seen anything about making the actual input page collapsible for each product?
There just seems to behind having all the product open to one huge scrolling page.
Seems to make more sense if each product would be collapsible so that you click it to open it?


And why doesn't it sort by input?
This seems to be more trouble than it is worth!

Very difficult to enter all 24 attributes for one product when it doesn't display them in the input page with any semblance of order?
Right now it comes up (For example)

Stock ID Variant Quantity in Stock Edit Delete
20 Color: Black 30 Edit Quantity Delete Variant
Size:: Small
32 Color: Red 30 Edit Quantity Delete Variant
Size:: X- Small
21 Color: Black 30 Edit Quantity Delete Variant
Size:: Medium
36 Color: Red 30 Edit Quantity Delete Variant
Size:: X-Large
26 Color: Black 30 Edit Quantity Delete Variant
Size:: 2X-Large

I enter them in order - per size color etc.
What am I doing wrong? ...if I am that is.

Thanks

Honestly, those are a few questions I've had myself, but if I had spent the time to investigate have forgotten the answers.

Knowing that there are a few versions of software that accomplish the same basic task, could you possibly identify which you have installed? Ie creinold's, the one developed/revamped/overhauled by robophung?

It seems the majority of the work on this project has been on the store front side of the house and the underlying data storage/manipulation. Good idea though to address collapsing the group of stock for an item. My guess is that just need to encompass that list with appropriate html and voila. As for the order of attributes when identifying quantities, I think (though not sure) that this is based on the order of option names being added to the database (not necessarily the sort order or the like). This also depends on which version of the software is used. It may be that the SQL statements that pull the data need to have a sort order applied to them. Again, I think most of the programmers here are trying to get something that produces cart results and will come back to make it better for the admins. Goal is to sell right?

[Kevin205]

Where can I downloaded this plugin from?

[cefyn]

Where can I downloaded this plugin from?

post #207 in this thread has the latest,I think.

[wmestaff]

Has anyone found a solution as to why the attributes are not inputted in any particular order? Also, I am having a few other problems myself.

1) I noticed that when you go to 'Add Quantity For Product Variant' on the 'Products with Attributes' page, the drop downs on that page (/products_with_attributes_stock.php?action=add&products_id=...) are what is reflected on the individual product listing in the store front, where customers view the product. I have changed the SQL (in file pad_base) to sort by products_options_values_sort_order and this is successful for single drop downs, but it does not seem to affect sequenced drop downs at all. Does anyone know where/how to change the SQL to sort the drop downs on the 'Add Quantity For Product Variant' page? I believe these are somehow connected and this may solve my issue in the store front. Here is an example of what I am attempting to describe: http://workingmans.com.previewdns.co...roducts_id=201

You can see that the attributes are loaded into the array and in the correct sort order (denoted by [order]), but when viewing the second sequenced drop down, the values are not by sort order (they are, instead, in the same order as on the 'Add Quantity For Product Variant' page).


2) I also am having trouble loading the 2nd attribute box for sequenced drop downs, but for only some of my products. After some observation, I believe this is due to the Stock ID (seen on the Products with Attributes Stock page) being too large, possibly out of rage. It seems the values that are too large end up being somewhere around ID 10000 or greater. Does anyone else have this problem, or know how to fix it? Here is the issue that I am referring to: http://workingmans.com.previewdns.co...roducts_id=198

The 2nd sequenced array does not display, despite the attributes being loaded into the array. This product worked before (albeit the 2nd array values were out of order, as described in my previous issue), but when I deleted the attributes and re-added them (thus giving them a new stock ID in the 12000 range), the 2nd drop down no longer displayed.

Sorry for the long post, I hope this is in the correct place. I am using both Stock by Attributes and Dynamic Drop Downs.