Hi,

 

I have a field called Invoice send date, and I would like a reminder to popup within 30 days of that invoice send date field, this is what I have so far in the script and is not working as it takes current date into account.

 

• Go to Layout [ “Invoice” (Invoice) ]
• Enter Find Mode [ ]
• Set Field [ Invoice::Invoice send date; Get ( CurrentDate ) & "..." & Get ( CurrentDate ) + 30 ]
• Set Error Capture [ On ]
• Perform Find [ ]
• If [ Get ( FoundCount ) > 0 ]
• Show Custom Dialog ["Invoice Reminder"; "Invoice is overdue, please check if it has been paid"]
• Else
• Show All Records
• Install OnTimer Script [ ]
• End If
• Install OnTimer Script [ ]

 

any help would be much appreciated!

and is not working as it takes current date into account.

 

That's not the problem – but you're looking into the wrong direction. While we would like to have our future invoices already paid, I'm afraid that's not gonna fly … use

Set Field [ Invoice::Invoice send date; "= " & Get ( CurrentDate ) - 30 ] // or "<", depending on how (soon) you handle unpaid invoices

You should probably add another step like Set Field [ Invoice::paidStatus ; 0 ] , or something to that effect, or you will see all the old invoices, regardless of status.

You should definitely look for invoices older than 30 days that have not been paid. Looking for invoices that are exactly 30 days old is not a good idea, because unless the script runs every day (including weekends and holidays) some invoices might slip detection. Not sure what the Install OnTimer Script [ ] is supposed to do here; IMHO, It's best to make this a part of your startup script.

thanks for all the help I am indeed looking for invoices older than 30 days, the trouble with using less than or less than equal to is it only shows invoices for this month and not the month before :S I also applied that script and it wasn't working properly, however using the '-' only shows invoices that are exactly 30 days old which I dont want. 

 

Ps. oh and Eos I have a status validation before the script runs, didn't show it here

 

complicated database already have it as part of my startup script

the trouble with using less than or less than equal to is it only shows invoices for this month and not the month before

 

This:

Enter Find Mode [ ]
Set Field [ Invoice::Invoice send date ; "<" & Get ( CurrentDate ) - 30 ]
Perform Find [ ]

will find all invoices older that 30 days. However, if you don't add a criteria for unpaid, it will always find something (like invoices from 5 years ago?).

works but only finds invoices that are exactly older than 30 but not within, sorry

exactly older than 30 but not within

 

Sorry, I don't understand what that means. Why don't you point out a specific invoice date that should have been found but wasn't - or the other way around.

for example if I select the invoice send date as 28/12/2013 if it not found as it should be because we r using less than, but if i select the invoice date as 1/1/2014 it is then found till 31/01/2014 which it shouldn't as it after 30 days from the current date.

 

what i mean by exactly is that if I select the date as 22/12/2013 and use your script it find the invoice that is exactly 30 days less than todays date, but not in between, hope u understand what I mean.

hope u understand what I mean.

 

No, i am afraid I don't. The script I suggested finds all invoices that are older than 30 days, relative to current date. There is no date to select. If today is 21/1/2014. then all invoices in the range from 1/1/0001 to 21/12/2013 will be found.

 

If you are experiencing something different, then you need to check your implementation - esp. that the 'Invoice send date' field is really a Date field and that it contains valid dates.

 

If that's not what you want, you need to explain your requirement in more detail.

The invoice send date is indeed a date field, but if I use this script 

 

Set Field [ Invoice::Invoice send date; " = " & Get ( CurrentDate ) ≤ 30 ]

 

it only finds invoices in range from 1/1/14 to 31/1/14

 

as you can see in the image above, the date entered in invoice send date is 26/12/2013 but is not found :s

Set Field [ Invoice::Invoice send date; " = " & Get ( CurrentDate ) ≤ 30 ]

 

 

Set Field [ Invoice::Invoice send date ; "<" & Get ( CurrentDate ) - 30 ]

 

Do you see the difference?

Do you see the difference?

 doesn't work as it only finds invoices that are exactly 30 days old please see below, this is not what I am looking for, I am looking for the invoices to be found within 30 days of invoice send date, thats why I was using the other script.

 

Does this work for you?

FindOverdue.fp7.zip

haani, there is a critical part missing in your screen shot.

 

You do not actually display the "Perform Find" step.

 

Perhaps it still has the "[Restore]" option selected?

It would be better to display your entire script.

 

Attached is a mod to Comment's file, converted to fmp12 format and with a brief pause to reveal the formatting of the find step.

His script design DOES work.

FindOverdueB.fmp12.zip

that doesn't work either it says 57 records found where it should only find invoices between 22/12/2013 till 21/01/2014

 

but I have found a workaround for it I created an invoice due date (calculation) whenever send date is entered a new due date is entered into the due date field then I changed my script to find invoice that are within 30 days of that due date

 

it should only find invoices between 22/12/2013 till 21/01/2014

 

That is definitely not "invoices that are older than 30 days". But if that's really what you want (and I am not quite sure why you would want that), then you just need to change the direction of the comparison :

Enter Find Mode [ ]
Set Field [ Invoice::Invoice send date ; ">" & Get ( CurrentDate ) - 30 ]
Perform Find [ ]

 No additional fields are necessary.

Edited January 21, 201412 yr by comment

I think u miss understood me or I wrongly wrote it, I meant within 30 days from the invoice send date, I have it working now with the workaround I posted above, thanks for your help though, much appreciated.

If you have trouble explaining what you are trying to accomplish, you can speed up the process sometime by providing a copy, or a demo of your files.

To attach a file, just follow the steps here

BTW, you can copy a script directly by Printing it to Preview and then copy and Pasting into your post here.

sure thanks, got it working in the end, thanks for helping me out everyone.