Newbies nnez Posted March 28, 2003 Newbies Share Posted March 28, 2003 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! Link to comment Share on other sites More sharing options...
danjacoby Posted March 28, 2003 Share Posted March 28, 2003 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). Link to comment Share on other sites More sharing options...
Pupiweb Posted March 30, 2003 Share Posted March 30, 2003 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 Link to comment Share on other sites More sharing options...
danjacoby Posted March 30, 2003 Share Posted March 30, 2003 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. Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted March 30, 2003 Share Posted March 30, 2003 Hi, auto-calc for description (would not change), lookup for price (would change) Link to comment Share on other sites More sharing options...
Pupiweb Posted March 31, 2003 Share Posted March 31, 2003 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 Link to comment Share on other sites More sharing options...
Newbies nnez Posted March 31, 2003 Author Newbies Share Posted March 31, 2003 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. Link to comment Share on other sites More sharing options...
Pupiweb Posted April 1, 2003 Share Posted April 1, 2003 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 Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted April 1, 2003 Share Posted April 1, 2003 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7859 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now