Reusing Spare fields in Product table

[tonygg]

Hi. I need to store some extra stock information (2 fields) for each product. Basically it is simply the breakdown of the stock between 2 types. Sorry about the long explanation before the question....

I know I can create custom variable but this makes them very difficult to use in Access. We use Access to make bulk changes in stock levels.

We do not need Sellerdeck to understand the information. The only requirement in sellerdeck is to be able to see the 2 values when we go into a product tab from within sellerdeck.

We are currently using the weight and alt weight fields to hold the 2 values. This worked fine but we now have to stop using them because we need to change to shipping by weight (to workaround a bug in sellerdeck exclude from shipping code).

Are there other fields in the product able that I could use and that I could get displayed in the product tab? Is there a field reference guide to the product table to help me find them?

Alternatively is it possible some kind person who knows access could show me how to display custom variables along side the product data in a table in access?

Many thanks

Tony

[NormanRouxel]

If you're not using Extended Info pages, then you could put your information into Extended Info / Text. That's a big field so you could put several bits of info in with a delimiter character between them.

[NormanRouxel]

And see http://community.sellerdeck.com/arch...p/t-49736.html where I show 3 ways to access User Variables from the database. They are for use with SD's built-in PHP but the queries would be similar for external programs.

[Andy@codepath]

Hi Tony

Spare fields are best avoided for the reasons you've already come across but if you do want to go down that route, you could use the two values in the Prompts tab. You would however need to modify the product page markup, and possibly the scripts, to prevent values in these fields from triggering Sellerdeck to ask these questions to the buyer during checkout. I would need to dig deeper to see if/how you'd do that.

Alternatively, the query below will list your products with the custom variable values next to them in Access. The downside is that there will be 1 row per custom variable (i.e. each product listed twice if you want to list 2 custom variable values. Just replace YOURVAR1 and YOURVAR2 with the names of each custom variable.

SELECT p.[product reference], p.[short description], v.[sname], u.[sValue]
FROM (product AS p LEFT JOIN userdefinedproperties AS u ON p.[product reference] = u.scontentID) LEFT JOIN variable AS v ON v.[nID] = u.nVariableID
WHERE ((v.sName = 'YOURVAR1') or (v.sName = 'YOURVAR2'))
ORDER BY p.[product reference], v.[nID]

Finally, if neither is suitable, our Catalog Export plug-in could produce a more readable product list, with the variable values 'in-line' (i.e. 1 row per product), in an Excel output format. More info here. We could also help you automate the process of transferring the data from the current spare fields to the custom variables, if this would be an arduous task manually.

Hope this helps.

[tonygg]

Thank you both for the help and the quick reply. I will look into both options. I guess if I can get the query to work using the custom fields might be the cleanest way. It would be really nice to get both custom fields onto the same row.

[tonygg]

Andy.

This query really helps. Thank you so much - I would not have worked it out in a month of sundays. I was hoping to beg for a bit more help on 2 points around the query:

1. I can live with the 2 rows if necessary but I need to add up these 2 values and compare the sum to the single value nStockOnHand (in the product table) to ensure they are equal. If they are not I would just mark the background of nStockOnHand in red. I know how to do this if the variables are in 1 row but don't have a clue on how to do it when they are on 2 rows. Is there a simple way to do this (or any pointers please)?

2. The query returns a load of product references that contain "!" (which I guess are duplicates?) Is there a way to filter these out of the query so it only returns the actual product?

Many thanks once again.

Tony

[tonygg]

I have managed to answer point 2 on my list but just cant see anyway of doing point 1. Any pointers or tips would be greatly appreciated.

Norman - I have looked at your three methods but must confess I don't know how to put them into access. They seem to be sellerdeck code. Do you happen to have versions that can work in Access 2010. (sorry if that is a stupid question)

Thanks once again

Tony

[Andy@codepath]

if you add (nProductType=0) to the "where" clause, this will exclude the ! products (duplicates) and also any product fragments, which are stored in the same structure.

Summing the custom variables would require bringing them in-line for each product which I don't think you could do in a simple query. I have responded to your telephone message via direct email with the two possible solutions we could offer.