Every author gets to have a unique code associated with them, and this code can be used to refer other authors into the system ( like an affiliate code ). So in the example above 3 and 5 get to use affiliate code of 1 and 4 gets to use the code of 2.
Now when they write articles it can be in published or draft state. And each published article is 1point.
So I want to calculate points against each of the author for the articles their affiliates have written and published.
eg
Author 1 has 2 affiliates who have used his code and they have published 4 articles = 4 points
Author 2 has 1 affiliate who has used his code and he has published 1 article = 1 point
Author 6 gets to use Author 4’s Affiliate code and he has published 1 article so Author 4 gets 1 point.
Now how do I generate this point system.
@Robert_Petitto Saw your recent video so many times both on YT and here on the forum and am not able to link the two tables to get the number that I want. Probably something that might look easy for you? I was breaking my head about it for a long while but today’s video of you pushed me to post this hoping they might be on the same lines.
Thanks in advance and apologies for directly tagging you in the post for an answer.
Seems like what you want to do is create a multiple relation from AuthorCode to Affiliate in the same sheet. Then create a Lookup column to get an array of all affiliated authors. With that Lookup array, you then create a multiple relation to Table2. Finally you create a Rollup count against that second relation.
And actually, for your rollup, maybe create an IF column in Table2 that returns 1 if Published, else 0. Then have the rollup sum that value in the IF column.
@Jeff_Hager I tried what you said and I was able to get through with step 1 which is to get the Author - Affiliate relation and now I can see who all are the “new” authors who are affiliated to the existing Authors using a lookup ( am able to get either all the new author affiliate code or their user names which is fine ).
But struggling to get past to the step2 which is creating a relation into Table2 which has Article information. Not sure if my understanding of how the relation works are wrong if am doing something wrong am not getting any results. Not sure if am mapping the correct fields.
To give a reference I have created a spreadsheet that one can import and use in your test app if need be.
Sounds like you have your relation column set up correctly, but your Lookup column should be grabbing the email address instead of the authorname or authorcode. What we are trying to get is an array of values that we can use to relate to a similar value in the Articles table. The only indication of the author in the articles table an email address, so that’s what we need to have in our Lookup column to create that second relation. A Lookup array of affiliate emails that can be linked to the article email through a relation.
@Jeff_Hager Ok now i am one step ahead in understanding how the relations work and what is required before mapping the tables. I created a column with email than with codes or authors and now am able to map and also roll up to get the article count.
