(Using FM 8.5 on a Mac)

I'm going crazy trying to figure this out, and I've tried searching
the group but can't find exactly what I'm looking for. Here's what I
have, with (I hope) unnecessary details omitted. A table with a
"propertyID", "jobID", "Date" and "Status" field which gets calculated
based on data from a related table. This table joins two other tables,
so propertyID is not unique and jobID is not unique. But no record
will contain the same propertyID and jobID (somewhat similar to an
InvoiceItems table).

I would like to add a portal to the layout which will show all records
which have the same propertyID AND are Active. Creating the
relationship (self join) between the propertyID field and itself shows
me all records with the propertyID, as expected. I tried adding a
second criteria to the relationship, joining the Active field (which
calculates to either 0 or 1) with a global calculated field (which
always calculates to 1). This doesn't work. I have a record where
Active is 0 and the global CONSTANT_TRUE is 1, and it still shows up
in the portal.

So, I decided to do a much simpler test. I built the relationship on
just the CONSTANT_TRUE field, and joined it to itself. As expected
(kind of, at this point), it showed all records in the portal. But
when I changed the relationship to NOT EQUAL, it still showed every
record. I expected it to show none.

There is clearly something fundamental about relationships that I
don't understand, maybe having to do with global fields or calculated
values. Any enlightenment would be greatly appreciated, as well as
better strategies for achieving what I'm trying to do.

Thanks,
Anthony

So, after much trial and error, I have figured out what everyone else
somehow probably knows - relationships can't be based on unstored
calculation fields. I've gotten my portal to work by using the
Lookup() function to pull in the data I need to calculate whether the
record is "active", allowing me to store the result. Unfortunately,
this fails as soon as that the looked up value changes.

A search on this topic seems to indicate that I've hit a limitation of
Filemaker in this respect. There are suggestions to run scripts
periodically to make sure the stored data is current. Anyone know if
the latest version of FM (8.5) might have any new features that may
help me get around this?

Thanks,
Anthony

On Mar 15, 4:35 pm, acuc... (AT) gmail (DOT) com wrote:

<acuccia (AT) gmail (DOT) com> schreef in bericht
news:1174030141.122713.144300 (AT) e1g2000hsg (DOT) googlegroups.com...
One way is to use a plug-in that triggers a script (such as the free
EventScript from S4HU) whenever the contents of the field changes. You can
then also just use normal fields and the SetField instead of lookup.

Keep well, Ursus

On Mar 16, 6:54 am, "Ursus" <ursus.k... (AT) wanadoo (DOT) nl> wrote:
I ran into a similar problem, trying to base a relationship on an
unstored calculation field, and I was basically told that it wasn't
possible. But then I discovered the solution to all my problems: the
Refresh Window script step, with the Flush Cached Join Results option
checked.

I currently have a system running at my company where value lists have
to change dynamically based on an unstored calculated match field. I
set it up so that each field is a button the executes a two step
script: Flush Cached Join Results via Refresh Window, and then Go To
the field in question. Works perfectly.

Nate

On Mar 16, 3:54 am, "Ursus" <ursus.k... (AT) wanadoo (DOT) nl> wrote:
Thanks. I actually tried that last night, but ran into a problem. The
field I would need to run the script from is a popup menu. EventScript
doesn't trigger the script until the field is exited (user clicks
outside of field, tabs to another field, goes to another record,
etc.). Unfortunately, a user has no way to know that. They change the
field, it displays the new item, they go out to lunch, script doesn't
run, everyone else is looking at old data. Also, the script that needs
to run accesses data from the portal on the current record. If the
user changes the field, then goes to another record, the script gets
triggered AFTER going to the next record, which means it calculates
the results based on the wrong data. Same thing if the user changes
the field then closes the file - it doesn't trigger the script while
the record is still active.

I do use EventScript for another (unrelated) part of my solution and
am very happy with it, it just doesn't work in this case.

Thanks again.
-Anthony