Can anyone clarify this:
Most of my trouble comes from trying to figure out the proper reference format from where ever Im trying to call it.
Payment History[Date] [Payment History][Date] [Payment History].[Date] [Payment History[Date]]
These are all valid formats depending on where you are and what your trying to do… but, how do i know which format to use?
CURRENT ISSUE
I have a table Students with a Reference to the Payments table called “Payment History”.
I’m trying to find all students that have NOT paid this month… Im using the following query and I just cant seem to get the reference correct.
SELECT(
Students[Name],
AND(
Year(Payment History[Date])<>Year(Today()),
Month(Payment History[Date])<>Month(Today())
) )
This query will go into the Email Template.
anyway, sorry for the double post here…
Thanks for all the help so far everyone…
COUNT(SELECT(Students[Name], AND( Year([Date])=Year(Today()), Month([Date])=Month(Today()) ))=0
is the basic but then the payment needs to be in the same table. IF the payment is in another table, you should read the status like…
COUNT(SELECT(Payment History[KeyColumn], AND( YEAR([Date])=YEAR(TODAY()), MONTH([Date])=MONTH(TODAY()), [NAME]=[_THISROW].[NAME]) ))=0
@Aleksi_Alkio That did the trick… used this Expression in a slice on Students and got exactly what I wanted.
COUNT(
SELECT(
[Payment History][Name],
AND(
YEAR([Date])=YEAR(TODAY()),
MONTH([Date])=MONTH(TODAY()),
[NAME]=[_THISROW].[NAME]
)
) )=0
Again, it came down to trial and error on the format of
[Payment History][Name] vs Payment History[Name]
Thanks a ton my friend.
@David_Hopkins So… I need this same result for use in the Phone Field of an SMS Workflow task.
I was using your methodology, for building the expressions, and for the most part it’s working… but I cant seem to get it to work on this one…
Im working in WF against the Reminders Table. (key, Ttitle, Message, Valediction, Last Sent) … standard for sending an SMS on update of the Last Sent field.
I need the cell numbers to come from Active Students slice where Payment History returns count=0 for Month(Today)…
Something like this…
SELECT(Active Students[Cell],
COUNT(
[Payment History][Name],
AND(
YEAR([Date])=YEAR(TODAY()),
MONTH([Date])=MONTH(TODAY()),
[NAME]=[_THISROW].[NAME]
)
)=0 )
But… that throws a great looking error…
… contains an invalid expression ‘Unable to find column ‘Payment History’, did you mean ‘Payment History’?’
(why, yes…
Yes I did mean Payment History… )
Ok… I don’t know where my head is at some days… but I walked away for a few mins… While I was clearing my head i realized that I have a Slice called “Missing Payments This Month”. So, I just set my WF to:
SELECT(Missing Payments This Month[Cell], [Status]=“Active”)
Yeah… sometimes it’s good to go away from the box so you can see it again from another direction☺
User | Count |
---|---|
35 | |
35 | |
28 | |
23 | |
18 |