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

[martinav]

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.

[timl]

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.

[martinav]

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...

[timl]

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?

[martinav]

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

[martinav]

@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.

[timl · Accepted solution]

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
             )
)

[TopShelf-MSFT]

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 

[martinav]

@timl ,

Yessir, this was effective!  Many thanks.

[timl]

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