13 Replies Latest reply on Jul 13, 2012 9:43 AM by ultranix

    Goal setting database

    ultranix

      Title

      Goal setting database

      Post

      I want to create a simple goal setting dabatase [GOALS table], in which i'd put goals of current year, 5 year goals and progress or reaching them.

      Each goal has it's (auto-enter) ID (id_goal).

      Each goal falls into 1 of 7 categories (id_cat), listed in categories table.

      Goals of the current year and 5 year goals are related, e.g. goal: "To earn $1500 a month" (this year's goal, category: finances) is related to 5 year goal: "To earn $3000 a month" (same category: finances).

      Every week I put my progress in [ACTIVITY] table, related to GOALS table.

      MY QUESTION IS: what's the best setting of database to track progress without copy-pasting progress of current year goals to 5 year goals table?

      Should I have a separate table for 5 year goals and then just relate the categories for it display progress? But as there may be more than 1 5 year goals of the same category, then it would mess up the whole thing.

        • 1. Re: Goal setting database
          philmodjunk

          This is an interesting project. I see two approaches that could be implemented:

          1) A single unified Goals table where goals for all categories and time frames may be recorded. You'd need to use a field to distinguish 5 year and yearly goals from each other and relationships and/or portal filters would use this field to distinguish between them.

          2) Use two tables--one for yearly goals and one for 5 year goals. This may lead to simpler relationships and not need portal filters to select between yearly and 5 year goals, but may make data entry more complex and adding a new set of goals such as 3 year goals a more invovled design change for your database. Separate tables might also make any reports that need to show data from both goals tables more complex.

          Thus each approach has it's advantages and disadvantages to balance when desiding on which one to use. I must confess to being a bit confused by this statement:

          to track progress without copy-pasting progress of current year goals to 5 year goals table?

          Why would you need to copy/paste anything from your activity table to either goals table? And there are ways to move data between tables in a script that are much better options than the copy/paste script steps.

          • 2. Re: Goal setting database
            ultranix

            Thanks, I came up with unified goals table, with 2 id's - id_goal and id_alt (alternate ID). For current year goals both ID's are the same. For 5 year goals, each goal has it's unique ID and id_alt, which is related to the yearly goal. In that case, no script steps, no copy-paste, no other stuff. Just one additional layout and relationship.

            I've got another question here. It's fine to have weekly updates shown for yearly goals, but i don't want it to be as detailed in monthly goals. So how could it be done to filter portal and display only the last entry of the month, i.e. last entry of march, april, may, june, and current entry (that is the last entry anyway) of july?

            • 3. Re: Goal setting database
              philmodjunk

              One entry per month--the last for each month?

              What an interesting challenge....

              I'd use this calculation to give all entries of the same month an identical value:

              cMonth: DateField - Day ( Datefield ) + 1

              this is the date for the first day of the month for each month.

              Then you can define a self join relationship based on cMonth on this table to match each record to all other records of the same month and year.

              Max (SelfJoinOccurrence::DateField ) = DateField

              will return True or the number 1 for the last record of any given month and thus you could put that calculation into a field named cLastOfMonthFlag and use

              PortalTable::cLastOfMonthFlag

              as part of the filter expression to filter out all but the last entry of each month.

              • 4. Re: Goal setting database
                ultranix

                I will check that last entry of each month feature later, here's what i want prior to that.

                As for tracking and progress evaluation. I am thinking of 10 point scale evaluation, where each point will have it's explanation and date of achievement, so i could calculate days in favor or against me.

                My preference is to be able to make up drop-down list, compiled of 10 fields (eval_info_1, eval_info_2, eval_info_3 etc.) and then have another 10 fields for ratings (eval_rate_1, eval_rate_2, eval_rate_3) and another 10 for dates that specific part of the goal should be accomplished (eval_date_1, eval_date_2, eval_date_3). The trick here is to use "progress entry layout", in which all active goals would be placed and aside to each goal there would be a 1) date field; 2) comment field and 3) evaluation field, which would be a drop down list, compiled of eval_info_1, etc. Then, after the value from the dropdown list is picked, it would possibly run a script (via script trigger) to get matching evaluation mark from eval_rate_1, etc. and matching eval_date, so i could easy track in which direction I am going.

                There is an easier way, though. I could pre-define list entering values (no dynamic value change without editing list manually), in such manner:

                10 - Excellent

                09 - Very good, etc. and then use Left(eval_info; 2) to extract eval_rate. This method deprives me of opportunity to have dynamic value list and most importantly - ability to track which goals are going on the right direction and which are not via date_estimated and current_date comparison.

                P.S. As this eval_info field contains very specific and only related to that goal info, it wouldn't be possible to unify those value lists. That example with "Excellent" and "Very good" was just for the explanatory purposes.

                • 5. Re: Goal setting database
                  philmodjunk

                  Any time someone starts talking about a series of fields with the same name, but for a number that's part of the name, I start asking if there is any chance that it would be better to use a related table of records--in this case 10 records each with an eval_info, eval_rate and Eval_date field.

                  it wouldn't be possible to unify those value lists.

                  I think you could if you used a conditional value list as a relationship can then filter the values to a different sub set of values for each rating.

                  I could pre-define list entering values ... in such manner:

                  You might consider entering an ID number that links your record to a specific record in a related table with the different values in different fields. Depending on the needs of your system, the data in these fields could be copied via auto-enter field options into corresponding fields in your table or you might just refer to the fields in the related table. (Depends on what you'd want to happen if the values in the related table were ever edited.)

                  • 6. Re: Goal setting database
                    ultranix

                    that's crazy. I wrote a detailed explanation and Forum returned me a message that comment cannot be posted. and no "back" function, no copy of text in clipboard. sad. i believe i have to re-think and re-write the post....

                    • 7. Re: Goal setting database
                      philmodjunk

                      Yep and sympathies. This is yet another bug we've "annoyed" the rightNow folks (Sorry, couldn't resist the sarcasm), with. There seems to be two bugs that can do this. One logs you out of the forum and you have to log back in unless your browser automatically logs you back in--that one can be avoided by logging out and back in every morning. And a second one that can kill off your reply at any time no matter what you do. Fortunately it happens pretty rarely, but even one long post gone missing is enough to temp one to make a head sized hole in one's computer screen...

                      Sometimes, with long involved posts, I get paranoid and copy the post to the clip board before clicking the Post Answer button.

                      • 8. Re: Goal setting database
                        ultranix

                        Ok, money always makes it clear.

                        The feature that I'm trying to accomplish throughout the value list is for weekly records and tracking.

                        Let's go into the Progress_Entry layout. It's a simple layout of those fields (again, one active goal for each category at a time):

                        Date (Autoenter, global field, Current date, able to edit)

                        1. Active goal of Category 1 [autoenter, global] |weekly comment [edit box, text,global] |yearly evaluation [drop-down list of yearly evaluation scale, final calculation result - number from 1 to 10] | 5 year evaluation [drop-down list of 5 year evaluation scale, final result - number from 1 to 10]
                        2. Active goal of Category 2 [autoenter, global] |weekly comment [edit box, text,global] | yearly evaluation [drop-down list of yearly evaluation scale, final calculation result - number from 1 to 10] | 5 year evaluation [drop-down list of 5 year evaluation scale, final result - number from 1 to 10]
                        3. Active goal of Category 3 [autoenter, global] |weekly comment [edit box, text,global] | yearly evaluation [drop-down list of yearly evaluation scale, final calculation result - number from 1 to 10] | 5 year evaluation [drop-down list of 5 year evaluation scale, final result - number from 1 to 10]
                        4. Active goal of Category 4 [autoenter, global] |weekly comment [edit box, text,global] | yearly evaluation [drop-down list of yearly evaluation scale, final calculation result - number from 1 to 10] | 5 year evaluation [drop-down list of 5 year evaluation scale, final result - number from 1 to 10]
                        5. Active goal of Category 5 [autoenter, global] |weekly comment [edit box, text,global] | yearly evaluation [drop-down list of yearly evaluation scale, final calculation result - number from 1 to 10] | 5 year evaluation [drop-down list of 5 year evaluation scale, final result - number from 1 to 10]
                        6. Active goal of Category 6 [autoenter, global] |weekly comment [edit box, text,global] | yearly evaluation [drop-down list of yearly evaluation scale, final calculation result - number from 1 to 10] | 5 year evaluation [drop-down list of 5 year evaluation scale, final result - number from 1 to 10]
                        7. Active goal of Category 7 [autoenter, global] |weekly comment [edit box, text,global] |yearly evaluation [drop-down list of yearly evaluation scale, final calculation result - number from 1 to 10] | 5 year evaluation [drop-down list of 5 year evaluation scale, final result - number from 1 to 10]

                        SUBMIT [button] (After I click it, it gathers all the data from globals, goes to particular layouts, searches for duplicated entries, and, if none found, enters new records for each goal in every of 7 categories and inputs values from global variables)

                        So, basically, I'm stuck with both of "yearly evaluation" and "5 year evaluation" lists.

                        After I explained layout and setting, I'm going to explain scheme of evaluations. Let's get back to: "To earn $1500 a month" (this year's goal, category: finances) is related to 5 year goal: "To earn $3000 a month" (same category: finances).

                        Accordingly, yearly goal would be "To earn 18000". So, here's how I track progress in evaluation scale. I divide that goal into small parts. In finances it's simple:
                        [pts - info - date projected]
                        10 - Earn 18000 (2012-12-31)
                        09 - Earn 16500 (2012-11-30)
                        08 - Earn 15000 (2012-10-31)
                        07 - Earn 13500 (2012-09-30)
                        06 - Earn 12000 (2012-08-31)
                        05 - Earn 10500 (2012-07-31)
                        ...etc.

                        As for 5 year goal of the same category: "To earn $3000 a month". I first of all accumulated projected earnings of this year (18000), then added up 1 year of monthly earnings of 2000 (12000), 2 years of monthly earnings of 2500 (30000x2) and 1 year of monthly earnings of 3000 (36000). So, 5 years total is 126000, so evaluation scale for 5 year goal would be:
                        [pts - info - date projected]
                        10 - Earn 126000 (2016-12-31)
                        ....
                        02 - Earn 18000 (2012-12-31)
                        01 - Earn 9000 (2012-06-30)

                        Now let's assume that I earned all of the planned 1500 in all 6 months (total - 9000), but in July my earnings would drop to 1050. So, If I am writing my progress today in the separate layout full of global fields, It would look like this (for reference, see above):
                        1. Earn 18000 | Earned 1050 | 4 (yearly evaluation is still 4, because it would turn to 5 only after I pass that 10500 total mark, which I didn't) | 1 (5 year evaluation is still 1, and it would only turn to 2 after I pass that 18000 total mark).

                        So here again, the major issue is how to set up that yearly evaluation (and 5 year evaluation) list, so it could display dynamic text values and after I select the text from the drop down list, it would (via script trigger) extract the 1-to-10 evaluation mark and the projected date.

                        • 9. Re: Goal setting database
                          philmodjunk

                          Am I correct that the user decides the level of progress that has been made towards a goal?

                          If all your goals are number based, it would seem simpler to simply record the current number (current monthly earnings or whatever applies to that goal), and let a calculation and/or a script assign the appropriate 1-10 number marking current progrress towards a goal.

                          I believe that you want "dynamic text" as the text associated with the rating values should display the numerical value that must be reached in order for that rating level to apply. I still think a single table of values using a conditional value list can work for this. It would provide additional flexibility but no matter how you slice it, this one is a complex issue. I've been reading and re-reading your last post and am still not sure i've full parsed out all the relevant details.

                          You've chosen to implement a layout design that limits you to a set number of categories. A design change could be made that opens the door to changing the number of categories just by adding another record to a table of categories.

                          • 10. Re: Goal setting database
                            ultranix

                            Yes, it is correct, user decides the level of progress that has been made towards a goal.

                            No, that financial goal is the simplest of all, so I chose to explain that complex solution throughout the simplest goal. Most of other goals are text based, so it would need manual input.

                            as for layout design, it is subject to change, if i see a better way to enter progress for all 7 category goals in one window. if i leave it as it is, i would only have to select values from 14 different drop down lists (7 categories, yearly and 5 year each).

                            • 11. Re: Goal setting database
                              philmodjunk

                              I think it comes down to setting up a table of "ratings". They might have this structure:

                              GoalID : Foreignkey to table of goals--makes conditional value list possible
                              Rating  : Number (Integer from 1 to 10)
                              Year     : Number identifying year Goal Should be met
                              Description : Text describing what must be accomplished for this rating to apply
                              Type    : identifes whether goal is 5 year or yearly goal
                              5YrID   : GoalID of corresponding 5 year goal, blank if type is yearly. Used in a self join relationship.

                              This would use this relationship for data entry:

                              Goals----<GoalRatings---<YearlyGoalRatings(an occurence of GoalRatings)
                              Goals::GoalID = GoalRatings::GoalID
                              GoalRatings::GoalID = YearlyGoalRatings::5YerID

                              so that you can use a portal to GoalRatings on your Goals layout to enter the 10 required rating values/descriptions for that yearly goal. 5  year goals would be created on a GoalRatings layout with a portal to YearlyGoalRatings.

                              Note that this design frees you so you do not have to have exactly 10 rating values for every goal if you find that it works better to use more or fewer ratings for a particular goal.

                              This lays the ground work for using a portal on a "monthly status" layout to record progress towards each goal instead of using 14 separate fields for this. And this, in turn, makes for a much simpler conditional value list setup to get the desired list of ratings.

                              • 12. Re: Goal setting database
                                ultranix

                                Thanks, I used most of that to solve issues, it worked.

                                Now another thing.

                                As I said earlier, in order not to duplicate progress entries for both yearly and 5 year goals, i used that method to use 2 id's - id_goal and id_alt.

                                For example: Yearly - GL0018 (id_goal) and GV0001 (id_alt). 5-year - GV0001 (id_goal) and GV0001 (id_alt). As you see, in 5 year goals, both id's match. they rather represent a category.

                                I am able to differentiate between yearly and 5 year goal's progress portals in using 2 different layouts, based on main GOALS table, just one portal uses data from PROGRESS_Goals table occurence (GOAL::id_goal = PROGRESS_Goals::id_goal) and another portal in different layout for 5 year goals uses data from PROGRESS_Goals_5 table occurence (GOAL::id_goal = PROGRESS_Goals_5::id_alt). 

                                But if I want to set up the layout (List view) of 5 year goals progress entries only, I get stuck. For 1 year goals I base layout on PROGRESS table (GOAL::id_goal = PROGRESS::id_goal). As 5 year goals don't have records of their own, but instead the related data from 1 year goals are used, I'm not able to get correct names of the goals. Other data, is here in the Progress table, like different rating from 1-year, different projected date, etc.

                                so how could i set up relationships to extract only 5 year goals and their correct names, instead of seeing 1 year goal's names.

                                 

                                p.s. bonus question. Is there a way to select and move up/down multiple lines in manage database, edit script windows?

                                • 13. Re: Goal setting database
                                  ultranix

                                  Phil?