Add sub-categories in a table with an option to change them

[BismaIftekhar]

Hello everyone,

I want to create a table with a certain row having sub-categories. The issue is they are not fixed and the user should have an option of changing these sub-categories. Is this possible? Please find below, for example the teachers should be able to change the courses and the associated percentages.

Can someone please guide me what to do?

[melohagan]

Hey @BismaIftekhar

This is an interesting use-case - thank you for the diagram!

It was fairly complicated, but I was able to come up with a solution using the community Super Table by @poirazis in combination with the Budibase JSON data type.

---

Step 1 - Create the Data table

Create a new table called Scores to keep track of the marks for each attribute, i.e. Attendance, Behaviour, Courses.

This tutorial won't cover relationships, but most likely you would want to link this table to your Teachers table.

Create a JSON type column called Attributes. Click Open schema editor and provide the following JSON schema:

{
  "name": "Courses",
  "subcat1": "Math",
  "submarks1": 20,
  "subcat2": "English",
  "submarks2": 30,
  "subcat3": "Language",
  "submarks3": 20,
  "subcat4": "Technical Skills",
  "submarks4": 10,
  "marks": 100,
  "hasSubCategories": true
}

The Form tab should appear as follows:

Make sure to Save Column. (You'll need to click it twice).

You will notice from the schema that we have hard-coded four sub-categories. This is a bit of a limitation in that you need to know the number of attributes ahead of time. There is an open feature request for handling dynamic form inputs: #5658

Also note the hasSubCategories property. This will determine if the sub-category fields should be shown or not.

Step 2 - Adding the Super Table

Make sure you have imported the SuperTable plugin as well as the companion Super Column and Super Cell plugins.

More information can be found in the custom plugins docs.

Next add the necessary components to your screen:

1. Data provider (for the Scores table)

2. Nest the Super Table - make sure the ID Column is set to _id
   

3. Nest three Super Columns. One for the name of the attribute, one for any sub-categories, and one for the marks.

4. Nest a Super Cell within the attribute name column. Provide the Value binding: {{ Attributes Column.Cell Value.name }}

5. Nest a Super Cell within the sub category column. Leave the Value field blank.

6. Nest a Super Cell within the marks column. Again leave the Value field blank.

Step 3 - Adding the form fields

With the table structure setup, we need to add in the form fields to allow the sub categories and marks to be edited.

Within the sub category column component, nest a Form component and select the Scores table as the Schema.
Next nest a Container component, and within it a field group component and add four Text field (one for each sub category).
Also add an Icon component to the container for saving the form (we'll implement the click action later).

Assign a field for each from the dropdown, for example:

Also assign the correct default value. For example: {{ Sub Category Column.Cell Value.subcat1 }}

Another step we need to do is only showing the field group and save icon if hasSubCategories is set. We do this through Conditional UI.

Click on the container component, and select the Conditions tab from the settings panel. Click Configure conditions and setup the following condition:

With the sub categories done, follow similar steps for the marks form fields. There are a couple of differences: use Number fields instead of text fields, and within the container have one field called Marks, and below a field group for the sub-category marks.
The conditional UI will be the same as above, but apply it to the field group instead. The Marks field will also need the condition, but inverse the boolean flag to false.

At the end your component tree should look similar to the following:

Step 4 - Saving the fields

The final step is to make the save icons update the existing JSON with the newly entered data. Click on the Save icon component for the sub-categories form and click Define actions.

Use the Add Column button to add the _id field (indicate which row should be updated) as well as the Attributes JSON field:

The JavaScript binding is as follows:

return {
	...$("Sub Category Column.Cell Value"),
	subcat1: $("Sub Form.Fields.Attributes.subcat1"),
	subcat2: $("Sub Form.Fields.Attributes.subcat2"),
	subcat3: $("Sub Form.Fields.Attributes.subcat3"),
	subcat4: $("Sub Form.Fields.Attributes.subcat4"),
}

Here we are taking the existing attributes data and updating the subcat 1-4 fields with the form fields.
Make sure to Save!

The Save Row action for the Marks form will be very similar, just different form fields:

JavaScript binding:

let cellVal = $("Marks Column.Cell Value")

if (cellVal.hasSubCategories) {
	return {
		...cellVal,
		submarks1: $("Marks Form.Fields.Attributes.submarks1"),
		submarks2: $("Marks Form.Fields.Attributes.submarks2"),
		submarks3: $("Marks Form.Fields.Attributes.submarks3"),
		submarks4: $("Marks Form.Fields.Attributes.submarks4"),
	}
}

return {
	...cellVal,
	marks: $("Marks Form.Fields.Attributes.marks")
}

There's a little but of logic here to determine if the marks are for sub-categories or the main category.

Result

App export:
Super Table Matrix-export-1685629864687.tar.gz

[BismaIftekhar]

Apologies for the late reply. Firstly, thank you so much @melohagan for such an elaborate response. Did not have the time to test it yet but the results seems like what I am aiming for.
Thank you. again :)

[BismaIftekhar]

Hello @melohagan another question please if you could help.

"How to retrieve data from a public form with dynamic entries?"

So,

Step 1:
The teacher creates entries in a Budibase Table that are dependant on classses for example. So Teachers of 2 Blue can create their own assessment criterias such as arithmetic, algebra etc.

Step2:
The students of 2Blue log in and grade themselves in those.

Question:
How to retrieve data in Budibase (the criterias and their marks) from the filled form by students considering they are not constant?

Once that data is written in the table, teachers should then be able to grade them too.

Any help would be appreciated.
Thank you