Days overdue not taking the year into account

[D4v3]

Hi all.

I'm new to this site so I apologise if this is the wrong place to post this.

My database tells me how many days are left before the due date and how many days overdue. This worked fine until the year changed. Now invoices that were due last year, are now saying 200 to 300 odd days till due instead of overdue. I can see that the problem is it still thinks it's the same year. For example. An invoice due on 31 December 2015 should be 26 days overdue on 26 Jan 2016 but instead it's about 339 days till it's due. I have looked and tried changing things but can not get it to work right. Any help would be much appreciated. Thanks in advance.   The code I have is....

Case (

Invoice::Paid = "Yes";

"Invoice Paid";

Invoice::Paid = "No";

Case (

DayOfYear(Invoice::Payment due) < DayOfYear ( Get ( CurrentDate ) );

"overdue by " & DayOfYear ( Get ( CurrentDate ) ) - DayOfYear ( Invoice::Payment due ) & " days";

 

(DayOfYear ( Invoice::Payment due ) + Contacts INVOICES CustID::Payment Terms Days) > DayOfYear ( Get ( CurrentDate ) );

DayOfYear (Invoice::Payment due) - DayOfYear ( Get ( CurrentDate ) )  &" days left";

 

(DayOfYear ( Invoice::Payment due ) + Contacts INVOICES CustID::Payment Terms Days)  = DayOfYear ( Get ( CurrentDate ) );

"Due Today"

);

"error"

)

Edited January 25, 2016 by D4v3
Forgot the year.

[Charity]

I think you can make it easier to get the days over due with this dropping that dayofyear stuff

Get(CurrentDate) - dueDate

if a negative it is overdue and be sure you make the calculation unstored

HTH

[D4v3]

Thanks for the reply, I will try that tomorrow and let you know how I get on.

[Fitch]

In other words, simplify your calculation above by removing every instance of "DayOfYear" and it should work as expected.

[D4v3]

So am I getting this right? should it look like this?

Thanks :-)

Case (

Invoice::Paid = "Yes";

"Invoice Paid";

Invoice::Paid = "No";

Case (

(Invoice::Payment due) <  ( Get( CurrentDate ) ) - dueDate;

"overdue by " & ( Get ( CurrentDate ) ) - due Date ( Invoice::Payment due ) & " days";

 

( Invoice::Payment due ) + (Contacts INVOICES CustID::Payment Terms Days) > ( Get ( CurrentDate ) ) - dueDate;

(Invoice::Payment due)  ( Get ( CurrentDate ) ) - dueDate &" days left";

 

( Invoice::Payment due ) + Contacts INVOICES CustID::Payment Terms Days)  = ( Get ( CurrentDate ) ) - dueDate;

"Due Today"

);

"error"

)

[dwdata]

Just worked this up in the Data Viewer:

Feel free to insert real fields where instructed:

Let([

a = Date(12,28,2015);    // Replace with Invoice Date field
b = 30;                           // Replace with Payment Terms Days field
c = a + b;                       // Due Date
d = Get(CurrentDate);    // Current Date
e = "Yes"                        // Replace with Paid field

];

If(c = "Paid", "Invoice Paid";

Case(
d > c, "overdue by " & d - c & " day(s)";
c > d, c- d & " day(s) left";
"Due Today")

)
)

Hope this helps!

[comment]

1. In which table is this calculation defined? I would have expected it to be in the Invoices table - so why are most of the referenced fields prefixed with Invoice:: as if they are coming from a related record?

2. What is the difference between Invoice::Payment due and dueDate?

[Fitch]

@D4v3 -- forget about dueDate, I don't think that's what @Charity meant.

@dwdata -- I was thinking along those same lines, but those kind of "a,b,c" variable names are a pet peeve of mine. When I set up a Let statement, I want to be able to come back to it the next day and not have to think about what I did. My version:

Let([
 today = Get( CurrentDate );
 dueDate = Invoice::Payment due ; 
 terms = Contacts INVOICES CustID::Payment Terms Days
    ];
 
Case (
  Invoice::Paid = "Yes";
  "Invoice Paid";

// else it's not paid

  dueDate <  today ;
 "overdue by " & today - dueDate & " days" ;

 ( dueDate + terms ) > today ;
  dueDate - today  & " days left";

 ( dueDate + terms ) = today ;
  "Due Today" 

) )

 

[comment]

4 minutes ago, Fitch said:

but those kind of "a,b,c" variable names are a pet peeve of mine.

I agree. But my list also includes variables that do nothing but rename a field.

[Fitch]

Naming things is hard.

[D4v3]

Thanks for all the comments..

I am very new to file-maker and database in general and have no programming skills so this is a very sharp learning curve :-). This database was already setup when i started to use it and i am trying to get my head around all this.

I have been reading a lot about file-maker but i am still at the novice stage, to say the least. I have started to make a new database to practice on but this problem hit me well before i was ready for it though, between this forum, you tube and a book i will get the hang of this but its going to take time. :-) 

i will put your comments into practices tomorrow and let you all know how i get on.

thanks again for the help and i apologize if i ask any silly questions.

 

 

[Lee Smith]

Hey D4V3,

It might interest you to know, that many of us got started developing in FileMaker for similar reasons and ways. Although FileMaker has a reputation for being easy to learn and use, if you go beyond a basic Rolodex file, the learning curve can get steep fast. In other words, the more you want out of your file, the more you’ll have to learn about its tools.  it’s actually not a bad thing to not had previous database developing experience. This way you not going to be influenced by how you learned other products tools and the ways they may do things.

If you haven’t read this article by Ocean West.  Anatomy of a good topic, please do so now.

By following the ideas expressed by Ocean West in his article, it should provide the necessary tools for asking a questions in the FM Forums.

1 hour ago, D4v3 said:

i apologize if i ask any silly questions.

No need to apologize, there are no such thing as silly questions.

 Lee

[Lee Smith]

Take a look at the Invoices.fmp12 example that came with your FileMaker Application.

It has a nice calculation for the Info field, Status Field and others.

Lee