General

Re: today () - automatic update as day changes without form open/refresh.

wilkies0106aw — Thu, 18 Jul 2013 10:48:40 GMT

Sorry can't help you out with a timer job. Not done those yet.

I still wonder why you can't use a SharePoint library with a calculted coloumn with a formula and use the function of today in the formula, not the today () of the form. Does that make sense?

Here are some good example.

http://kishansharepoint.blogspot.co.uk/2012/05/common-datetime-formulas-for-sharepoint.html

Re: today () - automatic update as day changes without form open/refresh.

Hypn0tik — Thu, 18 Jul 2013 04:21:59 GMT

Hi Wilkes,

I think our HR forms have slightly different purposes.

I have been given guidelines that my Sharepoint users want to be able to quickly and easily locate an employee record with the correct set of criteria for them to be approved for mine site access. They want to be able to use the library like an excel spread sheet with filters, they do not want to interpret if they have a expired licence or not, they just want them not to be displayed if they don't have it. For me this means ensuring the usual manual checking of expiry's is done inside the form, automatically and is hidden from the user unless they view the form. If a ticket expires the licence is removed from their record ensuring manual / human intervention is kept to a minimum and library filters are kept updated with the most current records because you cannot rely on humans to accurately update all records systematically without errors. The alerts the users want are so that our HR officer knows a ticket will expire within 15/30 days if the employee is currently working for us, in this case the alert can be forwarded direct to the individual.

We will only have about 10 users that will be viewing the library and in total maybe 200 records stored at any one time.

The problem I am faced with is the users need the ability to filter out the records with expired or non-valid licences and this means the forms today() function needs to actually be todays date every day. Not thedaytheformwasopened().

I have since learnt that you may manually set the fields date via manually coded direct XML modification run on a timer job but this still will not allow the form to run all its rules and remove the invalid licences until it is opened again.

I am at a loss, does anyone know if you can run a timer job to open, update and close a form? - I know this will stuff the version history inside the library but that not as important as having the correct records.

Re: today () - automatic update as day changes without form open/refresh.

wilkies0106aw — Wed, 17 Jul 2013 15:44:43 GMT

Several things come to mind.

I'm not sure how many users you will have looking at your system or how many employee records you will have. I initially think training and make it part of somebody's daily job to check for valid or expired dates. What if your email system fails - it happens.

If you want alerts, you might want to look at something like - http://social.technet.microsoft.com/Forums/sharepoint/en-US/10c4e3ba-e4db-4de4-87ad-a6bb8959bc7f/workflow-email-reminders-based-on-date-columns-that-change

I've never worried about how many coloums I have in a library. Just how many items I can see in a view. In some libraries I have lots of columns because I publish some columns into Word documents as part of a created document in a document set.

I tend to use the All Documents view as a show all everything view - Good for us Admins.

Then my default view is documents I've created - simple security measure if nothing else for end users.

As I read it, you only need to worry about some of the dates not all of the dates. If you published all expired dates and then used calculated columns, you could then use conditional formatting to highlight expired dates. You could then use views based on next 7 days or month etc. So you only need to check the once a week or month. My HR lot loved this, rather than copious amounts of emails for various HR forms and dates, since they had to compare request/days used etc against time sheets and payroll.

You could set up a site page with filtering by employee to view different views or required expired columns.

I think you would need a workflow to remove or change details inside a form and or have a data connection to update fields when a form is opened.

If you want to do things overnight I believe you have to create something called timer job.

Re: today () - automatic update as day changes without form open/refresh.

Hypn0tik — Wed, 17 Jul 2013 01:21:19 GMT

I am thinking, maybe I can have a workflow that runs nightly update the fields in the form?

Re: today () - automatic update as day changes without form open/refresh.

Hypn0tik — Wed, 17 Jul 2013 01:10:58 GMT

Hi Wilkies,

Ill give you a run down of my form.

I have created an employee record form to capture all employee details, the form includes the capture of any machine operator and drivers licences tickets along with about 30 other licences.

Many of these licences expire at various times i.e Our mine site operator tickets expire after approximately 2 years, our mine site medicals expire after only 28 days.
In order to accurately alert someone that a ticket has expired and remove the licence from their employee record (as its expired) I have created fields that capture the date in which the operators ticket or medical were attained.

In a separate hidden view I have validation that grabs today's date when the form opens (today()) and runs a calculation on the date they acquired the operator ticket and medical vs today's date.
This calculation spits out if they have a valid licence or medical or not. (if they do not have a valid licence or medical the field that is mapped to the form library column is displayed as blank) If it is valid, the word "valid" is displayed.

I have completed the validation inside the form an alternative to completing it inside the library to resolve 3 problems.
1. To keep the number of columns published to the library down to a minimum. (the expiry column on every record does not need to be published)
2. So that the form has the ability to remove or change details inside an employee record if the licence has expired
3. To make the filtering of the library easier, the user will only need to filter by "valid" or "expired"

The issue I have with a calculated columns in this instance;
1. It cannot remove expired records/licences from the form.
2. It would require all expiry fields to be published to columns in the library
3. It would require the end user who is looking for employee's with valid licences to run through the records to find an employee with non-expired tickets, rather than using a filter to remove them completely.

I know there is multiple different ways to tackle the problem I have.
I am open to suggestions and ideas and may inevitably need to run down the calculated column path if I cannot find a way to pull the current today's date from share point into the form and have it automatically update the records on a nightly basis.

Thanks in advance.

Hyp

Re: today () - automatic update as day changes without form open/refresh.

wilkies0106aw — Tue, 16 Jul 2013 09:06:06 GMT

I'm trying to understand what it is that you want exactly.

I think you will have to have some interaction with the form, if fields rely in the today field and they have to be updated etc.

Alternaively, you could have SharePoint calculated coloums. One would be your form date, another could be a calculated colum which might be more of what you want.

today () - automatic update as day changes without form open/refresh.

Hypn0tik — Tue, 16 Jul 2013 08:08:46 GMT

Hi All, Not sure if this can be done without opening the form itself. I have a bunch of calculations that rely on today(), but I do not want this static value or an on-load value. I want it to change with the current date - Automatically, can this be done in infopath without code - I dont mind creating a data connection to pull the current date from anywhere but as I am aware these are only activated again -- on load? Thanks, :)