Discussions

Expand all | Collapse all

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

QuickBaseCoach Dev./Training01-15-2018 22:31

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

    Posted 01-15-2018 20:57
    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. 


  • 2.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-15-2018 21:05
    @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.


  • 3.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-15-2018 22:35
    @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.


  • 4.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-15-2018 21:21
    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").


  • 5.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-15-2018 22:31
    I think that the OP wants a data entry form.


  • 6.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-15-2018 22:47
    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?


  • 7.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-15-2018 22:55
    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.


  • 8.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-16-2018 13:50
    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.


  • 9.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-16-2018 14:14
    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.


  • 10.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-16-2018 23:43
    Ahh thank you! This worked like a charm!


  • 11.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-15-2018 22:37
    @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.


  • 12.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-15-2018 22:40
    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. 


  • 13.  RE: I have multiple activities and multiple categories, i need a form that allows entry of this data

    Posted 01-15-2018 22:53
    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".