11 Replies Latest reply on Aug 20, 2012 10:00 PM by ultranix

    Relationships again



      Relationships again


      Nothing really fancy, but simple relationships and table occurencies question once again.

      I created table "Prizes", which will consist of rewards that i'm going to grant myself after reaching certain goal.

      So, tables are two: GOALS and PRIZES.

      from Goals: only important field is id_goal, as it is used in relationship.

      from Prizes: id_goal, id_prize (autoenter serial number with abbreviation "PR" in front), prize, status.

      I've got two layouts: One for prizes, another one for goals.

      What i want to accomplish..

      1) [via portal] be able to select prize in Goals layout (table occurence, which I'm having problems to set up) using a drop down list in a portal

      After I "select" prize from a drop down list, it would then "enter" goal_id into Prizes table/layout, and it would be better to have it related correctly, so it would automatically update whether i change it in Goals layout.

      2) somehow create "smart" list of prizes, so that i couldn't not take prizes, that are already assigned to other goals.

      so, any ideas on creating that list?

      and for relationships - do i need joined table? i rarely set those up myself, so I'm having trouble.

        • 1. Re: Relationships again

          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?

          • 2. Re: Relationships again

            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.

            • 3. Re: Relationships again

              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.

              • 4. Re: Relationships again

                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.

                • 5. Re: Relationships again

                  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.

                  • 6. Re: Relationships again

                    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.

                    • 7. Re: Relationships again

                      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.

                      • 8. Re: Relationships again

                        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.

                        • 9. Re: Relationships again

                          That joined table thing didn't work, so i may end up using script.

                          • 10. Re: Relationships again

                            How didn't it work? It should work.

                            • 11. Re: Relationships again

                              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.