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

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
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. 
Photo of Niraj Shah

Niraj Shah

  • 214 Points 100 badge 2x thumb

Posted 10 months ago

  • 0
  • 1
Photo of Avi Sikenpore

Avi Sikenpore

  • 532 Points 500 badge 2x thumb
@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.
Photo of Niraj Shah

Niraj Shah

  • 214 Points 100 badge 2x thumb
@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 QB in a few years so pardon my unfamiliarity.
Photo of Alex Wilkinson

Alex Wilkinson

  • 1,006 Points 1k badge 2x thumb
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").
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
I think that the OP wants a data entry form.
Photo of Niraj Shah

Niraj Shah

  • 214 Points 100 badge 2x thumb
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
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.
Photo of Niraj Shah

Niraj Shah

  • 214 Points 100 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
You have the setup backwards. The parents are the two tables of the Activities and the Categiries.

The join table of Activity Categiries is the child of both.
Photo of Niraj Shah

Niraj Shah

  • 214 Points 100 badge 2x thumb
Ahh thank you! This worked like a charm!
Photo of Niraj Shah

Niraj Shah

  • 214 Points 100 badge 2x thumb
@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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
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. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
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".