Hi,
I am trying to add a brand name to certain products in our feed. I've made a look up list with all of the brand names that should be added. Now, I'm trying to write a business rule to append the brand to the beginning of the OfferName. This is what I've gotten so far, and cannot figure out if it is correct. Do I need to reference the look up list first, or concatenate first?
CONCATENATE(IF(ISINLIST("Add Brand to Name", $offername)),REGEXREPLACE($$offername,"\[[^\]]+\]","")
The REGEXREPLACE expression is a rule already applied to the name, so we need to keep it in this expression as well, correct?
Thanks for any help!
Jamie
Hi Jamie,
I think you're close. Think of the CONCATENATE as putting together two things:
* The item's brand (MAYBE: only if that brand is in a lookup list)
* The item's offername, cleaned up by the REGEXREPLACE
in other words, the rule will look something like this:
[code:1]CONCATENATE(,)[/code:1]
Let's take a look at the pieces in brackets, starting with the cleaned-up offername.
1. Looks like your REGEXREPLACE is removing square brackets and everything inside them; is that right? So, for example, you've got a title like:
Some [delete me] Widget
and you want it to look like:
Some Widget
So far, so good.
2. For the brand, I assume your items have a field called "brand." What you want to do here is figure out a little piece of a rule that will output that brand value if it's in a list, and output nothing if it's not in the list. What I would recommend is create the list so that the brand values are in both columns. So for example, if "Acme" and "Ajax" are two of the brands you want to prepend, make the lookup list look like this:
name value
Acme Acme
Ajax Ajax
If you do that, then the following will do what we want (assuming the list name is what you posted):
[code:1]LOOKUP("Add Brand to Name",$brand)[/code:1]
Now we'll put the two together:
[code:1]CONCATENATE(LOOKUP("Add Brand to Name",$brand),REGEXREPLACE($offername,"\[[^\]]+\]",""))[/code:1]
That's pretty close. However, if we do that, we'll actually wind up with the brand jammed right next to the offername:
AcmeSome Widget
What I would recommend is to use JOIN. JOIN works just like CONCATENATE, except it will stick spaces, or commas, or whatever you want in between the pieces. So our pattern looks like this:
[code:1]JOIN(" ",,)[/code:1]
and the output would be:
Acme Some Widget
But now we have a new problem: if the brand is NOT in the lookup list, we'll get an extra space at the beginning. Not a problem; we just wrap a TRIM around the whole thing:
[code:1]TRIM(JOIN(" ",LOOKUP("Add Brand to Name",$brand),REGEXREPLACE($offername,"\[[^\]]+\]","")))[/code:1]
And that should do it.
Thanks Anthony! That worked for all sites except Google. We currently have the following business rule applied to our Google Base template:
IFBLANK(LOOKUP("Alternate Product Names", $model), REGEXREPLACE($$offername,"\[[^\]]+\]",""))
I tried to add it in to the formula you gave, however it wasn't valid...
TRIM(JOIN(" ",LOOKUP("Add Brand to Name",$brand),IFBLANK(LOOKUP("Alternate Product Names", $model) REGEXREPLACE($offername,"\[[^\]]+\]","")))
Did I put it in the wrong spot?
Thanks so much for your help!
Looks like you're missing a comma and a close paren:
[code:1]TRIM(JOIN(" ",LOOKUP("Add Brand to Name",$brand),IFBLANK(LOOKUP("Alternate Product Names", $model),REGEXREPLACE($offername,"\[[^\]]+\]",""))))[/code:1]
Believe me, I've had that happen to me tons of times; hard to keep track of those parens.
That worked...thanks!
Glad I could help.