Splitting large data entries into multiple single value columns

[efileno]

Hello,

My goal is to allow users to enter long text in a search box, at least 300 characters, and use the terms in that text to filter a collection of items.

I saw an existing thread with this interesting video posted by @Darren_Murphy. I like that idea a lot but, if I understand it correctly, it would essentially require me to create as many Single Value columns and If/Then/Else conditions as there are terms in the user’s data entry.

If this is correct, have any new features or techniques come out that might allow me to do this more efficiently? Or is there another approach I should consider?

Below are a few notes for additional context, in case it’s helpful :

• I found a couple of threads that suggest doing the split in Google Sheets since there SPLIT function automatically does what I need. However, I worry that the resulting experience would appear broken or very slow due to the sync delay between Glide and external tables. Let me know if that’s not correct though.
• For filtering, I would want to ignore common terms like “the” or “a”. My thinking there is to create a new column containing keywords for each items. Instead of doing the match between the user’s entry and the item’s full description, I’d only compare with the keywords.

I hope the description is clear enough. Let me know if you have any questions. Thanks!

[Darren_Murphy]

A lot has changed in Glide since I made that video.
If I was to do the same thing now, I think I’d just create a Joined List of search terms and pass that into a JavaScript column.

The important thing is to clearly define your matching criteria. If you read through that thread that you linked to, you’ll see that I went back and forth with the original poster trying to figure out what they actually wanted. That’s always the hardest part. Once you understand the goal, then it’s just a matter of applying some logic.

[efileno]

Thanks @Darren_Murphy for your response. I’m pretty new to Glide and I’m not yet familiar with Joint Lists or Javascript, so I have follow-up questions.

First, to explain my set up a bit more, I have:

• a Users table with in it a User Input column with the data from a text entry component
• an Items table with the item names, descriptions, and a Single Value User Input column pulling from Users

Here are some questions I have about your suggestion:

• At the moment, I’m capturing the user’s data in Users and then pulling it in Items with a Single Value column. Does this achieve what you had in mind with Joint List? If not, could you help me understand the goal of Joint List?
• After reading your post, I did a bit of reading about how to use Javascript in Glide. Is the goal of the Javascript bit here to take in the data entry and return it split in multiple columns? To be honest, I’m having a hard time finding how to write a code that would work for me. Is there any resource you know of that could help someone with no coding background do this?

I like your comment about the importance of clearly identifying the matching criteria. I can’t use an actual example but here’s my thinking using dummy info:

• The user will type a long “stream of thought” string into the Text Entry component, something like “Today I feel like having soup but I could also have something sweet, maybe fruits, or cake”
• The Items table will have one row per product, like “Tomato soup from brand A” or “Leek soup from brand B”, with names, descriptions, pictures, etc.
• Below where the user enters their data, I need a list of product that gets filtered based on what’s entered
• So in the sample data entry, I imagine that some terms like “Today”, “feel”, or “have” would be ignored whereas more meaningful terms like “soup”, “sweet”, “fruit”, or “cake” would help filter the list of products
• In my mind, I’m imagining that each product could have keywords so that I would compare these to the data entry, to avoid having to worry about terms that aren’t meaningful for filtering. Note that the keywords of each product should not change over time.
• I don’t want to worry about users doing typos at this point. So if the user types “suop” instead of “soup” then they won’t see matching products and that’s fine.

Does this feel like a clear logic, or are their holes I’m not seeing? If it’s clear, I realize it might be a lot to ask, but it’d be great to get some pointers about how to actually do this in Glide; again I’m new and I don’t have experience with Javascript.

Best

[Darren_Murphy]

@efileno

At the moment, I’m capturing the user’s data in Users and then pulling it in Items with a Single Value column. Does this achieve what you had in mind with Joint List? If not, could you help me understand the goal of Joint List?

Not exactly, and actually reading back I see that I didn’t really think that through.
What you have there is fine in terms of getting the search terms into the Items table.
Although you probably don’t need to do that (see below).

@efileno

After reading your post, I did a bit of reading about how to use Javascript in Glide. Is the goal of the Javascript bit here to take in the data entry and return it split in multiple columns?

No, the idea with the JavaScript column would be to do all the heavy lifting in terms of matching, and negate the need for any extra computed columns. You would feed it two values: the list of search terms (which could come directly from your User Profile row), and the Item description for each row. Then write a matching algorithm and return a positive result if there is a match.

That said, I’m wondering if you might be able to make use of the OpenAI Integration here?
I’m thinking specifically of the Answer Question about a Table feature. Although I haven’t done much with that myself, it seems to me that this might be exactly the type of use case it’s made for. So it might be worth experimenting with that.

[efileno]

@Darren_Murphy Thanks for your suggestions.

First, I spent some time setting up an Answer Column and it works great. I’m able to filter my collection as I wanted. However, I don’t think I’m ready to commit to paying for the OpenAI plan or credits as this point in my project since it’s a very early prototype.

Then, I looked at the Javascript option. Now, because I have absolutely no coding experience and couldn’t find anything online, I tried using an AI tool to create the code. I got this:

function matchesItemDescription(searchText, itemDescription) {
    // Convert both searchText and itemDescription to lowercase for case-insensitive matching
    searchText = searchText.toLowerCase();
    itemDescription = itemDescription.toLowerCase();

    // Split the searchText by spaces (you can also split by commas or other delimiters)
    const searchValues = searchText.split(' ');

    // Check if any of the search values are found in the itemDescription
    for (const value of searchValues) {
        if (itemDescription.includes(value)) {
            return true; // A match is found
        }
    }

    // No matches found
    return false;
}

// Example usage:
const p1 = "apple orange";
const p2 = "This is a green apple";
console.log(matchesItemDescription(p1, p2));  // true

I’m trying to plug the code in the “JavaScript code” field of the column’s settings but it’s now working. I linked p1 to the column containing the data entries in the Users table and p2 to the one with the item descriptions in the Items table. The error message I see is this : " Function Error. SyntaxError: Identifier ‘p1’ has already been declared".

Of course, I imagine I must be doing many things wrong here given that I don’t really understand how Javascript works. I thought I’d post it here in case anyone is able to help me make it work so I can try my idea live in the prototype.

I really appreciate all the help.

[Darren_Murphy]

Remove those last 4 lines:

@efileno

// Example usage:
const p1 = "apple orange";
const p2 = "This is a green apple";
console.log(matchesItemDescription(p1, p2));  // true

And replace them with this:

return matchesItemDescription(p1, p2);

You might also need to adjust the split value to match whatever you used for your Joined List of search terms. eg. perhaps the following:

const searchValues = searchText.split(', ');

[efileno]

Thanks @Darren_Murphy! It seems to work better now. I can search terms and see the collection filter automatically; pretty cool.

I do notice a strange behavior where:

1. I enter in the search box a word I know isn’t in any descriptions
2. I see the collection become empty, since there are no matches
3. I remove the word from the box
4. The items in the collection remain hidden
5. It also stays hidden if I type a word I know is in a description
6. To try and fix this, I refresh the whole page
7. Once it’s reloaded, the collection is still empty
8. I start typing and start typing a term I know to be in a description
9. The list populates again.

I’m not 100% certain how to replicate this consistently. For instance, there have been words I know aren’t found in any descriptions for which the issue doesn’t occur. I also don’t know if the issue is in the JS code or somewhere else.

Taking a step back, I think my prototype might be good enough for a demoing some of the functionality. However, I wanted to share this on the off chance that someone might know a fix based on this vague description. If not, I might mark this thread as resolved and reopen a new one, at a later time, to dig more into the remaining issue.

[Eric_Penn]

@efileno

I also don’t know if the issue is in the JS code or somewhere else.

Does it help if you try it in a published app instead of the builder?

[efileno]

You’re right @Eric_Penn; the problem disappears when using the published app. Thanks for suggesting this. I’m not sure if I should start a new thread, but I’m still running into an issue with the JS code. I’ll post it here in case anyone can help but let me at what point it’s best to start a new topic to keep things organized.

I implemented the tweaks suggested by @Darren_Murphy and then made some changes as I was testing more and noticing issues. What I have now is this code:

function matchesItemDescription(searchText, itemDescription) {
    searchText = searchText.toLowerCase();
    itemDescription = itemDescription.toLowerCase();

    const searchValues = searchText.split(',');

    for (const value of searchValues) {
        if (itemDescription.includes(value)) {
            return true;
        }
    }

    return false;
}

return matchesItemDescription(p1, p2);

The issue I have now is that when I enter “vacuum broom” where both “vacuum” is a keyword for product A and “broom” one for product B, instead of having products A and B in the filtered list, I have none. The list initially shows product A until I add a space and then “broom”.

I was playing around with this, mostly using an AI code generator, and got to the following code:

function matchesItemDescription(searchText, itemDescription) {
    if (!searchText || !itemDescription) return false;

    searchText = (typeof searchText === 'string') ? searchText.toLowerCase() : '';
    itemDescription = (typeof itemDescription === 'string') ? itemDescription.toLowerCase() : '';

    const searchValues = searchText.split(',');
    const descriptionValues = itemDescription.split(',');

    for (const value of searchValues) {
        if (descriptionValues.includes(value) || itemDescription.includes(value)) {
            return true;
        }
    }

    return false;
}

return matchesItemDescription(p1, p2);

However, with this code now I see a strange two strange behaviors:

1. If I type “vacuum broom” (assuming both terms are keywords for a different product): I type in “vacuum” and see the collection filter correctly, I enter the space and I lose the filtering to see the full unfiltered list, I add “broom” and the filtering comes back with both products (as expected). Not sure how to avoid losing the filtering as the users add spaces or stopwords in between matching keywords.

2. If I type “I” or "I " I get plenty of matches even though “I” is not a meaningful keyword. I assume it’s because some keywords contain the letter “i” and I should somehow ignore these common “stopwords”. But I’m not sure how to do it.

Again, if this is beyond of this topic’s focus, I’m happy to open another one. Let me know.

Let me know if you have thoughts.

[efileno]

I think I got to something that works fairly close to what I wanted to achieve:

function matchesItemDescription(searchText, itemDescription) {
    if (!searchText || !itemDescription) return false;

    const stopwords = [
        'a', 'about', 'above', 'after', 'again', 'against', 'all', 'am', 'an', 'and', 'any', 'are', 'aren\'t', 'as', 'at',
        'be', 'because', 'been', 'before', 'being', 'below', 'between', 'both', 'but', 'by',
        'can\'t', 'cannot', 'could', 'couldn\'t',
        'did', 'didn\'t', 'do', 'does', 'doesn\'t', 'doing', 'don\'t', 'down', 'during',
        'each',
        'few', 'for', 'from', 'further',
        'had', 'hadn\'t', 'has', 'hasn\'t', 'have', 'haven\'t', 'having', 'he', 'he\'d', 'he\'ll', 'he\'s', 'her', 'here', 'here\'s', 'hers', 'herself', 'him', 'himself', 'his', 'how', 'how\'s',
        'i', 'i\'d', 'i\'ll', 'i\'m', 'i\'ve', 'if', 'in', 'into', 'is', 'isn\'t', 'it', 'it\'s', 'its', 'itself',
        'let\'s',
        'me', 'more', 'most', 'mustn\'t', 'my', 'myself',
        'no', 'nor', 'not',
        'of', 'off', 'on', 'once', 'only', 'or', 'other', 'ought', 'our', 'ours', 'ourselves', 'out', 'over', 'own',
        'same', 'shan\'t', 'she', 'she\'d', 'she\'ll', 'she\'s', 'should', 'shouldn\'t', 'so', 'some', 'such',
        'than', 'that', 'that\'s', 'the', 'their', 'theirs', 'them', 'themselves', 'then', 'there', 'there\'s', 'these', 'they', 'they\'d', 'they\'ll', 'they\'re', 'they\'ve', 'this', 'those', 'through', 'to', 'too',
        'under', 'until', 'up',
        'very',
        'was', 'wasn\'t', 'we', 'we\'d', 'we\'ll', 'we\'re', 'we\'ve', 'were', 'weren\'t', 'what', 'what\'s', 'when', 'when\'s', 'where', 'where\'s', 'which', 'while', 'who', 'who\'s', 'whom', 'why', 'why\'s', 'with', 'won\'t', 'would', 'wouldn\'t',
        'you', 'you\'d', 'you\'ll', 'you\'re', 'you\'ve', 'your', 'yours', 'yourself', 'yourselves', 'feel'
    ];

    searchText = (typeof searchText === 'string') ? searchText.toLowerCase() : '';
    itemDescription = (typeof itemDescription === 'string') ? itemDescription.toLowerCase() : '';

    // Split by comma and then by spaces to handle both delimiters
    const searchValues = searchText.split(',').flatMap(s => s.split(' ')).filter(word => word && !stopwords.includes(word));
    const descriptionValues = itemDescription.split(' ').flatMap(s => s.split(','));

    for (const value of searchValues) {
        if (descriptionValues.includes(value) || itemDescription.includes(value)) {
            return true;
        }
    }

    return false;
}

return matchesItemDescription(p1, p2);

Thanks for all the help @Darren_Murphy and @Eric_Penn!