Field to 'look up' from two different tables?


#1

Hi there, I run a coffee service business. i have a table called “scheduling”, and then one for “sales orders”, and another “work orders”. my schedule for my driver includes a mix of coffee deliveries and work orders. So I would like to be able to generate either type of order and have it populate on the ‘schedules’ table. I also have a column on schedules for “complete”, and this would be defined (look up) by the status set for the order in the respective two tables. i can’t figure out how to have this column auto-complete from two different tables. I know how to use the ‘look up’ feature, but it seems in this case i am trying to have the column look to both tables. if this isn’t possible I suppose next option would be to create two different columns in Scheduling, one that points to the status if its a work order, and the other column if its a sales order?


#2

Im wondering if I could create an additional column for status (in scheduling) and use a formula that can pull the status from both of the other status columns.

That seemed to work to pull from the other two ‘status’ columns by doing a A & B formula. But it would be great for odd miscellaneous jobs that aren’t a sales order or work orders, to be able to input them into the ‘scheduling’ table, in which case this status column would also need to modify the formula such that if there is no content from A or B, then it will allow me to write “single line text”, anyone know how to do that?


#3

Hi, what kind of formula did you use for A & B?