Simple Value List???

[Fred Fisher]

I am slowly learning Filemaker having started making the transition from the PC world to the Mac world and from Access to Filemaker. My MacPro is my first Mac since my old Apple II!

I have a database that keeps a log of the computers and peripherals on our network. I have a table tblComputers that has a ComputerName field and a CurrentStatus field. I have a related table tblComputerLog that gets a value list from tblComputers using ComputerName. As it is now all of the computers are listed, active and inactive (Retired). I would like to have the value list in tblComputerLog create a value is of only the Active items.

I have tried many things but they all seem to end up in a dead end. I am sure my evil Access part of my brain is still influencing my thinking...

Any suggestions?

[mz123]

Create a calculation field called z_Active in tblComputers

The calculation is: "Active"

In your relationship that shows the dropdown, add a new line of logic:

Z_Active = CurrentStatus

Since z_Active always equals "Active" only "Active" records will be shown.

Hope that helps!

[Fred Fisher]

Martha,

Thanks for the info. I think I am doing something wrong. The tblComputer is the one to the many tblComputerLog. The field CurrentStatus is in tblComputer. If I put z_Active there as well I do not see a way to get it to work as you describe.

I am assuming that when you say "In your relationship that shows the dropdown" you mean open the relationships diagram and add it to there. What am I doing wrong?

Fred

[mr_vodka]

You can put z_Active as an unstored calc in tblComputerLog. Create a "new" relationship from tblComputerLog to a new table occurrence of tblComputer ( will put on your graph as tblComputer 2 ), keyed on z_Active to CurrentStatus. Now you can use this in your value list.

[mz123]

Ditto to Mr. Vodka.

But shouldnt it be a stored calculation? Relationships can't go off of an unstored calculation.... or so I thought.

[Fred Fisher]

I think I am almost there. My list still shows everything including "Retired" items.

I have a value list called ComputerActiveStatus. It is setup to use the values from tblComputer 2::ComputerName. The relationship between ComputerLog (left side of relationship) and tblComputer 2 (right side of relationship) is set to ComputerName=ComputerName and z_Active=CurrentStatus. z_Active is set to be a Auto-Enter Calculated Value set to = "Active".

Shouldn't the tblComputer 2 be on the left side of the relationship or does it not make any difference. If it supposed to be on the left side how do I put it there?

Fred

[mr_vodka]

Ditto to Mr. Vodka.

But shouldnt it be a stored calculation? Relationships can't go off of an unstored calculation.... or so I thought.

It can be stored by why store it in this case... Relationships with an unstored calc on the child side of the relationship can not be unstored. In this case, since the CurrentStatus field is stored and on the child side, we are okay and Active can be unstored.

[Fred Fisher]

Through trial and error and the leads that were provided here I came up with a very nice working solution.

I created a calculated field in tblComputers, set it to Auto Enter. The calculation was Case(CurrentStatus="Active";ComputerName).

Then I had to go to each record and change the CurrentStatus field from Active to Retired and back to active. This set all of the records where CurrentStatus was Active to the ComputerName. The fields where it did not equal active were set to be blank.

I then created a value list named ComputerActive. I set it to use values from tblComputer and the field cal_Active. I then went to the ComputerName field on the ComputerLog layout and setup a drop down, Auto complete to the Valus list ComputerActive.

Now in the computer log it will still display the records for "retired" computers but when you open a new or edit a record the drop down only lists the "Active" computers to select from.

Thanks everyone for the point in the right direction. :yay:

[comment]

I had to go to each record and change the CurrentStatus field from Active to Retired and back to active.

You could have avoided this by making the field a true calculation field instead of auto-enter. See, for example:

http://www.fmforums.com/forum/showtopic.php?tid/178921/post/215590/#215590

http://www.fmforums.com/forum/showtopic.php?tid/176320/post/203863/#203863