cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
patty789
Helper V
Helper V

Column with a nested table trying to filter based on user selection

Hello,

 

I a stuck on this for a bit. I have two combo box one user selects the plan number and the other displays the elevations, and i want to only to show for the elevations based on the selected plan. Here is the formula that i have yet nothing show up. 

Filter(ForAll(Ungroup(SAL.ProjectID(Label5.Text).elevations, "elevations").number, {number:ThisRecord.number}), number = Plan.Selected.Result).number  The screen caption below shows how the data source is structured so if the user selects that plan i want it filter to show the 5 options listed. 

 

patty789_0-1607361462235.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @patty789 :

I've made a test for your reference:

1\I assume there is a table:

ClearCollect(
    MyCollection,
        {
            baseTag: "445698",
            elevations: [
                {
                    id: 1,
                    number: "500"
                },
                {
                    id: 5,
                    number: "600"
                },
                {
                    id: 17,
                    number: "700"
                },
                {
                    id: 18,
                    number: "800"
                }
            ],
            number: "4569w",
            PlanMenuID: 17
        }

)

2\Add a combox control (Plan) and set it's items property to

MyCollection.number

3\Add an other comobx control and set it's items property to

ForAll(
    LookUp(
        MyCollection,
        number = Plan.Selected.number
    ).elevations,
    {
        id: Value.id,
        number: Value.number
    }
)

The Result:

28.gif

Best Regards,

Bof

View solution in original post

7 REPLIES 7
RandyHayes
Super User
Super User

@patty789 

Is there more to that JSON?  As I see it, the "number" is not associated with anything. The elevations element has a table in it, but it is not related (at least from the picture).

So, essentially, you have a "something" with at least two elements - a elevations element (with a table) and a number element.  So what is the "something"?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes This is what is after "number" then it starts with the next plan with its elevations. 

patty789_0-1607379554367.png

 

RandyHayes
Super User
Super User

@patty789 

Yes, but it's still the top of that picture that is in question.

Because, in general, what you want to do is a Filter on the data :

    LookUp(thatDataConverted, number="4569W")

Then you will be able to get to the elevations.

But I cannot say with certainty because I'm not seeing the full record for your JSON.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes at the top of the picture is just another column name

patty789_0-1607438742417.png

 

RandyHayes
Super User
Super User

@patty789 

Very good.  Thanks for the clarification.

 

So, it seems that your API is only bringing you back (or at least you are only concerned with) .elevations.

You are then trying to filter on the "number" in the outer part of the data (outside of the elevations).

You really need to work this from the outer to the inner.

Something like this:

ForAll(
    UnGroup(
        LookUp(SAL.ProjectID(Label5.Text), Number = "4569W", elevations),
        "elevations"
    ).Value,
    {id: ThisRecord.Value.id,
    number: ThisRecord.Value.number
    }
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes well because i want to establish the relationship with the plan number to only show the elevations when user selects is. so i have two comboboxes 

I tried your approach and its not working for me. I am not using a lookup instead using a filter. 

ForAll(Ungroup(Filter(SAL.ProjectID(Label5.Text),number = Plan.Selected.Result,elevations),"elevations").number, {number:ThisRecord.number})

 

patty789_1-1607460289430.png

 

 

 

v-bofeng-msft
Community Support
Community Support

Hi @patty789 :

I've made a test for your reference:

1\I assume there is a table:

ClearCollect(
    MyCollection,
        {
            baseTag: "445698",
            elevations: [
                {
                    id: 1,
                    number: "500"
                },
                {
                    id: 5,
                    number: "600"
                },
                {
                    id: 17,
                    number: "700"
                },
                {
                    id: 18,
                    number: "800"
                }
            ],
            number: "4569w",
            PlanMenuID: 17
        }

)

2\Add a combox control (Plan) and set it's items property to

MyCollection.number

3\Add an other comobx control and set it's items property to

ForAll(
    LookUp(
        MyCollection,
        number = Plan.Selected.number
    ).elevations,
    {
        id: Value.id,
        number: Value.number
    }
)

The Result:

28.gif

Best Regards,

Bof

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.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

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,678)