I've got my relationships figured out, and I've got some tables set up,
but I can't figure out how to make things work together.

Filemaker Pro 7.0

Here are my tables:
Items
Projects
ItemsProjects (Join Table)

Any item can occur in one or more projects. Each project has at least
several items.

At any given time, there will be one or more ActiveProjects, but only
one CurrentProject (the one the user is working on).

I would like the user to be able to add an item to a project.

I've got fields like ProjectsSN and ItemsSN as unique serial numbers in
those Tables. I made ItemsProjects with both those as foreign key
fields. I made a global field (g_currentProject) to hold the current
project's ProjectSN.

Should I script a button to make an ItemsProjects record with both the
ItemSN and the ProjectSN? That seems easy enough. But how can I prevent
the user from adding the Item more than once? My items are unique, so
they can only be used once for any given project, but they might be used
on a different project in the future.

Also, I'm trying to populate a value list with active projects that I
can use to fill in the g_currentProject field. I made a new table
occurance of Projects, called ActiveProjects. Projects has a checkbox
field (ActiveProject) with only one value "Active." I put a constant
global field on the Items table called g_k_active. I put "Active" into
that field. I tried to make a join between ActiveProjects and Items
where ActiveProject = g_k_active, but when I make a portal of that
table, it's filled with all the items, not just the ones with the
checkbox checked.

Thanks for any tips.

Bob

In article <h4u9f.25666$NJ.9638 (AT) bignews7 (DOT) bellsouth.net>, not (AT) not (DOT) not
says...
Simply don't let users create an itemprojects record that already
exists... have your script check for it first.

It sounds like you've got the right general idea here. Context becomes
important though. What table is the layout the portal is on set to? What
is the path through the relationship graph to the ActiveProjects?

(Also, make sure the fields inside the ActiveProjects portal point to
ActiveProjects, not some other table occurrence.)

42 wrote:
That makes sense! Is the best way to do that with a find command? Just
find on ItemSN and ProjectSN and if I find a record like that pop up a
dialog? That sounds easy.

I wasn't having luck with the portal, so I tried to step back and just
get the value list to work. If the value list came up empty it would
make more sense than showing everything -- I think I must have made some
really basic mistake that I'm overlooking.

I made a layout of Projects, and every other record has the Active
checkbox checked. But when I switch to the ActiveProjectsLayout (showing
records from ActiveProjects), all my Project records are shown, not just
the ones that have Active checked. On my relationship graph,
ActiveProjects is related to Items with Active = g_k_active, and I typed
Active into that field. If I then make a portal on my Items layout,
showing records from ActiveProjects, it comes up empty (that's on the
same layout where the value list has all the projects...)

Bob

In article <O0v9f.25681$NJ.8564 (AT) bignews7 (DOT) bellsouth.net>, not (AT) not (DOT) not
says...
Find works, or you can define a relationship and see if a related record
exists.

Hmmmm. Yes. It sounds like you're a little confused with the differences
between relationships and table occurences and how you must interact
with them to get the found set you want.

Any layout defined on *projects* will show *all* the project records, or
any arbitrary found set of them.

The key is to use the relationship to set your found set of projects.

e.g. from the layout displaying the table that has the global "Active"
field, a goto related records(activeprojects) to a layout displaying the
projects table (via any of its table occurences) will create the
appropriate found set in the destination layout.

(Make sure the 'show only related records' is "checked".)

----------

That's how to view a related record set within the layouts themselves.

The next step is portals: which allows you bring the related found set
into a portal, with the related records displayed in a list.

A portal is created on a layout set to the Parent Table Occurence, and
"portals" to the child table occurence, through the defiend
relationships.

To setup a portal, you would create it on a layout set to the parent TO
of the activerecords relationship, pointing at the child records of that
relationship.

Hope this helps get you pointed the right way. Its hard to explain
without pictures But its not that complicated once you "get" it.

In article <h4u9f.25666$NJ.9638 (AT) bignews7 (DOT) bellsouth.net>,
bob <not (AT) not (DOT) not> wrote:

To create new itmesProject records you can use a portal in Projects. The
appropriate Item records have to exist in the Items table.

Your setup as i understand it:

Project :: ItemsProject :: Items

With ItemsProject related to Project by ProjectSN
and ItemsProject related to Items by Item SN.

Allow creation of ItemsProject by way of the relationship to Project.

Put a portal in the Projects layout to show records from ItemsProject.

In the portal put fields for ItemsProject::kfItemSN, Items::ItemName,
and such other fields as you want.

Note that the ItemName field is from the Items table, NOT from the
ItemsProject Table. You can do this in FM 7/8.

Make a value list for Items based on the Item field Items::kpItemSN and
also showing the Item field Items::ItemName. Sort by the the second
field Items::ItemName (in FileMaker 8, you can show only the second
field). Call this value list Items

Format the portal field ItemsProject::kfItemSN to show the value list
Items ad a drop-down list or menu.

Now when you go to the last portal record and click in the field
ItemsProject::kfItemSN, you will get the value list. Select the desired
item. That will create a new record in the table ItemsProject, that will
have ProjectSN in the kfProjectSN field, and ItemSN in the kfItemSN
field.

Done.

This will made easier if you adopt a suitable naming convention for the
items, that will make them easy to find in a pop-up list, that will be
sorted alphabetically, which the value list will be. No idea what kind
of items you are dealing with, but you need to use names that will make
them easy to find in an alphabetical list.

Bill

--
For email, remove invalid.

42 wrote:

Yes, it helps. I also have the book "Special Edition Using FileMaker 7"
and it helps too. It even has pictures

It turns out I actually had my ideas fairly well sorted out, but I had
my field types different, so they just didn't work. Now on to the next task!

Thanks.

Bob

Bill wrote:
Hi Bill,

Thanks for explaining that. Especially the bit about using records from
a third table in a portal!

Things are working much better now, especially since I got all my data
types consistent!

In my case, I think it makes more sense to create ItemProjects from
Items, instead of Projects, but your instructions work for that too.

Thanks again

Bob