Jump to content

Inventory Related Items


This topic is 6103 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

1st post so be gentle :

OK here we go.

I have 2 tables.

a) Assets

: Computers

The concept is that all hardware peripherals and internal computer components end up in the table Assets. A Computer record has an id identifying the computer and multiple fields as

a) cpu

:( motherboard

c) HD Drive

d) RAM slot 1

e) RAM slot 2

f) Printer attached

The Assets table has data like:

a) type (like motherboard, ram, HD, GFX card etc)

:P title (like "ASUS A8NSLI")

c) serial (the serial of the specific part available in the inventory)

All Computer fields are foreign keys from the Assets table.

I tried making a layout form that would allow a user to add a new Computer record and displaying 6 drop down menus each fetching only the records from Assets of the specific type required but at no help. I am using value lists as drop down menus but I seem to not be able to constrict the values to a specific type.

Building a form based on mysql would be quite simple using multiple queries (each for every foreign key) like:

"select * from Assets where type = 'motherboard'"

and using the data I need.

I haven't managed in succeeding in this simple task on FMPA and it really frustrates me.


Edited by Guest
Link to comment
Share on other sites


Each value list will need a different relationship to the Assets table, where each is filtered by a different criteria. A good way to do this is to use a bunch of globally stored text fields as the parent keys in Computer (gTypeCPU, gTypeMotherboard, etc.) where each relationship matches the global in the Computer table to the Type field in Asset. Then populate each respective global field with the right Type. The value lists will then use each different relationship, showing related values only.

Link to comment
Share on other sites

  • Newbies

I was afraid of such an answer. This was the only possible modeling idea I could come up with but I was thinking if there is possibly another solution.

If the categories are solidified then this is a viable solution. But if the categories aren't and they are typed as-we-work with the db then it's not a viable solution. I think this is a shortcoming from FM's side.

Unless someone else has another possible solution.

Link to comment
Share on other sites

  • Newbies

According to the reply i got here and the self-join help from FM's guide I got the following setup.


The Peripheral Types Table consists of a list like:

Processor, Motherboard, RAM, VGA Card, Sound Card etc etc..


The Peripheral Table consist of the list of peripheral devices that are available like:

Western Digital ATA HD 80Gb, Western Digital ATA HD 120Gb, Western Digital ATA HD 160Gb, ViewSonic 17" TFT VGA Monitor, ViewSonic 17" TFT VGA Monitor , Pentium III, Pentium IV, Pentium M etc..


The Computers Table consists of multiple entries from the Peripherals table each should be from the correct type. e.g. a Monitor, or a Hard Disk or a Processor


What I did was create another (global) field at the Computers table which holds the value of the relevant (and correct) Peripheral type id. e.g. gMotherboardType is 1, gCPUType is 2 etc..

The Computers table has a relations with the Peripherals table, one for each type of Peripheral as seen on the right hand. All tables as viewed on the image on the right are instances of the Peripherals table and the relation is:

Computers Motherboards

Motherboard x id AND

gMotherboardType = Type

I have also created another relation as a self-join as seen with the 2nd column of table instances on the left side which are all instances of the Computers table

So I created value lists (one for every peripheral type). For example the Motherboard VL uses values from the Motherboards table (id and Title to be specific, using id and displaying Title only). It uses only related values from gMotherboardTypeRel table (the self-join of Computers table).

I created then a Layout (L1) with drop down menus for each type on the computers table.

If no records exis then when I create a new record displayed on all VL.

If I create a record (by hand from another layout (L2) without drop down menus) and then go back to L1 then I get the correct VL I expected.

Why don't I get values at the VL's in L1? How can I get them without having a prototype record in the start?


Link to comment
Share on other sites

Looks about right on the right side. Those relationships can then be used for looking at the chosen component's details in the Peripherals table. But the value lists' relationships don't look right (you can't really use a relationship that has globals on both sides). It should something like:

Computer <=> Motherboard_VL =

Computer::gMotherboardType = Motherboard_VL::Type

Computer <=> CPUType_VL =

Computer::gCPUType = CPUTType_VL::Type


That way it shows ALL peripherals of the matching type in each value list.

Once you get that working, you might also consider adding another criteria to each VL relationship to only show the Active peripherals of each type. This way you could keep the records of the old deactivated peripherals in the system, but only look at the active peripherals when you assign them to Computers.

Link to comment
Share on other sites

Looks about right on the right side.

Actually, if the ID in the Peripherals table is a unique ID, like an auto-enter serial number, you won't need the global to Type criteria in the relationships. You can simply go from the ID in the respective componentID field to the ID, like:

Computer <=> Motherboard =

Computer::MotherboardID = Motherboard::ID

Computer <=> CPU =

Computer::CPUID = CPU::ID


Link to comment
Share on other sites

There is another way, using three tables:




You relate Computers to AssetTypes with an x relationship, and AssetTypes to Assets as:

AssetTypes::TypeID = Assets::TypeID


AssetTypes::gComputerID = Assets::ComputerID

The disadvantage of this you cannot just browse records in the Computers table - you need to match the gComputerID field in AssetTypes to the currently viewed computer.

Link to comment
Share on other sites

This topic is 6103 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.