Big problem small brain

[Tmonk]

I'm working on a db for our little league baseball organization. I've had some good suggestions from the posts I've sent up and through browsing the threads. I'm hoping someone can help with a relation question. Creating databases is not my "day job" and consequently I'm probably asking a silly question. I have a Player ID file, Registration file, Family File, and a Coach File. The fields in the Player ID are specific to each player(ID,dob,calc. age, etc) Since more then one player can come from one family I separated the family file to contain info relating to a family. I use "phone" as a match to fill in the family info(if it already exists) on my main layout when adding a new player.(works)

I have my registration info in a portal on the main layout that identifies financial info about each player. (works)

What I can't seem to figure out is how to use the family file to help fill in related info in my coach file. Most people who coach will be included in the family list because they have kids in the league, however there are coaches who are just coaches. When I got this to semi- work, a coach who wasn't part of "family" would be added to family because of the way my relationship was set. If I disabled "allow creation of related files" I could not enter data in the fields where my data came from "family" that didn't already exist in the family file. Another issue was if I deleted a coach who also had player info related to my family file, it would delete all my player info related to that family but not the family info!

I'm struggling to make sense of it all!

[ESpringer]

It seems fine to have a family databases for all sorts of households in the community, including coaches' households and players' households (which often overlap), and it seems fine that you have "allow creation of related records" [not files] enabled. It sounds as though you believed there was a problem with that... because then you talk about "another issue" when it's not clear what was going wrong, or how this was only "semi-working"

However: I'm also baffled about how deleting a coach could delete NOT the household info but info on the PLAYERS from that household. Clearly, though, you don't want to delete portal rows from either the players' or coaches' file, because that would cause trouble that could look *almost* like what you describe... Assuming it's not that, then the next question would be how you've set up the coaches relationship to family file. Does each coach record use manually entered name and phone, and then pull up the other info based on a relation keyed to the phone number?

[Tmonk]

OK. I have experimented and came up with something that works when my "coachinfo" file is open. It's a calc field based on a maching phone# between coach and family. If a match is found it opens one layout, if not another. However, under "player info" where my main layout is, I can't seem to get this to work properly. I've attached the files to this response. If this helps better explain what I'm doing wrong, I'll take any suggestions. Thanks.

[ESpringer]

Could you say a little more clearly what the "this" is that you can't get to work? You want a different layout depending on whether there's a match? Say more?? Sorry I'm not following right away...

[ESpringer]

Tmonk,

To be honest, I'm not sure why you're working with scripts and multiple layouts for your coaches file, which I just took a look at. If you have a relation based on phone number, then the only fields you need are CoachID, first and last name, phone, year, and background check. On your coaches' layout (you don't yet need more than one), you just include all relevant fields *from* the other database alongside the "native" fields. To add the related fields, just drag a field from the bar at left onto the layout, and use the pop-down menu at the top to move away from "Current File" and to the FamilyInfo relationship. Then, you can place "address" and "zip" etc. directly on your coaches layout and it will pull the relevant info from the other database. (As soon as you enter a phone number that matches one in the family file, these other fields will fill in. If there is NOT a matching record after you type the phone number, then just start typing in the address city state fields (etc) and a new record will be created in the FamilyInfo file, assuming "Allow creation of related records" is turned back on.)

No lookups needed, and no scripts, and not even portals... Methinks that with all the high-end stuff possible in FMP, you're aiming for more complexity than you need, at least for now. (I notice that your scripts include "delete record" steps, without dialogue. Although I didn't look much further into it, my suspicion is that this is causing the mischief you mention earlier.)

Have I missed something about why you need more bells and whistles here?

Anyone else have thoughts?

[Tmonk]

Thank you for your suggestion. I'm using a shovel where a spoon would suffice!

[ESpringer]

Getting the hang of related fields can be tricky! Let us know how it works out...

-ESpringer