cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EpicTriffid
Resolver I
Resolver I

Collating multiple formulas into one

Morning all,

 

So I've managed to get a set of formulas working on a a gallery that creates a set of related items based on what is in the gallery above, like so (apologies for the flexible height gallery making it look weird):

 

9.PNG

When the glossary items change, so do the related items. It's taking every term in the top gallery, analysing it against a set of terms in a glossary data set (separated by ";") and returning the other items that also contain the same glossary term. This is done via the glossary button running this:

 

ClearCollect(
    seeAlsoPre,
        ForAll(
            'Glossary Items Gallery'.AllItems,
            Ungroup(
                ForAll(
                    Trim(Split(Category, ";")),
                    Trim(Split(Result,";"))),
                "Value"
            )
        )
);


Clear(seeAlso); ForAll(seeAlsoPre, Collect(seeAlso, Value))

 

The top collection (seeAlsoPre) is doing what i said above and finding the relevant items. This then drops them into an arrayed collection, which the second collection is then changing into a table of single values. The related items gallery then has this code:

 

Distinct(Filter(seeAlso, Not(IsBlankOrError(Result)), Not(Result in suggested.Title)),Result)

 

This bringing back a distinct list of non blank items that are not the same as the item current selected (i.e. in the screenshot above we are on the KPI item, so the gallery of related items is omitting KPI from the terms as it would just be linking to itself).

 

Now, due to trying to improve the interface a little, which isn't relevant, I was hoping someone might be able to instruct me how to aggregate all this code into the Items code for the related item gallery, so that I'm not creating collections and the like?

 

Cheers!

 

Matt

 

1 ACCEPTED SOLUTION

Accepted Solutions
EpicTriffid
Resolver I
Resolver I

I worked it out!

 

Distinct(
    Filter(
        Ungroup(
            ForAll(
                'Glossary Items Gallery'.AllItems,
                Ungroup(
                    ForAll(
                        Trim(Split(Category,";")),
                        Trim(Split(Result,";"))
                    ),
                    "Value"
                )
            ), 
            "Value"),
        Not(IsBlankOrError(Result)), Not(Result in suggested.Title)),
    Result
)

 

For anyone interested, or if you come across this kind of thing, I was trying to replicate my original code by running the ForAll here:

Clear(seeAlso); ForAll(seeAlsoPre, Collect(seeAlso, Value))

around the other ForAll here:

ClearCollect(
    seeAlsoPre,
        ForAll(
            'Glossary Items Gallery'.AllItems,
            Ungroup(
                ForAll(
                    Trim(Split(Category, ";")),
                    Trim(Split(Result,";"))),
                "Value"
            )
        )
);

Essentially, I was removing the collect functions and trying to mush them together, but it just wasn't working. Then I realised that Ungroup flattens a table, so I ran that around my original code, and was able to splice in my Distinct and Filter functions too! Basically, just use Ungroup to flatten a nested table!

View solution in original post

2 REPLIES 2
EpicTriffid
Resolver I
Resolver I

Hello all,

 

Hoping someone might have thought of a solution?

EpicTriffid
Resolver I
Resolver I

I worked it out!

 

Distinct(
    Filter(
        Ungroup(
            ForAll(
                'Glossary Items Gallery'.AllItems,
                Ungroup(
                    ForAll(
                        Trim(Split(Category,";")),
                        Trim(Split(Result,";"))
                    ),
                    "Value"
                )
            ), 
            "Value"),
        Not(IsBlankOrError(Result)), Not(Result in suggested.Title)),
    Result
)

 

For anyone interested, or if you come across this kind of thing, I was trying to replicate my original code by running the ForAll here:

Clear(seeAlso); ForAll(seeAlsoPre, Collect(seeAlso, Value))

around the other ForAll here:

ClearCollect(
    seeAlsoPre,
        ForAll(
            'Glossary Items Gallery'.AllItems,
            Ungroup(
                ForAll(
                    Trim(Split(Category, ";")),
                    Trim(Split(Result,";"))),
                "Value"
            )
        )
);

Essentially, I was removing the collect functions and trying to mush them together, but it just wasn't working. Then I realised that Ungroup flattens a table, so I ran that around my original code, and was able to splice in my Distinct and Filter functions too! Basically, just use Ungroup to flatten a nested table!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,310)