Can anyone clarify this: Most of my trouble ...

[David_Hopkins]

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…

[Aleksi_Alkio]

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

[David_Hopkins]

@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]

@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… )

[David_Hopkins]

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”)

[Aleksi_Alkio]

Yeah… sometimes it’s good to go away from the box so you can see it again from another direction☺