Dates ... Date Calculation

tallboy755 · August 19, 2003

Here is what i am looking for ... I have a field "Field A" that auto enters the Creation Date. Another field "Field B" that i need to have the current date in (today the field would have 8/19/03 and tomorrow 8/20/03 so on so forth). I need a field "Field C" to subtract the Creation Date "Field A" from the Current Date "Field B" to give me the number of days that the record has been the database ...

Please help ...

Logixx · August 19, 2003

A calc Today-Field_A with result number will do the job.

tallboy755 · August 19, 2003

No FREAKING way ... I had no idea that the word "TODAY" worked in calculations ... That has to be the most helpful thing that i have ever learned about filemaker ... GEEEZ I must be really elementary at this!!!

Lee Smith · August 19, 2003

Don't get to excited, it is better to use Status(CurrentDate) instead of Today.

Kurt Knippel · August 19, 2003

Today is a stored and indexed calculation, which is only ever refreshed when the file is opened in a normal copy of Filemaker. For DBs with lots of records this can take a long time. It is better to use the dynamic Status ( CurrentDate ) instead. Today is an old function left in for backwards compatibility.

Logixx · August 19, 2003

I just quoted Today on (say) educational purpose.

I obviously agree that Status(CurrentDate) should better be used in large files.

Fenton · August 20, 2003

To further confuse the issue, which is not so simple as you would first think, Status(CurrentDate) is only accurate if it is unstored.

If you want to be able to actually search for records by date in the file, there are 2 methods.

The first, and recommended method is to forget about storing the # of days in a field. Use a Find script to isolate the set of records instead.

In the Find script set the date to a number of days less than Status(CurrentDate), then Find. Status(CurrentDate) is always accurate when used in a script.

The records will naturally show in order of date created.

But this does not actually show the # of days. If that is critical, then you have to have a stored Status(CurrentDate) field.

Use a Loop or Replace operation to check/set it into a field in all records. This should be tied to some common script run whenever a file is accessed.

It shouldn't just be the startup script, unless you're positive people will close the darn files at the end of the day.

For example:

If [

-Queue- · August 20, 2003

Although for Tallboy's needs, it doesn't seem that a field containing the current date is necessary. Simply using Status(CurrentDate) - CreationDate as the calculation for number of days should suffice.

Lee Smith · August 20, 2003

Has anyone else other than me noticed that tallboy755 was happy as hell with Today, and hasn't been heard from since.

Lee

BobWeaver · August 20, 2003

He'll be back once he gets 400,000 records in his file and has to wait for the Today function to recalculate when he opens it.

Lee Smith · August 20, 2003

ROTFLMAO

tallboy755 · August 20, 2003

Im Back... Morning everyone ... OK the script Status(CurrentDate) is not working for me ... like noticed above when "Fenton" said that it will only work if its insorted ... Well I need it sorted ... does anyone have any better ideas than that stupendous script? ... I am always open for ideas ...

I will tell you a little about my database .. Maybe that will give someone a little better idea of what im looking for.

I run a photo lab and studio... In my database I enter a record to show it in production for my lab. I have an autoenter field that enters a "1" for each new record. At any time there may be 20-50 "Jobs in Production - JIB". When I finish a job it still saves the job in the current database, it just switches the (autoenter) 1 to a 0. So every day i run a search for all the records that have a 1 in the autoenter field. That gives me all the JIB. At which time I sort it by the the number of days left that it can be in production... Each different type of photography has a set number of days that it should be In Production. On this layout I have it tell my the number of days that i have left to finish the job. Furthermore I have it color coordinated.

RED = Past Due

YELLOW = 2-0 Days Left

GREEN = >2 Days Left

I will attach a copy of the file if anyone wants to view it..

In_Production.zip

Logixx · August 20, 2003

tallboy755 said:
At any time there may be 20-50 "Jobs in Production - JIB".

The are only a few users that need huge databases, in which I'd see 2 groups:

- Professionals in some specific businesses (directories...)

- Those who don't understand it's often convenient to clean and export old data. In my solution, I have a file which is a kind of 'engine' with a lot of calcs (eventually with Today ). As soon as a record is completed, it is exported to another file with only 'static' fields. This process maintains the calculating 'engine' very small therefore powerful.

tallboy755 · August 20, 2003

That is a possibility ... However I am in the dark a little about the whole export thing ... would I have to export to a data file and then import into my Out Of Production database... If that is what I end up doing then your right with only 20-50 jobs in production ... the today function would work fine...

However I still am open for other ideas

Logixx · August 20, 2003

tallboy755 said:
However I am in the dark a little about the whole export thing ... would I have to export to a data file and then import into my Out Of Production database...

Out Of Production could be a file by itself (archive), with a minimum of data and NO calc, best viewed through a portal in a main file for references.

In this same main file, but thru another relationship, you can create records for your In Prod file. When a job is done, run a script to export the data from one file to another.

Just some thoughts.

tallboy755 · August 20, 2003

Correct me if im wrong ... but when i setup a script to export records to another filemaker database .. It overwrites the entire database that i am exportting to .. i can't seem to get it to append the data

Fenton · August 20, 2003

Sorry, I did not mean to overwhelm. For one thing I had no idea you were only talking about 20-50 records at a time, and that you only wanted the calculation for a fairly simple operation.

My script is needed for people with thousands of records that need such an animal; where the "days" are used in further calculations or in a Find (which is very slow with unstored calculations).

I was also addressing other developers reading the thread, who may have need for the more complex script (and who might possibly have a tip for how to improve it :-)

BTW, you can of course sort the records afterwards. I was just saying that they naturally show in the order entered.

In your case the answers are simple. Either use the Today function, and wait for it to reevaluate every morning, which means you must close the file every night.

Or use Status(CurrentDate) - Creation Date as the calculation, setting its Storage Options to "Do not store." It will take a little longer to sort by the result, and a little longer for the display to calculate your colors (which would be unstored in any case, because of the global containerl; but this adds another level of unstored).

The speed hit will likely be acceptable with only 20-50 records.

tallboy755 · August 20, 2003

That is what i was looking for ... The Storage Options to "Do not store." makes everything work like a gem ... it does take a little bit longer ... however .. i am looking at a very small number of records. So I guess all is well that ends well..

-Queue- · August 20, 2003

For the record, any and all calculation fields (excluding auto-enter calcs, which techincally are not calculation fields at all) with Status() in them must be unstored if you want constant refreshing of the data they hold.

-Queue- · August 20, 2003

As of now, at least, exporting does not have append functionality. If you are merely moving data from one FM db to another, importing to the second file from the first might be a more efficient idea.

Unclegeo · August 22, 2003

Hi folks, new and in need of a similar solution.

I think Fenton said "unstored" not "unsorted" though I'm not sure if "unstored" is something new that I've not seen (likely) or a typo ;-}

My situation, simply stated, is to have records "expire" after say 30 days, i.e. to set a fields value (for items we sell) to "off list" instead of "for sale".

Because my life is more complicated than I'd like, this expire condition, of course, must only apply to vendors of a certain type -say "probationary" as opposed to "official".

I'm OK with this being a script I manually run or maybe runs whenever I open, though if it constantly (like a countdown timer) that would be just fine too!

It looks like you all are on to something here and I'll give Fenton's method a whirl but I too wonder if there's a more... parsimonious way.

Unclegeo · August 22, 2003

OOPS, just discovered what "page two" and "show all" is about (I said I was new). I think I have enough to try a solution but any ideas would be appreciated. I also see the "Unstored" issue has been addressed.

Guess I should have listened to Paul Harvey more.

Fenton · August 22, 2003

In your case I'd say yes, there is a more frugal approach. As I said, my long method is for situations with: 1. Lots of records, and 2. A stored number calculation result is absolutely needed for either Finds or other further calculations.

It is not needed whenever there's: 1. Not so many records, or 2. A Find can do the job.

Yours sounds like 2 would work.

You just need a script to find the "for sale" and "probationary" records which are more than 30 days old.

"Date" is your date field, must be on current layout.

Enter Find Mode []

Insert Calculated Result ["Date", "<" & DateToText (Status(CurrentDate) - 30) ]

Set Field ["VendorType", "probationary"]

Set Field ["SaleStatus", "for sale"]

Set Error Capture ["On"]

Perform Find []

If [ Status(CurrentFoundCount) > 0 ]

Go To Layout ["Form view"]

Loop

Set Field ["SaleType", "Off List"]

Go To Record/Request [Exit after last, "Next"]

End Loop

End If

Show All Records (optional)

I think that's right.

Himitsu · August 23, 2003

Like Tallboy, I entered in the "Today" calculation into a the field settings, now when I open it, it comes up with strange numbers like, 731450, I cann't see a reason why. How could this be a date? Any assistance would be greatly appriciated.

Lee Smith · August 23, 2003

That is FileMakers Date for 7/23/2003 expressed as a number. In order to see the date as a date, you need to change the field to result of Date.

Himitsu · August 23, 2003

well, I change the field type from calculation to date, but the old records still display that number, but if I create a new record, it shows today's date...

Himitsu · August 23, 2003

oh.. I got it.. in the calculation area, there is a result area... I set it to date.. and it worked.. thanks..

Lee Smith · August 23, 2003

Yep

Anatoli · August 23, 2003

RE: Like Tallboy, I entered in the "Today"

Better use Status(CurrentDate)

Today is quite obsolete function now.

Sign In

Dates ... Date Calculation

Recommended Posts

Create an account or sign in to comment

Create an account

Sign in

Important Information