Count cell entries in database

I am trying to find a formula that would allow me to count the number of cells with a certain test value in that column from my database and display in my app.

I am using COUNT but it won’t count cell with text data in it. Any suggestio? i am not sure if it is even possible or I need to use a different formula.

Hi!

I’m not 100% certain I understand your data structure, so please elaborate if I get this wrong, but you want to check all the items in your data for a certain value in a certain property? I would do this with REDUCE probably, something like

REDUCE(products, IF(item.value == "VALUE YOU WANT", accumulator, accumulator+1))

Hi @Mevi

Thank you for your reply. That is what I want to do. Count all items of a certain value in a certain property. Like how many times the word “Bag” is written and then display the total number.

Formula works, but it gives me a 1 for each time the word is written so I get 11111111, do I just wrap COUNT or SUM around the formula to get it to show the total number?

Not sure if I have made it any clearer.

ah lol it thinks it’s a String type, not a Number type, so just wrap it like NUMBER(accumulator)+1 and it should work haha

Thanks @Mevi, much appreciated.
I think I am almost there. It’s counting and it appears to be counting the right number but just not changing accordingly. I guess its almost like a stock level counter, in lack of a better way of explaining it.

Please elaborate, what isn’t changing?

Thank you Mevi,

it is counting the correct amount but if I delete an item it does not reflect this in the counting it stays even if I got into the app again. Appreciate all your help, sorry for not explaining it very well.

So @Mevi, this approach could also be used to “add one” to Post, Like, and Follower counters, yes? And, taking off from Dan’s most recent post, you would need to implement a formula script that, if a post is deleted, a like rescinded, or a user unfollowed, that the counter corresponding to each “subtracts one”?

  1. For example, in the image below, each answer card (bottom) has the heart icon representing a Like button. If a user clicks/taps it, the Like counters, both, in the profile bio at the top and for each respective post should tick one higher. If the user then “unlikes” the post, both respective fields should tick one down. Likewise, being a social Q&A platform, each answer should tick one higher in the answers field, etc. etc.

  2. Is there also a way to implement a default/initial number such as, literally, “0” in those fields, but then have them seamlessly begin counting up as the user begins engaging? I ask this because other platforms I’ve used would either have nothing appear at all when there’s less than “1” in the field or you would have a static “0” text block there that is then scripted to hide and spawn in the actual counter as soon as “1” post, like, or follower is registered.

Also curious how to best combine that with an overall stat-tracking system on Appgyver to promote the most-liked posts in a 24-hour period on some kind of leaderboard – but that might be another thread altogether. I suspect you’d need database collections for each user’s individual posts to track and compare with every other user’s most popular posts before finally generating that list on the leaderboard page. Maybe I’m making this more complex than it needs to be. It’s been melting my brain here.

Thank you!

@Dan if you have pulled the data again, the count should reflect it correctly… Do you delete the item from the app or directly from the database? The data variable (data.products for example) needs to have been updated via Get Collection in its logic canvas. If you haven’t changed anything in the data logic canvas and the count does not show the correct amount even after 5s have passed, I would recommend checking if the deleted item is still in the data variable or not (you can see this from if it appears in the list still, or by binding a paragraph to ENCODE_JSON(data.products) for example)

@Dominik_Greene Welllll, I don’t know if this thread here has much to do with at least Like counts, but perhaps for Post and Follower counts it could be used similarly. The counter of any of these should, if what they are counting is a data variable, automatically show the right value without any need to change it.

  1. Looking at your picture, it looks like the top “likes” would count total likes the person has done and the like in the message or something is something the user likes. It comes down to how you structure your data – will each message have the information of data.Message.likedByUsers which is a List of all the users that have liked the post, for example? Then you could either A) check all messages ever and do the count for messages liked by user dynamically (but this is likely to become slow when there are very many messages) or B) keep a separate either List or Number with the User of all the posts they have liked (or just a count of liked posts). Either way, you would change both data.Message.likedByUsers to include/remove the User’s id and data.User.messagesLikedByUser from the same tap of the heart. A different data structure might also be fine, this is just an example.
  2. Yeah I would just initialize those both to 0 to start with in your data, so it is just tied to the data variable without needing to do any weird hiding stuff.
  3. Yep, this is again a question of data structure :smiley: include the timestamp of when the like was given in the data.Message.likedByUsers (so perhaps it would include user id and timestamp for each Object) and you can do the count of liked within 24h etc.
1 Like

This is some great value right there, Mevi. Thank you! I will definitely bookmark this also for future reference. That’s what I needed to know, and now I realize I’ve just been complicating it a little too much in my head.

And just a few other things just to finally get them off my mind. Promise this is it!

  1. Do tooltips exist natively in AppGyver for web apps? For anyone who may wonder, that’s the small text box that may appear when the cursor is hovered over a component like a button or icon explaining what the action would be should you click it. Unfortunately, I don’t seem to be able to find any mouseover triggers. :confused: Coming with 2.0 maybe?

  2. Is there a maxlength-type function for input fields which allows you to limit the number of characters that can be typed into, say, a multiline field? I’m looking to limit the numbers of characters to 350 for the question-asking input field on each user’s profile. Nothing in the component properties and no formulas for it, either.

Which folds into:

  1. Is there a way to generate a visual text character counter – or countdown, rather, that would start at, say, 350, and with each text character/number/whitespace symbol/emoji entered into the input field, it would tick down one. I’ve experimented with page variables and dug through the formula list trying different combinations. Unfortunately, I’m not even sure there’s logic currently on the AppGyver platform that could trigger an event every time a new character is entered.

“Component onChange” in the logic editor offers enough control to that level. Rather, it only triggers an action once. There also doesn’t appear to be a simple ADD or SUBTRACT options in the formulas where I could use that to trigger the counter up or down per character via a page variable.

TL;DR – A text counter that counts down for each character entered and counts back up if text is deleted.

Thank you for spending the time answering these things and making it look easy, Mevi!

Happy to be of service :relaxed:

  1. Not yet and I haven’t heard of any news. A hover class/event for web would be great, and a feature request for that is here if you want to go like it. What I would suggest to do for now is to place question mark circle icons where applicable and on tap show a floating container with the tooltip.

Going straight to 3. I have actually done a situation like this where there was a top limit on an input field content – in my version the user could type more than 178 characters, but at that point got a warning (and was unable to submit the form). It was nothing complex – see below.

(similarly you can set the submit button to disabled if pageVars.message length is over 178 or whatever amount of letters)

1 Like

Thank you @Mevi, I think I am almost there :grinning:

It is now counting the right table and also adjusting when I make changes. The only thing is that it is counting 1 lest than it should. Any idea? I have 11 entries but it is only showing 10.

I have put this in the formula

REDUCE(data.BrowseposterX1, IF(item.Plant_Group == “Trees”, (NUMBER(accumulator)+1), NUMBER(accumulator)))

Thank you again for your help.

Hi! Not sure, perhaps try initializing the accumulator with REDUCE_INIT and see if that would fix it for you. If not, I would probably just initialize to 1 instead of 0 or add an extra +1, if it’s constantly one off :sweat_smile:

I’m just feeling really stupid here, @Mevi. I’ve been trying to figure exactly what is connected to what and where over the weekend – when I have time to actually work on the web app! So I’m going to skip the comment text as you have in your screenshot and go straight for a counter function like this:

Regarding “0/350”, the first “0” it it’s own text component, the dynamic number that counts up for each character in the “Ask a question” multiline text input. The “/350” is just a static text field positioned next to it to allow the user to compare the number of typed characters to the max permitted on the fly. I’ve set a page variable for the “0” counter and I’m assuming the variable type should be number. I’ve also connected the “0” to that character_counter variable I made.

What I don’t understand is the backend logic here. What does your logic flow (if any) look like for the two respective components? How exactly were your variables connected, if you don’t mind sharing. Specifically, where is the connection between the input component and the page variable which then allows your length formula to calculate the number of characters typed to display below?

I’d really appreciate if you could help me wrap my head around that! :slight_smile:

Hi! Don’t worry, you’re learning something new :blush: I would skip the counter. What you need is the data/page variable which is bound to the input field value. Then just bind to a paragraph something this formula (with correct variable name):

LENGTH(pageVars.comment) + ”/350”

As a result you should get 0/350 or 148/350 or whatever the length of the comment is.

1 Like

Thank you @Mevi that worked. Just added +1 and it is giving me the right number. Not sure why, but it works. :grinning:

1 Like

I hope you already found a solution :innocent: But if you didn’t, I would simply put a formula into the disabled property of the button that is like LENGTH(pageVars.comment)<351. No IF needed, since that is already a statement that returns true or false.

1 Like

I like your approach better than my own convuluted method. :slight_smile: I just noticed two errors in the formula. With it applied directly as-is, it disables the button if the count is LESS than 350.

  1. The < character should be >.
  2. For some reason, it worked appropriately with the 351 set to 350 instead. Anything above 350 and the button was disabled then.

In other words, since it was attached to the same page variable as the counter we discussed above, it became LENGTH(pageVars.character_counter)>350. Hmm, I might just use the same method to actually set the button to invisible and spawn in a container sized and styled similarly to the button but colored orange with the text bluntly stating “too long” or something. Or maybe I could set the button color to change to red…

Thank you, Mevi!

1 Like

Boom! After experimenting a bit, it worked as intended.


VS

Thanks again!

1 Like