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?
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.
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.
If there’s no match, add a new entity where name is equal to ‘Product ID’ and contact is step 1 contact.
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.
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.
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?
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.
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.
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
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?
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
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.
Gosh, we were suffering from a huge blind spot. You triggered me with:
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.