Forum Discussion

NirajShah1's avatar
NirajShah1
Qrew Cadet
8 years ago

I have multiple activities and multiple categories, i need a form that allows entry of this data

I have 10 activities and 10 categories on a crosstab form. At the cross section of each activity and each category a numerical value is entered. (See picture below).  I've been scratching my head as to the most efficient way to do this in QB but cannot come up with a solution. 
App builders
Ask about Quickbase functionality
Forms
  • AviSikenpore1's avatar
    AviSikenpore1
    Qrew Trainee
    8 years ago
    @Niraj - Are you just trying to enter that information in a single record in a table ? 

    You can either use a grid edit table or a many to many relationship using an intermediate table. So, what exactly are you trying to accomplish ?

    if your set of questions/ crosstab is not going to change or expand over time you can bite the bullet and create 100 fields (10x10) and capture the values in a single form using alternate field labels. This method is not sustainable when the parameters of the cross-tab change.
    • NirajShah1's avatar
      NirajShah1
      Qrew Cadet
      8 years ago
      @Avi. Thanks for your reply.

      Here's what I am trying to accomplish: The columns in my picture represent categories of age ranges that are served by every activity (rows). Inherently, each row and column are related as for every activity a category will be served (the numerical variable) that goes in each box. 

      The thought process I've come up with so far is to create a  table "Activities" and then have the relate that to "Categories" and have the user add a category for each Activity. Where I get stuck is how can the aforementioned relationship capture the fact that every activity will have a category. Would a many to many accomplish this?

      For now, the set of questions / cross tab will not change, but they may later on. I know I can do this the tedious way by creating all the fields, but I'm sure there is an easier way that I am missing out on. I haven't use Q__B in a few years so pardon my unfamiliarity.
  • AlexWilkinson's avatar
    AlexWilkinson
    Qrew Assistant Captain
    8 years ago
    Have you tried creating a Summary report? This works if you have one field (e.g., "Activity Name) for the rows of the crosstab and another field in the same table for the columns (e.g., "Activity Category").
    • NirajShah1's avatar
      NirajShah1
      Qrew Cadet
      8 years ago
      Yes, its a data entry form, however, I am lost on how to construct it so that all activities and the categories they belong to (everything in the picture) can have a numerical value. Would I just create a table for every activity and every category and then relate them to one another?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      There will be a single table of Categories and a single table of Activities and then a middle or join table of Activity categories.  That is the table which will have two parents.
    • NirajShah1's avatar
      NirajShah1
      Qrew Cadet
      8 years ago
      I remember from my past QB knowledge that I have done this before, but its been awhile. So I create one table of the 10 activities, and one table of 10 categories, and both tables would be children to the parent intermediary table of activity categories? 
      I don't have access to my previous apps to see how I constructed this so I'm still a  bit lost.
      By doing the proposed method, the user would utilize the intermediary table and select an activity and select a category and then input the numerical value into that selection. This would seem to work.
  • NirajShah1's avatar
    NirajShah1
    Qrew Cadet
    8 years ago
    @Alex. Thanks! I thought about it but I need something where users can enter data for the middle cells. The summary report would be the end deliverable once data is entered.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    The only native way I can see would be to have a button to create the 100 blanks records and then use Grid edit to do he data entry.  But the data entry would be "vertical", not in a square format like that. 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Yes, this does sound like it needs a Many to Many relationship.  If you make that relationship, and can make a button to create those 100 records, then it will work.

    Of course, that will lead to the question of the most painless way to make those 100 records.

    One way would be to make a parent record called like Survey Cycle or whatever, inherently a data gathering effort is called.  Perhaps they are Semesters.  

    You could make the Parent as a template record by using excel to load up those 100 blank records, buy connecting them to the proper Record ID# Parents in the two parent tables.

    Then use the wizard in the Application management to make a button to import those 100 blank records into any new "Semester".