Lookup function not working where I try to compare with multiple columns in sharepoint list

[AD1990]

Hi,

My table 'Tuition' will have the tuition details.

Student ID(Text type), Student Name(Person type), Student Email(Person type), Teacher ID(Text type), Teacher Name(Person type), Teacher Email(Person type), Tuition Subject(Choice type), Tuition Status(Choice type), Start Date(Date type), End Date(Date Type).

Teacher can offer multiple subjects but one student can apply only once to the teacher for a subject, i.e. Chemistry.

Student cannot apply to the same teacher for subject chemistry, but can apply for physics if the teacher has that subject available for tuitions.

Because of this, my 'Tuition' table will have multiple rows with the same student and teacher data but only the subject will be unique to each row.

When the teacher approves the tuition(by clicking the button), the 'tuition status' field will change from 'applied' to 'ongoing'. And the gallery row should disappear from the 'applied' section of the screen. 

Patch function I wrote is:

Patch
(
'Tuition',
LookUp('Tuition', Teacher_ID=Gallery9_1.Selected.Teacher_ID && Student_ID=Gallery9_1.Selected.Student_ID && Lower(Tuition_Subject.Value) = Lower(Gallery9_1.Selected.Tuition_Subject.Value)),
{
Tuition_Status: {Value: "Ongoing"},
Start_Date: Now()
}
);

Refresh('Tuition')

The problem here is probably the Lookup function is unable to locate the exact row cause the clause(highlighted and is in italics)

is not working/updating the data. It doesn't throw an error.
What change should I make? 

[Nogueira1306]

Patch
(
'Tuition',
LookUp('Tuition', Teacher_ID=Gallery9_1.Selected.Teacher_ID && Student_ID=Gallery9_1.Selected.Student_ID, Lower(Tuition_Subject.Value) = Lower(Gallery9_1.Selected.Tuition_Subject.Value)),
{
Tuition_Status: {Value: "Ongoing"},
Start_Date: Now()
}
);

Refresh('Tuition')

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Last Post on Community

My website!

[AD1990]

@Nogueira1306 

I tried what you suggested but I am getting the above error.

[Nogueira1306]

My mistake, sorry

Patch
(
'Tuition',
LookUp('Tuition', Teacher_ID=Gallery9_1.Selected.Teacher_ID , Student_ID=Gallery9_1.Selected.Student_ID, Lower(Tuition_Subject.Value) = Lower(Gallery9_1.Selected.Tuition_Subject.Value)),
{
Tuition_Status: {Value: "Ongoing"},
Start_Date: Now()
}
);

Refresh('Tuition')

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Last Post on Community

My website!

[AD1990]

@Nogueira1306 
Still error. Invalid number of arguments.

I have also tried putting in semi colon to differentiate the fields in the second argument inside the Lookup function. But it's of no use.

[Nogueira1306]

I forgot that is a Lookup...

Okay, try this,

Patch
(
'Tuition',
First(Filter('Tuition', Teacher_ID=Gallery9_1.Selected.Teacher_ID , Student_ID=Gallery9_1.Selected.Student_ID, Lower(Tuition_Subject.Value) = Lower(Gallery9_1.Selected.Tuition_Subject.Value))),
{
Tuition_Status: {Value: "Ongoing"},
Start_Date: Now()
}
);

Refresh('Tuition')

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Last Post on Community

My website!

[RandyHayes]

@AD1990 

Your formula is in general correct.  Avoid using a First(Filter combination in your formulas as this will impact your performance and wastes resources as the Filter will iterate on ALL records and then First will return just one.

LookUp is the correct formula to use.

I would consider the following change to your formula:

Patch('Tuition',
    LookUp('Tuition', Teacher_ID=Gallery9_1.Selected.Teacher_ID && 
           Student_ID=Gallery9_1.Selected.Student_ID && 
           (Lower(Tuition_Subject.Value) = Lower(Gallery9_1.Selected.Tuition_Subject.Value))
    ),
    {
        Tuition_Status: {Value: "Ongoing"},
        Start_Date: Now()
    }
);

Two things here:

1) In the formula the two Lower functions are surrounded by parens as sometimes PowerApps infers its own order of precedence for the And operations.  This will ensure that the two lowers are evaluated and the result of that (true or false) is then And'ed with the rest.

2) Avoid using Refresh on your Datasource.  It will *already* have the updated data that you just submitted in the Patch.  Refreshing it will only slow down your app to regather the datasource again in your session table.  Not needed because it already has it.

I hope this is helpful for you.

[AD1990]

@Nogueira1306 @RandyHayes 

I think inherently nothing is wrong with my formula(as mentioned by Randy Hayes) and neither is anything wrong with the formulas provided by you two. 
I think, the permutation of teacher_id, student_id and tuition-subject is not able to exactly pin point the specific row in the sharepoint list. To me, those three should be more than enough to uniquely identify the row, but Powerapps is unable to do so. So either my list has more that one rows with that permutation of columns or I need to introduce a unique column to differential all rows from one another(probable number the rows 1, 2, 3...).
I'll look through my list tomorrow and remove repetitive rows(if any) and try the formula again. I'll update you two.
And thank you for the replies and explanations.

[RandyHayes]

@AD1990 

LookUp will return the very first occurrence of the record that matches your criteria.

If you want to double check things, you can always throw a label on your screen and set the Text property to:

LookUp('Tuition', Teacher_ID=Gallery9_1.Selected.Teacher_ID && 
       Student_ID=Gallery9_1.Selected.Student_ID && 
       (Lower(Tuition_Subject.Value) = Lower(Gallery9_1.Selected.Tuition_Subject.Value)),
       true
)

This will show true if it is found and false if not.

If it shows false any you feel is should be true, then I would start to pull that formula apart and hard-code in values.  i.e. change Gallery9_1.Selected.Teacher_ID to a known ID, etc.

See if that way you can narrow down to which part of the formula is causing you some grief.

[AD1990]

@RandyHayes Hey, thank you for the response. I checked as you  suggested and the lookup is returning value. I'll do analysis on this later on. For now I have introduced a column with unique id for the patch function to work as I am on a tight deadline. I really appreciate the explanation in the responses most of all. Thanks.