Help - Listing students who abandoned

[Aristotelis_T]

Hi, we are using appsheet to keep track of student history and I need to create a view/report with the students that have abandoned the classes (have no payment for 2 months).
I tried starting from a virtual column to build it inside-out but, I am simply… lost, sorry! Not even sure which approach to use (new view based on new slice, new table, virtual columns report… ??)

The scheme is simple: we have a Students table and a Payments table.
The key of the Payments table is a concatenation of the columns
» Course (name of course, unique string),
» Month (which holds the day 1 of the month of the respective payment)
» ContactID (ref to the ContactID column which is the key of the Contacts table)

How do I go about building this?
Any suggestions would be greatly appreciated

[LeventK]

@Aristotelis_T
How the non-payment status should be distinguished? Is it 2 successive payments or at least 2 payments even distinct? Which column you have in your column structure to filter the payment data?

[Aristotelis_T]

Hi @LeventK
The logic I was considering for the ‘abandonees’ was something like:
last “Month” date between 35 and 96 days ago.
(Month holds the day 1 of the month of the respective payment)

[LeventK]

Aristotelis_T:

last “Month” date between 35 and 96 days ago.
(Month holds the day 1 of the month of the respective payment)

@Aristotelis_T
Do apologize but I’m a bit lost in your statements. Provided we consider today as 4/29/2019, does your [Month] column have the value of 3/1/2019 or 5/1/2019? And for that “…35 and 96 days ago…” statement, do you wish to compare the day diff as per today or respective to the payment date of any particular month?

[Aristotelis_T]

@LeventK, sorry if I wasn’t clear.
And also a small correction: it is between 65 and 96 days ago (not 35).
The table holds info of payments.

Example1:
Let’s take for example a payment of March made on 17th of March: the Month column holds the value 1st of March.
Being today 29/04, the 1st of March is less than 65 days ago, hence the student is not considered that has abandoned yet and should not be part of the view/report.

Example 2:
Payment of February made on 11th of February
“Month” value: February 1st
Difference to 29/04: 28+31+29= 88
88 is greater than 65, hence the student should appear in the abandoned list/view/report.

Hope it is clear now

[LeventK]

@Aristotelis_T
Thanks for the detailed explanation. However, I still couldn’t understand one thing:
In both of your examples, though the payment dates were due (March 1st / March 17th and February 1st / February 11th) but payment were done in both, why have you considered the 1st example as not abondened but the 2nd one as abondoned??

In your initial statement, you had said:

Aristotelis_T:

Hi, we are using appsheet to keep track of student history and I need to create a view/report with the students that have abandoned the classes (have no payment for 2 months).

so you are actually seeking for non-payment statuses only. Sorry but still not getting there

[Aristotelis_T]

Hi @LeventK, thanks for your persistence
The school has a social character. The payments, though small, they are monthly.

We are in April, so:
» if a student has not paid since February (i.e. paid the month of Febr but not paid March or April) then he is flagged as absent/not continuing/abandoned.
» if a student has paid the month of March (although he has not paid April yet), he is not flagged as such.

In practice, the 65 days limit allows for a buffer/margin before a student is flagged as abandoned.
Did I manage to make it clearer now?

[LeventK]

@Aristotelis_T
Do apologize for bothering you with my Qs but I now have the idea. Let me think about this a bit and then I’ll get back to you with a solution.

[LeventK]

Aristotelis_T:

The scheme is simple: we have a Students table and a Payments table.
The key of the Payments table is a concatenation of the columns
» Course (name of course, unique string),
» Month (which holds the day 1 of the month of the respective payment)
» ContactID (ref to the ContactID column which is the key of the Contacts table)

@Aristotelis_T
I have one last question about how you are referencing the payments table with the students table. You had noted that you have a [ContactID] column which is ref to the Contacts table but does Payments table OR Contacts table contains any column that we can reference to students? For example [StudentID] etc. And I assume that you have some columns like [Payment Status] and/or [Payment Date] in your Payments table.

[Aristotelis_T]

Hi @LeventK, thanks for your atitude and persistence.
The scheme is quite simple actually.
Students are contacts that have payments
There is no separate “Students” table.

Yes, there is a Payment Date (with the date of the payment) and a Month column with the 1st day of the month paid (for example on 15 of April (=payment date) a student paid the month of May (Month = 1st of May of 2019).

There is no Payment Status: Simply, if there is a payment entry/line/record, then that month for that course is paid. If there is no payment entry, it is not paid.

Obviously this may not be the best or most complete of schemas.
Hope I managed to comunicate it clearly.
Happy to clarify any more doubts.

[LeventK]

@Aristotelis_T
Provided I have understood you correctly, below expression in a Virtual Column set in the Contacts table, will populate the abandoned status for each contact record:

IFS(
	AND(
		COUNT(SELECT([Related Payments][Payment Date],ISBLANK([Payment Date])))>=2,
		TODAY() - ANY(SELECT([Related Payments][Payment Date],[_RowNumber]=MAX(SELECT([Related Payments][Payment Date],AND(ISNOTBLANK([Payment Date]),NOT(MONTH([MONTH])=MONTH(TODAY()))))))) >= 65
	),"ABANDONED"
)