Syntax for Tooltip lookup

[Mano Vasilakis]

Hi, I am a novice with FM so if this is painfully obvious please be merciful. I have bought and read the FM8 missing manual and am still a bit hazy about how to proceed with the syntax for a tool tip of this type.

I am creating a workout database for my home. I have fields that contain the Exercise and the Method used to execute the exercise.

Flat Bench would be an example of an Exercise

Barbell or dumbbell would be examples of a Method.

Now I have up to 6 Exercises and 6 Methods for each workout. (Exercise 1 -6 and Method 1 -6)

I want a tooltip that would find the last workout I did with the selected Exercise and Method and tell me how much weight/reps/and time between sets I did. Depending on the cell that I mouse over.

I think it would be cut and dry if there were only one Exercise and one Method cell but I have 6. I might perform flat bench barbell next week in Exercise 3 and Method 3 but I want the results in a mouse over in the Exercise 2 Method 2 Weight cell next week.

To complicate matters I would also like a max performed listed for that set (1-6) as well. (Also it should filter out other users and only reference exercises that the user selected in the current sheet performed)

I have attached the file I am working on because I know this description does not do justice to what I want to achieve. I have added text tooltips in the CHEST layout to illustrate what I would like the tooltip to do.

Please help me. As it is the database is useful as the home gym log I want. But with the tolltips in place it would help me reference where I’m at and decide on weight for the next set quicker.

(A bit off topic. This is my first FM (and database project) and I really like Fm so far)

Thanks for any help you can offer.

Mano

Lords_Gym_Clone.fp7.zip

[Ender]

Hi Mano,

I'm afraid your current structure makes this very difficult. It would be better if you could structure the sets and reps in a related table. I'm envisioning a table of Workouts, a table of Workout Groups, and a table of Workout Sets, where there are many Workouts for a User, many Workout Groups for a Workout, many Workout Sets for a Workout Group.

Although this structure is more complex to set up, the advantage is that all the reps for any of the excercises would be in one table. With this, filtering a relationship to show the related weights/reps/and times for a particular excercise, User, and Method would be fairly easy. You would then be able to use that relationship to show the last related excercise in your tooltips.

[Mano Vasilakis]

I'm not sure I follow. Please keep in mind that this is the first Database that I have designed. Please give me an example of the fields that would fit in each table and the way they would relate. (I don’t understand the way you recommend to break up the tables)

Also would doing a conditional find work better if I used repeating fields instead of making six occurrences of each? I kind of shied away from using repeating fields because they were not recommended in the book.

[Ender]

It's rather complicated to get the interface to look like what you have (with multiple exercises entered on one layout) and to get the tooltips to work. I've worked out a relational solution, but I'm not sure how much needs explaining.

I've attached your file with my changes to make it relational, and used those relationships to make the relevant related data show in the tooltips. I've only done this for four of your six exercises, so you'll still have to add those last two yourself, following the convention I've set up.

If you have specific questions, let me know.

Lords_Gym_v2.fp7.zip

[Mano Vasilakis]

Ok, Holy Cr@p!

First off let me say thank you.

The layout is very cool. I think I understand why you took this approach.

I want to fully understand how you made this work.

What is gSet, why is it global? ( What info does this field actually hold?)

Also what is Workout Set n Line by User_Exercise_Method? And What is Workout set n Lines?

I think I get this much:

Workout – houses each Exercise

Workout set – houses Exercise and Method for each Exercise and Creates a unique Workout ID

Workout set line – Houses the data for the individual sets. It also uses Workout ID (Unique for each Exercise) and WorkoutSet# 1-6 (******* that’s slick)

I’m just not sure I get the relationship below Workout Set 1. (I understand you made different tables to separate the storage, and I get that you used an ID to ref the Uname, Exercise, and Method. But I don’t get the relationship bet “Workout Set n Line by User_Exercise_Method” and “Workout set n Lines”.

I understand a bit more about how to set up relationships with line data by looking at how you set this up. This setup changes the way I think about how to set up multiple lines of data. Plus the way you set it up makes it much more scalable than the flat layout I had. (I could easily add 2 more sets per exercise and not rewrite the whole thing!)

Thanks again for the time you’ve taken to help me.

[Ender]

What is gSet, why is it global? ( What info does this field actually hold?)

That set of fields are filters for the relationships to each respective Workout Set n table occurence. Each one must be populated in Browse Mode with the correct Set# 1-6. Then it will show and allow creation of records through those relationships.

Also what is Workout Set n Line by User_Exercise_Method? And What is Workout set n Lines?

Those both represent additional TOs (table occurences) of the Workout Set Line table. Essentially, they allow you to see the same table data filtered by different criteria. Color coding and a naming convention of the TOs is used here, to identify multiple TOs of the same table.

The Workout Set n TOs are needed to show multiple related Workout Sets on the same layout, and allow those relationships to create records on the fly just by typing into their fields. Same thing with the Workout Set n Lines TOs.

The Workout Set n Line by User_Exercise_Method TOs are needed for the Tooltips. They are each a relationship into the Workout Set Line table filtered by the User, selected Exercise, and selected Method.

I understand a bit more about how to set up relationships with line data by looking at how you set this up. This setup changes the way I think about how to set up multiple lines of data. Plus the way you set it up makes it much more scalable than the flat layout I had. (I could easily add 2 more sets per exercise and not rewrite the whole thing!)

Yes, there is a lot of power in relational design. If you didn't need to view/enter the data from multiple sets from a single layout, the structure would be much simpler. You would only need the base TOs plus one for filtering by User_Exercise_Method. This would be much more flexible, allowing any number of sets. The drawback would be having to navigate into each exercise set separately.

I didn't really look into this, but you should consider trying to incorporate your other exercises into this one structure, by adding an Exercise Type or something (to the layout and to the relationships), plus any special fields needed for those other exercises.

[Mano Vasilakis]

I didn't really look into this, but you should consider trying to incorporate your other exercises into this one structure, by adding an Exercise Type or something (to the layout and to the relationships), plus any special fields needed for those other exercises.

Yes when I saw the complexity of the design I started considered that. Doing this 6 times would be allot plus it’s not scalable. I can't just add a new muscle group if I decide. I'm thinking of making a Drop down value list for muscle group. Then I will need to figure out how to filter out exercises that are not related to that exercise. I did come across something like that in the Missing Manual. Whether or not I am capable of accomplishing it is another story altogether :

I did figure the setup was a cyclical relationship of some sort. But I'm still not sure how it works. I'll stare at it some more. I'm sure it'll click.

Also please explain how the portal knows what cell = what ref # and what ref # to assign to each field. (Or doesn't it matter? I think I know the answer but I'm not 100%)

That set of fields are filters for the relationships to each respective Workout Set n table occurrence. Each one must be populated in Browse Mode with the correct Set# 1-6. Then it will show and allow creation of records through those relationships.

huh?

At some point I’m sure gSet or WorkoutSet# is auto populated. But where is this defined?

Is gSet unique for each and every set? This is probably the hardest thing for me to understand because I see how it fits but I’m not sure why it fits. From where is the number populated?

[Mano Vasilakis]

Oh one more thing. Why do you have a Workout Set table and then a Workout Set n table? It looks as though you are not using that table and the Workout Set Line table. Is that correct?

OK never mind. I think I get it. The "n"'s are referances, not real tables.

Edited April 30, 2006 by Guest

[Ender]

I'm thinking of making a Drop down value list for muscle group. Then I will need to figure out how to filter out exercises that are not related to that exercise.

Look for information on 'conditional value lists'. There are many explainations in the forums, and some example too.

Also please explain how the portal knows what cell = what ref # and what ref # to assign to each field. (Or doesn't it matter? I think I know the answer but I'm not 100%)

The WorkoutID and WorkoutSet# in both the Workout Set n and Workout Set n Lines tables are populated when entries are made into the related fields. Because those are the key fields, they get their the values of the parent keys, in this case from Workout::WorkoutID and Workout::gSet_n. Even if a Workout Set n record doesn't exist, when you go to enter a value in one of those Workout Set n Lines portals, both the Workout Set n record and the portal record itself would be created (with their keys populated).

At some point I’m sure gSet or WorkoutSet# is auto populated. But where is this defined?

Is gSet unique for each and every set? This is probably the hardest thing for me to understand because I see how it fits but I’m not sure why it fits. From where is the number populated?

The gSet_1 field is a global that must be populated manually with a 1, the gSet_2 field must be populated with a 2, etc. Since these are globals, the values will exist for all records. Once populated, you won't need to deal with them anymore unless the file is cloned. They need not be present on any user layouts. Remember, their only purpose is to filter those relationships.

Oh one more thing. Why do you have a Workout Set table and then a Workout Set n table? It looks as though you are not using that table and the Workout Set Line table. Is that correct?

The Workout Set and Workout Set Line tables are the primary relational structure. It is this structure that gets used for the auto-enter calcs that populate the User, Method, and Exercise fields in Workout Set Line. This structure might also be used later for other types of things, like if you ever needed a summry report, or if you needed to find those people that performed a particular exercise.

[Mano Vasilakis]

Thanks for the time you took to help.

I added the other 2 sets and created a conditonal value list for the exercises. And well the interface actually looks better than the first time. (I was able to offset the time field and that was my intent.)

Thanks for the tip to look up "conditional value lists". I entered a case statement in a calculation field that chose a value list based on a new field "muscle group". It worked pretty well, except for when the field was indexed. The methods I saw from the google search work much better.

All is well now and I'm ready to start using it. Ill try it out tonight.

Thanks again,

Mano

[Ender]

I'm glad it's coming together for you!

[Mano Vasilakis]

Hi Again,

I'm having a problem with the fields you set up.

In the Workout Set table and the Workout Set Line table certain fields are not getting populated.

Workout Set is not populating Username and

Workout Set Line is not populating Username, Exercise, or Method.

When I start with a new user I need to add 3 new records before the fields begin to populate. It's driving me nuts. I tested the original you sent to me and it’s behaving the same way.

(Delete all the records and start a new user.)

If you have time please take a look. I'll keep trying to figure out why its not updating but I'm guessing it's something simple that I'm overlooking.

Lords_Gym_v2c.zip

[Ender]

I don't know why the Lookup() function is not pulling in the Username into Workout Set consistently, but if you change that to an auto-enter Lookup (instead of an auto-enter calc) it should work. You may have to do the same with the Workout Set Line lookups.

Also, the new Workout record must be commited after selecting an Exercise and Method before the lookups in the Workout Set Line table will work. I can see that this will be an annoying thing to work around, so you could instead relookup the WorkoutID in the Workout Set Line table after the data entry is complete, like as part of the script to leave the record.

[Mano Vasilakis]

Very strange the lookup function is not working either. How do you set up a script to run after a record you leave a record. Also Do I have to set it up for each Workout Set Line N? or is there a way to do a loop for that. (Sory this project got way over my head fast!)

And in the Workout set line you never assign a unique id. How does the portal know which record to access? If I wanted to lookup a certain record like the 3rd weight in set one how would that work?

(It doesn't help that I have a newborn at home and I'm short staffed at work. I am trying to read up on this in between but its not exactly light reading)

[Ender]

How do you set up a script to run after a record you leave a record.

Well, I would insert something like this into any scripts that navigate out of the Resistence Log layout:

Commit Records/Requests [ No Dialog ]

Go to Related Record [ Show only related records; From table: "Workout Set Line"; Using layout: "Workout Set Line" ]

Relookup Field Contents [ No Dialog ; Workout Set Line::WorkoutID ]

Go to Layout [ original layout ]

Commit Records/Requests [ No Dialog ]

You could make it conditional, and only run this part of the script if new Workout Set Line records were added since navigating into the layout.

Of course, all of this means you should disable access to switching layouts via the status bar on the side. Force the users to only use your scripted buttons.

[Mano Vasilakis]

Dude, I feel so stupid.

I just changed the field from text with auto enter to a calculation.

(Field = field from other table)

It works every time. I want to break something. (I've been working on this all day.)

thanks for the script though I was trying to figure out how to lookup each of the 6 sets. I guess they thought of that with relookup field contents.

Ah scripted buttons that makes sence. I was looking for an option to run on record exit.

[Mano Vasilakis]

Ok now I feel dumber still. You cant index a calculation field with = field. You have to use Lookup (field)

And from reading around a bit I find this is a common problem. So I will try your solution.

Also is there a way to have the tooltip lookup a particular line? What I mean is that if it finds the criteria in User/Exercise/Method can it return Line n from the portal depending on the line that I make active? Having just the last weight/rep/time doesn’t help much. I want to know what the 1st/2nd/3rd... was so that I can adjust the weight for my next set.

(Btw the boxes you labeled as sets are actually exercises. sets are the individual lines in each exercise. So we had 6 exercises and 6 sets (lines) max each. This is really no big deal but I don’t want to confuse you when I ask for the weight in set 1-6.) I took the liberty of adding a new set number box (1-6) in the Set Line field but I'm not sure how to call the weight from that line.