Counting words inside a List of Text in multiple objects

Gustavo Santos

Hi all,

I have a record with fields as List of texts. I'm trying without success to build a function stack to count how many times an specific word appears.

In the exemple below, you can see 3 records and 2 list of texts. If I want to count how many times the word "Proud" appears, how should the function stack be?

Gustavo Santos

Thank you @Mihaly_Toth and I guess I have to repeat the query for every different word I want to count right?

Mihaly_Toth

Well, yes and no. You do have to loop to know that how many items are there for a specific expression. But there are multiple approaches to this as well.

1. Query the database with the "Query all records" with each expression. (**Not best practice and can be done much better**)
2. My suggestion: Do a different approach to the input. Instead of asking one single text as the input for this whole expression request a "list of texts".
   1. Step 0 to change the input to a list of texts:
   2. 
   3. This allows to use the "Overlaps" filter in the query builder which will return you all the records that are found for any of the input texts. But following this the "count" reply would not make any sense, as it would only return the number of ALL the items that match ANY of the criteria. So how next?
   4. 
   5. Return the records as a "list" as a regular query all records would return. Following this we can do something in the endpoint logic that would allow us to count each individual text input.
   6. 
   7. To do that let's create an empty [] array (list) variable, I call it "aggregate".
   8. 
   9. Then add a for each loop that goes through each of the "multiTextInput" and inside that let's add an "update variable" function. That will update the "aggregate" to the "aggregate" value and we'll a few filters
   10. 
   11. What formulas shall we add to make this happen?
   12. 
   13. Well there is one inside which is a more technical one and I'm pretty sure it could be done differently in Xano, but this is just a more concise solution.
       1. So we first set the aggregate value to itself, to keep the state of the list following each iteration of the for loop.
       2. Then we add a new value to the list with the "push" filter. The next value should be an object with two properties.
       3. This we can do with the "set" filters. The first one is the input text that we want to count for and the other is the number of found items.
       4. for the second property we do this, just to have it in more detail:
          
       5. we use the "filter" filter which in fact selects specific values from the provided list, which match the condition provided in the code. See the code here:
       6. return $this.Powerful.includes($var.input);
       7. 
       8. Here the code checks if the current item in the loop (which is named as "input" in this case appears inside the list of texts in any items of the list of records that we got from the DB. This "is in the list" method is the .includes(), where the input of the method should be the item that you want to find. Note that you would have to change the "Powerful" part of the code respectively to the property name in the table where your list of texts is.
       9. And the final formula is the "count" which just counts those items that are selected by the "filter" filter.

This whole logic would then result in a variable that is a list of objects and looks like this, on the data that you have provided with the input of ["Proud", "Faithful"]:

Notes.

There are some other minor things that might come in play. When users do a wrong lowercase, uppercase expression, or other similar things. That can cause trouble.

Hope this helps further.

Gustavo Santos

Thank you.

I couldn't find the filter Code to add the expression. Where are they available? I'm still using the free version for testing.

Mihaly_Toth

So there we go with the no-lambda / higher order filter approach. One interesting thing that the no-code way seems to perform a little bit quicker as well. The general overview of the logic:

And the details:

1. create variable "forNoLambda" is the same as in the previous replies the "forGraph"
2. inside the for loop, which is again the same as in the above replies I have added a function from the "Data manipulation > Arrays" section, which is the "Find all elements".
3. 
4. This was the overall configuration and now let's see the detailed one:
5. 
6. So what do we do here? The $thishere is the single record of your data table which was returned with the "allFoundRecords" list. To avoid using higher order filters I do an "intersect" filter, which is checking if the first array (Powerful) has any items of the second array (which is created here on the fly with the [] and the "Push" filters, where I push the current input text, that is the active item in the for loop. And to really know if there is any matching item, we need to count the items that are in both of the arrays. If that number is more or equals than 1, then we have a matching item, if not, we go on
7. We did this, to avoid the usage of the "filter" function. So now our "update variable" for the "forNoLambda" looks like this:
8. 
9. We just need to push the count of those items that were found in the previous function.

I really hope that this will solve your problem.

Oh, yeah and it will return the same result as the other ones:

Mihaly_Toth

Well, the easiest way of doing this would be to create a "Query all records" from a table function. Inside the filter tab add in the condition "Proud" in myTable.Powerful. See image:

This filter shall return you a list of items from your table that will have the "Proud" as text in the Powerful list of texts. For example this:

The next step would be to change the output of the "Query all records" function to return not the list of objects, but rather the count of the items that were returned. Do this as shown on the following images:

Select the count option and you're good togo. The response of this function will be in your case 2:

Hopefully, this helps. Good luck with building.

Gustavo Santos

Hi Man, thanks again. I will build the second solution. One last help. After the group of words is counted, I will need to create 2 arrays to use as a bar chart. On the X-axis goes the the names as labels and y-axis the count values.

How to structure that?

XLabels{“Proud”, “Faithful”}

YLabel{2,1}

Mihaly_Toth

In that case, instead of the "aggregate" variable, I would do a different logic on the update variable. Let's name this new variable"forGraph".

Then do the same loop as before, just inside the update variable function should look like this:

Try to replicate this formula cascading in the exact same order. This will give you a result like this:

A brief explanation of the filtering above. It "pushes" the current text value to the "x" property of the forGraph object. And then does the calculation for that specific value similarly as in the answer above and pushes that value to the y property of the forGraph object.

Mihaly_Toth

Well, I forgot about that.. 😢

This whole process can be done with built in filters, I just need more time to come up with that. 😅