Conditional Lookup

[Oyseka]

Hi, I have a requirement to lookup data in my line items file differentiated by the client name.

The structure is as follows;

Client table related to Invoicing table by Client ID

Invoicing table related to Line Items Table by Invoice Number

Line Items Table related to Items Table by Item Number

One of the clients items price is different so I defined the price field in line items as:

Case(Company="CHECKERS";Lookup(Items::Price);Lookup(Items::Price1)

and that did not work so I tried changing the field to

If(Company="CHECKERS";Lookup(Items::Price);Lookup(Items::Price1) and that doesn't work either.

can anyone help please

[mz123]

If(Company="CHECKERS";Items::Price; Items::Price1)

try that!

[Oyseka]

Sorry, that won't work because it "HardWires" the result so any changes made in the Items table are applied to existing invoices

[aaa]

Hi, Oyseka!

Make field as autoenter without checking "Replace existing". Must it work.

[Oyseka]

Tried that and that does not work either, the line items table only brings in one price no matter who the customer is. Is it possible that there is a problem with the relationship between the tables

Edited May 25, 2006 by Guest

[comment]

Does it work if you commit the record, the re-select the product?

NOTE: Please delete you duplicate posts on this subject in the Relationships section.

[Oyseka]

No, it still only brings in the value from Price1 no matter who the company is

[comment]

I can't tell from your description if this is an implementation problem, or if you are facing the problem described here. It actually doesn't matter much, because you will face that problem anyway. I still don't know a good solution to that one.

Perhaps making the user select the customer by a script (which would conclude by committing the invoice) would be a decent workaround.