Constructing a portal & summary

[sinisa93]

Hi!

I have two separate questions regarding portals and how they work. Before I start let me describe what I'm doing. I'm constructing DB for local karate club. I have one table/layout with general info about members, I have second table/layout with records about belts (including color of the belt, when & where the belt was obtained, the mentor of the member & sparing partners of the member etc.) and I have 3rd table/layout that shows various activities of each member (there are also several other tables but not important for this topic). All tables are connected via key field (ID number)

QUESTION 1

I have no problem creating portals on 3rd layout to show me which belts particular member has with all additional info from the second table that go along with it. BUT I don't know how to create a portal that shows all members of the club that had particular member as sparing partner. Thing is that I have 3 separate fields for sparing partner because each member can have up to 3 sparing partners and I simply don't know how to connect them all so I can see the list of all members that have particular member as one of three possible sparing partners.

QUESTION 2

I know how to place count of related records in filtered portal but I'm not sure how to get a Total of this counts.

Any help is much appreciated!

 

[Steve Martino]

A lot of information is missing.  The relationship graph, the filtered portal expression, etc.  But I think a better description of the work flow is needed.

it seems to me like if you solve the structural problems, the rest will be easier to work out. 

Table for belts could/should be just a list of belts. 

The member getting a belt is an activity (promotion) and should be in its own table.

Sparring is also a type of activity that may be in the same table or could be a separate table.  I think it could go in the same table, but I would put it in a separate table for now because it has different attributes then promotions. 

A sparring session will be a join table between members and sparring.  On a layout you could have a sparring session with all the info related to a sparring session-date,time,round, opponents,outcome, notes.

i would use a pop up or modal, to select members via a dwindling value list that will eliminate the member, and each opponent as selected-so you can't pick someone twice.  You could use global fields, then after selection and validation, script the creation of a new sparring session.

[comment]

7 hours ago, sinisa93 said:

Thing is that I have 3 separate fields for sparing partner

That's not a good idea. You should use either a join table or (in a pinch) a checkbox field. The important question here is this: if Member B is selected as the sparring partner for Member A, does that mean that Member A is also the sparring partner of Member B?

[sinisa93]

Hi!

Thank you for answering back. I can provide relationship graph but I will have to translate it to English.

@Steve: I was not really precise. Belts table is actually a promotion table and the reason why I can't separate sparring from promotion is because It's part of the promotion i.e. there are 3 sparring partners at the promotion for each member getting a belt. There are no separate sparring sessions needed for record.

@comment: Member A can be athe sparring partner of Member B

Tnx!

[Steve Martino]

OK, so lets try some scenarios:

4 members:  John, Bob, Mary & Frank

John and Mary are up for promotion.

John spars with Bob & Mary

Mary spars with John & Bob

Now how do you want to present the records?

Example:

Promotion 12/1/16-Brown Belt

    John-

         Sparring Partners:  Bob, Mary

         Result:  Pass

     Mary-

         Sparring Partners:  John, Bob

         Result:  Pass

Or is the sparring match with Mary, John, & Bob only one sparring match where 2 people are eligible for promotion?  Or will they spar multiple times during this promotion cycle?

         

[sinisa93]

OK. 

I have one record per promotion. So, if John is black belt I will have 7 records for each of his (successful) promotions/belts.

Each promotion record has following fields: ID number, Name & Surname of student earning the belt, date of promotion, place of promotion, belt color, mentor, sparring partner 1, sparring partner 2, sparring partner 3, notes. There is 1 sparring match with 3 partners and only 1 person is eligible for promotion (the one that is getting the belt).

Person who was a sparring partner can also be the student who earns the belt but this is not connected to her/his sparring partner role. So for this person I will have again the same promotion record as above and (some other people listed as his/her sparring partners for particular promotion/belt)

I have no problem with the "promotion based portal" showing all promotions for each individual . BUT I'm not sure how to construct "sparring partner based portal" meaning to get the list of all promotions where specific person was sparring partner.

Other problem is getting the total count (in a portal) of how many times certain individual was sparring partner.

Thank you for the patience

 

 

[Steve Martino]

Is it possible to post the DB with some sample data?

2 hours ago, sinisa93 said:

m not sure how to construct "sparring partner based portal" meaning to get the list of all promotions where specific person was sparring partner.

Do you really need portal for this or just a report.  The reason I ask is that is seems like everyone is a sparring partner eventually.  So you want to see for example:

1)  12/1/16  John Smith-promotion to black belt.  Sparring partners:  Jane Doe, Bruce Lee, Chuck Norris (no way John won!)

2)  12/1/16  Jane Doe-promotion to brown belt.  Sparring partners: John Smith, Frank Beans, Chuck Norris

3)  11/30/16 Frank Beans-promotion to black belt.  Sparring partners: John Smith, Jane Doe, Chuck Norris

Or you just looking for this:

                                Summary of sparring partners:  Jane Doe-2

                                                                                   John Smith-2

                                                                                   Bruce Lee-1

                                                                                   Chuck Norris-3

                                                                                    Frank Beans-1

[sinisa93]

I know what you mean but I need portal because on this particular layout I also have additional data that don't fit the report. This layout/table concerns various activities of each member and being a sparring partner is just one of them.

What I need is I guess, specific relation from "sparring partner" ( promotion table) to "name" (various activities table). In this way I'm able to list all promotions where one particular person was sparring partner. The problem is that sparring partner role is distributed in 3 different fields and I'm having a problem how to define relationship that would cover all 3 sparring partner fields.

 

[comment]

On 11/30/2016 at 3:02 PM, sinisa93 said:

@comment: Member A can be athe sparring partner of Member B

This doesn't answer my question. But I think you have answered it here:

On 11/30/2016 at 7:08 PM, sinisa93 said:

Person who was a sparring partner can also be the student who earns the belt but this is not connected to her/his sparring partner role.

If - as it would seem - the person earning the belt is not the sparring partner of his/her parring partners, then this can be rather simple.

 

8 hours ago, sinisa93 said:

The problem is that sparring partner role is distributed in 3 different fields and I'm having a problem how to define relationship that would cover all 3 sparring partner fields.

Yes, that is the problem here - and the solution is as stated above:

On 11/28/2016 at 9:28 AM, comment said:

You should use either a join table or (in a pinch) a checkbox field.