FM 13 sub summary can not be 'restricted' by find ?

[ron G]

 I have a membership application.  (Let me put up front, that I have been developing this app for several years and initially I made a mistake in the design by separating my Candidates table from my Members table... too late now)

 

Each candidate has a member that 'coaches' him.  This name is put in the Candidates::Coach field.  It is a popup that get's it's value list from Members::cLast_First_MI.  This works well.

 

The table structure is:

Candidates::KF>--------------Members::KP

 

I have a report that sub summaries all the 'Coaches' and shows all their 'Candidates'

(The layout/report doesn't change if it is based on Members or Candidates.)

 

 

The problem is that if I put the field Members::ACTIVEInactive on the sub summary, it always shows the ActiveInactive status of the top most Candidate.  I want it to show the ActiveInactive status of the Coach  (whose name is located in the Member table).  Do I need to do a lookup?  Is there an ExecuteSQL solution ?

 

Thanks

 

Ron

 

 

[comment]

I couldn't understand this remark:

 

(The layout/report doesn't change if it is based on Members or Candidates.)

 

You are describing a one-to many relationship where one member can coach many candidates. A report summarizing candidates by coach needs to be produced from the Candidates table. The data describing the candidate goes into the body part of the layout, and the (related) data describing the coach goes into a leading sub-summary (when sorted by Candidates::Coach) part. Your screenshots aren't clear enough for me to tell what you have and how different it is from what I have just described. I am also not sure what this has to do with finding.

[ron G]

Right.

The data describing the candidate goes into the body part of the layout, and the (related) data describing the coach goes into a leading sub-summary (when sorted by Candidates::Coach) part.

This is what I have done.  And, it seems to work.

 

However, each Candidate::Coach (Sub-Summary) has, in the (Body) MEMBERS:: table a field called Members::ACTIVEInactive; which holds their membership status.  (Candidates and Members)

If I FIND Members::ActiveInactive="Inactive" FM looks at the top most Member::Name and uses that ActiveInactive field value.  It does NOT look at the Sub-summary field (Candidate::Coach).  I suspect I should put the ActiveInactive field in Candidates.  But the ramifications of moving a field make my head hurt.  8-)

 

So, I am looking at ExecuteSQL as a solution but I have zero experience with that command but am attracted 

[comment]

However, each Candidate::Coach (Sub-Summary) has, in the (Body) MEMBERS:: table a field called Members::ACTIVEInactive; which holds their membership status.  (Candidates and Members)

 

I am afraid I can't follow your description. If a field is in the Members table, then it describes the status of a member, not of a candidate. If you sort the candidates by the member that coaches them, then any field from the Members table that you put into the sub-summary part will come from the record of the member that coaches that group. How else would you want it to be?

 

 

 

If I FIND Members::ActiveInactive="Inactive" FM looks at the top most Member::Name and uses that ActiveInactive field value.  It does NOT look at the Sub-summary field (Candidate::Coach).  I suspect I should put the ActiveInactive field in Candidates.  But the ramifications of moving a field make my head hurt. 

 

I don't understand this part either. What is your purpose here? Why are you performing a find? Do you want to include only candidates whose coach is an active member in your report?

[ron G]

Yes.  It is confusing.  And I think most of that confusion springs from my 'ancient' decision to separate the Members and Candidates tables.  ugh

 

That being said....

 

The Candidates::Coach field is filled with a popup of the Members::Name field.

The sub-summary field is Candidates::Coach and the Body is the Members::Name

 

I find if I do a 'find' on Members::ActiveInactive="Inactive" it finds all the Members::Name Fields who are "Inactive".

 

I need a method to take the Candidates::Coach field (which is a name in Members::Name), lookup that name and return it's Active/Inactive status.  Could is simply be that the addition of a lookup field in the Candidates table would do it?  ....hmmm  Your thoughts?

[comment]

I think most of that confusion springs from my 'ancient' decision to separate the Members and Candidates tables.

Uhm ... no. Forgive my being blunt, but most of my confusion comes from the difficulty to decipher what you're saying. For example:

 

The sub-summary field is Candidates::Coach and the Body is the Members::Name

 

I think you mean - and this took me quite a while - to say:

 

The Candidates::Coach field is in the sub-summary part and the Members::Name field is in the body part.

 

To which I would say:

I see no purpose in showing the Candidates::Coach field at all. As I said earlier, all fields describing the coaching member should be in the sub-summary part - so that's where the Members::Name field belongs. Putting in the body makes no sense, since it would just repeat the same name for all candidates in the same group.

 

if I do a 'find' on Members::ActiveInactive="Inactive" it finds all the Members::Name Fields who are "Inactive".

 

No. You are producing the report from the Candidates table. If you do a find for Members::ActiveInactive="Inactive" while you are in a layout of the Candidates table, you will find candidates whose coach is inactive. Earlier I asked what is the purpose of the find - I don't think you have answered this.

 

I need a method to take the Candidates::Coach field (which is a name in Members::Name), lookup that name and return it's Active/Inactive status.

 

I don't understand this either. Even if I get over the strange language (surely the Candidates::Coach field is NOT a name in Members::Name; I presume you meant to say that the Candidates::Coach field is a matchfield in the relationship and therefore contains the same value as the corresponding Members::Name matchfield on the other side of the relationship; which, being obvious, needs not to be said at all) - even after that, I don't understand the purpose of doing what you describe. There is no need to lookup anything from the Members table: just put the field from the Members table on the layout of Candidates and it will show data from the first (and only) related record in the Members table - i.e. from the record of the current candidate's coach.

[OlgerDiekstra]

The way I read this, is that everyone is a member. If a member is coached, their details are duplicated in the candidates table, and then linked via the candidates::coach field to member table.

To create a report of candidates and coaching members, you should be able to create a report based on the candidates table, with the candidates names in the body, and in the subsummary part list the candidates::coach. Assuming the relationship is setup correctly, (via candidates::Coach to members) you should be able to add the status field from members:: as well in the sub summ part. For troubleshooting, list the members::name field as well (or whatever you call it). If you get the same name every time, your relationship is broken.

When you then sort by candidates::coach, you should get the report you are after.

[ron G]

OlgerDiekstra:

I think you understand the problem.  And I appreciate your answer.

Concerning the relationship:

Members:                                Candidates:

PKMemID                       --->>  FKMemID

Name                                       CoachName    (CoachName is a popup of the Members::Name)

ActiveInactive

 

I am unable to put the Members::ActiveInactive field in the SubSummary 'band' and have it show the values for the CoachName in the corresponding Members::ActiveInactive.   Instead the report shows the Members::ActiveInactive value for the first Members::Name in the BODY of the report.  Huh?

[flyjar]

I think there are a few things that need to happen here:

1-in the candidates table, you should store the memID of the coach rather than the name. Instead of CoachName, let's call this FKCoachID

2-the value list for the coach pop up should be 1st field memID, 2nd field name, show only 2nd field. This will look the same on the layout as what you currently have, but will store the ID instead of the name.

3-this is the key to Doing what you want: you need a 2nd occurrence of Members that links the Candidates::FKCoachID to Members 2::PKMemID

In your subsummary part, you can put Members 2::Name (the coach name) and you can use Members 2::ActiveInactive to get the status of the coach.

[comment]

in the candidates table, you should store the memID of the coach rather than the name.

 

You are right that the relationship should be based on an ID rather than on a name. However, it has nothing to do with the problem at hand, and can be put aside until that is solved (along with the question of why are members and candidates in two separate tables).

[flyjar]

You are right that the relationship should be based on an ID rather than on a name. However, it has nothing to do with the problem at hand, and can be put aside until that is solved (along with the question of why are members and candidates in two separate tables).

That may be true. I was just worried about adding a new relationship that is based on a data field instead of a key field. The other worry is what happens if another member has the same name as the coach--the data picked up by this relationship could be for the wrong person.

Anyway, my read on the problem is that the 2nd TO for members is what is needed. Since the OP needs to know the value of Members::ActiveInactive for the coach when the context of the report layout is an entry on the candidates table.

Taking Comment's comment to heart...If we don't fix the key vs name issue right now and leave everything as is, the solution is still in my my #3 above. The only thing to change is that it should say that the relationship would be Candidates::CoachName to Members 2::Name. This will give the needed info for the subsummary report.

[bruceR]

Flyjar: none of that is necessary. No alternate TO is required.

 

If Ron G can't get Comment's suggestion to work, then there is something missing in Ron G's description.

 

Have a clone of the file or an accurate description of the relationships would be helpful.

[comment]

Anyway, my read on the problem is that the 2nd TO for members is what is needed.

 

I don't think so. Were you able to reproduce the problem? I wasn't - and I cannot say what is needed to solve a problem, unless I can [a] reproduce the problem and show how it goes away when the solution is applied.

[OlgerDiekstra]

Hi Ron,

 

OlgerDiekstra:

I think you understand the problem.  And I appreciate your answer.

Concerning the relationship:

Members:                                Candidates:

PKMemID                       --->>  FKMemID

Name                                       CoachName    (CoachName is a popup of the Members::Name)

ActiveInactive

 

I am unable to put the Members::ActiveInactive field in the SubSummary 'band' and have it show the values for the CoachName in the corresponding Members::ActiveInactive.   Instead the report shows the Members::ActiveInactive value for the first Members::Name in the BODY of the report.  Huh?

 

Ah, I see, so you don't really duplicate the member in the candidates table.

What you'll need to do (similar to what flyjar suggested) is create another member TO (ie Coaches), and change the candidates::coach field to be FK to your Coaches TO. That way you'll be able to reference any field of the coach on your summary.

So:

Members:                   Candidates:          Coaches (Members TO):

PKMemID            >>   FKMemID             

Name                         FKCoachID     >>  PKMemID

ActiveInactive                                          Name

                                                                ActiveInactive

 

You can then use a valuelist to pick a coach out of the Coaches TO.

 

Your summary would then be based on the Candidates table with the names of candidates in the body part, and the Coach::name in the subsummary part (and base the subsummary part on sorted by coach::name).

[ron G]

Hi Ron,

 

 

Ah, I see, so you don't really duplicate the member in the candidates table.

What you'll need to do (similar to what flyjar suggested) is create another member TO (ie Coaches), and change the candidates::coach field to be FK to your Coaches TO. That way you'll be able to reference any field of the coach on your summary.

So:

Members:                   Candidates:          Coaches (Members TO):

PKMemID            >>   FKMemID             

Name                         FKCoachID     >>  PKMemID

ActiveInactive                                          Name

                                                                ActiveInactive

 

You can then use a valuelist to pick a coach out of the Coaches TO.

 

Your summary would then be based on the Candidates table with the names of candidates in the body part, and the Coach::name in the subsummary part (and base the subsummary part on sorted by coach::name).

I got it!    It seems counter intuitive but it works.  (I say 'counter intuitive' because I can not express a narrative of what is going on... Can you help with this?)

 

I changed the relationship to include separate TO's :  (Yes, I should have a FK_Coach but I am on version 2.48 and there are hundreds of users (my app is 'freeware') so perhaps I could create a calculated FK?    In any event, your suggestion works ... Thank you very much...  ron

 

 

[OlgerDiekstra]

Using first/lastname for a key is a recipe for failure. You really need to use a unique key. Are there any people in your database with the same first and lastname? Using first/lastname as a key, your coach would change depending on the sorting of the relationship (between "candidates 3" and "members 4") and therefore also your activeinactive status. That's because the relationship would use the first coach it encounters. Change the sort order and a different coach ends up being on top.

 

Not sure what the issue is though with updating it. If the users all have the app on their devices, then it would include the DB? In which case each would see different data from the other anyway. If you have a hosted DB, it wouldn't matter either. The minute you change it, it's changed for everyone. Unless you use data separation and have a different UI DB that's installed on each device and pulls data from a hosted DB.

 

As for how the extra TO works, maybe this helps: http://forums.filemaker.com/posts/3bcee00751

[ron G]

My 'membership' app, is a stand alone app.  Each time a user gets a new version, they run an import routine which brings their data into the new version.  It is pretty straight forward and it works.

 

If I ad a FK_Coach field I need to 'refresh' the Coach Popup to generate the 'new' FK_Coach field.  Got any ideas?

 

Thanks

R