Count Likes between Users

[dreamerdan]

Hi all. Maybe the solution is simpler than I'm making it, but I'm getting confused:) I have an Appsheet App where users give likes to posts of other users. If they have a total of 5+ likes between them, they share common-interests (connection).

The active tables are [Giver][Receiver][Likes]. I'm able count individual likes, but the trick is to combine the likes also. 

Example: User A gives 3 likes to user B and user B gives 2 likes to user A. That's 5 between them and I'd like a view that would show the matching users having 5+ likes in common: (A and B Share 5+ likes)

From google sheets I've tried to combine "CountIf" with "Concatenate" with no luck so far.

Appreciate any feedback. Thank!

[Marc_Dillon]

Make a table that has 1 record for every possible combination of 2 users. Calculate the total likes in each direction per record with a COUNT(SELECT()) + COUNT(SELECT())

[TeeSee1 · Accepted solution]

Assuming the LIKES table has

id, giver, receiver, etc...

Then I would create another table, say, LIKES COUNTS which has,

id, giver, receiver, give count (vc), receive count (vc), sum (vc), match flag(vc)

where give count is

COUNT(
 FILTER(
  "LIKES",
  AND(
   [giver] = [_THISROW].[giver],
   [receiver] = [_THISROW].[receiver]
  )
 )
)

receive count is 

COUNT(
 FILTER(
  "LIKES",
  AND(
   [giver] = [_THISROW].[receiver],
   [receiver] = [_THISROW].[giver]
  )
 )
)

sum is

[give count] + [receive count]

 match flag

[sum] >= 5

You could of course combine all of the above into one vc if you like.

I am sure there are other ways. This is one example. 

This could quickly grow in data volume and vc comutation, so you may want to consider this performance tips

https://support.google.com/appsheet/answer/10105761?hl=en

[dreamerdan]

Hi TeeSee1,

Maybe I spoke a little too soon. Your expressions don't show any errors and they work if I manually add the connections, but for some reason they are not automatically picking up the values once the flag limit is reached (5+). I don't want to put the info manually. When I Test the expression, nothing shows. And I do get this message below the expression: Note: This expression could impact performance.

Any ideas why the formulas, which show no errors, are not displaying the values in the virtual columns? 

Thanks.  

[TeeSee1]

Note: This expression could impact performance.is displayed anytime you create a VC. So unless it actually impact the App performance you can ignore it.

VC values are updated when the App syncs data, so VC's that are based on data from other tables may take some time before they are updated.

---

@dreamerdan wrote:

not automatically picking up the values once the flag limit is reached (5+).

---

Not sure what you mean. what values are they not picking up?

[TeeSee1]

Some Q&As from the past on VC updates

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Best-way-to-autoupdate-column/m-p/345942

[dreamerdan]

The table name is different here but it's the LIKES mentioned before.This is the view, but the this one connection I added manually.
konexions table backend

Perhaps this visual might explain better. I adjusted some of the names but the idea is the same. "konexions" table is the LIKES. "heartgiver" is "giver" and "replier" is receiver.  The "likes/hearts" table (not shown) that your expressions are filtering contains these columns with other posts and replies. 

ID

Reply

Replier

HeartsGiven

HeartGiver

 I thought the filtering would automatically add the posts and replies to the konexions table and would flag it when 5+ Likes/hearts are reached between any two giver and receiver. But as mentioned, it's not. Unless I add two users manually into the konexions table which would defeat the purpose. 

Anyway, thank you for your patience with me on this. I appreciate it. Let me know if you have any other questions. 

[TeeSee1]

The way I assumed the App is configured is,

LIKES: id, giver, receiver <- this captures all the likes given by the giver to the receiver. You have many duplicates of giver-receiver combination.

CONNECTIONS: id, giver, receiver, vcs to count rows in LIKES. <- this has only one row per unique giver/receiver combination value.

If you want to automatically create entries in CONNECTIONS whenever a row is added to LIKES, then you can create a bot to do that. You should make sure that no duplicates are not made.

As for showing only those connection with 5+ likes, you can create a slice.

[dreamerdan]

You assumed correctly. The "connections" table is ONLY supposed to record likes that are >=5 between giver and receiver from the "Likes" table .

I thought that's what the first two VCs would do. and the "total" and "Flag" VCs would be a yes/no indicator of that. 

What I'm saying is that  nothing is being added to the "connections" table even is the limit of 5+ is met. But if I manually add a giver and receiver to the "connections" table, then I see the LIKE counts between them and a yes/no indicator of 5+. 

I didn't think a bot would be needed for that, but I'll look into it too.

Also "giver" and "receiver" are not duplicated in the LIKES table, that I'm aware of. There is a "reply" column which is the original post. 

Would you mind reviewing the first two expressions again to make sure nothing is missing? For example, in the first one you have "giver" to "giver" and "replier" to "replier":

COUNT( FILTER( "Hearts", AND( [HeartGiver] = [_THISROW].[HeartGiver], [Replier] = [_THISROW].[Replier] ) ) ) 

And in the second, you crisscross "giver" to "replier" and "replier" to "giver"

COUNT( FILTER( "Hearts", AND( [HeartGiver] = [_THISROW].[Replier], [Replier] = [_THISROW].[HeartGiver] ) ) )
 

 Thanks 🙏

[TeeSee1]

I don't think I understand you fully.

---

@dreamerdan wrote:

Also "giver" and "receiver" are not duplicated in the LIKES table

---

If this is what you have, where are like votes captured? Hearts table?

My formulas count the number of rows in table LIKES based on giver/receiver and receiver/giver conditions. (also check the +5 condition).

All the counts/check results are captured in CONNECTIONS table.

It would help if you just list all your tables with their key (actual key, not necessarily the arbitrary UNIQUEID() column(s) and how/when rows are added to them.

So far I see,

USERS (giver, receiver master table, straight-forward I think)

HEARTS (same as my Likes? if so, how are rows added? when a giver gives a heart to a receiver, one new row is added?)

CONNECTIONS (again, counts rows in HEARTS. they need to be added manually or by bot or save event of HEARTS)

[dreamerdan]

"If this is what you have, where are like votes captured? Hearts table?"

Yes. The "Hearts" table captures the Likes based on a "reply" from an original post by the "likes" giver. 

"My formulas count the number of rows in table LIKES based on giver/receiver and receiver/giver conditions. (also check the +5 condition)." 

Yes, your formula is good but I just gotta find a way to fill in the giver and receiver in the "connections" table in order for them to display. The 5+ condition seems to be OK too.     

"All the counts/check results are captured in CONNECTIONS table."

Yes.

"It would help if you just list all your tables with their key (actual key, not necessarily the arbitrary UNIQUEID() column(s) and how/when rows are added to them."

Please see the "connections table" I sent before. The "hearts" table has a unique ID with "give heart" as label. the two columns of interest there are "replier" and "heart giver" as I said before. Both are "Ref" columns to the user Images. The "likes" are manually given and the rows are then created accordingly.   

"So far I see, USERS (giver, receiver master table, straight-forward I think)"

Yes! Also is where the posts originate.

"HEARTS (same as my Likes? if so, how are rows added? when a giver gives a heart to a receiver, one new row is added?)"

Yes. The hearts are "Likes".  I explained how the rows are added above. 

"CONNECTIONS (again, counts rows in HEARTS. they need to be added manually or by bot or save event of HEARTS)"

Yes, but the idea is to display the giver and receiver automatically and your "count" formulas with the flag are then displayed.

The bots appear to be preconfigured. Not sure if they will accomplish the task of displaying the giver and receiver from "hearts" table to the "connections" table. But I'm still looking into it.   

HEARTS is a separate table. The focus, as I mentioned, should be to just pull giver and receiver from there and place in connections.

Hope that gives you a better picture. If I can send you more pics, I will after removing sensitive info.   

🙏

[TeeSee1]

My confusion is the HEARTS table.

---

@dreamerdan wrote:

The "likes" are manually given and the rows are then created accordingly.   

---

How is one 'like' represented in HEARTS or someplace else? If one like is captured as one row in HEARTS, you have to have duplicate replier/heartgiver combinations in HEARTS Apparently this is not the same as Likes that I have in mind.

Another confusion. The id of CONNECTIONS is a ref to HEARTS??

My design is based on the following.

• One like vote is captured in HEARTS table. (many rows with same replier/heartgiver combination)
• CONNECTIONS counts the rows in HEARTS. A row is either added manually or by a bot. The bot needs to be configured by you (not preconfigured).
• Create a slice of CONNECTIONS to display rows whose match flag is true.

Yours, I guess, is

• A table to capture like votes

---

@dreamerdan wrote:

The "likes/hearts" table (not shown) that your expressions are filtering contains these columns with other posts and replies. 

---

• HEARTS ??

  ---

  @dreamerdan wrote:
  

  COUNT( FILTER( "Hearts", AND( [HeartGiver] = [_THISROW].[HeartGiver], [Replier] = [_THISROW].[Replier] ) ) ) 

  ---

  This will always give either 0 or 1 because you have only one row per one HeartGiver/Replier combination.
• CONNECTIONS has only those rows that have 5+ likes

[dreamerdan]

We are on the same page as I said. Your formulas work. The only problem is that the matching rows in "hearts/likes" are not displayed automatically in "connections". The slice will work but again without the users' displayed it may cause the same problem. I will try it and let you know.

The "hearts" table, as you correctly guess has many rows with differnt users liking each others original posts. Those posts are in another table which I don't think we need to accomplish this? The "hearts" ID is unique and label is the numeric "like" given. 1 like per user per post. Can I reference the ID to "likes"?

I know I have to create the bot. What I meant is that I didnt see a formula that would taylor to what we are trying to achieve. If you think it's possible, plz let me know the formula.

Im still working on some other possibilities. Will let you know if anything.

Thanks so much for your interest and help.

[TeeSee1]

You can create a bot similar to the above.

Here when a record is added to a table Reservation Requests, a row is created in a table Reservations. (from one table to another).

EDITED: You need two actions. One from giver to receiver and the other from receiver to giver.

In the Behavior of the this Action, you detect duplicates CONNECTIONS (in your case) to avoid any.

[dreamerdan]

Thanks for those example images. I'm working on it to see how I can get the desired result.

What I'm noticing though is that it might be a bit of a challenge since there appears to be a circular reference between [heargiver] / [replier] and your formulas that display 5+ hearts between them in "konexions". They depend on each other.

There must be a way to simply incorporate something into your formulas that not only filters the 5+ hearts from the "hearts" table but ALSO adds the related [giver] and [receiver] to the [giver] and [receiver] rows in konexions? That would seem to be the simplest.   

My Logic with the Bot would be something like this: When "konexions"[flag]=Yes, Add to "hearts" [heartgiver] to konexions [heartgiver] 

And when "konexions" [flag]=Yes, then Add to "hearts" [Replier] row to konexions [Replier]. 

But you see the circular reference I mentioned? The [Flag] row in konexions first needs the giver and receiver in order to equal "Yes".  The same issue would appear to be present with a Slice.  

But, I am looking into your suggestion about the two actions. One from giver to receiver and the other from receiver to giver. 

That would imply adding or updating the [giver] and [receiver] rows in konexions when a heart is given from the "hearts" table. 

Eventually, we'll find the right solution. Appreciate the help!

🙏 🙂




 

[dreamerdan]

Hi again,

I said I'd let you know if I come up with anything. After going back and forth with expressions and bots I decided to play with it on the backend, where I'm a little more comfortable.

And through a series of UNIQUE and COUNTIFS formulas, I was able to  work out the giver and receiver sides that will update with every LIKE given. I'm down to the very last step, which I think is far less challenging. I just posted a google-sheets question on another platform  and waiting to see if I get any feedback. The attached image should be clear as to the final desired result. I'd appreciate any input you might have here as well. The Xs are to show the duplicate rows I want removed but the final formulas can leave them blank. 

Your formula is still very useful and maybe later on I can use it into this or other parts of the App.  

Thanks again for the interest and continued support!

🙏

[dreamerdan]

Works like a charm TeeSee1. I had a couple of columns similar to what you offered but just didn't know how to bring them together with the right expressions. Will look to your expertise in the future if I need help. Thank you so much for the thorough explanation and layout. 🙂🙏👍