Lookup() Not Working Correctly with Nested JSON

[ericonline]

Ok, I might be going insane here, but this Lookup() function is not functioning correctly. Its so complicated though, I don't know if it can be explained in a forum post, but here goes.

Schema of Collection colThings (an API Response):

{
    "level1": [
        {
            "level2_location": {
                "city": "SEATTLE ",
                "state": "WA"
            },
            "level2_thing": [
                {
                    "level3_thingNumber": "11223344",
                    "level3_thingList": [
                        {
                            "level4_thingID": {
                                "thingInit": "ABC ",
                                "thingNum": "     778899"
                            },
                            "level4_thingStatus": {
                                "statusIndicator": "Y"
                            }
                        }
                    ]
                }
            ]
        }
    ]
}

Gallery Items Property:

(Adding a column to measure the number of things with StatusIndicator "Y". Used elsewhere in the app)

AddColumns(
    First(
        First(
            colThings.level2_thing
        ).level2_thing.level3_thingList
    ).level3_thingList,
    "statusSum",
    If(
        level4_thingStatus.statusIndicator = "Y",
    1,0
    )
)

OnChange of a Toggle Control in each Gallery item:

(Big thanks to Kris Dai for assisting with the Remove() function here!)

If(
    ThisItem.level4_thingStatus.statusIndicator <> 
        If(
           toggle.Value = false,"Y",
           toggle.Value = true,"N"
        ),
    Collect(colPOSTstatuses,
        Table(
            {
                level3_thingList:
                Table(
                    {
                        level4_thingID: {
                            thingInit: ThisItem.level4_thingID.thingInit,
                            thingNum: ThisItem.level4_thingID.thingNum,
                            thngPrefix: ThisItem.level4_thingID.thngPrefix
                        },
                        level4_thingStatus:{
                            statusIndicator: 
                            If(
                                toggle.Value = false,"Y",
                                toggle.Value = true,"N"
                            )
                        }
                    }
                )
            }
        )
    ),
    Remove(colPOSTstatuses,
        LookUp(colPOSTstatuses,
            First(level3_thingList.level4_thingID).level4_thingID.thingNum= ThisItem.level4_thingID.thingNum
        )
    )
)

The Lookup() function that is not working:

If(
    IsBlank(
        LookUp(
            colPOSTstatuses.level3_thingList,
            level3_thingList.thingNum = ThisItem.level3_thingList.thingNum,
            level4_thingStatus.statusIndicator
        )
    ),
    "Blank",
    LookUp(
            colPOSTstatuses.level3_thingList,
            level3_thingList.thingNum = ThisItem.level3_thingList.thingNum,
            level4_thingStatus.statusIndicator
        )
)

Test:

• I have 1 item in colPOSTstatuses and 3 items in the Gallery.
• The Lookup() function above is in each Gallery item.
• All 3 have a value (either "Y" or "N").
• 2 of 3 SHOULD have a value of "Blank" (text, not Blank() ).

The issue?:

• Can a Lookup() function handle equipmentNum and statusIndicator being in separate "subtables" of the nested JSON?
• Where is the Lookup() getting 3 values ("Y", "Y", and "N") when only 1 record exists in the Collection!?!

[ericonline · Accepted solution]

Thanks @KickingApps !! 

The problem:

• Gallery reference collection (colThings) was different than the Lookup reference collection (colPOSTstatuses).
• I needed to define the full "path" to the colPOSTstatuses field

Was:

If(
    IsBlank(
        LookUp(
            colPOSTstatuses.level3_thingList,
            level3_thingList.thingNum = ThisItem.level3_thingList.thingNum,
            level4_thingStatus.statusIndicator
        )
    ),
    "Blank",
    LookUp(
            colPOSTstatuses.level3_thingList,
            level3_thingList.thingNum = ThisItem.level3_thingList.thingNum,
            level4_thingStatus.statusIndicator
        )
)

Changed to:

LookUp(
    colPOSTstatuses.level3_thingList,
    First(First(colPOSTstatuses.level3_thingList).level3_thingList.level4_thingID).level4_thingID.thingNum = ThisItem.level4_thingID.thingNum,
    First(First(colPOSTstatuses.level3_thingList).level3_thingList.level4_thingStatus).level4_thingStatus.statusIndicator
)

[ericonline]

eh! This only returns the First value in colPOSTstatuses! Need a way to return the value for each item in the Gallery. 

[ericonline · Accepted solution]

ok @ericonline ... lets try this again 🙂 :
This worked to bring results back for every Gallery item, not just the first value in the Collection:

LookUp(
    colPOSTstatuses,
    First(level3_thingList.level4_thingID).level4_thingID.thingNum= ThisItem.level4_thingID.thingNum,
    First(level3_thingList.level4_thingStatus).level4_thingStatus.statusIndicator
)