Hi all,
I am stuck trying to find a complete solution for this. It seems super easy (which makes me feel more dumb) yet there are some details I might be missing.
I have 2 apps: app A and app B.
Both have several fields and include these 3 fields: Full Name, Phone(s), Email(s).
This is what I want to achieve:
When a record is created in app A,
IF Full Name is not empty OR Phone(s) is not empty OR Email(s) is not empty,
I want to be able to search app B to know if there is any match and perform different actions depending on the case.
Further details to help understand all the involved scenarios:
There are two main things to check in app A against app B, if the field in app A is empty or not, and then if the content of the field exists in app B.
Here I enclosed some tables with the potential scenarios for better understanding.
✓= Yes, the field in A is empty. ✘ = No, the field in A is NOT empty. The case number 8 is discarded since we need at least a non-empty field in app A.
The symbol ＝ means that the content of the field A exists in B. The symbol ≠ means that the content of the field A DOES NOT exist in B. The app B could have several phones or emails but we need to know if the one(s) in app A exist(s) in these or not.
Depending on the case I know all the actions I want to perform. For example in the case 1 BASE, since there is not a full name (empty) and these two fields do not exist in app B, this would lead to the creation of a new related record in app B containing these two records. Then in some cases I could append the fields, etc.
The major problem I have is the “verification of the conditions part” in the automation implementation.
Many many thanks for your time reading and efforts here.
Best,
R.J.
I am not sure that I fully understand your requirements even with all you have given us so if I have missed the point or you need areas expanded or changed please do let me know.
Now we are going to search App B for the email, name and phone separately and they could be empty - Tape doesn’t like searching with an empty value so we are going to make a value to search against if the field is empty - I guess you could do a series of nested conditionals searching only if not empty or you could use more code whichever takes your fancy.
So now you have an array of relevant records in App B and once you have this you should be able to do just about anything you need to do further.
Now this works if there is only one phone number or email in the App A record field if there could be more than one number in the APP A phone field it falls apart a bit and I think it would require more code. I should also say that I have tested this but not fully.
Dear Jason,
Firstly, please receive a huge thank you. As always your help is very much appreciated.
Let me give you some more context to better explain myself.
Imagine app A acts as a kind of “central inbox” and app B acts as a “curated clean list of contacts”.
Now let me make depict a quick simulated example of how this would play.
Initially we have a record in app B like this:
app B Full Name Jason Melotte
app B Phone(s) +449999
app B Email(s) contact@jmc.email
Then someday we got a record created in app A (incoming from whatever form, email, message, call, meeting, etc.) like this:
app A Full Name Jason Melotte
app A Phone(s) +448888
app A Email(s) info@jmc.email
This is the case number 7 since (in the recently created app A record) all the fields are filled (not empty ✘). Then the subcase is 7a because Jason “entered again” (a new record was created in app A) and we have an equal full name (＝) but he somehow has a different phone (≠) and a different email address (≠).
When I said “I know the actions I want to perform” is because in this example I know that the action I want to perform is to add these new phone number and email address to app B in Jason’s record. I also know exactly what I want in every and each case to happen if the conditions are met. The problem comes when trying to filter or discriminate conditions. I have tried lots of different ways to “classify” and then verify conditions (app A against B).
The case number 8 is discarded since this means that there is an error or something happened I need to look at precisely because the “central inbox” (app A) cannot have an incoming record without (at least) one of these 3 fields filled (f.name, phones, or emails).
Now that I added this I am going to fully analyze in depth your reply (I stopped at the very beginning).
let collection = var_namematches;
const collectionsToMerge = [
var_emailmatches,
var_phonematches
];
collection.push(...collectionsToMerge.flat());
console.info(JSON.stringify(collection,null,2));
const matchingRecords = jsonata(`$distinct(**.record_id[])`).evaluate(collection);
console.info(JSON.stringify(matchingRecords));
let fields = {};
// Add only if `var_name` is not the default value
if (var_name !== ">") {
fields["518921"] = var_name;
console.info(`Name: ${fields["518921"]}`);
}
// Add only if `var_email` is not the default value
if (var_email !== ">>") {
fields["518925"] = var_email;
console.info(`Email: ${fields["518925"]}`);
}
// Add only if `var_phone` is not the default value
if (var_phone !== "a") {
fields["518926"] = var_phone;
console.info(`Phone: ${fields["518926"]}`);
}
// Update the APP B Record
const { data: updateResponse } = await tape.Record.update(matchingRecords, {
fields
});
console.log(JSON.stringify(updateResponse, null, 2));
What this does is check if the values are our phony email etc and does nothing if that is the case if they are not the phonies it builds the relevant JSON for a record update from the APP A field values and then updates the record that was found.
This assumes that you always want the values from the new record in APP A and that there is only a single record in APP B that is matched. If there could be multiple records in APP B then the update method could be changed to the new batch update and update all of the found records in one go.
Hopefully, that makes sense, I think there is more error checking etc to put in but it should give you a starting point
Hi again Jason,
Thanks a lot for your answer and for this last update to it.
Do you think there is a way to use some more automation blocks in the workflow reducing the script to the essential only? I am asking this because I have lots of different conditions depending on the subcase. I think that this granularity would both make it easier to test and troubleshoot, and also potentially ease maintenance or upgrading.
You are correct assuming the hypothesis of only one unique record in app B. And for the different values in app A fields the idea is to append the new non-present values in app B (when conditions are met).
For example I have all these actions depending on the subcase:
1BASE
Create a record in app B passing the fields in app A to the created record in B.
Update the record in app A setting a relationship with the recently created record in app B.
Then I perform a calc and save result in a variable and then I update the recently created (and now related) record in app B with some more info that includes something like “First time that this contact contacted”.
1B
Update the record in app A setting a relationship with the corresponding record in app B.
Then I perform a calc and save result in a variable and then I update the related record in app B with some more info that includes something like “The contact has contacted again”.
1A
Update the record in app A setting a relationship with the corresponding record in app B.
Then I perform a calc and save result in a variable and then I update the related record in app B with some more info that includes something like “The contact has contacted again” and adding the value of app A email(s) in the corresponding field.
There is a pattern in between subcases:
For all → I always set a relationship between the record in app A and the corresponding one in app B.
The BASE subcases → always return the creation of a new record in B (in a similar way for all but depending on the letter the field(s) changes).
The B subcases → always return the actions described for 1B but varies depending on the letter the field(s) changes.
All the rest of subcases → always return a similar set of actions like the ones described for 1A but varies depending on the letter the field(s) changes.
There is an exception for example for the F.Name field. I am not appending it to the matching (already existing) record in app B. This is the case for example in 3C. This is trying to prevent messing with the names and I prefer to control this manually.
Thank you very much Jason. Your efforts here are immensely appreciated.
R.J.
Hi @Jason,
Thank you very much for your answer. I have been trying to further develop starting from your solution but I am finding some similar problems as the ones I already found implementing solutions for this before. I am beginning to realize that this could be due to a misunderstanding on my side.
Would you mind clarifying to me how dependent/independent are these blocks from each other in this edited copy of your workflow? I mean are they depending on the previous block somehow or supposed to be independent?
Besides, I reimplemented a way to add the cases where since no coincidence is found a record is created in B (with info from the record in A) but I would be really grateful if you could point me towards how you would personally handle those cases in this context with blocks.
Also, I am not 100% sure on why you implemented this unique ID.
Finally don’t we need to introduce this if condition after searching? And couldn’t we simply make a “general search” in B without filters (and simply use the if condition afterwards)?
