How to have formula work across several documents in one collection❓

Formula typically have the index of the object. Somehow, formula read one document only. How to make them read all documents within one collection?
Example:
GROUP(data.Experiences[0].services, price, COUNT(prices))

There are formula functions, that are used on entire collections. For example have a look on the MAP() formula.
You just need to figure out, which formual functions give the result you are looking for :wink:

1 Like

Thanks! Yes MAP is a great formula.
But MAP processes one list (like “services” below) instead of all “service” arrays in each document. I need to crawl through several documents that look like below:

DocID 6fg0dagad6 (created by user1):

field1: "xxx",
field2: "yyy",
services [
        {category: "sports", description: "hmmmm", price: "149"}
        ]

I have this binding available data.Experiences[0].services but the index [0] is an issue as it binds to one docm not all docs with a services [array] within my collection.

Hi Fred,

If you are trying to calculate total price of suppose say shopping cart you can use REDUCE. Suppose you need to get a list by group some category you use MAP to combined values as list. Then add further logic on it. If this answer is not suitable can you share like sample data how it is and what is the results you excepting from it.

Thanks for chipping in.
In my case I need to count how many times a grouped price is mentioned. If I have:
Doc1:

name: "yyy",
services [
        {category: "sports", price: "149"},
        {category: "stay", price: "249"}
        ]

Doc2:

name: "xxx",
services [
        {category: "sports", price: "149"},
        ]

I want GROUP() to return:
149 (2 counts)
249 (1 count)
My current formula only goes through the first document in the collection:
GROUP<service, index, price, prices>(data.ProvidersDB[0].services, service.price, {price: price, count: COUNT(prices)})
And for you @stayfoolish the MAP formula reads only the document whose index is referred to (here [0]), not all Docs from one collection:
MAP(data.ProvidersDB[0].services, item.price)

Hi Fred,

Please use below formula to achieve it
GROUP(FLATTEN(MAP(pageVars.Document,item.service)),item.price, key + " ( “+ COUNT(items) + " counts )” )

1 Like

Gorgeous! I owe you big time.
The key formula was then FLATTEN(). It opens the door to all documents :+1:
Here is my slightly amended formula - with curly brackets and a sort_by_key - to make it a list of sorted objects, which I can use in repeat:
GROUP(SORT_BY_KEY(FLATTEN(MAP(data.ProvidersDB, item.services)),"price"), item.price, {price: key +" ("+ COUNT(items)+")"})

1 Like