I have an inventory DB, Tables are "Product","WholesaleProduct", "Invoice","Suppliers" and "Product Item Detail". When I create an invoice on the Invoice portal I have the fields Wholesale Product ID, Product Description and Product ID. The product ID is my number the wholesale product ID is the supplier number. I want to be able to enter either number & have the product description come up. I have created another field called Product Calculated Name to hold the formula Case ( Wholesaler Product ID>1 ;Product Description::Product Description;Product ID >1 ; Product Description::Product Description )The formula works when I enter the Product ID but it does not work when I enter the wholesale product ID. Can anyone please tell me what I am doing wrong?

Thanks for any assistance offered I've included the file for clarification.

DR_Sample.zip

I found the problem. You are pulling the wrong data from the wrong field in the wholesale lookup.

Your calculation says:

Case ( Wholesaler Product ID > 1 ;Product Description::Product Description ;Product ID > 1 ; Product Description::Product Description )

Change it to this and it will work:

Case ( Wholesaler Product ID ≠ "" ;WHOLESALE PRODUCTS::Product Description;Product ID ≠ "" ; Product Description::Product Description )

_____

See the wholesale products lookup?

Also, I suggest you change your logic to = "" as opposed to > 1. The = "" means "does not equal an empty field". >1 might not work if you have a product id of 1 (even though this is probably unlikely.)

Another suggestion is that when you change pricing in this database, your invoices are going to change in value as well. if you were to go back to this invoice in 1 year, the totals will be different because the calculation is unstored.

I suggest using the lookup on a regular text field or number field, or for multiple relationships, using a calculated text/number field and use the case and lookup function.

Hope this helps!

Good job!

Thankyou for your suggestions, the formula worked but I'm not sure what you mean in your last sentence "I suggest using the lookup on a regular text field or number field, or for multiple relationships, using a calculated text/number field and use the case and lookup function."

Should I scrap the "Product Calculated Name" and try to get the results I need in another way? I'm pretty new to this, the more I do the more confused I get, which is probably why I've tried to achieve these results in many ways but have not been successful. If your could provide some clarrification I'd apppreciate it Thanks