Have problem with a one of my relationships in my database.

Have table for general computer information and one these fields is called “Computer Type” and is validated by using a value list with 3 options desktop, laptop and server. Below is the two tables and I have cut out most of fields.

Computer Information Table

ComID (Unquie Primary Key)

ComName (Unquie)

ComputerType

Etc

Server Maintence Table

ComName

TicketNo (Unquie)

Etc

The relationship is ComName on both tables and is one to many. What I am trying to do is only create records in the “Server Maintence Table” pulling out the information from the “Computer Information Table” which have the “Computer Type” field set to “Server”. So the user on a layout which displays records from the “Server Manitence Table” . Select’s ComName field and have drop down list only showing the records of the servers in the Computer Information Table.

From what I understand this can be done by a calculation which I have not got to far with. If anybody could shine some light on where I am going wrong or point me in the right direction that would be great help.

Thanks

Steve

If I'm "hearing" you correctly,

Creat a calc field in SERVICE MAINT TABLE called "CompNameService" with the calculation

CompName & "Server" (check result as text)

so that it results with the field CompName and the word "Server" right after it

Example result: "G5TowerServer"

Then create a calc field in the COMPUTER INFORMATION TABLE called CompNameComputerType with the calcultion

CompName & ComputerType. Exampel Result: "G5TowerServer"

Create a relationship (e.g. CompNameType) between those two tables and those two fields and you should be view only those computers that are servers in your drop down list.

Also you can do that same for any other tables (desktop, laptop, etc) to show only those items

Thanks Jeff for your help with this matter. You have hit the nail on the head what I am trying to do. I know I could do this by simply using a value list but I would have edit the list everytime I add a server to the network. I would perfer a dynamic way.

I have tried what you have suggested but I unable make this work.

I am pretty sure that I am doing something wrong and still not got my head around how FileMaker working.

If could please give my sample database once over and maybe point me in the right direction what I am doing wrong.

Seems the calculations are working but unable us value list pull out the correct information using the ComName field (Server Manitnece Table)

Many Thanks

Steve

Server.fp7.zip

Try this on for size.

I adjusted the calcs (from my earlier post) and added another relationship. I also played around by adding a couple of lookups to your original, as well as, added a computer type field to the server list. This will allow you to have a Maintenance table rather than a "Server maintenance table" and a "Laptop Maintenance table", etc., shoud you desire.

Sorry, but I probably won't be able to help anymore as I'm job hunting.

Hopefully someone else will come along and finish what I started or do a better job

Enjoy!

Server02.fp7.zip

Edited August 17, 200619 yr by Guest

Thanks alot Jeff thats solution ideal and thanks for your help. I will now use this solution in my main database. Good luck with your job hunting.

Many Thanks

Steve

Edited August 17, 200619 yr by Guest

The relationship is ComName on both tables and is one to many. What I am trying to do is only create records in the “Server Maintence Table” pulling out the information from the “Computer Information Table” which have the “Computer Type” field set to “Server”. So the user on a layout which displays records from the “Server Manitence Table” . Select’s ComName field and have drop down list only showing the records of the servers in the Computer Information Table.

Steve--

I'll start by saying I haven't looked at your files, or Jeff's modifications. However, for starters, I don't see why you use the ComName field as your relationship when you have a Primary Key COMID field that could serve you more reliably.

As for your select list, I would prefer one of the following:

1) create a global field in the Server Maintenance table that you can change (using a dropdown based on the comtype valuelist) that will control which com records you view, or

2) create a stored global calculation in the Server Maint table with the calculated value of "Server"

and in either case build a simple relationship between the comtype field and this field. This table occurrence can then be used to create a dynamic value list of all computers with that comtype. Use option 1 if you want to give users the ability (ever) to select computers of the other types; use option 2 if you're only ever going to select the servers. [Hint: you're probably going to need the first option somewhere down the line].

David