Creating an expression to Track the History of Another Column (List has Elements of Mismatched Types)

[jthurston]

Hello,

We have a CLASS column (Type=REF) that tracks which class a student is currently in. I want to create a CLASS HISTORY (Type=EnumList) column that will show a list of all previous values in the CLASS column, and the date of change. So for example, maybe the CLASS value is Smith.Lit101, and the CLASS History would say Smith.Lit101 8/23/21, Harris.Mth200 4/15/21, Piper.Phil101 3/12/20, etc.

I don’t think that the “Change” column types will work for this (right?). So I’m trying to create a bot. The trigger is set to occur when a change is made to the CLASS column. Here’s my attempt at the expression used in the process part of the bot:

SORT( [Class History] + LIST([Class], Today()) - LIST(""), TRUE)

So, begin with the current CLASS HISTORY value, then add another list item that contains the current value of the CLASS column + today’s date.

AppSheet won’t accept this formula, since it says the list has elements of mismatched types. I’m not exactly sure what the cause of the mismatch is, or how to get around it. Is there a way to tell AppSheet to allow mismatched elements? Or is there a better way to do this?

Thank you!

[WillowMobileSys]

I would recommend a child table named CLASSES of which has columns of CLASS, DATE, LOCATION and any other data you might want to track.

You would add a new Class row each time the student starts a new class and mark the others as “Completed” or some such setting.

You can then divide the list of classes into an Active list and a History - each shown in their own child table under the Student.

This is easier to manage and the structure will fit future growth better - such as tracking multiple active classes or further sorting of the history by Year, etc.

[jthurston]

Hi John,

Thank you very much for your answer. We actually have a CLASSES table that has that type of information. However, it’s a parent table, and STUDENTS is the child. We use the CLASSES table to allow teachers to get reports with lists of all their students, and info they might need for each student. This is also how we assign students to a class (The CLASS column is a ref column that connects to the CLASSES table, and lets users choose one of the class options from a dropdown).

I’m figuring this out as I go, but I thought since there are many students for each class, it made sense to have CLASSES be the parent. I wonder if it is possible to continue using the CLASSES as a parent table to congregate information about each class, but also use it for the purpose you’ve described.

[WillowMobileSys · Accepted solution]

Justin_Thurston:

We actually have a CLASSES table that has that type of information. However, it’s a parent table, and STUDENTS is the child.

I see. Now that I know your structure is a bit more sophisticated, I think what you need is an ENROLLMENTS table that marries a Class and a Student together. Then you can get the related list of Students for each class but also the related list of classes for each Student.

[jthurston]

So with the ENROLLMENTS table, is that basically an intermediary table that allows for a many-to-many relationship? Would this sample app be the best place to start to figure out how to accomplish this?:

https://www.appsheet.com/samples/Create-orders-that-have-many-items-Track-items-that-have-many-order...

I’m looking at the sample app now. And it makes me think that the thing to do is have ENROLLMENTS be a child of both STUDENTS AND CLASSES, with a REF column that links to both tables. Is that right?

And is there a way to have this ENROLLMENTS table automatically update when a user chooses a class in the class column? Or is it best to just drop the CLASS column altogether, and have users use enrollments instead?

Thank you!

[jthurston]

It works! I was able to create a bot that triggers on changes to the CLASS column, and that automatically creates an entry in the ENROLLMENTS table.

Thanks so much.

[WillowMobileSys]

Justin_Thurston:

So with the ENROLLMENTS table, is that basically an intermediary table that allows for a many-to-many relationship?

Yes! But it is an important component to the system.

Justin_Thurston:

And it makes me think that the thing to do is have ENROLLMENTS be a child of both STUDENTS AND CLASSES, with a REF column that links to both tables. Is that right?

Yes, however, a child can only have 1 parent in AppSheet - i.e. only one column can be set with the “Is part of” flag turned on.

I think if of Enrollments as being related more to the Student so I would create the Parent/Child between these two.

ENROLLMENTS would have a Class Ref column which means AppSheet will automatically provide a “Related Enrollments” virtual column in the CLASSES table. Perfect for identifying all the students enrolled in the class!

Justin_Thurston:

I wonder if it is possible to continue using the CLASSES as a parent table to congregate information about each class, but also use it for the purpose you’ve described.

I’m not sure what you mean by “parent”. But, CLASSES is definitely a valid, important table.