Having trouble with relationships

[NewToFileMaker]

Hi everyone,

I'm a real newb when it comes to using Filemaker Pro Advanced 8 and I have a little problem.

I want to show a dropdown menu on some lay-outs which shows records from other tables.

The problem is that when I'm trying to use value lists I can only use 1 or 2 tables to show the records from.

So my problem is that I'm trying to create a field with a dropdown menu that shows records of several different tables.

I have one table called "inbetween" with all the nessecary values for this.

Those values are:

User Name

License Number

Computer Name

Serial Number

I have a lot of other tables who need to have a relationship to this one.

Here are the tables that I currently have

(I'm only showing the fields that need a relation to the "inbetween" table):

Server:

Serial Number

User Name

Server Name

Headphone:

User Name

Printer:

User Name

Webcam:

User Name

Desktop:

Serial Number

User Name

Desktop Name

Phone:

User Name

Laptop:

Serial Number

User Name

Laptop Name

Monitor:

User Name

Mouse:

User Name

Keyboard:

User Name

Speaker:

User Name

Microphone:

User Name

Scanner:

User Name

Serial Number:

Value

License:

Value

Applications:

Computer Name

License

Operating Systems:

Computer Name

License

Users:

User Name

So what I'm trying to do is:

Make sure that the field of table "Users" is related to the User Name in "inbetween"

Then I want to make a relation for all other tables with User Name and also relate them to "inbetween"

Now when I want to show the value User Name in a field it should show all names of the values from the table "Users" in a dropdownlist.

(Yes I know I can use value lists, but the problem is that I want to also do this for more then 2 tables...)

Now "Laptop Name" from "Laptop" should be related to "Computer Name" from "inbetween"

"Desktop Name" from "Desktop" should be related to "Computer Name" from "inbetween"

"Server Name" from "Server" should be related to "Computer Name" from "inbetween"

now I want the values from "Applications" "Computer Name"

and from "Operating Systems" "Computer Name" to be related to all these values. So I want to have a dropdown menu that shows:

All the names of all the laptops, all the names of all the desktops and all the names of all the servers in 1 field.

This also counts for License and Serial Numbers. I want the values of them to be stored in "inbetween" "license number" and "serial number"

so that I can use them in "Applications" "License", "Operating Systems" "License",

"Servers" "Serial Number", "Laptop" "Serial Number", "Desktops" "Serial Number".

So... I have no idea how I would make relations like these to do what I'm trying to do :S

Can someone give me an example on how to make these relationships? Or how to show them in dropdown menu's using more then 2 tables?

I'm having a lot of trouble figuring this out. Help is really appreciated.

Here is a screenshot of what I have (It's in dutch so it might look weird for you)

(It's really messed up right now :S)

the "inbetween" table or "tussen" table doesn't have any records and I want the records to be filled with the apropriate tables as I have explained above.

It doesn't work at all >.<!

[bcooney]

Filemaker's ease of use is both a blessing and a curse. The curse is that you can create a system that's completely wrong - quickly, and you have. Sorry.

If user's have items related to them, each item is a record (not a table) that is related to the parent user by UserID (not name!). You may wish to have an ItemType table, for more accurate reporting. Then, you'd specify an ItemType for each Item record.

Here's a great white paper to read My link. I'm afraid it's time for File>New.

hth,

Barbara

[NewToFileMaker]

Filemaker's ease of use is both a blessing and a curse. The curse is that you can create a system that's completely wrong - quickly, and you have. Sorry.

If user's have items related to them, each item is a record (not a table) that is related to the parent user by UserID (not name!). You may wish to have an ItemType table, for more accurate reporting. Then, you'd specify an ItemType for each Item record.

Here's a great white paper to read My link. I'm afraid it's time for File>New.

hth,

Barbara

ok, thanks

So... I want to create a database that has the following:

Each user may have one or more computers.

computers exists of servers, desktops and laptops.

Each computer may have one or more applications.

Each computer has one and only one operating system.

Each computer has one or more drivers.

Each computer may have one or more security programs.

Each user may have one or more peripheral devices.

peripheral devices exists of mouses, speakers, webcams, keyboards, monitors, microphones, headsets, phones, scanners and printers.

Each device has one serial number.

device exists of peripheral devices and computers.

Each software may have one license number.

software exists of operating systems, applications, security programs and drivers.

my question is: which tables would I need?

Would I also need to create tables for the category's? Like for example: Computers, Peripheral Devices, Device, Software?

Could you give me a small example?

[comment]

I would say that Users have Hardware, and hardware can have Software installed on it. Since the same software can be installed on more than one device, you should also have a join table of Installations.

Would I also need to create tables for the category's? Like for example: Computers, Peripheral Devices, Device, Software?

Strictly speaking, yes - but I believe in this case you could get away with a custom value list.

[NewToFileMaker]

I would say that Users have Hardware, and hardware can have Software installed on it. Since the same software can be installed on more than one device, you should also have a join table of Installations.

Strictly speaking, yes - but I believe in this case you could get away with a custom value list.

yes, but wouldn't that be strange? putting every piece of hardware and it's property's inside one table called "hardware"?

Or would I have to do this in a different way?

Wouldn't it be easyer to create a table for each piece of hardware and then create a table named hardware that's linked to the other pieces?

For example: I want to insert a record for a Desktop. The desktop has a motherboard, a graphics card, a network card etc...

Will I have to put every piece of hardware and it's property's in the hardware table? Because this will be one giant table then 0.o!

[comment]

Will I have to put every piece of hardware and it's property's in the hardware table?

Well, that depends on two things:

1. How diferent are the properties of each type of hardware?

2. What is the purpose of keeping all this data?

If the properties are wildly different, you might use a child table of Properties {Name; Value}.

[NewToFileMaker]

Well, that depends on two things:

1. How diferent are the properties of each type of hardware?

2. What is the purpose of keeping all this data?

If the properties are wildly different, you might use a child table of Properties {Name; Value}.

1. well.. the hardware specifications for example: a mouse, scanner, printer, monitor etc.. can be verry different then that of a desktop, laptop etc.

2. The database is supposed to be a program for a company to stock their inventory of computers, what programs are used on that computer, which user uses the computer etc..

These types of data are quite nessecary for a company. For example: when the company wants to know what hardware spacifications a computer has in their office they can simply look it up with the database. (I'm not actually gonna sell it to a company, it's just a practice I have to do and I'm getting kinda stuck on it :S)

So your saying I need to create a table called properties that is a child of the hardware table?

EDIT: I'm sorry for being such a noob >.>

[comment]

So your saying I need to create a table called properties that is a child of the hardware table?

I am only presenting some options; I cannot make the decision for you. Let me give you an example:

I presume each piece of hardware has a Manufacturer, Model and (often) a SerialNumber. So those would be fields in the Hardware table.

Now how about the resolution of a scanner? If you envision a need to find scanners with resolution ≥ 600 dpi, then you would need a dedicated field or record for this property. If it's just a matter of consulting the specifications of a device (e.g. when troubleshooting), then the entire specsheet could be held in a text field (or as a PDF in a container field).

[NewToFileMaker]

I am only presenting some options; I cannot make the decision for you. Let me give you an example:

I presume each piece of hardware has a Manufacturer, Model and (often) a SerialNumber. So those would be fields in the Hardware table.

Now how about the resolution of a scanner? If you envision a need to find scanners with resolution ≥ 600 dpi, then you would need a dedicated field or record for this property. If it's just a matter of consulting the specifications of a device (e.g. when troubleshooting), then the entire specsheet could be held in a text field (or as a PDF in a container field).

This is what troubles me. I have already created a lay-out for my database. Each device has it's own page where you can adjust the records of that device. Putting everything in a hardware table will make it hard for going through records. Imagine you can watch a laptop in the Server lay-out. :S

Is there a way to solve that? Maybe calculations in script maker?

I also created a list view for each device :S

[comment]

The same type of layout could be presented by finding hardware in Category = "Server" and showing it with a portal to child hardware (a self-join) and a portal to Properties.

Do not let layout considerations affect your data model.

[bcooney]

This is such a common need, that I couldn't help but Google. Have you looked at this?