Whether customer or staff created an order

[kwirky]

Hey all,
I am trying to find a smart way to verify whether the person who created the online order is a staff member or a customer (customers have access to online ordering system).
Obviously I can create a formula which would list staff members and compare to the creator;

if {MainJobDetails.CreateUserName} = "John Smith" then "Staff" else
if {MainJobDetails.CreateUserName} = "Mary Magoo" then "Staff" else
"Customer"

and do a listing for all staff members and incorporate an if then else. I would be able to get what I want.

BUT...
We have high staff turnover and I wanted to use a separate table & field called {Users.FullName}, which I could then use to identify whether it was a staff or customer:

if {MainJobDetails.CreateUserName} in {Users.FullName} then "WW Staff" else "Customer"

However I am having significant issues trying to link this table so that I can get the report to check if the creator is on that list. If they are on the list, then I would want them to be "Staff" however if they do not appear on the list, then they should be designated "customer".

Am using CR 2008 and SQL database.

Any help would be fabulously wonderful

 

[lbass]

You should be able to use a left outer join FROM the MainJobDetails table TO the Users table on the name field. Then use a formula like this:

if isnull({Users.FullName}) then
"Customer" else
"WW Staff"

Of course the names in each table would have to exactly match (if they do).

-LB

 

[kwirky]

Thanks for that