Inventory Related Items

[ktolis]

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)

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 September 30, 2007 by Guest

[Ender]

Welcome,

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.

[ktolis]

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.

[ktolis]

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?

[Ender]

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

etc.

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.

[Ender]

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

etc.

[comment]

There is another way, using three tables:

Computers

AssetTypes

Assets

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

AssetTypes::TypeID = Assets::TypeID

AND

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.