Count various records and display sum in Zoho

Question

In Zoho Creator, I've got two form that needs to be linked together.

Form A: Is a reward counter, where certain questions are awarded certain points, and is stored per entry, in a column called "Points_Earned".

Form B: Is an identity database, contain basic details of persons, such as name, surname, membership in DB, and so forth.

The PK between the two forms is a Person Identity, in the format "UID - Lastname, Firstname" (These fields appear in Form B as separate columns, but in Form A, displays as one concatenated string in a dropdown box, field name Reward_Member).

Objective:

Form B has a column called Total_Points, which needs to display a sum of all Points_Earned for the specific UID.

The SQL expression is similar to: select SUM(Points_Earned) from Form_A where Reward_Member = Form_B.UID + " - " + Form_B.Lastname + ", " + Form_B.Firstname;

Problem:

I'm unsure how to execute the aforementioned logic in Deluge. I have tried the Aggregate method, but it doesn't seem to count the sum of the records, and the Knowledge Base articles aren't of much help either. The result remains blank.

This is the script I have used to try to display the Sum (placed in EDIT>>ON_LOAD):

//Variables
curpts = 0;
emp_lookup_val = input.Employee_ID + " - " + input.First_Name + ", " + input.Last_Name;

//Perform the sum aggregation function
curpts = Add_Loyalty_Entry[Reward_Member == input.Employee_ID].sum(Points_Earned);

//Set the Text Field Total_Points with the calc result
input.Total_Points = curpts;

Example Data:

Form A:

+---------------+---------------+
| Reward_Member | Points_Earned |
+---------------+---------------+
| 1 - Doe, John |       3       |
| 1 - Doe, John |       2       |
| 4 - Crow, Bob |       1       |
+---------------+---------------+

Form B:

+-----+-----------+----------+
| UID | Firstname | Lastname |
+-----+-----------+----------+
|  1  |   John    |   Doe    |
|  4  |   Bob     |   Crow   |
+-----+-----------+----------+

DEBUG INFO

I used the following expression to see what the data stored in Reward_Member looked like, and got the result showed after the expression:

Expression: alert input.Reward_Member.toString() + " earned " + input.Points_Earned + " Point(s)."; Result: 3485853000000015046 earned 1 Point(s). ... Looks like BigInt

zohojayesh zohojayesh · Accepted Answer · 2018-03-31T03:31:16

Thanks for posting u’r Zoho Creator question here

You have almost got your code , its just , in the criteria : instead of comparing with Employee_ID you have to compare it with input.ID , the actual record ID of the given record.

So u’r code should look like this.

curpts = Add_Loyalty_Entry[Reward_Member ==  input.ID].sum(Points_Earned);
input.Total_Points = curpts;

Explaination

Because it seems you have used the form B lookup in form A and named that lookup as Reward_Member. So if u’ll log a lookup value directly u’ll be getting the record ID of the item you selected in the lookup as you have just shown in u’r debug info.

so the lookup should be compared with the record ID of the item you want to compare.

So If you want to fetch all the records in form A related to a respective Reward_Member value, you have to compare the value with the ID of that respective Reward_Member record.

Since you are writing this code on Edit of the Form B ( which seems to be the Reward_Member form ) : to access the ID of that form’s record you have to use input.ID system field.

( if u’ll be writing this code out of that form u’ll have to fetch the ID first and then compare it in u’r criteria )

And the Other Approach you could use is

Compare the Employee_ID of Reward_Member lookup field with u’r input.Employee_ID field , and that will balance the criteria and work as well.

curpts = Add_Loyalty_Entry[Reward_Member.Employee_ID == input.Employee_ID].sum(Points_Earned);
input.Total_Points = curpts;

Best Approach

And the best place to put u’r code would be on submit ( both add and edit ) of the form A ,

Cause whenever any Reward_Member will be rewarded with any points , on submit of the record in form A, it will eventually calculate the new total for submitted Reward_Member and update its Total_Points in the Form B , that will make u’r system consistent instantaneously.

To do that on submit of Form A you should put this code

    if(input.Reward_Member != null)
    {
        total_points_earned = Form_A[Reward_Member == input.Reward_Member].sum(Points_Earned); // calculate updated total points earned
        rm_rec = Form_B[ID==input.Reward_Member]; // fetch reward_member record
        rm_rec.Total_Points = total_points_earned; // update the calculated total points into the fetched reward members record    
    }

And u’r good to go.

Count various records and display sum in Zoho

1 Answers