cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tuonoman
Helper IV
Helper IV

Invalid formula a value with data source is expected

HI

I am creating a stock and product management application. In a gallery I get a table with different IDs and not product and reference. I don't display the IDs but the name equivalent to the ID (via a lookup function).

With({_items:
    Search( 
        AddColumns(PRODUCT As _product; 
           "TAG_NAME"; LookUp(TAG; ID_TAG = _product.ID_TAG; TAG_NAME);
           "PROVIDER_NAME"; LookUp(PROVIDER; ID_PROVIDER=_product.ID_PROVIDER1; PROVIDER_NAME)
        );
       tdSearchinput.Text; "PRODUCT_NAME"; "TAG_NAME"; "PROVIDER_NAME";"MANUFACTURE"
    )};
    
    ForAll(Sequence(CountRows(_items));
        Patch(Last(FirstN(_items; Value)); {LineNumber: Value})
    )
)

This gallery takes me to a form that allows me to view product details.

Unfortunately in "ITEM" I have the following error

Invalid formula a value with data source is expected

 

Would someone please tell me why I'm making this mistake?

Capture.JPG

thanks for your help

 

1 ACCEPTED SOLUTION

Accepted Solutions
LRVinNC
Super User
Super User

No, you don't need the With statement on your detail page.   That is only needed on the gallery. 

 

Let's try this.  In Data Source, keep PRODUCT.  In Items, let's create a lookup to find the selected record.  I don't know the names of all your columns so you will need to find the right column names to use, but the format should be something like this...

 

Lookup(PRODUCT, PRODUCT_NAME = LISTEPRODUCT.Selected.Label3.Text)  

 

where Label3.Text is the correct name of the field in your LISTEPRODUCT gallery that displays the name of the product in your gallery and PRODUCT_NAME is the correct name of the column in PRODUCT containing the matching name.  

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

11 REPLIES 11
LRVinNC
Super User
Super User

 What is the name of your gallery from which you are selecting the item to display?

 

Here's a similar example where Gallery1 is displaying items from datasource PrepItem.  When the user selects an item, the form is displayed.

 

Gal1.jpg

 

 

Here's the form which specifies the same datasource, and then the galleryname and selected row with Gallery1.Selected.  Make sure your capitalization is correct on your gallery name.

Gal2.jpg

 

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi @LRVinNC 

 

Thanks for your help.

 

if I understand your proposal put the same designation that is in the "item" of my gallery in the "datasource" of the form.

the problem is that in the "item" of my gallery I have a formula.

is this not a problem?

 

tempsnip.png

LRVinNC
Super User
Super User

No, the formula is absolutely fine for your gallery.  I just did a very simple example.  It's the Items for your form that will need <name of gallery>.Selected, such as Gallery1.Selected in my example.  The key is it is the name of the object (a gallery) from which you selected the item to show on the form, followed by .Selected to specify which item from the gallery to display.

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
tuonoman
Helper IV
Helper IV

Hello @LRVinNC 

Thank you for your help and example.
But something I don't understand.
You tell me that in the "DetailForm1" ITEM = LISTEPRODUCT.SELECTED and that DATASOURCE must be equal to ITEM of the gallery LISTEPRODUCT.
In my case ITEM of LISTEPRODUCT is a formula. What should I put then?

 

capt2.pngcapt1.png

LRVinNC
Super User
Super User

No, you don't need the With statement on your detail page.   That is only needed on the gallery. 

 

Let's try this.  In Data Source, keep PRODUCT.  In Items, let's create a lookup to find the selected record.  I don't know the names of all your columns so you will need to find the right column names to use, but the format should be something like this...

 

Lookup(PRODUCT, PRODUCT_NAME = LISTEPRODUCT.Selected.Label3.Text)  

 

where Label3.Text is the correct name of the field in your LISTEPRODUCT gallery that displays the name of the product in your gallery and PRODUCT_NAME is the correct name of the column in PRODUCT containing the matching name.  

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

@LRVinNC 

 

Your solution works, thank you.

The problem is that in the end I can only go back up the information that is in the gallery and not the details that don't appear in the gallery.

Did I understand correctly

LRVinNC
Super User
Super User

No, now that you have located the correct record via the lookup, you can access ANY column in that data source.  The columns do NOT have to be in the gallery.  You'll be able to add any of the PRODUCT columns to your form. 

If you would please, be sure to mark the answer which helped you solve your problem as solution.  That will help close out the question in our system.

 

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
tuonoman
Helper IV
Helper IV

HI@LRVinNC 

 

Yes, it works great, but the information that I upload via the "ADD COLUMNS" and "lookup" functions does not come up.
Any ideas?

 

capt3.png

LRVinNC
Super User
Super User

That's because Add Columns doesn't change your underlying data source.  So when you lookup the correct record for your detail screen it is only getting the Product record.  So you'll have to do one of two things:

1.  Add the additional columns you want to use on the detail page again (which is not very efficient)

2.  Save the augmented records in a collection, then work off the collection so that you have access to the entire set of columns you want to be able to use on both the summary and detail pages.  This is the more efficient option but will require some changes to operate off the collection.

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

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 (3,382)