Jump to content

Days overdue not taking the year into account


D4v3
 Share

This topic is 2345 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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 by D4v3
Forgot the year.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Newbies

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"

)

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

) )

 

Link to comment
Share on other sites

  • Newbies

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.

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2345 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.