Relationship help needed

[wolfcry911]

Hi there. I'm pretty much a newbie with Filemaker. I've done a little work with it here and there, but all flat database types until now. I've been trying to learn how to develop relational dbs correctly, but I haven't wrapped my head around it quite yet. I've read a couple of books, and visit forums, and subscribed to Filemaker magazine, but I'm still not there. I need more hands on work. So I'm trying to develop a small db for experience.

It's purpose is to keep track of the computer systems at work - their specs, location, etc. for a small company (about 10 systems). I've created two tables, a computer specs table and workstation table. The specs is just that, the brand, processor, memory, storage, optical, etc.; and the workstations give building, floor, room, and location. I've set a unique primary key in each table, and a unique (to make it a one to one relation) foreign key in workstation TO. This is because there is only one computer per workstation (even if a second computer were to be used in a workstation, a new workstation would be created). All is working fine thus far - unless I have a design flaw I don't know about.

Now what I'd like to do is to create a portal in the workstation TO layout that shows computers that are not in use, and further, be able to select a computer if either a) the workstation has no computer, or if the workstation will be getting a different computer. Now I have a calculated field (in_use) in computer_specs that returns 0 if it's not currently related to a record in workstations, and 1 otherwise. Is this the right step in reaching my goal? How can I use this field to display unused computers in a portal? Or am I totally missing the boat here?

Once I have accomplished the display of available computers, how can I select one from the list and assign it to the current workstation?

Thanks for any insight,

Chris

[Fenton]

I wrote a nice long answer explaining how to do this. Then, while trying to attach the file, Safari crashed; a rare occurrence. But I'm to lazy to write again. Study the file. It works.

ComputersInUse.fp7.zip

[wolfcry911]

Fenton, Thank-you!

I'll have some questions, but I'll be studying this for awhile first.

[Fenton]

Basically you have the Computers being used. They are the ComputerIDs in the Workstations table. You need a mechanism to collect them all. A value list, with the all values option, is all the values for the specified field in the specified table.

But we need those values in a calculation field. Because we want to use it in a relationship. That is what ValueListItems (Get(FileName), "ComputersUsed") does, returning them all as return-separated lines. It must be an unstored calculation field, to be dynamic. Which is fine, as it is going to be the originating side of a relationship.

A multi-line field in a relationship can match ANY line to a matching record in the target table.

If you used it with a normal "=" operator in a relationship to Computers, you'd get the computers used in Workstations, in Computers. But you want the opposite. FileMaker 7/8 lets you use "≠" not equals as an operator. So that gives you the Computers not yet used in WS. Since it is a relationship it can be used to produce a portal.

The Refresh [x] Flush cache join results keeps the display updated properly.

Edited July 30, 2006 by Guest

[wolfcry911]

Hi Fenton, thanks again for the file and explanation. I'd like to think I wasn't that far off - I had tried to work with valuelists but was missing the ValueListItems function. I do have a couple of questions though.

What is the refresh button for? Doesn't everything refresh when making a selection anyway?

Is the valuelist "ComputersNotUsed" actually used (as the db is now)? I can't seem to find a reference to it.

Could a calculated field be used in one to many relationship with a global? The global being the one and the calculation the many?

[Fenton]

No, everything doesn't refresh, if you just put something in the field (or take it out). The portal button runs Refresh also. In real life, you would ONLY make a selection via the portal; you would not allow entry into the ComputerID field. I left it open so you could see how it doesn't refresh without that script step.

ComputersNotUsed is the value list being used for the drop-down in ComputerID. But it doesn't refresh worth crap either. So, yes, it's a pretty useless VL. Delete it and make that field non-enterable for your solution (in Browse mode).

Basically it's best to use a scripted solution, because of the less than ideal refresh capabilities when you use ValueListItems on a related value list.

[wolfcry911]

Thanks for your time and input, Fenton. I now see the valuelist used in the computerID dropdown - and the reason for the refresh button.