One prize and only one prize for every goal? One goal and only one goal for every prize?
If so, do you need two tables or just a prize field in your goals table?
It may be a field in goals table, but...
I always jot down prizes, that come to my mind and are not currently assigned to the goals. for example, for now, i don't feel i can afford expensive car for achieving on of my yearly goals, but in 5 years i might do that, so i just leave it without assigning a goal to it.
other than that, it's one and only one prize for every goal.
If you have a serial number, __pkPrizeID, then you can use this relationship:
Goals::_fkPrizeID = Prize::__pkPrizeID
On your goals layout, format _fkPrizeID as a drop down list where __pkPrizeID is the primary field in the value list. A name field from the Prize table can supply a secondary field value.
thanks, it worked. how to do it other way around? meaning, if i go to prizes layout and then from a dropdown list change the goal i assigned the prize to, it would also change in goals layout.
If you want only one way or the other, not both use the __pk field in the goals database and put the _fk filed in the prizes database.
I get a sneaking suspiscion that you want both, however.
so how do i do both? set up another relationship for goals layout?
the purpose is easy: to be able to assign prize from the goals layout and also to be able to assign a goal from the prizes layout.
And after i change the assigned prize in the goals table, it would change in prizes table accordingly and vice verse - if i change the assigned goal in the prizes table, it would change in goals table accordingly.
You'd need to do a bit of extra "fiddling" to make that happen.
Even though it's not a many to many relationship, you could use a join table:
Prizes::__pkPrizeId = Prize_Goal::_fkPrizeID
Goals::__pkGoalID = Prize_Goal::_fkGoalID
Enable "Allow creation..." for Prize_Goal in both relationships shown above.
Then assign a Goal on the prizes layout with a drop down list format on the Prize_Goal::_fkGoalID field. Assign a prize on the Goal layout by doing the same sort of thing with the Prize_Goal::_fkGoalID field.
There are also ways you might set up that use a script to update the link field when selecting a record from the primary key side of the relationship.
i tried playing around and setting relationships that basically mimic the concept that works in the goals table: as in prizes table the prize ID is the primary key, so i tried to set up a relationship to use in prizes database, that uses Prizes::fk_GoalID = Goals::pk_GoalID, but it only attempted to change goal id's, and not re-assign goal to a specific prize. not sure if i can come up with other solution alone.
EDIT: all the above was posted before your last reply. I'm going to check your latest reply and report with the progress.
That joined table thing didn't work, so i may end up using script.
How didn't it work? It should work.
I might be doing something wrong, but it's not after all that important. i can live with the idea of only being able to change goal in prizes layout, as it accomplishes my task better than being able to assign prize in a goals layout.
thanks for your effort, i would be grateful if you could take a look into that small and simple file i sent you (weekly, monthly, yearly view), because once i solve that, i believe my solution would be finished.