Need help with portals

[nnez]

I am trying to create an inventory function where I have a database "products" that contains information on all items purchased including product number, description, vendor, etc.

I also have a database "items" that is the main listing of all items/equipment that are purchased. This is also where users would input all the serial numbers, product numbers, assignments, quantity, etc.

What I want is to be able to have either a portal or some other mechanism so that when someone is entering data on a new purchased item, once they enter the product number, it is looked up against a matching record entry in the products database. Then all the information for the product including description, vendor, etc is filled in automatically. So you see the products database must have unique entries based on the product number and if there is not a matching product, then a new entry is created in the products database using all the information entered.

I have been perplexed by what the best way to handle this situation could be. It could be a change of design, or use of portals that i'm not familiar with, or use of scripts.

Please help!

[danjacoby]

Not a portal, but either a calculation field or an auto-entry calculation. First set up a relationship based on Product Number, then change the other fields to calculations (or auto-entries) and see the rest of the fields fill in by themselves.

I'd also set the ProductNumber field to choose from a popup list of the contents of the ProductNumber field in the related file. If there's a new product number, it would then have to be entered in the related file first (create a button to open it and create a new record).

[Pupiweb]

You need a lookup

You've a Products.fp5 file, one record per product, uniquely identified by a ProductID field (usually a FM serial number)

In your LineItems file create a ProductID field and a relationship linking this field to ProductID in Products.fp5

Set the "detail" fields (like Product name, vendor, price ...) to "lookup" these data from the Products.fp5 file using the above mentioned relationship

[danjacoby]

Save yourself some headaches -- use an auto-calc rather than a lookup. Lookups are buggy.

Actually, if the subordinate data will never change, use calculation fields.

[Ugo DI LUCA]

Hi,

auto-calc for description (would not change), lookup for price (would change)

[Pupiweb]

Never seen a look up fail with no reason

The situation nnez describes is the classical situation in which you want to autoenter data but not have them updated constantly so a calc is out of question

I cannot see any advantage of autoenter vs lookups in this situation but ... FioleMaker is nice because it allows to obtain a result in several ways

[nnez]

What about being able to update the products.fp5 with new records if there are new products. Ideally, this would be done through the details.fp5 interface. I'm not sure if there is an easy way to check products.fp5 after a product number is entered to see if there is an existing record and autoenter descriptions, vendor, etc. If not, then somehow allow the user to enter information about the new product and add a record to the products.fp5 file.

[Pupiweb]

It can be done, in several ways, depending on personal preferences/taste ...

Usually the products are entered using a value list displaying ProductID and name .... if this is the case I'd add a <New Product> button bringing the user to the products file to create the new product record

As I said there are many ways to do it, the basic concept is that you need to create the product record before adding it to an order

[Ugo DI LUCA]

Hi,

Value list is the way to go rather than direct entry of Product_Id. I have some bad "souvenirs" of my first database with some bad codes being entered.

More, in the solution you propose, any error while entering a Product_Id would return to creation of new products !

Pupiweb,

I agree there is always a reason for bad behaviours of lookup fields. But there also are some cases where you want some info to be entered by calc to keep them updated.