Using SQL data sources, LOOKUP Join in Gallery is returning blank values!

[Sveygal]

Hey guys, 

I'm trying to have my gallery read from two SQL tables by establishing a LOOKUP in the Items property. My two data sources are already established in the data tab:

The item property in its entirety looks like this: 

Filter(
    AddColumns(
        SALESORD_LINES,
        "ORDERDATE",
        LookUp(
            SALESORD_HDR,
            SEQNO = 'SALESORD_LINES'[@HDR_SEQNO]
        )
    ),
    HDR_SEQNO > 0 && STOCKCODE <> Blank() && STOCKCODE <> " " && HDR_STATUS = 0
)

The problem I have is that this returns a completely blank gallery, despite no formula problems or delegation issues.

I tried troubleshooting:

• Getting rid of the join and just using either SALESORD_HDR or SALESORD_LINES sources returns values as expected
• Isolating the join statement by removing the filter does not help

I created this based of this example from this video from Krishna Vandanapu - MVP .  Granted, his example was referencing Sharepoint data sources, but the example should apply to SQL sources as well right? 

Any help would be greatly appreciated. 

[v-jefferni · Accepted solution]

Hi @Sveygal ,

To prevent performance issue, when using SQL Server tables as the data source, you will only get data in the column that you just added. You may test this with a collection to check the result value of AddColumns:

ClearCollect(colTest, 
             AddColumns(
                 SALESORD_LINES,
                 "ORDERDATE",
                 LookUp(
                     SALESORD_HDR,
                     SEQNO = 'SALESORD_LINES'[@HDR_SEQNO]
                 )
             )
)

Please use ShowColumns function to add value to all the data columns you need.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[Sveygal]

This clears it up @v-jefferni , thank you very much.