Related tables with reused Product IDs

[andis8853]

Hi,

I have an order entry system that I need help with. One of our layouts is associated to an FM table named "Order Items". There is no script or query used in the current functionality.

When the user goes to the screen where they enter items for an order, all they need to do is type in the Product ID # then the name, price, etc are automatically filled in based on the relationship between the "Order Items" and the master "Products" tables by the Product ID #.

What's occuring is that some of our Product ID #'s are being reused after a product is retired and now there's a problem. Here is my quandry…

For example:

Product A with Prod ID #123 is retired

Product B is is a new product that has been assigned Prod ID #123

Both A & B need to stay in the master Products table for historical purposes, and each one has a unique Record ID.

But…now when a user enters Product ID on the item entry page Product A information is being filled in automatically.

I don't necessarily want Product B info to be filled in all the time, but I would like to have a way for the user to choose Product A or Product B that will allow the appropriate info to be populated.

I hope this makes sense.

Any and all assistance is greatly appreciated.

[Digital Life]

The relationship will always find the first occurrence of the Product Master. So it always brings in the info from the old record, not the new one. You can't assign the old number to a new product master.

[IdealData]

A relationship can be based on more than one field.

If you had a field in the product file that maintained the "active status" to be "yes" or "no" then you could use a global field in the data entry file and combine this with the "prod id" field and use the two fields for your relationship.

You can dynamically assign the global field to be "yes" or "no" depending on the relationship requirement at the time - thus you could determine active or inactive records too.

[andis8853]

Hi,

Thank you for the idea of using an additional relationship.

Unfortunately, this won't work in our case because a Product # may be re-used more than once. When product A retires and product B uses the same product #, it's not a problem. But when the time comes that product B retires, product C will re-use the number.

There will be times when I will need to have the order populate the information from the retired A or B product.

What I'm really hoping for is a way to have products A, B and C come up somehow and the user selects which one they want on the order.

So far from playing around I am able to make a portal on the Order Item screen, so all choices for the Product # come up.

I don't know if I'm heading in the right direction, but what I need now is a way to select the appropriate product and have the Order Item screen populated correctly.

Any additional assistance is appreciated.

Thanks!

[Genx]

So how is this structured...

Do you have multiple products for one product number, if that's the case do you have an "active" field, and have you looked at conditional valuelists to give you the option?

[comment]

a way to have products A, B and C come up somehow and the user selects which one they want on the order.

That shouldn't be too hard.

Note that you could also have another table for the ProductCodes, where you could manage the related Products in a portal.

SharedProductCodes.fp7.zip

[andis8853]

Thank you "comment" for helping me with this. The example you attached to your post was exactly what I was looking for.

I very much appreciate your assistance, especially for taking the time to create the example for me.

--Andrea :-)

[skearton]

This file is great! I need to know how to take it a step further...

How would you take the selected line item and add it along with others, so that the customer could see the sum of their purchases? Could this also be done on the same layout? Would you have to create another table? I want the user to go through the process of selection in a simple, logical way. Kind of like when you are looking for a part for your car online. The first field lets you select your car manufacturer, then the second field lets you select the year of the car. The third field then lets you select from the models available for that year. The fourth shows the styles available from the previous choice and so on.

[comment]

In the real implementation, there would be an Invoice table, and the InvoiceLineItems would be shown in a portal on the Invoice layout (note that the InvoiceID already exists in InvoiceLineItems).

The selection process is basically an example of what's known as a conditional value list - you can find plenty of other examples of this technique here, including cascading choices.