Expired date calculation

[alex005]

Hi,

What I'm looking for help this time around is how to generate a report showing the clients that have not been to for a while.

I have a seperate table in my database for this and in it are,

PtId (a lookup value from another table), CurrentDate (Auto enter calculation)

LastVisit (a lookup value from a another table (which is displayed within my database within a portal)

I guess the way I'd like it to work is that when the button is pressed a script is called into action that will get todays date, look at the last visit dates and return the result of all the records that fall within that criteria in the form of a report.

[alex005]

As it stands at the moment I have the layout and button configured. I have a script in place which has a step in it like this ...

Insert Calculated Result [select; ExpiredPatients::CurrentDate - ExpiredPatients::LastVisit]

When run the process doesn't show what I'd expect ( I expected to get nothing : ) it shows all the records regardless of when they last came in.

Should anybody need any clarification I can help, (once someone replies to my other post explaining what I'm trying to do to me so I can understand it my self ... Joke that was a poke fun at me by me)

[alex005]

Ok,

I think I'm barking up the wrong tree with the calculation.

I can formulate what it is I what to do like this ... currentdate - lastvisitdate = x, if x > y (y is the period of time I want to be able to change in a dialog box) then send the results to a layout.

[alex005]

State of play now is that ...

1) I have a way in place that almost does what I want it to. It's adapted from a script that has been given to me in order to find who's birthday is coming up in the next 7 days. The adaptaion is rather than +7 in the calculation it does -7 (obviously this will only find records that have not been in the 7 days) and the field references have been changed to look in the right places for the revised information, but I'm still getting all the records. I think that my failing is in that the portal field is not being looked at properly.

2) I'm developing a complex as at the time of writing 43 people have viewed this post and I'm the only one contributing ... have I found something that FM can't do and your all afraid to tell me.

:

[alex005]

Hi Alex005,

Thanks for the reply ...

Another thing I've noticed that I've done wrong, I've written the script to look at a portal row. I assumed that it would just look at the last row, but I guess this is not the case.

[mr_vodka]

Ok I'll bite...

I think that the reason there has been no other replies is because we dont really have too much info to go on here regarding how your tables are setup.

Find records that are earlier than a few days back can be fairly simple. However, how to effectively provide advice to your situation without further info is not.

You can use an unstored calc to figure out when the last date each client's appointment was but searching on a unstored calc can take a while sometimes. Otherwise, if its stored in a field somewhere you can search for a particular date much faster. It is up to you to decide which route to go.

Here is how to find records for the past lets say 60 days in a date field.

Enter Find Mode []

Set Field [ YourDate; Get (Current Date) - 60 & "..." & Get ( CurrentDate ) ]

Perform Find []

or

Set Field [ YourDate; "<" & Get ( CurrentDate ) - 60 ] can be used in the set field step

[alex005]

Hi mr_vodka,

Many thanks for the reply, It does now seem obvious that while everyone (other than me) on FMForums is at genius level you don't all possess the ability to read my mind, and not giving enough information is my first mistake.

I'm not to sure of the correct naming convention, but here goes. I have a table called PatientData and within that lies another table called PatientPayments (shown via a portal). Now in the PatientPayments there is a column that displays the last time a payment was made. What I'm trying to do is find the linked records for payments that were made more than say 60 days ago. I do also have a date field in the PatientData table that is for the LastVisit made and this is a date selection field. I think that the easiest way to do this is via the LastVisit date field. I also have a report ready to use that I want the results of the visit sent to this is called ElapsedDates.

Most humble apologies for not giving you all more information earlier. Thanks mr_vodka for pointing this out.

[mr_vodka]

I am unfortunately still not following. Are you trying to find people that havent been there in 60 days or payments that are overdue?

If you are trying to find payments that are overdue, I would start off by adding an invoice date and a payment due date. Then you can find all those that are overdue.

Please let us know what you are looking for.

[alex005]

I see where the confusion lies. I want to find out who hasn't been for a period of time. I mention the payment date because this is a date that they were last in. It transpires that using this PaymentDate is not a good way of working out this calculation as sometimes people will make a payment over the telephone and not when they come for an appointment. I do also have a date field which is used to record when they have come for their appointment, but what I haven't been able to do is have the field automatically infilled with a Get ( CurrentDate ) and then store this until the next time they visit. What happens when I run the script is that the date is infilled with the current date, stored but when I then scroll past that record when I'm checking the data the field updates itself, therefore implying that they visited on the day that the record was last browsed.

I'm not concerned with missed payments, I'd just like to know who hasn't been for a while. The way I was doing this was by using the last time they made a payment, assuming patients only ever paid at the same time as attending an appointment.

[comment]

Assuming that you have a Patients table and a Visits table, related on PatientID; and that each visit record has a field indicating the date of the visit (not sure what your issue is with that):

To find patients that DID NOT visit during a given period, find those that DID and switch the found set to Show Omitted Only.

[alex005]

I've been thinking to use a script that was to be used to find birthdays in the next 7 days to find birthdays that were 7 days ago. Are the principles not the same? The script I use to find the future birthdays is attached. I was thinking to just insert a -7 where the +7 is in the script. I think that the table references are sound because the fields appear in the layout referenced in the script.

I have tried the above ways but not been able to get them to work.

ExpDate.pdf

[alex005]

It's a bit "chunky" in operation, but I got it to work.

I used the birthday script attached above, but deleted everything above - Set Error Capture [On] - and ammended the Set Field to - "<" & Get ( CurrentDate ) - 4.

The 4 is just to test my sample data.

Just need to refine its operation now.