On a layout based on "shoesTable", that table having a record for each shoe, make a portal from the Running Log Table.
The shoe table should be linked to the RunningLog Table by "ShoeID"
That portal should show one line for every record in RunningLog that has the corresponding ShoeID (If not, check the portal setup and the relationship shoes::ShoeID <---> RunningLog::ShoeID)
On the Shoe table, make a "totalMiles" field which is a calculation of:
Sum(RunningLog::Miles) which would total up the milage of all of the related routes (related by ShoeID above)
Does this make sense or should I give more detail?
Wow Thanks for your help. After tons of trial and error I finally got it. However, couldn't get the last part to work. I couldn't get it to tally the total shoe miles on the shoe layout.
I understand the importance of using a ShoeID to keep things straight but is there a way tto have that info automatically entered? I want to be able to select which shoe it is by shoe name from the Workout Log. Can I have 2 fields in the Workout Log that are Shoe Used and ShoeID. Shoe Used would be a drop down menu that uses the values of the name s of the shoes from the Shoe list and then depending on which one I choose, enter in the ShoeID.
You would want to autoenter a shoeID only when entering a new pair of shoes. You can set this field to AutoEnter Serial under the Field setup (Define/Manage Database depending on which version you're on)
Check out the topic "Current Table" on this forum, I just posted how to create a vaoue list based on a serial# but show the names (books in the case of erre104, shoes in your case). I think that this is what you're after. This way you'd actually be selecting ShoeID (which is what you need to do) as you look at shoe names (which is easier for you to use).
Are you portaled through well? I think I may have skipped an important step. The mileage data you want to sum is actually on your Trip Table. You would need a relationship with the trip table, use ShoeID again, that portals the trips taken into a layout based on Shoes Table. I guess the Sum you really want is Sum(Trips::miles) to sum up the milage related to that ShoeID. Sorry if that is too confusing. Does this make sense to you?
Oh man I'm having some trouble. Everything is working great with the portal. The weird thing is that I can only get the mileage to Sum on one shoe. The others are blank. Another strange thing is that if I add a run to the one shoe that works, the only way to get the shoe's mileage to change is to clear the value, switch to another record, and switch back.
Thank you so much for your help and patience.
check out the "Storage" settings on your calculation field. It should be Sum(), and it should be "Do not store calculation results- recalculate when necessary"
If everything is working great with the portal, and your Sum() field is "summing" the right field, and it is unstored, then it should be working like a marathoner.
The Sum() should be adding up the same field that is showing through the portal...
so if the portal is seeing the records fine, look at the Sum() and the storage.
Let us know how you do...
Ok I got everything summing correctly and it looks great. Thank you for all your help.
One more thing: I don't want to have to go to the shoe layout and double check the ShoeID every time I enter a run. Is there a way to choose the shoe name from a drop down menu and have the appropriate ShoeID auto-enter in another field?
From the Running Log, I want to enter *Asics* in the *Shoe Used* field and have it trigger a *1* in the *ShoeID* field. Or enter *New Balance* and have it enter *2*.
That way I don't have to remember the ShoeID and it just works in the background. I thought using Lookups maybe would be the way to do it. If it is, I can't figure it out.
Again, this noob really appreciates your help.
Trying not to come across as a wiseguy, but I have to asume you didn't check out the thread I referenced above...
another poster was dealing with the exact same thing.
You would define a value list on ShoeID (he used BookID), but show only data from the field "Shoe Used" (he used Book Name). When you pick the shoe/Book by name, it would actually put the correct ID# into the UniqueID field.
Please check out the thread link above and post back if you have questions about it. I'd be happy to walk you through it if you need more than is on that thread. Thanks!