Jump to content

Relationship help needed


wolfcry911
 Share

This topic is 5607 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

  • Newbies

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5607 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.