Hi Guys,

I use the pDescription2 field to store an alternate product code (which is not shown on the site).

This is because some of my manufacturers make identical products and sometimes one of them will be out of stock, so I can then order the equivalent from another manufacturer.

What I want to achieve is this:

On the order emailed to the store, the product code shows in the details column as 1234 for example.

When an alternative is available (i.e. pDescription2 is NOT empty) I want the product code to show as *1234 (adds an asterisk to the product code)

Any awesome coders out there that could point me in the right direction?

cheers
Ross


Ross Fraser
ross@chain-me-up.com.au
https://www.chain-me-up.com.au

Hi Ross

Just bumping this up for you in case anyone has missed it and has any suggestions.

Andy

Please feel free to review / rate our software

Thanks Andy,

I imagine that in the incemail.asp file, I would have to open the product table and find the matching pDescription2 field for any products purchased and test if it was empty or not.

I'm not hot on database access, hence my problem.

cheers
Ross


Ross Fraser
ross@chain-me-up.com.au
https://www.chain-me-up.com.au

Hi, I am far from an "awesome coder", but I get lucky sometimes.
I had a look and it's going to be a bit more than a simple if/then statement.

You would need a select statement to open the products table.

something like (this is not actual code that should work, just a general idea) sSQL="SELECT pID,pDescription2 FROM products WHERE pID="&rs("cartID")

My initial thinking is look for the variable assigned for the product ID in the store email.
The write an if/then statement that goes something like (this is not actual code that should work, just a general idea)

If pDescription2 <>"" then
cartProdID = "*" & cartProdID
end if

I am working on a few things at the moment, but in a few days, I may be able to work this out on my dev site.
Unless you have a test site you can run through it yourself.

Thanks Dave,

I'll have a play around later today (hopefully) and let you know how it goes.

cheers
Ross


Ross Fraser
ross@chain-me-up.com.au
https://www.chain-me-up.com.au

Hi Dave,

I tried and failed!

I think that this line - sSQL="SELECT pID,pDescription2 FROM products WHERE pID="&rs("cartID")

should be - sSQL="SELECT pID,pDescription2 FROM products WHERE pID="&rs("cartProdID")

but I kept getting a 500 server error when running the rs.open sSql statement.

I'd love to see if you can make it work,
cheers
Ross



Ross Fraser
ross@chain-me-up.com.au
https://www.chain-me-up.com.au

On my dev site, I have friendly errors turned off. That will give you the clue of what the problem is. I will see if I can take a look at this in a few days.

We are still working on shipping orders that came in on Cyber Monday...

Well, I've gotten pretty far on this, but the only way it works, is it would add the asterisk prefix to all emails it seems and it actually changes the product ID in the admin and adds the asterisk prefix.

I am trying some conditional statements and some alternate placement of my code, but no luck yet.
I'll take another look tomorrow.

Hi Dave,

sounds like you are making more progress than I did.

It doesn't really matter that all emails get the asterisk, shop is required, customer is neither here nor there.

If you are only querying the product database from the incemail.asp file, I don't understand why the product id would change in the admin?

Thanks for your efforts so far,
cheers
Ross


Ross Fraser
ross@chain-me-up.com.au
https://www.chain-me-up.com.au

We are not just querying the database, we are trying to change the cart product ID to something different
But only in the email.

The body of the email is created within a function function getrecpt(oid) starting around line 16 and ending a few hundred lines down.
This is where all of the tables are created and the products and any options are created.
My instinct tells me to work in this area and that is where I successfully add the asterisk when pDescription2 has some content.

But the select statement (around line 140) and opening of the connection is rs.open sSQL,cnn,0,1 and the "1" there means that the items selected are "locked" and
cartProdID cannot be changed rs("cartProdID") = "*" & rs("cartProdID") unless I change the opening of the connection to rs.open sSQL,cnn,0,2
ADODB.Recordset error '800a0cb3' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
I don't like that approach because I don't know what knock on effects will occur from changing (unlocking) that.
We don't want to open your site to a cross site scripting attack or something nasty like that.

It may be that we need to assign a variable for cartProdID and then change that in the email that's being sent.
I am still working through the code to get a better understanding of how it's all tied together.

Hi Ross, I am close.
I can do all emails, but making an effort to change it on store owner emails only.

David

Hi Dave,

that's great.

It is only important that the email to the store has the *

It doesn't matter if the customer has it on their invoice.

Thank you so much for all your efforts.

cheers
Ross


Ross Fraser
ross@chain-me-up.com.au
https://www.chain-me-up.com.au

Hi Ross, I have tried to work it out where the asterisk only shows on the store owners email and I just can't get it working right.
Perhaps we revisit another time and try again.
I also realized I am working on the incemail.asp script from version 6.8.1 and there have been some changes in 6.8.2, but I don't think that is going to affect this bit of code.
I have only tested this on emails being resent from the admin, so be sure to place a test order at the front end of your store to be sure it's all working as expected.
I would save an unmodded copy on the server in case you have to quickly restore the original file.

2 CHANGES TO BE MADE
STEP 1
starting around line 140 you could see the code below in red and you need to add the part in green so it looks like below.


JUST BELOW THAT AROUND LINE 162
STEP 1
Fine the following line and change the part in red to the part in green



Hopefully you don't get flooded with calls as to what the asterisk means...

Ok, I did think of a way just now to remove the asterisk on the customers email, but it would remove any asterisk that appears anywhere in the email.
So for example if the customer has typed in an asterisk somewhere in the additional information section, it would be removed. Although I think that would be unsual, and you would still see it in your admin, so maybe you can use this.

Down near the bottom of the incemail.asp script, you will see the following batch of code and you need to add what I have there in green.

Hi Dave,

that all works like a charm.

Thank you so much for all your efforts, I'm very appreciative.

Cheers
Ross


Ross Fraser
ross@chain-me-up.com.au
https://www.chain-me-up.com.au

No worries Ross.

Hi Dave,

this section of code messes up the email to the customer

if sendcustemail then
if htmlemails=TRUE then
Call DoSendEmailEO(custEmail,sEmail,"",replace(replace(emailsubject, "%ordername%", ordName), "%orderid%", sorderid), IIfVs(NOT hasheaders,emlhdrs & "<body class=""receiptbody"">") & replace(replace(replace(emailheader, "%messagebody%", replace(recpt,"<!--%digidownloadplaceholder%-->","<tr><td align=""right""><strong>"&xxDigPro&":</strong></td><td align=""left"" colspan=""3"">" & digidownloademail & "</td></tr>")), "<br />", emlNl), "*", "") & IIfVs(NOT hasheaders,"</body></html>"),emailObject,themailhost,theuser,thepass)
else
Call DoSendEmailEO(custEmail,sEmail,"",replace(replace(emailsubject, "%ordername%", ordName), "%orderid%", sorderid),IIfVr(trim(xxTouSoo)<>"",xxTouSoo & emlNl & emlNl, "") & replace(replace(replace(emailheader, "%messagebody%", orderEmailText), "<br />", emlNl), "*", ""),emailObject,themailhost,theuser,thepass)
end if
end if

It looses formatting but I can live without it.

see - https://www.ecommercetemplates.com/support/topic.asp?TOPIC_ID=109142

cheers
Ross

Ross Fraser
ross@chain-me-up.com.au
https://www.chain-me-up.com.au

Hi Ross, Are you sure it's that? Because all we are doing there is saying if there is an asterisk, replace it with a blank space.
Otherwise, that is all stock ect code. Just a simple addition.
My emails to myself in testing were fine.

I'll see if I can send you a message and then you can email me a copy of the incemail.asp script you have.

Hi Dave,

absolutely sure.

I tried changing the * to a # (in both sections of code) and the same happens, the email loses its formatting.
(I thought the asterisk might be acting as a wildcard)

Anyway, removing the lower code with the replace functions causes it to work correctly.

Just to be absolutely clear, the email to the store is perfect, the problem is only with the email to the customer.

cheers
Ross


Ross Fraser
ross@chain-me-up.com.au
https://www.chain-me-up.com.au