cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Trying to build a collection from items from a sharepoint list...

I need help writing this statement.  

 

ForAll(PartNumbers_Updated_Pre.MD_PartNumber,Patch(NewMDL, LookUp('[dbo].[VIEW_POWERAPPS_BASIC_ACCTIVATE_DATA]','[dbo].[VIEW_POWERAPPS_BASIC_ACCTIVATE_DATA]'.ProductID=PartNumbers_Updated_Pre.MD_PartNumber,ID),{ID: ID, ProductID: ProductID, Available: Available}))

The expectation is that I get a table of data given Where "MD_Partnumber" from PartNumbers_Update.Pre equals "ProductID" from the SQL table.  The columns "ID" and "Available" need to be pulled int NewMDL.

 

This statement I think communicates the logic, but does not resolve.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Trying to build a collection from items from a sharepoint list...

Hi @martinav 

You should be able to do this with something like this:

 

ClearCollect(Collection3,
AddColumns(RenameColumns(Collection1, "MD_PartNumber", "MD_PartNumber1"),
"Available",
LookUp(Collection2, MD_PartNumber1=MD_PartNumber).Available,
"Location",
LookUp(Collection2, MD_PartNumber1=MD_PartNumber).Location
)
)

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Trying to build a collection from items from a sharepoint list...

Hi @martinav 

It's a bit tricky to say without being in the designer but the bits I've highlighted in red are the parts that I would experiment with removing.

 

ForAll(PartNumbers_Updated_Pre.MD_PartNumber,
       Patch(NewMDL, 
             LookUp('[dbo].[VIEW_POWERAPPS_BASIC_ACCTIVATE_DATA]',
                    '[dbo].[VIEW_POWERAPPS_BASIC_ACCTIVATE_DATA]'.ProductID=
PartNumbers_Updated_Pre.MD_PartNumber,ID), {ID: ID, ProductID: ProductID, Available: Available} ) )

Assuming that PartNumbers_Updated_Pre is the name of your SharePoint list, you should be able to ForAll over this without specifying MD_PartNumber. Perhaps if you make this change, the IntelliSense/any error messages will guide you towards the correct syntax in the remainder of the formula.

 

 

Super User
Super User

Re: Trying to build a collection from items from a sharepoint list...

Your reccomendation is what I started with.  Adding all that extra were my efforts of desparation to try to get it to work.  Somtimes, its just a nuance in symantics.

 

The flyover help is also bloody worthless...

 

 

Super User
Super User

Re: Trying to build a collection from items from a sharepoint list...

Hi @martinav 

Is there any part of flyover help that you can share?

 

Just to confirm, is there definitely no mismatch in data types? For example, if '[dbo].[VIEW_POWERAPPS_BASIC_ACCTIVATE_DATA]'.ProductID is defined as an int in SQL Server, is MD_PartNumber defined as a numeric data type in SharePoint?

Super User
Super User

Re: Trying to build a collection from items from a sharepoint list...

Yes.. Ive bailed on this approach.  More later.

Super User
Super User

Re: Trying to build a collection from items from a sharepoint list...

@timl ,

 

Ok... lets start over with what I'm trying to accomplish:

 

I have two collections:

 

Collection1([ID, MD_PartNumber,PartName,Rev,Status,Approved])

 

Collection2([ProductID,MD_PartNumber,Available,Cost,Location,Vender,Active])

 

I want to make Collection3([ID,MD_PartNumber,PartName,Rev,Available,Location])

 

I need Collection3 keyed on MD_PartNumber.  MD_Partnumber is non-repeating in both collections.

 

I dont think it sounds complicated...  I just cant seem to make it happen.

Super User
Super User

Re: Trying to build a collection from items from a sharepoint list...

Hi @martinav 

You should be able to do this with something like this:

 

ClearCollect(Collection3,
AddColumns(RenameColumns(Collection1, "MD_PartNumber", "MD_PartNumber1"),
"Available",
LookUp(Collection2, MD_PartNumber1=MD_PartNumber).Available,
"Location",
LookUp(Collection2, MD_PartNumber1=MD_PartNumber).Location
)
)

View solution in original post

TopShelf-MSFT
Level 10

Re: Trying to build a collection from items from a sharepoint list...

Thanks for posting in the community @martinav and I'm sorry you're experiencing this issue. Can you review the above reply and advise if it was helpful?

 

Thank you,

 

@TopShelf-MSFT 

Super User
Super User

Re: Trying to build a collection from items from a sharepoint list...

@timl ,

 

Yessir, this was effective!  Many thanks.

Super User
Super User

Re: Trying to build a collection from items from a sharepoint list...

Great, I'm glad that worked for you @martinav!

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,933)