Referencing an iteration of a ForAll Loop

[Anonymous]

Hey guys, 

I'm trying to iterate through a local collection to send each item to the SQL Server Database. I'm having difficulty finding any documentation on how to reference the iteration. What I currently have, I found from a forum post and I'm unsure on whether LINEITEM was a collection of their own since I haven't found anything official about it. Here is what I currently have:

ForAll(ResourceCost,
Collect('[dbo].[ResourceCost]',
{
ProjectID: projid,
RoleID: LookUp('[dbo].[LINEITEM]', RoleID),
DailyCost: LookUp('[dbo].[LINEITEM]', DailyCost),
ResourceProfessionalFees: LookUp('[dbo].[LINEITEM]', ProFees),
ResourceID: LookUp('[dbo].[LINEITEM]', ResourceID),
ProfitSharing: LookUp('[dbo].[LINEITEM]', ProfitShared),
LeadBonus: LookUp('[dbo].[LINEITEM]', LeadBonus),
CreatedOn: Now(),
CreatedBy: "POWERAPPS_PRA",
LastModifiedOn: Now(),
LastModifiedBy: "POWERAPPS_PRA"} ));

I realize LookUp is missing the condition, I've tried with conditions but I don't know how to reference the item other than '[dbo].[LINEITEM]', LINEITEM.VALUE, or ThisItem.VALUE. I've also tried the First() function but it jacks everything up. Any insight towards this issue would be much appreciated!

Thanks,

Dan

[mdevaney]

@Anonymous
To do this change your code for RoleID to:

LookUp('[dbo].[ResourceCost]', ID=ResourceCost[@ID], RoleID)

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

[Anonymous]

Thanks for the response, but wouldn't '[dbo].[ResourceCost]' search the table from SQL Server? I tried LookingUp the collection instead since the table isn't populated yet. Here is what I have:

RoleID: LookUp(ResourceCost,ID = ResourceCost[@ID], RoleID)

and I get the error "Name isn't valid. This identifier isn't recognized." under 'ID' and '[@ID]'.

[mdevaney]

@Anonymous 

Yes, it would search the table from the SQL Server.  I thought that was what you were trying to do.  Lol, my bad.

If you simply want to insert every line of the collection into SQL it can be done like this.  It will work so long as the collection has all of the same column names and data types.

Collect('[dbo].[ResourceCost]', ResourceCost)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Anonymous]

Essentially, that is what I'm trying to do; but there are additional fields from the collections that need to be added. In some instances I will probably have to do a LookUp where id matches id for a field from another collection. So is there a way to reference the item in the loop?

[mdevaney]

@Anonymous 

Essentially this:

LookUp(target_collection, ProjectID=ResourceCost[@ProjectID], target_column)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[v-siky-msft]

@Anonymous 

Do you mean you want to save [dbo].[LINEITEM] values to some [dbo].[ResourceCost] columns based on the same id?

please try this:

RoleID: LookUp('[dbo].[LINEITEM]', <LineItemID>= ResourceCost[@<ID>], RoleID)

or

RoleID: LookUp([@'[dbo].[LINEITEM]'], <LineItemID>= ResourceCost[@<ID>], RoleID)

Note: Replace the <LineItemID> to field name of lineItem, and replace <ID> to field name of ResourceCost.

If this doesn't work, please post back the error screenshots.

Sik

[Anonymous]

Should I be targeting the collection? This clears the errors, but I don't see where this is selecting the individual iteration exactly unless it's the [@ID]. Sorry, I'm just trying to have a better understanding of how this works.

[Anonymous]

This isn't working for me either. I tried with and without the [@] around the dbo and with and without the angle brackets. Without the angle brackets took away the operator sign error.

[yashag2255]

Hi @Anonymous 

Collect is used for local collections, if you are looking for a way to directly add the data to datasource, you need to use Patch as:

ForAll(RenameColumns(ResourceCost, "RoleID","RoleID1","DailyCost","DailyCost1","ResourceProfessionalFees","ResourceProfessionalFees1", "ResourceID","ResourceID1", "ProfitSharing","ProfitSharing1", "LeadBonus", "LeadBonus1"),
Patch('[dbo].[ResourceCost]', Defaults('[dbo].[ResourceCost]')
{
ProjectID: projid,
RoleID: RoleID1,
DailyCost: DailyCost1,
ResourceProfessionalFees: ResourceProfessionalFees1,
ResourceID: ResourceID1,
ProfitSharing: ProfitSharing1,
LeadBonus: LeadBonus1,
CreatedOn: Now(),
CreatedBy: "POWERAPPS_PRA",
LastModifiedOn: Now(),
LastModifiedBy: "POWERAPPS_PRA"} ));

This will create new records in the datasource. The issue occurs when the iterating collection column Name is same to the datasource. In that case, you can rename the columns.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!