GROUP function question

I can’t seem to figure out the GROUP function. (I’ve read a number of posts on this forum but the answer still eludes me.)

I have data stored in Firebase. The collection is downloaded to Appgyver as “theData”. I’m using REST API direct integration if that makes a difference. The format of a single record, when downloaded by Appgyver, is this:

[
    {
        "name": "projects/<projectID>/databases/(default)/documents/<collectionName>/<recordID>",
        "fields": {
            "total": {
                "integerValue": "119"
            },
            "active": {
                "booleanValue": true
            },
            "name": {
                "stringValue": "Randy"
            },
            "date": {
                "stringValue": "08-24-2022"
            }
        },
        "createTime": "2022-08-24T20:44:19.770997Z",
        "updateTime": "2022-08-25T14:45:24.994049Z"
    }]

The schema interprets each field as an object with 1 property. So, for example, Appgyver says “total” is an object and its one property is “integerValue”. Appgyver also sees “name” and “date” as objects with a single property, namely, “text”.

There are multiple similar records differing by name, total and date.

I want to group by name and produce a SUM of the “total” field for each name. “total” is defined as a NUMBER in Firebase and interpreted as “integer text” by Appgyver.

I’ve tried quite a few different formulas but none work. The one I think is closest to what it should be is:

GROUP(theData, item.total, {total: key, name: items})

This does not work (I get back a NULL list).

I have to admit, when I read the help on GROUP I don’t understand it - which is obviously the problem!

Any help is appreciated.


------------------------------------------------- Update --------------------------------------------------
Well, this code is grouping by name, but it is not producing a SUM of the total field. I’m close. Help is appreciated.

GROUP(theData,item.fields.name.stringValue, {name: key, sum: SUM(fields.total.integerValue)})

For each group I am just getting sum: 0

Not 100% sure but try to switch your integer text to NUMBER() then do the SUM on that number.

Thanks for the suggestion. Sadly, that does not work either. I think it has something to do with the nesting by Firebase. The entire collection is contained within a key called “documents”. Appgyver “strips that out” when the key “documents” is placed in the “response key path” (I’m using REST API direct integration). So what Appgyver sees is a list containing objects with 4 properties (name, fields, createTime, updateTime). These objects are my records. “fields” is itself an object with properties. Two of the properties of “fields” are “name” and “total”. (This name is different from the other “name” which is the record identifier created by Firebase.) It is the nested “fields.name” that I want to GROUP by - and that is happening with the modified code above. But I cannot figure out how to access and sum the nested “fields.total”. Frustrating.

(You probably know all of this. I’m writing it out mostly trying to think it through.)

------------------------------------------------ Update ------------------------------------------

Ok, I am “inching closer.” :slightly_smiling_face: The following code groups by name and presents a number for “total”, but it is only the number from the first record. Obviously, it is returning only the value in the first record since that is what I am telling it to do (items[0].fields.total) - but I don’t know how to explicitly tell Appgyver to iterate over the records. (I should not have to tell it to iterate over the records because it is already contained within a component configured with the repeat-with property).

GROUP(theData,item.fields.name.stringValue, {name: key, total: SUM([NUMBER(items[0].fields.total.integerValue)])})

This is the result that I get from the above code. It is close, but not returning a correct SUM.

[{"name":"Tom","total":119},{"name":"Dick","total":114},{"name":"Harry","total":102}]

I would try to replace the “0” (zero) with the current index (“index”) but that option disappears from the formula editor when I place the cursor within the “items[0].fields.total.integerValue”. :frowning_face:

I feel you need to use SUM_BY_KEY() so that the sum runs on the same key name ‘total’ across all objects in your list.

Thanks for the response. No joy. I tried this:

GROUP(theData,item.fields.name.stringValue, {name: key, total: SUM_BY_KEY([theData[0].fields],"total")})

but I get zeros for the sums. :frowning_face:

I’m not familiar with such REST schema structure so my thinking is limited here.
Try with PLUCK() to isolate the name key values of each object, then wrap it inside SUM().

This seems to be really close to the solution.

Maybe try this:

GROUP(theData, item.fields.name.stringValue, {name: key, sum: SUM_BY_KEY(LOOKUP(LOOKUP(LOOKUP(items, "fields"), "total"), "integerValue")))})

I am not 100% sure if it will work.
Could you please share a schema of Your data resource collection response?
If you could, I would just recreate the schema with a page variable to be able to test the formula. But as I usually work with Firebase connector, I am not too sure of the Firebase direct rest api response schema.

@Mihaly_Toth The formula you suggested is not quite right. Appgyver highlights the closing curly brace in red and will not allow me to save the formula (error is "Invalid syntax: unexpected “}”).

When I cut out the third LOOKUP() like this:

GROUP(data.datColl, item.fields.name.stringValue, {name: key, sum: SUM_BY_KEY(LOOKUP(LOOKUP(items, "fields"), "totalDue"), "integerValue")})

I get these errors: It highlights items in red and totalDue in red. It tells me that

(1) “Value totalDue is not found in any of the following: name, fields, createTime or updateTime”.
(2) I also get a warning that a list type is not assignable to an object type.

Of course, in my data, “totalDue” is NOT a value, it is a key. It is misinterpreting “totalDue” as a value and not as a key. I don’t know how correct that misinterpretation.

But I can save this formula. It appears to successfully group by name (I only get two items in my repeating list, but the sum does not appear in the secondary label (because apparently it cannot find “totalDue”). We are close! Thanks!!

====================== EDIT ======================
I just confirmed that the version Appgyver will let me save (i.e., only two LOOKUP’s; see above) is grouping but the sum is NULL. (I put the grouping formula in a page variable and examined it with the debugger.) If I can get Appgyver to understand that “totalDue” is a key, it will probably work.

================== Edit Again ==================
If I surround the first LOOKUP function with square brackets (to turn it into a list) the warnings change. They become:

(1) Object type is not assignable to a list type
(2) List type is not assignable to an object type
(3) List type is not assignable to an object type

Here is the dummy data I am working with.

[
        {
            "name": "projects/<projectID>/databases/(default)/documents/<collectionID>/<recordID>",
            "fields": {
                "totalDue": {
                    "integerValue": "100"
                },
                "name": {
                    "stringValue": "Jane"
                },
                "selfImprovement": {
                    "integerValue": "0"
                },
                "reading": {
                    "integerValue": "0"
                },
                "workout": {
                    "booleanValue": true
                },
                "date_paid": {
                    "stringValue": ""
                },
                "paid": {
                    "booleanValue": false
                },
                "date": {
                    "stringValue": "08-02-2022"
                },
                "videoed": {
                    "integerValue": "0"
                }
            },
            "createTime": "2022-09-03T13:59:40.963134Z",
            "updateTime": "2022-09-03T13:59:40.963134Z"
        },
        {
            "name": "projects/<projectID>/databases/(default)/documents/<collectionID>/<recordID>",
            "fields": {
                "selfImprovement": {
                    "integerValue": "0"
                },
                "reading": {
                    "integerValue": "0"
                },
                "date": {
                    "stringValue": "09-03-2022"
                },
                "totalDue": {
                    "integerValue": "100"
                },
                "date_paid": {
                    "stringValue": ""
                },
                "paid": {
                    "booleanValue": false
                },
                "name": {
                    "stringValue": "Jane"
                },
                "workout": {
                    "booleanValue": true
                },
                "videoed": {
                    "integerValue": "0"
                }
            },
            "createTime": "2022-09-03T13:59:56.892447Z",
            "updateTime": "2022-09-03T14:12:03.682788Z"
        },
        {
            "name": "projects/<projectID>/databases/(default)/documents/<collectionID>/<recordID>",
            "fields": {
                "videoed": {
                    "integerValue": "0"
                },
                "name": {
                    "stringValue": "Jane"
                },
                "reading": {
                    "integerValue": "0"
                },
                "workout": {
                    "booleanValue": true
                },
                "selfImprovement": {
                    "integerValue": "100"
                },
                "totalDue": {
                    "integerValue": "100"
                },
                "paid": {
                    "booleanValue": false
                },
                "date_paid": {
                    "stringValue": ""
                },
                "date": {
                    "stringValue": "07-02-2022"
                }
            },
            "createTime": "2022-09-03T13:59:51.863653Z",
            "updateTime": "2022-09-03T14:12:19.734793Z"
        },
        {
            "name": "projects/<projectID>/databases/(default)/documents/<collectionID>/<recordID>",
            "fields": {
                "name": {
                    "stringValue": "Pete"
                },
                "date_paid": {
                    "stringValue": ""
                },
                "workout": {
                    "booleanValue": true
                },
                "paid": {
                    "booleanValue": false
                },
                "date": {
                    "stringValue": "07-01-2022"
                },
                "totalDue": {
                    "integerValue": "100"
                },
                "videoed": {
                    "integerValue": "0"
                },
                "selfImprovement": {
                    "integerValue": "0"
                },
                "reading": {
                    "integerValue": "0"
                }
            },
            "createTime": "2022-09-02T22:24:43.077502Z",
            "updateTime": "2022-09-02T22:24:43.077502Z"
        },
        {
            "name": "projects/<projectID>/databases/(default)/documents/<collectionID>/<recordID>",
            "fields": {
                "videoed": {
                    "integerValue": "10"
                },
                "workout": {
                    "booleanValue": true
                },
                "date": {
                    "stringValue": "06-28-2022"
                },
                "date_paid": {
                    "stringValue": ""
                },
                "name": {
                    "stringValue": "Pete"
                },
                "selfImprovement": {
                    "integerValue": "10"
                },
                "reading": {
                    "integerValue": "10"
                },
                "totalDue": {
                    "integerValue": "160"
                },
                "paid": {
                    "booleanValue": false
                }
            },
            "createTime": "2022-09-02T22:26:07.645271Z",
            "updateTime": "2022-09-02T22:26:07.645271Z"
        },
        {
            "name": "projects/<projectID>/databases/(default)/documents/<collectionID>/<recordID>",
            "fields": {
                "videoed": {
                    "integerValue": "5"
                },
                "paid": {
                    "booleanValue": false
                },
                "workout": {
                    "booleanValue": false
                },
                "reading": {
                    "integerValue": "0"
                },
                "date_paid": {
                    "stringValue": ""
                },
                "name": {
                    "stringValue": "Pete"
                },
                "totalDue": {
                    "integerValue": "15"
                },
                "date": {
                    "stringValue": "07-27-2022"
                },
                "selfImprovement": {
                    "integerValue": "0"
                }
            },
            "createTime": "2022-09-02T22:25:13.761037Z",
            "updateTime": "2022-09-02T22:25:13.761037Z"
        },
        {
            "name": "projects/<projectID>/databases/(default)/documents/<collectionID>/<recordID>",
            "fields": {
                "totalDue": {
                    "integerValue": "145"
                },
                "date": {
                    "stringValue": "09-02-2022"
                },
                "videoed": {
                    "integerValue": "15"
                },
                "workout": {
                    "booleanValue": true
                },
                "name": {
                    "stringValue": "Pete"
                },
                "reading": {
                    "integerValue": "0"
                },
                "date_paid": {
                    "stringValue": ""
                },
                "selfImprovement": {
                    "integerValue": "0"
                },
                "paid": {
                    "booleanValue": false
                }
            },
            "createTime": "2022-09-02T18:00:13.123583Z",
            "updateTime": "2022-09-02T19:48:38.050869Z"
        },
        {
            "name": "projects/<projectID>/databases/(default)/documents/<collectionID>/<recordID>",
            "fields": {
                "paid": {
                    "booleanValue": false
                },
                "selfImprovement": {
                    "integerValue": "7"
                },
                "date": {
                    "stringValue": "09-04-2022"
                },
                "reading": {
                    "integerValue": "0"
                },
                "workout": {
                    "booleanValue": false
                },
                "videoed": {
                    "integerValue": "0"
                },
                "name": {
                    "stringValue": "Pete"
                },
                "totalDue": {
                    "integerValue": "7"
                },
                "date_paid": {
                    "stringValue": ""
                }
            },
            "createTime": "2022-09-02T22:25:37.561068Z",
            "updateTime": "2022-09-02T22:25:37.561068Z"
        }
    ]
}]

(Early on I had trouble with the Firebase connector, so I switched to the REST API direct integration which I understood a bit better. Now that I am becoming more familiar with Appgyver, I may give the Firebase connector a try again - though I was not able to find much documentation on its use).