Thread: Available at the following locations...

I've posted something like this before, but I am not sure I am making sense.

We have 13 locations and each location is assigned a number (StoreID). I want to store that StoreID in the Procut or ProductVariant table, with each location being seperated by a comma. From there, I want to be able to have some sort of an IF statement, so on the Product page, when a customer clicks on an item, underneath the price, it will read:

Available at the following location(s):

Then have each store number correlate with it's actual location. SO, if 01,03,29 are in the table, then it would list:

Store 01
Store 03
Store 29

I know I need this in the table(s) and I know I need to do something in the XM package, but I am not all that versed in XML as of yet, although I have made some modifications already.

Could someone please help with this and kind of walk through it step-bystep?

Thank you SO much!

~D

Is there a particular reason you want to store each location reference separated by a comma rather than the store names themselves? Also do you really need to store it comma separated like that, wouldn't it be better to directly input the info as XML nodes in the ExtensionData field?

Basically I think dayhawk covered it pretty well in this other thread but you don't seem to fully understand his answer. The XML node code he suggested should be put directly into the ExtensionData field in your Product table. This means you don't need to worry about cross referencing store numbers with store names (although you could still do this if you really wanted).

You will want to add his suggested query to the XML package for the pages you want this functionality on.. this might be something like "product.SimpleProduct.xml.config".

He then defines XSL parameters which in themselves won't do much but are used here as a sort of coding shortcut. So.. to display the first store using dayhawk's answer you would need to use the following bit of code (which you might want to place inside some kind of IF statement or loop)

Code:

<xsl:value-of select="$loc1"/>

Hope this helps!

~O

Originally Posted by Orangey

Is there a particular reason you want to store each location reference separated by a comma rather than the store names themselves? Also do you really need to store it comma separated like that, wouldn't it be better to directly input the info as XML nodes in the ExtensionData field?

Basically I think dayhawk covered it pretty well in this other thread but you don't seem to fully understand his answer. The XML node code he suggested should be put directly into the ExtensionData field in your Product table. This means you don't need to worry about cross referencing store numbers with store names (although you could still do this if you really wanted).

You will want to add his suggested query to the XML package for the pages you want this functionality on.. this might be something like "product.SimpleProduct.xml.config".

He then defines XSL parameters which in themselves won't do much but are used here as a sort of coding shortcut. So.. to display the first store using dayhawk's answer you would need to use the following bit of code (which you might want to place inside some kind of IF statement or loop)

Code:

<xsl:value-of select="$loc1"/>

Hope this helps!

~O

Well, right now, we only use StoreID's for our stores. We do not have the store names anywhere. That being said, I would think that I would need to do some sort of CASE statement for each store name.

I understand to some degree about storing that code in the ExtensionData field (although we're using that field already for something else). Is there another field that can be used?

How would that product know which store(s) it is located in for a potential customer to see?

Sorry if I seem slow today . . . lack of sleep and I just can't wrap my head around this for some reason.

Thank you very much!

~D

Anyone . . . ? Please . . . ?

Originally Posted by donato

Well, right now, we only use StoreID's for our stores. We do not have the store names anywhere. That being said, I would think that I would need to do some sort of CASE statement for each store name.

What version of the storefront are you working on?

I'm not sure why you'd need to use CASE statements but really that is only worth worrying about at the end when you've managed to actually store your data and then get something to display on your product pages. I wouldn't even worry about messing about with CASE statements or whatever else until you've got that basic functionality working.

Originally Posted by donato

I understand to some degree about storing that code in the ExtensionData field (although we're using that field already for something else). Is there another field that can be used?

I think using the ExtensionData field is there for scenarios like this one... however if you're already using it then I guess you could either extend the table to include another field or preferably create a new table which has the ProductID as the primary ID thing (and whatever other fields you also need) so you can then do a JOIN on the two tables and essentially extend the Product table without having to modify it.

Originally Posted by donato

How would that product know which store(s) it is located in for a potential customer to see?

I'm not really sure what you mean here... If each ProductID is listed in a table, one of the other fields in that table would contain the info about which store(s) it is in. You would have to put all this data in yourself, probably through some SQL update or an insert if you're making a new table. It would work exactly the same way as all the other product information is stored and displayed on a unique ProductID basis.

I'm not sure where you're getting stuck so I'm not sure where to try and help. What have you tried to do so far and what problems have you run in to? I still think the answer in this other thread should be enough to get something working.

Originally Posted by Orangey

What version of the storefront are you working on?

I'm not sure why you'd need to use CASE statements but really that is only worth worrying about at the end when you've managed to actually store your data and then get something to display on your product pages. I wouldn't even worry about messing about with CASE statements or whatever else until you've got that basic functionality working.




I think using the ExtensionData field is there for scenarios like this one... however if you're already using it then I guess you could either extend the table to include another field or preferably create a new table which has the ProductID as the primary ID thing (and whatever other fields you also need) so you can then do a JOIN on the two tables and essentially extend the Product table without having to modify it.



I'm not really sure what you mean here... If each ProductID is listed in a table, one of the other fields in that table would contain the info about which store(s) it is in. You would have to put all this data in yourself, probably through some SQL update or an insert if you're making a new table. It would work exactly the same way as all the other product information is stored and displayed on a unique ProductID basis.

I'm not sure where you're getting stuck so I'm not sure where to try and help. What have you tried to do so far and what problems have you run in to? I still think the answer in this other thread should be enough to get something working.

Thank you so much, Orangey. You're really taking the time to help me and I appreciate it more than you know.

I think I may be complicating this . . .

I think that creating a new table would be best. Do you think it would be best to insert the StoreIDs seperated by a comma in a single field or should I insert a new record for each StoreID (location) pertaining to that product?

I am on version 8.0.1.2.

Thank you once again,

~D

Technically, you should have 2 new tables. A table to keep the store information, and a table to contain the relationships between products and stores. As you are on v8 you can create a table called 'Store' and 'ProductStore', but you couldn't do this on v9, because there are already tables called 'Store' and 'ProductStore'.

Code:

CREATE TABLE [dbo].[Store](
	[StoreID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL
-- any other extra column data you need in here
) ON [PRIMARY]


CREATE TABLE [dbo].[ProductStore](
	[ProductID] [int] NOT NULL,
	[StoreID] [int] NOT NULL
) ON [PRIMARY]

Originally Posted by esedirect

Technically, you should have 2 new tables. A table to keep the store information, and a table to contain the relationships between products and stores. As you are on v8 you can create a table called 'Store' and 'ProductStore', but you couldn't do this on v9, because there are already tables called 'Store' and 'ProductStore'.

Code:

CREATE TABLE [dbo].[Store](
	[StoreID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL
-- any other extra column data you need in here
) ON [PRIMARY]


CREATE TABLE [dbo].[ProductStore](
	[ProductID] [int] NOT NULL,
	[StoreID] [int] NOT NULL
) ON [PRIMARY]

I see . . . then how would I call this in the XML?

Thank you SO much!

Originally Posted by donato

I see . . . then how would I call this in the XML?

Thank you SO much!

Sorry to be a pain, but does anyone know how I would call this in the XML from the two tables (or however)?

Thanks again!

Use the same method as I described earlier and as described in the other answer in the other thread which I keep linking to. You'll have to use an SQL query at the top of your xsl template and then reference some of the xml from that later on in the template page (like in the answer in the other thread).

What bit are you getting stuck on? What are you trying that is currently not working as expected?

Originally Posted by Orangey

Use the same method as I described earlier and as described in the other answer in the other thread which I keep linking to. You'll have to use an SQL query at the top of your xsl template and then reference some of the xml from that later on in the template page (like in the answer in the other thread).

What bit are you getting stuck on? What are you trying that is currently not working as expected?

Thank you. As I had said before, I'm quite new to XML/XSL . . .

In that case it might be a good idea to look at some basic xslt tutorials and guides such as http://www.w3schools.com/xsl/ and then look through the xml package templates which the storefront uses as this should help you understand how they work.

You might also want (on a dev environment) to change the debug flag at the top of your xml package to "on" as this will let you view all of the xml which is being returned and then manipulated by the template. This will give you some help with the x-path you will need to use when displaying whatever values you need. (don't leave this mode on by accident if you put the page live)

Originally Posted by Orangey

In that case it might be a good idea to look at some basic xslt tutorials and guides such as http://www.w3schools.com/xsl/ and then look through the xml package templates which the storefront uses as this should help you understand how they work.

You might also want (on a dev environment) to change the debug flag at the top of your xml package to "on" as this will let you view all of the xml which is being returned and then manipulated by the template. This will give you some help with the x-path you will need to use when displaying whatever values you need. (don't leave this mode on by accident if you put the page live)

Thank you VERY much!

Originally Posted by Orangey

In that case it might be a good idea to look at some basic xslt tutorials and guides such as http://www.w3schools.com/xsl/ and then look through the xml package templates which the storefront uses as this should help you understand how they work.

You might also want (on a dev environment) to change the debug flag at the top of your xml package to "on" as this will let you view all of the xml which is being returned and then manipulated by the template. This will give you some help with the x-path you will need to use when displaying whatever values you need. (don't leave this mode on by accident if you put the page live)

OK. Please bare with me . . .

I already have this at the top of my XML.config file:

<query name="Extensions" rowElementName="Product" retType="xml">
<sql>
<![CDATA[
SELECT Product.ProductID,
CONVERT(xml, ExtensionData) as ExtensionData
FROM Product
WHERE Product.ProductID = @ProductID
FOR xml path('Product')
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" />
</query>

Now, how would I go about querying the new tables and match that to the ProductID in the web page? I want to be able to display each location that item is available at, so I am assuming I will need to do some sort of loop and if it is NOT available at any location, then display a message that states, "Sorry, but this item is not available at the moment. Please check back soon."

I am sorry, but I am more of a "show me it once - I get it" type of learner.

Is there any way you could please give me an example, given the tables I just created (thank to you) on how I would accomplish such a task? I know if I were to see it first, then I would get it immediately. I know it may sound like a poor excuse, but it has worked for me my whole life.

Thank you very much in advance . . .

~D

I have something like this at the top . . .

<query name="StoreLocation" rowElementName="Product" retType="xml">
<sql>
<![CDATA[
SELECT Product.ProductID, ProductStore.StoreID, Store.Name
CONVERT(xml, Name) as StoreName
FROM Product, ProductStore, Store
WHERE Product.ProductID = @ProductID
AND ProductStore.StoreID = Store.StoreID
AND Product.ProductID = ProductStore.SKU
FOR xml path('Product')
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" />
</query>

Now I have this at the top:

<query name="StoreName" rowElementName="Store" retType="xml">
<sql>
<![CDATA[
SELECT Product.ProductID, ProductStore.StoreID, Store.Name,
CONVERT(xml, Store.Name) as Name
FROM Product, ProductStore, Store
WHERE Product.ProductID = @ProductID
AND ProductStore.StoreID = Store.StoreID
AND Product.ProductID = ProductStore.SKU
FOR xml path('Product')
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" />
</query>


And I am trying to call it this way:

<i>
*Available at the following location(s): <xsl:value-of select="aspdnsf:StringResource(/root/StoreName/Store/Name)" disable-output-escaping="yes" />
</i>

However, nothing is showing on the page . . . I still think I need to do some sort of loop, correct?

Thanks again . . . .

~D

Firstly are you sure your SQL query is working as intended? I'd try running it against the database directly (replacing the @ProductID with an actual productID to test) using some SQL databasey programmy thing like SQL Express.

If you're certain the query is correct (and I cant really help too much with that as I don't know how your tables are set up) then perhaps the problem is your x-path in the xsl-select. The best way to check would be to enable debug mode.

As far as your loop goes.. you could probably just put an <xsl:for-each> tag around your xsl-select.

Originally Posted by Orangey

Firstly are you sure your SQL query is working as intended? I'd try running it against the database directly (replacing the @ProductID with an actual productID to test) using some SQL databasey programmy thing like SQL Express.

If you're certain the query is correct (and I cant really help too much with that as I don't know how your tables are set up) then perhaps the problem is your x-path in the xsl-select. The best way to check would be to enable debug mode.

As far as your loop goes.. you could probably just put an <xsl:for-each> tag around your xsl-select.

Thank you SO much for getting back to me!

I now have this and it works like a champ!

SELECT Product.ProductID, ProductStore.StoreID, Store.Name,
CONVERT(xml, Store.Name) as Name
FROM Product, ProductStore, Store
WHERE Product.ProductID = '1050'
AND ProductStore.StoreID = Store.StoreID
AND Product.ProductID = ProductStore.ProductID
FOR xml path('Product')

However, I am getting this error now (I have no idea why) which states the following:

XmlPackage Exception: Exception=Last Trace Point=[]. Expression must evaluate to a node-set.


System.ArgumentException: Last Trace Point=[]. Expression must evaluate to a node-set.

at AspDotNetStorefrontCore.XmlPackage2.TransformStrin g() at AspDotNetStorefrontCore.AppLogic.RunXmlPackage(Xml Package2 p, Parser UseParser, Customer ThisCustomer, Int32 SkinID, Boolean ReplaceTokens, Boolean WriteExceptionMessage)

Honestly, I have no idea what this means . . .

And this is what I have in the XML:

<i>
*Available at the following location(s): <xsl:for-each select="aspdnsf:StringResource(/root/StoreName/Store/Name)" />
</i>

And if I just try this:

*Available at the following location(s): <xsl:value-of select="aspdnsf:StringResource(/root/StoreName/Store/Name)" disable-output-escaping="yes" />

The page loads, but nothing is showing after "*Available at the following location(s):" . . .

I'm not sure what to do from here . . . Thank you so much for your help.

~D

Sorry I wasn't more clear about the for-each looping thing... try it like this:

Code:

<xsl:for-each>
<xsl:value-of select="/root/StoreName/Store/Name" disable-output-escaping="yes" />
</xsl:for-each>

I also don't know if you need retType="xml" in your query at the start, are you sure your expected store data is appearing correctly in the returned xml when you enable debug mode?

Try enabling debug mode within the xml package and searching for the <StoreName> node within the text which should have children of <Store> and then possibly multiple <Name> entries with differing values (one for each store I'd imagine?).

Originally Posted by Orangey

Sorry I wasn't more clear about the for-each looping thing... try it like this:

Code:

<xsl:for-each>
<xsl:value-of select="/root/StoreName/Store/Name" disable-output-escaping="yes" />
</xsl:for-each>

I also don't know if you need retType="xml" in your query at the start, are you sure your expected store data is appearing correctly in the returned xml when you enable debug mode?

Try enabling debug mode within the xml package and searching for the <StoreName> node within the text which should have children of <Store> and then possibly multiple <Name> entries with differing values (one for each store I'd imagine?).

Hmm... when I do it that way, I get this error:

Missing mandatory attribute 'select'. Am I supposed to have the select statement inside the for-each tag?

When I try this: <xsl:for-each select="/root/StoreName/Store/Name"></xsl:for-each> I get nothing. I just get my *Available at the following location(s): text....

I also tried putting aspdnsf:StringResource() in there as well and passing the oath in there, but that's when I get that error again...

And with the XML debugging turned on, I DO see the store locations in the runtime:

<StoreName>
<Product>
<ProductID>1050</ProductID>
<StoreID>01</StoreID>
<Name>MT. PLEASANT, PAMT. PLEASANT, PA</Name>
</Product>
<Product>
<ProductID>1050</ProductID>
<StoreID>02</StoreID>
<Name>SOUTH HILLS, PASOUTH HILLS, PA</Name>
</Product>
<Product>
<ProductID>1050</ProductID>
<StoreID>11</StoreID>
<Name>MIDDLEBURG, OHMIDDLEBURG, OH</Name>
</Product>
<Product>
<ProductID>1050</ProductID>
<StoreID>12</StoreID>
<Name>NORTH OLMSTED, PANORTH OLMSTED, PA</Name>
</Product>
<Product>
<ProductID>1050</ProductID>
<StoreID>15</StoreID>
<Name>THE POINTE, PATHE POINTE, PA</Name>
</Product>
<Product>
<ProductID>1050</ProductID>
<StoreID>17</StoreID>
<Name>CENTURY III, PACENTURY III, PA</Name>
</Product>
<Product>
<ProductID>1050</ProductID>
<StoreID>18</StoreID>
<Name>OAKWOOD VILLAGE, PAOAKWOOD VILLAGE, PA</Name>
</Product>
<Product>
<ProductID>1050</ProductID>
<StoreID>20</StoreID>
<Name>MONROEVILLE, PAMONROEVILLE, PA</Name>
</Product>
<Product>
<ProductID>1050</ProductID>
<StoreID>23</StoreID>
<Name>AKRON, OHAKRON, OH</Name>
</Product>
<Product>
<ProductID>1050</ProductID>
<StoreID>25</StoreID>
<Name>CANTON, OHCANTON, OH</Name>
</Product>
<Product>
<ProductID>1050</ProductID>
<StoreID>27</StoreID>
<Name>MENTOR, OHMENTOR, OH</Name>
</Product>
<Product>
<ProductID>1050</ProductID>
<StoreID>29</StoreID>
<Name>WEXFORD, PAWEXFORD, PA</Name>
</Product>
</StoreName>

So... should it be /root/StoreName/Product/Name? I tried that and it doesn't work either....

And this is what it looks like next to the product:

*PRICING IS FOR THE LEG TABLE.

*Available at the following location(s):

[I don't understand why the locations are not listing under here or even near here anywhere?]

Even this isn't working:

<xsl:for-each select="root/StoreName/Store">
<tr>
<td>
<xsl:value-of select="Name"/>
</td>
</tr>
</xsl:for-each>

Oh yeah sorry I made an error in the for-each bit, I've obviously been out of the LOOP for a while.

It should be something like this:

Code:

<xsl:for-each select="/root/StoreName/Product">
<xsl:value-of select="Name" disable-output-escaping="yes" />
</xsl:for-each>

I think it'll be something like that?

Originally Posted by Orangey

Oh yeah sorry I made an error in the for-each bit, I've obviously been out of the LOOP for a while.

It should be something like this:

Code:

<xsl:for-each select="/root/StoreName/Product">
<xsl:value-of select="Name" disable-output-escaping="yes" />
</xsl:for-each>

I think it'll be something like that?

YOU DID IT!!!!!!!!!!!!! I didn't have disable-output-escaping="yes".

THANK YOU SO MUCH!!!!!!!!!! I cannot thank you enough!

Please check your email soon . . .

No worries, was happy to help and glad you got it sorted in the end.