7 Replies Latest reply on Feb 24, 2012 3:46 PM by RachelG

    data entry design question (newbie)

    RachelG

      Title

      data entry design question (newbie)

      Post

      I am struggling with a data entry design question that I would love any feedback on.

      Each of the organizations in my org table could perform many services in my services table. I have a table with a unique pk of org_service to track this. Each of these services for each of these orgs has many age ranges that could apply to the service, part-time and full-time offerings for these services, different admission schedules and tuition costs. I have 4 tables set up that use an org_service_ID as the foreign key to capture these relationships.

      From a data entry point of view I am now trying to design a layout that makes sense to the user when entering the service information for an organization.

      What combination of layout tools might best work to minimize user confusion ? (I am thinking portals might be an answer but I don't really understand how I could use them effectively in a data entry situation.)

      Thanks in advance for any insight.

        • 1. Re: data entry design question (newbie)
          RachelG

          As an update to my thinking, I think it may make more sense to do conditional value lists…

          Right now my plan is for users to select the list of services that an organization provides using a checkbox control type. The IDS for each of the services selected are then stored in the org_services table. I think I then need to institute four sets of conditional value lists based on the services selected using the checkbox. Is it possible to create a conditional value list based on multiple answers from selections in a checkbox? (note: my four sets of conditional value lists would not be hierarchical- meaning that the second is dependent upon responses from the first etc. Instead all four are derived from service selections made in the checkbox control type.)

          thanks

          • 2. Re: data entry design question (newbie)
            philmodjunk

            I don't really have a clear picture of the tables and relationships involved so this may not be the best approach, but....

            Conditional valuelists are based on a relationship where a value selected in a field in table1 (usually the layout or portal's table) controls what values from a related table are visible in the list. Here's a fact you may not know. If you set up the field from table1 as a checkbox field and select more than one value, the values are stored in the field separated by returns. When a field with such a list of return separated values is used in a relationship, it matches to fields in the related table if any one of the listed values matches the value of the key field in the related table.

            Example: If I select Apple and Orange check boxes in such a field, my value list will list both the relatd values for Apple and also the related values for Orange.

            • 3. Re: data entry design question (newbie)
              RachelG

              Thank you for this information.  This was the assurance I needed that while my answers from the multi select are stored in one field separated by returns they will still be searchable when I start creating reports.  In my literal view of relationships I would rather they have separate entries in the table but in the end it makes no difference b/c the end result will be the same.  

              • 4. Re: data entry design question (newbie)
                philmodjunk

                Storing the values in separate records are much more flexible for some reporting needs.

                Keep in mind that I said "this may not be the best approach". It depends on how your data is structured and the things you need to do with it.

                • 5. Re: data entry design question (newbie)
                  RachelG

                  Sure.  I understand that your suggestions need to come with the caveat that you can't be omniscent about a db that you've never seen the structure for. :-) But I appreciate the word of caution because I want to plan for the maximum flexibility possible from a reporting perspective if this project takes off the way I would like.  My main reason for doing this is to try to figure out what story my data is telling me.

                  Right now I am considering a checkbox option for users to select all the possible ages that could use a particular service.  It would be easier for users to simply enter an age range for each service an org offers such as ages 2-5 for example.   The one issue I have with this is that in some cases the age ranges start in months because these are services for infants through school age children.  Would there be a way for me to attempt an age range field that users could enter?

                  And again thanks much for any time spent on my behalf.  

                  • 6. Re: data entry design question (newbie)
                    philmodjunk

                    You can do this one of two ways that I can think of right off the top of my head:

                    Use a text field for entering/storing the range, but then calculation fields extract the start and end values in separate fields for use in constructing value ranged finds or in relationships based on a range of values.

                    Have the user enter a range directly in the two separate fields to begin with and then use a calculation field to combine the two values for display/reporting purposes.

                    To handle ranges that refer to months or years, you can add an additional field for both the start and end values where a value list is used to select the units (years or months). Then a hidden calcluation field converts the entered value into a standardized value that works for both years and months:

                    If ( StartUnits = "months" ; StartDataEntry / 12 ; StartDataEntry ) // converts start values entered as months into a fraction of a year.

                    is one possible calculation for such a field.

                    • 7. Re: data entry design question (newbie)
                      RachelG

                      Thank you. This calculation worked like a charm. For posterity's sake here is what I did:

                      Purpose: I needed to create a way for users to enter the age ranges that specific services cover. The age range could be entered in months and/or years. In this case, using calculated date fields would not make sense.

                      There is probably a more elegant way to implement this but here is what I did:

                      1)I added 6 new fields to my table:
                      -age_range_start
                      -time_unit_start
                      -age_range_end
                      -time_unit_end
                      -age_calc_start
                      -age_calc_end

                      2)I created a value list of months|years. On the layout I implemented a popup control type for each of the time_unit fields and assigned the mnths|years value list.

                      3)I assigned each of the calc fields the calculation provided by Philmodjunk. (These are hidden and will be used for reporting purposes.)

                      (tip: pay attention to whatever your field names are if using this calc for your purposes.)

                      This enabled me to use a portal for this particular table in my layout, which is a timesaver for data entry.

                      shew… I am making my way up the learning curve.