Here is another confusing situation for me. ...

AppSheet AppSheet Q&A
1

Here is another confusing situation for me.

I have 2 tables, Students & Payments with a reference key of Name

I’m try to build a report template that will pull all Students and their payments for a certain year.

I want the report to look like this…

Name 1

payment 1 - date

payment 2 - date

payment 3 - date

Name 2

payment 1 - date

payment 2 - date

payment 3 - date

Name 3

payment 1 - date

payment 2 - date

payment 3 - date

I’ve built something like this… but it’s not working.

<<Start: OrderBy(SELECT(Students[Name], [Status]=“Active”), [Name])>>

[Name]

<<Start: OrderBy(SELECT(Payments[Name], AND([Name]=Students[Name], Year([Date])=Year(Today())) ), [Date])>>

[Payment] - [Date]

<> <>

I’ve also tried to use Payments as the primary and nest the call for student info … but that also didnt work right.

Does someone have an example of this?

or suggestion?

Thanks again…

2

@David_Hopkins

I think you already got this working using the Start expression:

<<Start: OrderBy(SELECT(Payments[key], AND([Name]=[_THISROW].[Name], Year([Date])=Year(Now()))), [Date])>>

Here is the Start expression I would use:

<<Start: OrderBy(SELECT([Payment History][key], Year([Date])=Year(Now())), [Date])>>

I have updated the Start Expression documentation to describe how to write a Start expression having a reverse reference with a condition. See topic “Reverse Reference with a Condition” in this article https://help.appsheet.com/behavior/workflow-sending-email/start-expressions-in-embedded-templates

3

@Philip_Garrett_Appsh I see how using the reverse reference negates having to use the condition of name=name. It seems to make the report generation faster as well.

Thank you for pointing that out … I have a few more reports to write, and I’ll be using this format in the future.

4

@Philip_Garrett_Appsh On a related note, any suggestion how I could only show Students that made payments last year?

I’ve started with All Students… and then pulled payments associated with those students.

But not all students were there last year, so I’d like to get “All students that made a payment last year” then get all payments associated with them.

5

@David_Hopkins

I would create a slice over the Students table that only contains students who made a payment last year. By using a slice, you divide the problem into two steps.

In step one you ensure the slice contains exactly the records you want.

In step two you run the report over that slice.

6

@Philip_Garrett_Appsh Thats exactly the path I chose. I think the problem Im having at this point is getting the slice query right.

7

@David_Hopkins

Try:

COUNT(SELECT([Payment History][key], Year([Date])=Year(NOW())-1))>0

More important, here is the approach I use to write expressions.

  1. I start with the table I want to interrogate. In this case it is the Payments table.

  2. I then add a virtual column to that table to help me debug the first part of the expression. In this case I want to determine if a payment happened last year. I wrote this expression:

Year([Date])= 2017

I tested it and it worked.

  1. Next I replace 2017 with “Year(NOW())-1” and got this expression:

Year([Date])=Year(NOW())-1

  1. I then went to the Students table and added a virtual column to see if I could count the payments he student had made regardless of the year. I wrote this expression:

COUNT(SELECT([Payment History][key], true))

I tested it and it worked.

  1. I then combined the two expressions so I both count and filter on payment date. I wrote this expression:

COUNT(SELECT([Payment History][key], Year([Date])=Year(NOW())-1))

I tested it and it worked.

  1. I then compared the result of the expression to zero. This returns “true” if the student payed last year. I wrote this expression:

COUNT(SELECT([Payment History][key], Year([Date])=Year(NOW())-1))>0

I tested it and it worked.

In general: 1. I create expressions by starting with the simplest expression I can write.

  1. I then make the expression richer in very small steps.

  2. I work from the inside out. I write the inner most expression first. 4. I create temporary virtual columns and use the expression wizard to create and debug each step in my expression. 5. I never put an expression in a workflow template without verifying it as I described above. Debugging an expression by entering it directly in a workflow template is much too hard so I always debug it as described above.