I built a Travel Requests database for the company I work for. In doing the
research and analysis I discovered that most people do the same return
flights at varying intervals, i.e. from Office A to Office B then Office B
back to Office A.
To make life easier for the users I set the Travel form to self populate
the From to To fields based on the details of their last flight.
Code
You can use this method for any application where people are repeating
previous requests. Here's how to do it.
Create a hidden view called "(UserLookups)" that lists all their
transactions in ascending date order, i.e latest last.
The first column is categorised using the full hierarchical name of the
user.
The next column (unsorted)shows the manager who approves their requests
(column 3)
The next column (unsorted) shows where the first flight left from -
their home town (column 4)
The next column (unsorted) shows where they flew to - the destination
(column 5)
Note: Unsorted columns are in ascending creation date order
The Fields on the form:
HOME field
field type = Dialog List -
editable (uses a view lookup to
select where the
flight will leave from)
Default Value
Home:=@Subset(@DbLookup( "" :
"" ; "" : "" ; "(UserLookUps)" ;
@UserName ;
4);-1);
@If(@IsError(Home);"<Select
from List>";Home)
DESTINATION field
field type = Dialog List - editable
(uses a view lookup to select where the
flight will leave from)
Default Value
Destination:=@Subset(@DbLookup
( "" : "" ; "" : "" ; "(UserLookUps)" ;
@UserName ; 5);-1);
@If(@IsError(Destination);"
Both fields work in exactly the same way so here is a explanantion of
formula in the Home field.
The formula assigns a temporary variable called "Home".
It gets all the previous values from column 4 using the user name as a
key: @DbLookup( "" : "" ; "" : "" ; "(UserLookUps)" ; @UserName ; 4) which
returns a text list.
It gets the last value of the list by using @Subset(textlist; -1) where
text list was the lookup.
It then checks if the lookup failed i.e the user has no previous
bookings.
If it did fail then it places <Select from List> in the field.(remember
this is a Dialog List.
If it was a successful lookup it puts the value in the temporary
variable "Home" in the field.
Given that most users do the same return flights, you can also use the same
formulas for the return flights.
Using this method, when a user creates a Travel Request the retrun flights
are already filled in along with the name of their manager in the approver
field. They love it.
What can go wrong?
The lookup will return all the column values that it finds in the column
for that user, if user Elvis Brown has made 6,000 flights the lookup will
return a text list that 6,000 items long, at least it would if this did not
blow out the limit of what a DBLookup will return. If you have that many
documents then you need to be doing some archiving.
Another Tip:
Have a field called ArchiveDate, date/time - computed with this formula:
@Adjust(<return-flight-date>;0;3;0;0;0;0) where <return-flight-date> is the
name of th field with the date of the last flight in the journey. This
formula sets the ArchiveDate to 3 months in the future from the last flight
date. Once you have that you can build a simple script agent that users a
date/time function called DaysBetweenThenAndNow which I posted in this
Script Tip here:
http://searchdomino.techtarget.com/tip/1,289483,sid4_gci899579,00.html?FromTaxonomy%2Fpr%2F283841
Rate this Tip
To rate tips, you must be a member of SearchDomino.com. Register now
to start rating these tips. Log in if you are already a member.
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.
