Listing but only once

[Enigma20xx]

I'm trying to make a simple data base for workouts.

Some of the tables are: plan, muscle group, exercise... And a join table.

From the plan presentation (plan table) and through a portal showing the join table I set the day, muscle group and the exercises.

I print the plan as a report from the join table (using day and mg parts to sort) like so:

Day 1

Chest

- bench press

- flyes

Biceps

- curl

- Scott

Abs

- hanging leg rise

What I'd like to accomplish is following day X, to have the list of muscle groups of that day. Something like "Day 1 (chest, biceps and abs).

I'll appreciate any help.

[comment]

It's a little difficult to follow, but I think that if you remove the body part from (a copy of) your existing report layout, you will get exactly what you ask for?

[Enigma20xx]

Hi comment, as always thanks for your answer.

I think I didn't explain myself as well as I thought, [emoji1]

By now everything works fine.

What I can't do is to list the muscle groups (their names) involved in that particular day, in the part that sorts by day.

I believe i need a calculation field to show that content but my knowledge doesn't go that far as to work that calculation out.

It should look like this: Day 1 "Chest, biceps and Abs".

Thanks again. [emoji106]

[comment]

I think I didn't explain as well as I thought, [emoji1]

 

I am afraid so. Let us assume a simple structure of:

 

Plans -< PlanItems >- Exercises >- MuscleGroups

 

where the PlanItems join table has fields for:

 

• PlanID (or perhaps you are using a date field to link to Plans?)

• ExerciseID

 

Now, if you produce a report from the PlanItems table, sorted first by PlanItems::PlanID (or date), then by Exercises::MuscleGroupID, and let the layout have 2 sub-summary parts, one for each of the two sort fields, you'll be almost done. You just need to place the MuscleGroup name field from the MuscleGroup table inside the sub-summary by Exercises::MuscleGroupID part, and the Exercise name field from the Exercises table inside the sub-summary by PlanItems::PlanID part. There's nothing to calculate here.

[David Nelson]

I thought it meant how to show a calculation like you do which is called concatenation showing the related fields but substituting carriage return for comma.  I forget how but if that might be what you want, I would work on it.

 

HTH

[comment]

I am sorry, but I am not with you at this point.

[Enigma20xx]

I've made a sample file that I hope can help.

 

The structure is not exactly as comment says but I think is not wrong like I've done it. I'm sure comment can disagree and tell me why I shouldn't do it that way .

 

As always thank you for all your advices.

 

 

Maybe David Nelson got my idea.

 

A calculation should be done to summarize the muscle groups worked out on a particular day (this is the step I don't know how to do).

 

I attach a sample file and a picture of how my idea should look like.

 

I apologize if sometimes (or all times ) I'm not clear enough but English is not my native language so I do my best.

WorkoutPlans.zip

[eos]

A calculation should be done to summarize the muscle groups worked out on a particular day (this is the step I don't know how to do).

 

You'd create a summary field for that purpose, which you can then use to summarize every part of the layout (i.e. count per group, per day, grand total).

 

Note also that you can sort (and group) the report by related fields, so you're not limited to serial keys, but can sort on human-readable data.

 

To produce and display a listing of exercises per group in the summary-part, use a self-join and a calculated field and delete the body part. 

 

See attached file.

WorkoutPlans_eosMOD.fmp12.zip

[Enigma20xx]

Thank you eos. As always your MODs are great.

 

Your report is very useful too but in this case is not what I'm looking for ("the proper workout sheet").

 

Exercises can't be listed all together because I need to set the sets and reps for each one.

 

When I see a day, I need to know what muscular groups are going to be worked that particular day.

 

Eg. Day 4 (I'm gonna work chest and back), so I want to see those mg listed next to Day 4.

WorkoutPlans_eosMOD - 2.zip

[eos]

Your report is very useful too but in this case is not what I'm looking for ("the proper workout sheet").

 

Well, it seems that now you know how to get there. 

[Enigma20xx]

Do I?  

 

The picture I attached is just an example of what I'm trying to do, but I haven't done it.

[eos]

Do I?

 

You should – by extending the technique I showed for listing Exercises to MuscleGroups.

 

Exercises can't be listed all together because I need to set the sets and reps for each one.

 

There were no such fields in your original file; regardless, you could still list them incl. sets and reps by listing a calculated field.

 

See here. The calc field uses ExecuteSQL(), but you could just add a PlanItems self-join by day to get the MuscleGroups listing via a “native” method.

WorkoutPlans_eosMOD_v2.fmp12.zip

[Enigma20xx]

 

You should -- by extending the technique I showed for listing Exercises to MuscleGroups.

 

I aplogize but I swear I've tried to extend the technique to MuscleGroups but my calc field never worked well.

Now I know that what was wrong was the relationship.

The new self-join table is related by day and planID because if it's only set by day, the list shows muscle groups for all day 1 of all plans.

 

I have added a Case to your let function. It Works and I hope it is correct.

It is useful for the training sets and for the warm up sets too, but sometimes there's no warm up sets, so nothing should be shown.

 

I don't understand the usage of the field to fail. Maybe I confused you.

Some exercises have several differet tips or advices, but is not a category. I'm a bit lost in that field.

 

This is more than I expected.

 

Thank you very much to all.

WorkoutPlans_eosMOD_v2 B.zip

[bruceR]

Like this it seems.

WorkoutPlans_eosMOD_v2 BFR.fmp12.zip