Answered

How to do a lookup once in a forall()

(0) Share

Report

Posted on by martinav

3,340

I want to ping the SQL table once per record. Instead of a lookup for each column one at a time. My forall() may get quite big. I want to limit the server calls.

ClearCollect(AttachedFile_Data,ForAll(AttachedFile_PreData,
{ FileName: FileName,
 WO_Num: ThisRecord.WO_Num,
 SNRange: ThisRecord.SNRange,
 Filename: ThisRecord.FileName,
 WO_Line: ThisRecord.WO_Line,
 OrderNumberOB: ThisRecord.WO_Num&"-"&ThisRecord.WO_Line,
 ShouldBeName: "WO "&WO_Num&"-"&WO_Line&" "&SNRange&".pdf",
 GUIDOrder: LookUp('[dbo].[View_Orderboard_Acctivate]',OrderNumber=WO_Num And LineNumber=WO_Line).GUIDOrder,
 GUIDOrderDetail: LookUp('[dbo].[View_Orderboard_Acctivate]',OrderNumber=WO_Num And LineNumber=WO_Line).GUIDOrderdetail,
 FileNameCheck: If(SNRange=LookUp('[dbo].[View_Orderboard_Acctivate]',OrderNumber=WO_Num And LineNumber=WO_Line)._SNValve,true,false)
}

The statement above works, but I want to do a single lookup into a variable, then use variable.GUIDOrder and variable.GUIDOrderDtail, etc. I'm sure there is a way to do this with a With() statement, but I couldnt place it in this statement in a place where it worked proper. Well, I never resolved syntax problems.

Helpie?

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (2)

Verified answer

CU-18081211-6 9,270 Moderator on at

Like (1)

Report
Copy link

Link copied!
@martinav ,

ClearCollect( AttachedFile_Data, ForAll(AttachedFile_PreData, With({currentLookUp:LookUp('[dbo].[View_Orderboard_Acctivate]',OrderNumber=WO_Num And LineNumber=WO_Line)}, { FileName: FileName, WO_Num: ThisRecord.WO_Num, SNRange: ThisRecord.SNRange, Filename: ThisRecord.FileName, WO_Line: ThisRecord.WO_Line, OrderNumberOB: ThisRecord.WO_Num&"-"&ThisRecord.WO_Line, ShouldBeName: "WO "&WO_Num&"-"&WO_Line&" "&SNRange&".pdf", GUIDOrder: currentLookUp.GUIDOrder, GUIDOrderDetail: currentLookUp.GUIDOrderdetail, FileNameCheck: SNRange=currentLookUp._SNValve } ) ) )

I think that you are aware that this function makes SQL query calls as many records AttachedFile_PreData has.

Hope it helps !

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report
Copy link

Link copied!

@gabibalaban ,

Hmm, this is what I did, and it broke. I'll try again and see, and report back.

Was this reply helpful? Yes No
CU-18081211-6 9,270 Moderator on at

Like (2)

Report
Copy link

Link copied!

@martinav ,

If my proposal not works, please let me summon the @RandyHayes's power for a little help. 😊

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report
Copy link

Link copied!

@gabibalaban ,

I think I know why... i'll check in a bit.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!

@gabibalaban

It's looking good! It's the way to do it.

Was this reply helpful? Yes No
Verified answer

martinav 3,340 on at

Like (0)

Report
Copy link

Link copied!
Ok, I remember now. doing the With() there breaks ThisRecord due to ambiguity. The As varForAllValue in the ForAll() fixes this. THanks @gabibalaban and @RandyHayes !

learCollect(AttachedFile_Data,ForAll(AttachedFile_PreData As varForAllValue,With({varLookupFirst:LookUp('[dbo].[View_Orderboard_Acctivate]',OrderNumber=varForAllValue.WO_Num And LineNumber=varForAllValue.WO_Line)}, { FileName: varForAllValue.FileName, WO_Num: varForAllValue.WO_Num, SNRange: varForAllValue.SNRange, Filename: varForAllValue.FileName, WO_Line: varForAllValue.WO_Line, OrderNumberOB: varForAllValue.WO_Num&"-"&varForAllValue.WO_Line, ShouldBeName: "WO "&varForAllValue.WO_Num&"-"&varForAllValue.WO_Line&" "&varForAllValue.SNRange&".pdf", GUIDOrder: varLookupFirst.GUIDOrder, GUIDOrderDetail: varLookupFirst.GUIDOrderdetail, FileNameCheck: If(varForAllValue.SNRange=varLookupFirst._SNValve,true,false) } )))

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@martinav

Good catch! I took a quick look once @gabibalaban included me on the post. Sorry was knee deep in work that I didn't look closely for that...you are correct - in general, stay away from ThisRecord. Use the As clause as much as possible.

But, in the context of your formula, you don't need the As clause as each record generated in your ForAll will be generated in context of the AttachedFile_PreData records, so there is no collision.

Was this reply helpful? Yes No