Relationships and calcualtion...

[Publi-Script]

Hi all,

I have a database setup with these 5 tables (CUSTOMERS, PRODUCTS, ORDERS, ORDER_ITEMS and CUSTOM_CODES).

CUSTOM_CODES is used to "override" the internal PRODUCT(prodID) and is linked to the CUSTOMERS table by CUSTOMERS(id). In essence, each customer has its own set of "sku's" for the items they buy.

I have made a portal of ORDER_ITEMS related to the ORDERS by ORDERS(orderID). In this portal, I need to list:

PRODUCTS(name);

PRODUCTS(prodID)/CUSTOM_CODES(sku);

PRODUCTS(category)

PRODUCTS(price)

The problem I am having is how can I tell FM to lookup the value of CUSTOM_CODES(sku) for any CUSTOM_CODES record where CUSTOM_CODES(custID)=ORDERS(custID) & portal row PRODUCTS(prodID)=CUSTOM_CODES(prodID)? If no corresponding record is found I would like the value to default to PRODUCTS(prodID)

I figure I should be using a calculated field with the lookup command somewhere in there, but more importantly, I believe I will need to tweak my relationships for this to work. I must admit, I have a pretty hard time wrapping my head around this one.

All help is welcome.

Michel

[comment]

In ORDER_ITEMS, you need a calculation field cCustomerID =

ORDERS::CustomerID

Once you have that, you can establish a relationship to a second occurrence of CUSTOM_CODES as:

ORDER_ITEMS::cCustomerID = CUSTOM_CODES 2::CustomerID

AND

ORDER_ITEMS::ProductID = CUSTOM_CODES 2::ProductID

and use this relationship to lookup the customer's "private" SKU.

[Publi-Script]

Works like a charm, thank you a whole bunch!