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

[patty789]

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. 

[RandyHayes]

@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"?

[patty789]

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

[RandyHayes]

@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.

[patty789]

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

[RandyHayes]

@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
    }
)

[patty789]

@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})

[v-bofeng-msft · Accepted solution]

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:

Best Regards,

Bof