When setting up a look up is there a way when adding multiple "When" clauses if it should do "And" or "Or"?
Within a particular rule, you can add multiple conditions to create AND logic.
To create OR logic, you'd want to create multiple rules and order them in the order you want the rules to be evaluated. The list of rules is evaluated from top to bottom, so creating multiple rules should give you a way to create OR logic.
So what would we do if we want to create AND logic for the same Fill field? So for instance we have a field to display a Teacher. We only want Teachers to display that have a Status of 1 AND a selected School ID.
Where are you getting the value for Status? Currently what you're describing isn't available in simple lookup rules, but should be doable with SQL stored procedure lookup rules. Form fields like School ID and Status could be input parameters for a stored procedure that returns a teacher and list of students in its result set.
The Status is a Form field that has a default value of 1. This field will be hidden because 1=Active in the database. This is using an Oracle database, so stored procedures aren't an option.
As long as the field values are being matched against columns in the table, you should be able to accomplish what you're trying to do, whether by using multiple conditions within one rule or by using multiple rules in a specific order.