Hi all,

I'm using FMP6. I've got two databases. One ("A") consists of three
fields, one of which ("Foo") serves as a value list for a field ("Bar")
in the other ("B") database. Now, I want to add a field to database A that
serves as a reporting function. What this field would do is reflect how
many records in B have the field Bar containing the contents of this
record's Foo.

Or to explain it another way, I just want a field in B that shows how many
records in A use the value that's in B's Foo field. An example of how it
would look without the report fields:

Database A
Record 1, field Foo: "Blah"
Record 2, field Foo: "Crap"

Database B
Record 1, field Bar: "Blah" (<- via a Value List pointing at A::Foo)
Record 2, field Bar: "Blah" (<- via a Value List pointing at A::Foo)

Now, I want my report fields to make database A look like this:

Record 1, field Foo: "Blah", field CountInB: 2
Record 1, field Foo: "Crap", field CountInB: 0

But I don't know how to create that relationship. I don't mind if it's
not an auto-updating number; I could just create a script to loop through
the records when the report is needed. I've tried looking at self-joining
and other things, but I haven't yet figured out how to make it work.

I hope I've explained myself clearly enough... If I haven't please let me
know and I'll try again. I hope somebody here can help me.

Thanks!

Steve Freitas
Costa Mesa, California

Steve Freitas wrote:

I think I would do this : in second database, auto-enter number field
called 'Constant1'. Define as auto-enter data = 1, disallow changing. In
first database, set up relationship to second database, foo to bar. In
first database, create calculation number field = sum(foo\bar::Constant1)

When a record matches in second database Foo to first database Bar, its
Constant1 value will be added to the sum of all other matching records
and displayed on the appropriate record in the first database.

I hope that wasn't like the hardest way possible to do this, but its the
first thing that came to my mind. If others have alternatives, I would
enjoy hearing them.

Good luck with that, if that doesn't answer the question, please post a
follow-up.

Hi Kurt,

Thanks so much for your reply! I'm working on implementing it now. A few
questions, though. You mention to do this:

Hmm, from A my relation right now doesn't involve B::Constant1, it's from
A::Foo to B::Bar (or, as read from the define relationships window, "Foo =
::Bar, Related File is B.fp5"). And I'm confused by the use of the
backslash.

I tried doing field = Sum(B::Constant1), but that obviously doesn't give
me what I want. Hrm. I'll keep poking at it.

I don't quite get it, as foo is a text field, not a number, yet I'm trying
to divide it by 1? No, that's a backslash... So I guess I'm missing what
that bit's referring to.

Just as a reminder, my terms are defined as such:

Database "A"
Field "Foo"

Database "B"
Field "Bar"

Thanks again!

Steve

Hi Kurt,

I take back what I said! Sum(B::Constant1) worked perfectly! Thank you so
much for your guidance.

It does confuse me a little bit, though. Although I did set up the A::Foo
-> B::Bar relation, nowhere did I reference that in my field. How does it
know to use that relation in restricting that Sum statement?

And a follow-up question: What if I wanted to add an additional qualifier
to the Sum? Like, say, only Sum those records in B which were created in
the last 30 days?

Thanks!

Steve

Ah, okay, now I'm getting the hang of the syntax. I just misread your
earlier suggestion's Sum() statement.

I'm also starting to figure out how relationships work. I wish I could
cascade them... I'd like to do a relationship which first matches with all
the records of a certain date, then do another one which constrains the
result of the first relationship, then dump that into the Sum(). Anything
like that exist?

Thanks!

Steve

Ah, okay, I think I got it figured out. I replaced B::Constant1 with a
field which calculates whether that record is in the desired date range,
then coughs up a 1 or a 0 as a result. Then when A calls Sum(), it doesn't
have to worry about any date issues; it just adds up all those calculated
1s or 0s and arrives at the desired result.

Thanks again for all your help!

Steve

Steve Freitas wrote:
Yup, you caught me taking shortcuts and not being very clear at all. I
seem to have written my earlier post without any complete sentences, and
with some incomplete thoughts as well. My apologies, please see below.


Please bear with me while I attempt to correct myself. I am recommending
the creation of one new field in Database B, called 'Constant1'. It
should be a number field, with auto-enter option for data, specify a
value of 1, prohibit modification of value.

Create a new relationship in Database A, matching field "Foo" with
Database B, field "Bar".

Create a new field in Database A, a calculation number field called
"Count of Bars with this Foo". Define it to be the sum of Constant1
through the relationship of Foo to Bar. (My shorthand at my work is to
use matching field names with a backslash as the names of relationships)

Now you have a field in Database B that will always have a value of one
for every well-formed record. Summing that field through a relationship
gives you a count of related records. Setting up a relationship from
your source field 'Foo' to your value-list-populated field, 'Bar',
allows you to count the number of 'Bar''s that match your 'Foo', right?

I'm guessing that each record of Database A has a different value for
'Foo', and all values of 'Foo' are represented by a record in Database
A. If that is the case, then the calculated field "Count of Bars with
this Foo" can be placed on each record of Database A and give you a
report of Bars that have the same Foo-value.

The point of a Constant1 field is just to give you a 'known', something
you can hang your hat on. You always know it will be '1', so you can use
it to count, to test for the existence of a related record, that sort of
thing.

I hope that made more sense this time, I'm actually proof-reading it
before posting. Is that allowed on usenet, or have I violated some code
of conduct?

If that helped or if there are still questions unanswered, please post.

Good luck.

Steve Freitas wrote:

should have checked them before making my own reply. Say, do you sleep?
I thought you were in Pacific time zone, but you seem to be posting
round the clock.

Anyway, congrats on your advancements thus far. You're definitely on to
a sticky bit when you try to set up relationships that use dates.
Matching an exact date is easy enough, doing an inequality is not too
bad, but matching a start and end date range is nice and chewy. I have
to do it in a couple of relationships at my work, where we allow a user
to set a beginning and ending date and match transaction that lie between.

If you can get away with selecting your dates in the 'source' file and
just summing from the 'report' file, good. Do it.

Good luck.

Sleep? That's for sissies! Yeah, I'm in California, but hey, when a system
won't do what I want it to do, I have a difficult time letting go...

Sounds complicated. Hopefully I'll never have to attain your level of
expertise. :-)

Yep, it's working like a charm. I read your other post, thanks for
all the work on it. It did illuminate a couple of finer points for
me. Thank you again for your help!

Steve