Hello,

I've been using Filemaker for only a few months now and I've run up against a snag on my database. I work in a chemistry lab and I need to keep track of our inventory. The way my database is set up is with 3 main tables: Chemical Info, Ordering Info, and Inventory Info.

I have linked these three tables by a common field "Inventory#". I've set up a layout with tabs and portals so that you can view all of the information about a certain chemical on one layout.

My problem comes with adding new chemicals to the list. Usually one would enter a new chemical into the database from the table "Chemical Info." This assigns the chemical a serialized "Inventory#". Then I have to go to the "Ordering Info" table and create a new record and manually enter in the "Inventory#" and the chemical name. Then I have to do this again for the Inventory Info table.

Does anyone know how to set up the database so that when someone enters a new chemical in the Chemical Info table that it will automatically make a new record with the assigned Inventory# and name of the chemical in the other two tables?

Thanks so much

js22

Then I have to go to the "Ordering Info" table and create a new record and manually enter in the "Inventory#" and the chemical name.

No only the Inventory#, the other info is brought in by the linking ...it ought only to be stored in one unique spot.

However does this thread provide most of the answers:

http://fmforums.com/forum/showtopic.php?tid/177621/post/209611/hl//

--sd

Thanks for your quick reply. The thread helps with what I am going to do with the information eventually.

I see your point for entering the chemical name only once and I'll make those changes.

I'm still wondering if there is a way to set up a way to automate it so that I wouldn't have to manually go through and setup the Inventory# in the other two tables.

Thanks

js22

This is because chemical_info is part of the data stored in the inventory table, while exits and arrivals both are ordering info.

But a quick search shows me you've been there already:

http://fmforums.com/forum/showtopic.php?tid/182106/post/229143/hl/transactions/

Usually one would enter a new chemical into the database from the table "Chemical Info.

Wrong place, it should be done with the transactions entry, new items without ID's should utilize the "Allow creation of related..." feature found in the relations def. dialog. Examine this old ver. 3 template:

http://www.filemakerpros.com/Related_Match_Field.zip

Regard these tips as matters to investigate ...because I have too little to go on to give precise answers. I can't get why you have 3 tables for this ...before I have more info or a template to dissect?

--sd

Soren,

Thanks so much for your patience and help. My problem was that I had allow creation of records in this table checked on both ends of the relationship. By unchecking the side with chemical inventory when I start entering data in it autofills in the inventory number. Before it would just create a new inventory number.

This is a template that I threw together. Would you take a look and tell me if there is an easier way to do this. I'm new at this and portals are still a little tricky for me.

thanks

js22

Chem_Template.fp7.zip

Alright the way you have placed the various fields in their respective tables, kind of making it a little difficult - frankly ought vendor on one side and catalog#/price be in two different tables, since the vendor as shown in your data supplies various items - but then instead could the many2many functionality be made by cutting the TO into two TOG's with a selfjoin and a global field and making the validation take care of matching vendors for the linking purposes.

The portal is then cut up in two, according to aspect. I have switched the order the data is entered, since the line in portal is the same line in the next tab, but since we here are dealing with at one2one relation the last records creation dependent on the creation in the first tab - I think it's a logical flow seen from the users POW.

The next TOG and layout is the many2many in the opposite direction, listing the purchases made at each vendor selected by the global field.

Now strict normalization would have lead to other table names, and other locations where the fields would reside ...but as often is it compulsory to follow the layouting as given...

--sd

Chem_TempMod.zip

Edited December 23, 200619 yr by Guest
added a few changes