Relations without concocted fields

[ESpringer]

I remember when we needed a dummy field with fixed auto-enter value of 1 for universal relations across tables, and I'm so happy we don't need that but... I'm feeling driven to do something similar, and hope I don't have to.

Context: I'm volunteering a solution for a nonprofit preschool . Where their old flat-file solution had made mothers, fathers, doctors (and their contact info etc) into "properties" of children, I'm happily building a table of adults with join-table relations to kids -- parents of one may be emergency contacts of another, etc. Nice to have adult phone number changes reverberate through multiple roles, etc. (They're so impressed. )

Problem: There are lots of places where I need to make a calc or layout pull up "this kid's doctor" or "how many emergency contacts for this kid" data. Filtered portals are great on the fly, but (1) school is licensed for v10, not v11 (& I'm too amateur to be confident with baking them into a runtime yet); and (2) calcs (and merged fields, etc.) will still pull on the first sorted but unfiltered adult found. Right?

I'm staring at the relationship graph, pulling up the relation definition between kid and "ThisKidsDoctor" join-TO, and WISHING I could just specify role-specific relations based on matching: Kid#=@Kid# AND "doctor"=Role. In other words, I want to build something one-sidedly filter-like into the relation itself.

Instead, I resort to either:

(1) concocting global fields in the kid table to help anchor each kind of role-relation (could I stack them into repeated fields? probably not for merge purposes, right?): one for the text "doctor", another global field to house "dentist" string, another for "emergency", another for "pickup".

(2) concocting a boolean calc field that flags this and that relation (isDoctor?) and making a global "1" field in the kids table that can connect with this and that join-path as necessary. (But then the direct path to *create* records in the join table via that relation is blocked...)

I really *love* having my fields reserved for... um... data. Is there an elegant solution I'm missing? Or suck it up? But which way?

[bcooney]

I tend to create tables for such things as Roles. I'd model a school solution with one table for People, Roles, Contacts (join btw peo containing a roleID). People would have Addresses (with AddressTypeID). Each Address would relate to a COMM record (with Phone, Email, Mobile, Fax). I'd also mark an Addr preferred (and store that ID in People).

btw, I still have zz_Constant1_c in all my tables. I use it to filter a relationship if I need to. For example, to make it a bit more clear which contact is a Doctor, I'd have a calc in Contacts, flag_IsDoctor_c, which equals one if the contact record has the RoleID for Doctor. Then, to show the person's doctor the relationship I'd build would be Peo::PeoID = Contact::PeoID and

zz_constant1 = flag_IsDoctor

[ESpringer]

Thanks, bcooney. It sounds like you're suggesting the second of my two approaches. Interesting that you're still making a field for that constant "1" anchor... It seems FM could have easily built us a variation on the universal join (is there a technical name for that symbol?) that just makes Boolean connections (any record to positive numeric values, positive to positive, positive to any).

And otherwise I'm doing pretty much what you recommend, although I opted for putting address and phone right into adult records with repeating fields. It's not structurally ideal, but I also may need to pass this database on to even more novice FileMaker volunteers at some point, and I'd rather not have their eyes glaze over more than necessary... :)

[ESpringer]

Actually, I'm confused, bcooney -- it seems you suggest NOT having a join table for roles, but keeping roles in each person's records.

But then for multiple-role people (of which I have lots -- up to six or eight for some people) do you use a repeating field, or return-delimited values, or what?

More specifically, I need to be able to track that Adult 1 is Kid 2's grandmother with alternate pickup authorization AND that that same Adult is doctor to Kids 3 and 4, as well as first emergency contact for Kid 3. Because roles each index to a kid (not just to the whole school like "principal" or "school nurse" would), it seems to me that a join table is structurally better. No?

For example, I need to be able to delete roles readily without deleting the adult data behind them. Surely that's much more efficiently done by deleting a join table record than by scripted surgery on a multi-value field in the adult record...?

[comment]

concocting global fields in the kid table to help anchor each kind of role-relation

The question is how many of those do you want to view at the same time.

If it's only one, you can use a single relationship filtered by a global and set the global to the relevant role according to the selected view (in tabs, for example).

If many, you could use a portal to the Roles table, and set a global field there to the currently viewed kid's ID.

[bcooney]

I do suggest a join table "Contacts." As I said, its a join btw people and roles.

However, I use a table for Roles and relate by RoleID, not any text values.

[bruceR]

Instead, I resort to either:

(1) concocting global fields in the kid table...

(2) concocting a boolean calc field that flags...

Concocting? I don't think I've ever heard this word used in a FileMaker context and it isn't clear what you mean.

Do you mean defining a field? Specifying a field in a relation? Calculating? Entering values into?

[ESpringer]

Bruce, "concocting" is not exactly a technical term.

I was just referring to a field "cooked up" for no other reason than to make relationships work better.

That is, a field that does not contain anything that could properly be called DATA either about the things themselves (the real-world stuff/people/relations/possibilities that a given table tracks) or about FM's internal tracking process (such as modification dates, record IDs, etc.)

With portal filtering (and that "X" relation to join TOs), there's LESS call for doing things like making a field in each table whose job is just to stand there and hold "1" and so...

I had gotten lulled into thinking I could build an elegant solution with hardly any such "concocted" fields. But I've woken up again. I promise to respect those little otherwise-meaningless fields that are indispensable to knitting the data together. :

[ESpringer]

thanks, comment...

Yes, I need simultaneous access. That is, the school needs a layout based on "kids" that neatly puts each one's respective parents, doctors, emergency contacts, etc. into visually distinct places (via unfiltered (v10) portals or merged fields). I do see how a temporary global field might work -- in the old fashioned portal-filter-workaround way -- for certain purposes, but not for this perspicuous printable/visually-scannable overview purposes... Or rather, I could juggle multiple global fields to help with ad-hoc filtering of parallel paths between between tables, but then I'd be back in the same boat I was trying to avoid!

So, I've gotten over my fetish for "real data fields". It was a fantasy spurred by real advances in v10 and v11...