Calculate dynamically

Emre Gover

A project requires 
a static database (updates by sales per user), 
and a  dynamic database (calculated every hour, or by a web request).

Dynamic database updates user bonuses from static db sales.

Here is the catch. There are conditions for bonuses.
Each user connects to another user, as a line. user1 >user2...> userx
Each user has personal sales score, and team score.
Team score = personal + all members connected after him, not before him
So bottom member userX has team score = self score,
one above has team =self + userx
top member has team = self + all others

Bonus is updated to a user in a Dynamic database
when self score >100 AND team >500

Can this be done with Xano only functions, or do I need external tools for these kind of calculations?

Ray Deck

Seems like for each person, you need to calculate the team score. So in a general case, and without knowing specifics in your system, I imagine that for each user, you would need to :

0) Get user's score as "self"
1) Create a variable "nextuser" that is initialized with the value of the current's user's connectedUser,  
2) Create a variable "teamscore" initialized at zero, and 
3) A while-do loop to fetch every user connection from the previous user ID via query until you run out. You search the table for the current "nextuser", get their score, add it to teamscore, then set "nextuser" to the connecteduser value, then go again. The "while" condition is that nextuser is some positive value - basically, when there is no nextuser, you stop. 


I can also see how you can implement some clever variable caching to reduce the number of queries this will require. But either way I don't imagine this will be very pretty performance-wise. However, if its in a background task it wouldn't affect the direct user experience. 

Emre Gover

I believe this has to be background task, say every 5 mins, if 
each user may have couple of branches (tree structure) and
depth of some branches are more then 10 users.

users may see the updates every 5 mins or more.

So what I understand from your reply, I can do this with Xano, without external  service that I have to study also. 🙂