cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tundaka
New Member

Lookup not working properly in ForAll?

Hello everyone, I am fairly new in PowerApps so now, I don't know it's limitation or I just haven't found the solution.

I have a simple app that I want to do, basically it's a bulk upload tool of salary. My idea was to put it in a delimited text, and then delimit it in a gallery.

 

tundaka_0-1648042828137.png

 

Here's my code in the upload button :

 

 

 

ForAll(
Gallery2.AllItems,
Patch(
'Salary Series',
Defaults('Salary Series'),
{'Employee ID':
LookUp(
Employees,
'Employee ID' = txt_employeeID.Text
)
},
{Value: Value(txt_Value.Text)}
)
)

 

 

 

 

tundaka_2-1648042990979.png

 

it works, but it doesn't lookup the employee id, it only does it in the first row. (Employee ID is a Lookup column to Employees table). Hopefully someone could help me.

32 REPLIES 32
RandyHayes
Super User
Super User

@tundaka 

So, a little confused on the over all design then...

In your original picture you show a gallery of delimited items, but you are mentioning a text input for this.

From your picture, is the idea to have the person enter the employee ID into one text input and then the salary in the second, and then for the records created/updated, you want the two combined together with a delimiter?

Please clarify.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
RandyHayes
Super User
Super User

@Gelos 

Good to meet you too!

 

Well, the formula still has the ForAll used backwards - it is being used like a For Loop in some programming language, which PowerApps is not.  ForAll is a function that returns a table and is best used as designed.

 

In the case of the Value column, it is a second column in the record from what I can gather, but there is no need to treat it as a separate record, it can all be included in the one record being generate for the Table.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Where do you get employee Id. What is the value:value(txt_value.txt) is it second parameter or it is employ id you wanted

 

ok. I understand.

But I used bulk upload like this.

ForAll(
Gallery2.AllItems,
Patch(
'Salary Series',
Defaults('Salary Series'),
{'Employee ID':
LookUp(
Employees,
'Employee ID' = txt_employeeID.Text,(coulumn of employeeid from employess table)
)
},
{Value: Value(txt_Value.Text)}
)
)

In my side. It will also worked perfectly. Please clarify me. 

RandyHayes
Super User
Super User

@Gelos 

Sure it will work, but your performance will suffer from it!

The ForAll is backward.  Functions like Patch and Remove, etc. all accept tables for their parameters.  The reason for that is so that you can process all of the table PRIOR to instantiating that function.  Those functions are performance expensive.  So, if there is not concern for performance, then using it like a for loop will do, but if you want your app to be performant, then using it properly is best.

When it is used like a for loop, it will instantiate the Patch function over and over...this is performance expensive.  Providing the patch function with a table of records to work with will instantiate the patch function once...which will be highly performant.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi @Gelos , I'm getting this error:

tundaka_0-1648046975588.png

 

but for more details, sorry if I'm terrible at explaining, and I probably used the word delimit wrong.

Value is the salary.

 

P1603:214000||
P2111:216000||
P0001:236000

Employee_ID: Salary

|| = is for a new record

The person enters it like this:

 

P1603:214000||
P2111:216000||
P0001:236000

 

and then split it and copy it in the gallery.

and then the upload button does the patching:

Employee ID Column = txt_employeeID

Salary Column = txt_Value

 

(Employee ID): (Salary) 

|| = is for new record

RandyHayes
Super User
Super User

@tundaka 

So there is still confusion over the picture with a gallery in it and now you are showing text that is delimited with double pipes and a colon. 

 

If you are working from the text you are showing, then your formula would change to the following:

Patch('Salary Series',
    ForAll(Split(yourDelimitedText, "||"),
        With({_record: Split(Result, ":")},
            {'Employee ID': First(_record).Result,
             Value: Value(Last(_record).Result)
            }
        )
    )
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

My reason for having a two step process (split it first, display it in gallery, and then upload), is so that the person could still do a quick review if the employee would receive the correct salary. 

 

I tried your last code, but this is error that I am getting.

But reading it like this, since the employee id is a lookup column, from what I understood, I cant just patch a string in a lookup column right?

 

tundaka_0-1648048383019.png

 

 

 

RandyHayes
Super User
Super User

@tundaka 

A couple of things:

1) you are trickling in the details that are needed to resolve your issue!  You have now mentioned a lookup column.  Is that what you are dealing with - a lookup column?  Is 'Employee ID' the lookup column?

2) What is your datasource?

3) Please clarify this - (based on your picture) you have a text input control (the box on the left) that is where your users will paste in the text.  Then you have a Gallery (right) that will show all of the undelimited items with the ability for the user to edit them if needed.  And finally, it is that gallery of separated empID and Salary that you want to write back to the datasource.  Please let me know if I am understanding your design properly.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (5,513)