cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jarrystowe
Helper II
Helper II

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

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
jarrystowe
Helper II
Helper II

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?

View solution in original post

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

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
jarrystowe
Helper II
Helper II

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?

View solution in original post

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

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,472)