Publi-Script

I knew that, but as soon as I make my choice and let go of the control, the Product_ID is shown not the product name. I wanted to know if it is possible to set a field to be linked to one record value (Product_ID) and display another the value of another field form the same record (Product name). Or do I have to set up another calculated field that would display the Product name? I prefer for to to be calculated because those "looked up" values could change. It is my understanding that "Looked-up" values are only verified once, as opposed to calculated values that are constantly refreshed. That said, I do not know how to have my formula returning the SKU of that product for that customer. For some reason, it does not consider the Customer_ID. Can't seem to be able to make that work either... Am I doing something wrong?

Thank you for trying to help me. Relationships are hard (pun intended) and they still make my head spin at times. I made some adjustments based on your suggestions but I am still left with some questions: Can I still display the [color:red]Product Name in a field use to choose [color:red]Product ID? My portal row is setup like this ([color:red]Product Name, [color:red]Sku/Product Code, [color:red]Category, [color:red]Brand, [color:red]Units, [color:red]Price). Following your suggestion, my Product name column now show [color:red]Product_ID's which is not really user friendly. The [color:red]SKU is still not returning the associated SKU for the current product. I have setup [color:brown]Job Lines [color:red]SKU to the following formula: [color:blue]Lookup ( Custom_Codes::SKU ; Products::GTIN ). It is still returning the first SKU (corresponding to that product) whether or not it is asociated to the [color:brown]Jobs's [color:red]Customer_ID. Here's how my relationships are setup: Will I be able to populate my row entries by either filling the [color:red]Product_ID or the [color:red]SKU? TIA

Hi all, I am currently facing two situation with a portal which I believe to be related to relationship. This portal reside on a Job form where each row relates to a record in the [color:brown]Job_Lines table. Each job gets assigned to a [color:red]customer. Problem 1: The first column of the portal contain the [color:red]Product Name field (value list of all the product). Once selected, the rest of the line get populated as well. In column two however, two different values can be inserted: [color:red]Product Code or [color:red]SKU. [color:red]Product Code is taken from the [color:brown]Products table and [color:red]SKU comes from a [color:brown]CustomCodes table (Sometime a customer will prefer to have their own code inserted in the job rather than see ours. So a table that logs replacement codes (SKUs) for any single product for a given client is used). So upon choosing a [color:red]Product Name, I have FM lookup for a corresponding value in the [color:brown]CustomCodes table, if it is found it gets inserted, if not [color:red]Product Code is used instead. I thought I crossed that hurdle as is does input a corresponding [color:red]SKU to the current [color:red]Product Name, BUT it does not take the customer into account (it just seem to find the first corresponding value in the table which may or may not belong to that customer). Question is, how can I lookup a value based on two criteria? Problem #2: Is it possible to populate this portal using EITHER the [color:red]Product Name field or the [color:red]Product Code/SKU field? For this I believe I need to set calculated value for both these field but somehow any lookups returned nothing. I believe both these problem to be related to relationships and that is where I have problem figuring this all out. Any helps will be greatly appreciated. Michel

Works like a charm, thank you a whole bunch!

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