Using Gallery with an AddColumns formula then setting on select variable to ThisItem - error w forms

[jarrystowe]

Hi all,

Two parts which are causing me issue...

Filtering a gallery datasource that has MANY lookup fields on sharepoint

This was 'solved' by leveraging "Add Columns" 

My Gallery.Item

SortByColumns(
    Filter(
        AddColumns(Main,
            "Program_Name",ProgramName.Value,
            "Author_Email",Author.Email)
        ,Value(FormSearch.Text)=ID||StartsWith(Author_Email,FormSearch.Text)||StartsWith(Program_Name,FormSearch.Text)),
    "ID",
    Descending
    )

This allows me to use a wildcard filter box and search for ID, Program Name (lookup), and Author (lookup) - this however created another issue around my on select property

My original Gallery.OnSelect before using 'add columns'

Set(selectedIC,ThisItem)

With this my Form.Item was able to be set to selectedIC and worked perfect - with add columns however it shows me an error of 

"Invalid formula.  Expected a value compatible with 'DataSource'."

My other attempt that also works, but still dispalys the error

My Gallery.OnSelect (Kind of works)

Set(selectedIC,LookUp(Main,ThisItem.ID=ID))

All of this being said - the form actually still works using either of these as the item - but it shows an error on in the editor - new items can be created and edited, but the error is making me feel iffy about my process.  

Being able to actually filter on a lookup and not have delegation issues is a big priority - my other work around has been to duplicate the lookup fields in the datasource as simple text and simple retain the value in an invisible field for what was selected.  This seems like an inefficient workaround but does work.

Any thoughts?

[jarrystowe · Accepted solution]

Setting my Main Forms Item property to..

LookUp(Main,selectedIC.ID=ID)

Does seem to work - feels like there is more of a delay when changing selection, does remove the error message tho, maybe there is a better way?

[v-micsh-msft · Accepted solution]

There is no other workaorunds, as far as I can tell.

Using Addcolumns here would change the datasource structure for the gallery, which means the data source for the gallery is not the same as the form control any more.

To make the selected record work, we need to map to the original record with the data source configured in the form control.

Which is the Lookup function you used.

Regards,

Michael

[jarrystowe]

Definitely Michael - I think my 'unease' is from my perception of "Doing a lookup seems like it would take more time than just having an item!"... I'd guess they are functionally pretty similar 🙂