@ problems with portal

[Publi-Script]

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

[bcooney]

Job_Lines needs the customerID from the Job, so that you can relate to CustomCodes by CustomerID and ProductID to lookup the SKU. I am assuming that CustomCodes sits between Customers and Products and stores both these ids.

Don't relate Job_Lines to products by name. Use ProductIDs. You can create a value list that includes both the ProductID and Product Name, but store the ProductID in the Job_Lines table.

[Publi-Script]

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

Edited November 2, 2008 by Guest

[bcooney]

Format the ProductID to a popup menu and assign it the value list of Products. This value list consists of ProductID and ProductName, but only shows the second field.

The SKU field in JobLines is a text field with the entry option of Lookup. It looks across the relationship you have to CustomCodes and brings back the SKU value.

You can set ProductID to a lookup, as well. Create another relationship to CustomCodes by matching on CustomerID and SKU.

[Publi-Script]

Format the ProductID to a popup menu and assign it the value list of Products. This value list consists of ProductID and ProductName, but only shows the second field.

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?

The SKU field in JobLines is a text field with the entry option of Lookup. It looks across the relationship you have to CustomCodes and brings back the SKU value.

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.

You can set ProductID to a lookup, as well. Create another relationship to CustomCodes by matching on CustomerID and SKU.

Can't seem to be able to make that work either...

Am I doing something wrong?