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โฆ
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
@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.
@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.
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.
@Philip_Garrett_Appsh Thats exactly the path I chose. I think the problem Im having at this point is getting the slice query right.
Try:
COUNT(SELECT([Payment History][key], Year([Date])=Year(NOW())-1))>0
More important, here is the approach I use to write expressions.
I start with the table I want to interrogate. In this case it is the Payments table.
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.
Year([Date])=Year(NOW())-1
COUNT(SELECT([Payment History][key], true))
I tested it and it worked.
COUNT(SELECT([Payment History][key], Year([Date])=Year(NOW())-1))
I tested it and it worked.
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.
I then make the expression richer in very small steps.
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.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |