This is a tricky one and I’m trying to be as specific as possible.
I would want to filter a collection (using the built in “filter data” function) according to an answer from chatGPT.
The answer is a JSON:
{
“marketing_methods”: [
“SEO”,
“Social Media Ads (Facebook / Meta Ads, Snapchat Ads, TikTok Ads)”,
“Influencer Marketing”,
“Content Marketing”,
“Event Marketing”
]
}
Now each entry in the collection has a comma separated list of attributes that may or may not include any of the items in the above JSON.
Also, these attributes occur in random order.
Now my question is: How can I filter out all entries in the collection that include all of the attributes in the JSON?
What I’ve tried so far:
“Collection entry includes JSON” => Doesn’t work since its looking for the whole JSON instead of the items
Query JSON and create an array, then look if separated values of collection includes values from array => Doesn’t work since “includes” does not work with computed columns
separated list contains values from array
array items contain array
Do you have any idea on how this could be solved in a neat fashion? I would know how to do it in regular code - its a simple “match” and if all matches are TRUE, then include collection item in list. But here it is a little weird it seems (or i don’t see the simple solution)…
Okay, I’ve found a solution with implementing code and the code also works. However, the output is not returned to the cell in the database.
Do you know of any bug that’s currently active or is it sth else?
this is the code:
function findMatchingIds(termsStr, idsStr, itemsStr) {
// Parse the JSON strings into objects
const termsObj = JSON.parse(termsStr);
const idsObj = JSON.parse(idsStr);
const itemsObj = JSON.parse(itemsStr);
// Function to replace non-breaking spaces with regular spaces
function normalizeSpace(str) {
return str.replace(/\u00A0/g, ' ');
}
const matchedIds = [];
// Iterate over each item and its corresponding ID
for (let i = 0; i < itemsObj.leistung.length; i++) {
const item = normalizeSpace(itemsObj.leistung[i]);
// Check if all search terms are in the item
const allTermsMatch = termsObj.terms.every(term =>
item.includes(normalizeSpace(term))
);
console.log(`Item ${i}:`, item); // Debug log
console.log(`All terms match for item ${i}:`, allTermsMatch); // Debug log
// If all terms match, add the corresponding ID to the matchedIds array
if (allTermsMatch) {
matchedIds.push(idsObj.verzeichnis_id[i]);
}
}
console.log("Matched IDs:", matchedIds); // Debug log
return matchedIds;
}
// Example usage with your JSON strings
findMatchingIds(p1, p2, p3)
=> p1, p2 and p3 three are the variables (JSON Objects).
=> the console log tells me that the array is generated correctly.
But you said the console log does show that MatchedIds does contain an array of values?
I’m thinking it might be the fact that you are trying to return an array when the JavaScript column can only return a string. You might have to change the return within the function to return matchedIds.toString(); to convert the array to a string.
