set field to hold current date everyday

[Raheel Ahmed]

I am stuck in a wierd situation. I am working with a data filled lookup table which has a field called "valid_until" which houses date entries. For some records in this table there are dates in this field, however for the others it is blank. It is the blank fields that I am trying to manipulate.

This is what I have come up with so far. I went into the field rules->data entry of the table and then set the default value for the "valid_until" field to have a conditional default value where the condition would be: ISBLANK("Valid_until") and the default value would be: date()

However, just when things seem to be going the right direction another problem arises. First of all, all this seemed useless as the table already has all the data in it and this default data entry only applies when the user is entering data into the table. Another problem is that even if i had managed to set the date of the blank "valid_until" fields to "date()" which is the current date than that current date would not be the current date the day after.

Im trying to figure out if there would be a way to somehow have these blank "valid_until" fields set to be always the current date regardless of what day it is. IS that somehow possible? If so how would i even apply it to a table which already has data?

If anyone has any clues, I would appreciate any help. Thank you all.

[Robert Emery]

RE: set field to hold current date everyday

Do you actually need to set the values in the table, or do you just need to display the current date on the form?

As I see it, if you change the values in the table, how would the field rule know which records to change tomorrow, because they are no longer blank.

Try using a calculated field:
show_date=if(ISBLANK("Valid_until"),date(),Valid_until)

You could then use that field's OnArrive event, or an invisible button on top of the field to pop up a dialog box (IF in change mode) to prompt for a new date, assuming you need to manually change the value.

Bob

[Tom Cone Jr]

RE: set field to hold current date everyday

Raheel,

I agree with Robert. There's no reason to substitute a temporary field value in each empty 'valid_until' field. Just arrange your script or report to substitute the desired field value in your layout or report. Besides, once you stick a value in each of these fields, how will you find them again when you get back to work the next day. Since they'll no longer be empty how will you automate the process of substituting in the table new field values for the exact same records?

-- tom

[Raheel Ahmed]

RE: set field to hold current date everyday

Thank you guys for your thoughts. I am sorry for this late reply. Let me provide more details and maybe you can see whats going on better.

There is a lookup table called: "authority" which houses the field in question: "valid_until". This table has 100 records (peoples names) that were imported from excel. For some records there is date for the "valid_until" field. The purpose of this date is to give a that record authority status up until that "valid_until" date. Therefore, on a form this lookup table is filtered by an expression that eliminates all those records whose "valid_until" date has past or expired.

It works beatifully, however for some records in the authority table there are not dates. This was because these records (people) were valid_until "Further Notice". It is this that I am trying to tackle this in Alpha. So i thought the best way to do this would be to have those records, with valid_until date set to "Further Notice" be set to the current date every day, so virtually there is no certain "valid_until" date because everyday this person's would be an authority figure.

I thought about simply setting these records (peoples) with "Further Notice" valid_until status to a specific date, say one year from today. However, some people might be valid for more than a year and some less and therefore it later on unwanted people will get filtered out by the expression.

I hope you better understand my scenario now. If there is another alternative that seems better please let me know, I would love to hear it.

[Stan Mathews]

RE: set field to hold current date everyday

I'm not sure how the records with empty valid_until fields are ever going to have proper valid_until dates set, but how about this.

Run a operation upon application startup each day that marks the records in question, then an operation that puts in the current date.

At the end of the day, reverse the process, check for marked records, blank the valid_until field and unmark the records.

This would assume that you never legitimately set the valid_until date to the current date for any purpose other than to aid in your filtering.

[Robert Emery]

RE: set field to hold current date everyday

Raheel, why don't you want it to just display "Further Notice"?

show_date=if(ISBLANK("Valid_until"),"Further Notice",date_format(date(),"Month d, yyyy"))

Just a thought,
Bob

[Raheel Ahmed]

RE: set field to hold current date everyday

Stan, I like your idea, however that sounds like too many operations running in the background creating unnecessary overhead. I am gona go for what Rob has just advised. I think by simply putting in "Further Notice" in the "valid_until" field things would work out fine.

But now, I would like to get some aid in how I would construct the filter expression for the lookup field (this field is in another table all together) such that all records in the "authority" table are displayed except for those whose "valid_until" date is either expired or is "further notice"?? Before I was simply using this filter expression: valid_until>=date()

This filter expression would eliminate all those authority personnel whose valid_until date had expired. Now I would like to continue using this in conjunction with all those personnel whose valid until status is set to "Futher Notice".

Furthermore, I dont understand where I would use the script shared by Rob: show_date=if(ISBLANK("Valid_until"),"Further Notice",date_format(date(),"Month d, yyyy"))

This script will put in the "Futher Notice" in the blank "valid_until" fields but what is the next expression doing: date_format....??

I also cant enter characters in valid_until field as it is currently set house DATE fied type. Looks like every avenue i take is resulting in dead ends...

[Robert Emery]

RE: set field to hold current date everyday

"...I dont understand where I would use the script shared by Rob:
show_date=if(ISBLANK("Valid_until"),"Further Notice",date_format(date(),"Month d, yyyy")) "

the last part *should* have been:
... ,date_format(Valid_until,"Month d, yyyy"))

In Form design mode, you would declare a variable named show_date defined by the given expression. If the field Valid_until is blank, the variable show_date will be set to "Further Notice", otherwise it will contain the Valid_until date with the formatting shown.

"Before I was simply using this filter expression: valid_until"=date()
This filter expression would eliminate all those authority personnel whose valid_until date had expired. Now I would like to continue using this in conjunction with all those personnel whose valid until status is set to "Futher Notice". "

We are not changing the value in the field, simply displaying something else if the field is empty. Your filter should still work.

In order to change the value of the Valid_until field, you could put an invisible button on top of the calculated field which opens a dialog box asking for the revised date, then saves it to the table and finally refreshes the screen.

Bob

[Howard G. Cornett]

RE: set field to hold current date everyday

Why not just set the valid_until date 10 years (or more) in the future? Just make sure that you enter the year as 4 digits (especially if it is past 2020) and display all 4 digits of the year on your form so there is no confusion. For example just use the date 12/31/2050 as your valid_until date. This would be very simple to put into all your blank fields with a replace operation, and you would not need to change your filter.

Howard

[Raheel Ahmed]

RE: set field to hold current date everyday

Guys thanks a lot for your wonderful ideas. I liked rethinking over the various possibilities shared to me by all of you. This is how I have tackled my problem. I simply set the filter for the lookup field to be:

(Valid_until "= date() ) .or. isblank("Valid_until")

All those records who have "valid_until" dates as "further notice" is set to blank and using this way they are caught by the filter and added into the lookup field.

Once again thank you for your time and cooperation.