Replace Regex help

[Marloes]

Hi guys

Hope anyone can help me with a replace regex formula.

I have 2 fields

1. Instant - Product ID’s
2. Correction product ID

What I would like to accomplish is the this.

If(IsEmpty([Correctie product ID]), [Instant - Product ID’s], else I want [Instant - Product ID’s] where the [Correctie product ID] is being replace by “” (so basicly nothing) )

Is this somehow possible?

Thanks!

[Chr1sG]

Not sure I get what you’re trying to accomplish. Can you give a concrete example of the values and what the result should be

[Marloes]

Hi Chris

• In field number one the result could be something like: [1] [6] [15] [21]
• In field number two the result could be something like: [1] OR [6] [21]

The desired result is field 1 - field 2.

• So in the first example the outcome should be → [6] [15] [21]
• So in the second example the outcome should be → [1] [15]

Is this clear enough?

[Chr1sG]

So you want only those numbers which exist in [Instant - Product ID’s] and not in [Correction product ID]?

[Marloes]

Yes! That is correct.

[Chr1sG]

I don’t think it is possible with a single regexreplace formula since it is implicitly performing a find/replace for an unknown number of possible values.

If you know that the [Correction product ID] field can only ever contain values from a fixed set, then you could theoretically write a multi-nested regexreplace formula, but it would be v ugly.
(and I’m assuming it’s not the case for you anyway)

What is the ultimate objective?
I’m guessing you’re trying to compare the IDs of the enities in two collection fields…?
If so, is it possible that you could use an automation to update the [Instant - Product ID’s] field every time the [Correction product ID] is changed?

[Marloes]

Ah, makes sence.

Well the ultimate objective is in line with this topic. So indeed…

@Chr1sG

compare the IDs of the enities in two collection fields…?

Since there is no way to union two collection fields using a formule we have created a workaround like Yvette explained in the other post.

But we also need to be able to make corrections, when a lead isn’t interested in a product anymore. So to make it happen we

In our case I can’t see how an automation could update the [Instant - Product ID’s], because it is a join formula based on the linked product ID’s.

If(
  [Uitsluiten van 'Interesse in'] = true,
  "",
  If(
    [Interesse in product].Count() < 1,
    "",
    [Interesse in product].Join("[" + [Public Id] + "]", " ")
  )
)

Think we need to find a different solution (read workaround).

[Chr1sG]

@Marloes

But we also need to be able to make corrections, when a lead isn’t interested in a product anymore.

How does the Correction field get populated then?

[Marloes]

I’m not sure about that yet. Depends on how we can make it all run smoothly. It could be something like:

• A product being unlinked → Then we know product ID so the Correction can be set.

But if a lead shows interest in the product again, we should be able to undo the Correction.

Right now I’m thinking about creating an extra database (instead of using a formula field). Let’s call it ‘Interested in - Product ID’. But then I would need the following automations, which I am not quite sure if this is possible or not.

1. When a lead shows interest in a type of product (based on one of the possible customer journey activities) → Check if the contact already has an entity where the name of ‘Interested in - Product ID’ is the same as the ‘Product ID’ of the product they’re interested in AND where contact is equal to step 1 contact. → If so, do nothing.
2. If there’s no match, add a new entity where name is equal to ‘Product ID’ and contact is step 1 contact.
3. With a formula on contact I can create a string of numbers, based on the linked ‘Interested in - Product ID’s’. And with an automation I can link the right ‘Products’ they’re interested in.
4. When a Product gets unlinked from contact → find the ‘Interested in - Product ID’ linked to that contact and update the checkbox ‘not interested anymore’. If that checkbox is checked, don’t show the product ID number in the string (from step 3). This already works.
5. If a contact shows interest again in a product from which the ‘Interested in - Product ID’ - ‘not interested anymore’ is checked, than uncheck. The string will be updated again and this will trigger the automation to update the ‘Products’ they’re interested in.

The tricky part is that I can’t do step 1 and step 2 in one automation. So I need to have a checkbox in step 1. But than in step 2 I have no contact I can refer to (since products can have many contacts, so I can’t specify the right one).

A solution might be → Always create a ‘Interested in - Product ID’ and if it’s double, remove the lasted. (instant or in a weekly cleanup). But that fiels heavy since a contact can have many customer journey activities and (for example) an email campagn can lead to many customer journey activities in many customers.

What do you think when you read this? Should 1 and 2 (and even step 5) be possible in 1 automation?

[Chr1sG]

@Marloes

I’m not sure about that yet. Depends on how we can make it all run smoothly.

When I asked, I was rather asking about what the process is - how does the lead express that they are no longer interested, and does this directly affect the data already in Fibery in some way? Or does it require manual intervention.

[Marloes]

@Chr1sG

Or does it require manual intervention.

Most likely via a manual intervention. I do not think it is desirable to automatically determine that a lead is no longer interested in a product so

@Marloes

A product being unlinked

is probably done manually.

[Chr1sG]

In that case, why can’t the person manually adjusting, just unlink the no-longer-interested-in Product from the field called [Interesse in product]

[Marloes]

Because the ‘I’m not interested anymore’ is basicly the only thing they’re doing manually. Couple of examples:

• If the lead clicks in an email and that email is part of a campagn that is linked to a product, the customer journey will tell us, that they clicked in the email. The product is automatically linked to the customer journey, so ‘product interested in’ should be added to the contact.
• If a lead has a salescall for a specific product, same thing happens.
• And so on.

So unlinking is basicly the only thing that is happening. But in that case we need to prevent linking this product again when a different customer journey step commes in.

But there are a lot of ifs and buts. I think it is too complex to fully understand this setup without seeing the setup.

[Chr1sG]

@Marloes

I think it is too complex to fully understand this setup without seeing the setup.

Maybe

I’m just trying to understand the ideal flow, independent of how it might be implemented, and I’m a bit confused, since at one point you wrote

@Marloes

if a lead shows interest in the product again, we should be able to undo the Correction.

and yet

@Marloes

in that case we need to prevent linking this product again when a different customer journey step commes in.

It sounds as though there are quite a lot of ‘if…then…’ decisions needed to determine if a product ID should be linked to a customer at any given point in time, and some of those can’t be automated, since they require a decision by a human, no?

[Marloes]

@Chr1sG

I’m a bit confused

Haha, I totally understand (and relate to that every once in a while). Let me try to explain.

If a lead is interested, at some point there’s a contact moment, such as a sales call. It may be that after the sales call, the lead indicates they are no longer interested in the product after all.
→ This product should be excluded from ‘Interested in’ products. I would like to do this by setting the checkbox at ‘not interested anymore’ in the database 'Interested in - Product ID’s to true. This is step 4, mentioned above.

A year later, during a new launch of this product, the lead signs up again for a webinar around this product. The lead turns out to be interested again. Maybe his situation is changed or has not improved over the past year.
→ This contact already has an Interested in - Product ID. Only the checkbox ‘not interested anymore’ needs to be set to false. This is step 5, mentioned above

@Chr1sG

It sounds as though there are quite a lot of ‘if…then…’ decisions needed to determine if a product ID should be linked to a customer

Yes, and no. In fact, every postive action in the customer yourney which is related to a product results in

• Step 1 → Check if contact already have a ‘Interested in - Product ID’ with the same product ID.
• Step 2 → If not, create one and link contact.

Every negative action (not interested anymore, so product there interested in is unlinked) results in

• Step 4 → Look for the ‘Interested in - Product ID’ with the same product ID and contact and set checkbox → True.

Every positive action for a product that has a previous negative action results in

• Step 5 → Look for the ‘Interested in - Product ID’ with the same product ID and contact and set checkbox → false.

[Chr1sG]

Assuming that there is a many:many relation between the ‘lead’ db and the ‘product’ db (not sure what they are actually called) then for this:

@Marloes

• Step 1 → Check if contact already have a ‘Interested in - Product ID’ with the same product ID.
• Step 2 → If not, create one and link contact.

then you don’t need a conditional. A relation field does not allow the same item to be linked more than once, so if you try to link something, and it is already linked, then there is no change.

Overall, it seems like the rules should be:

Positive action → link product to lead
Negative action → unlink product to lead

I don’t quite understand why this needs to work with the IDs, and why it can’t work directly on the relationship

[Marloes]

@Chr1sG

I don’t quite understand why this needs to work with the IDs, and why it can’t work directly on the relationship

I know we have a logical explanation for this, but I can’t reproduce it. Let me dive in and I’ll come back to it.

[Marloes]

Hi @Chr1sG ,

Gosh, we were suffering from a huge blind spot. You triggered me with:

@Chr1sG

Positive action → link product to lead
Negative action → unlink product to lead

What we where trying to accomplish is to create a single line of ‘Interested in’ products inside contact. Because that’s where everything comes together from the different sources. Since we where so focused on that, we failed to think the other way around → Update the contact from the source.

So thank you very much for mirroring us!! We cannot emphasize enough how much we appreciate your help.

Thanks!