I hope one of the many knowledgeable people on this group of forums will be able to assist. I have a database for our HR department. Within the database we retain proof of identification of employees for Anti-Money Laundering (AML) and I need to create a report that shows when identification has expired. I'm trying to create a script that will search for a) active employees either passports or drivers licenses (as the type of document) and c) expiry date < today.

 

My problem is which command or function to use to carry out the find requests and the correct syntax for the request. The layout I've developed for the find is Find Layout for AML and the layout for the report is AML Information. The script is Report of Expired AML Docs. Please see the attached zipfile of the database.

 

Many thanks for your patience and assistance.

 

SkiffyNew HR.fmp12.zip

Your script should look as follows:

 

 

#Report to provide a list of expired AML documents to user for only Active Employees
#K Skiffington 23 Sept 2013
#
New Window [Name: "Expired AML Report"; Height: 800; Width: 500; Top: 20; Left: 20; Style: Document]
Go to Layout ["Find Layout for AML"]
Set Variable [$Today; Value:Get ( CurrentDate )]
Enter Find Mode []
Set Field [Documents::Type of doc; "Passport"]
Set Field [HR Information::Work Status; "Active"]
Set Field [Documents::Expiry Date; "<  $Today"]
New Record/Request
Set Field [Documents::Type of doc; "Drivers License"]
Set Field [HR Information::Work Status; "Active"]
Set Field [Documents::Expiry Date; "<  $Today"]
Perform Find []
Go to Layout ["AML Information"]

The shortcut "//" is to display the date on a layout only; it does not work in calculations.

 

The way you entered the data in the Set Field steps told FM to look for a repetition of a repeating field. The correct syntax uses a semicolon.

 

The Set Field for the expiry date was incorrectly formatted.  To enter a calculation in a set field, it must be enclosed in quotes, and the field name is not required.

 

To create an "OR" find, use the New Record/Request command

Thank you Doughemi, all works except the OR for Passports or Drivers License. I have tried

              Set Field [Documents::Type of doc[]; "Passport" or "Drivers License"]

but that doesn't work. I now understand that to use the Set Field command, I need to first click on the Specify Target Field button to insert the field, and then on the Calculated Result button to insert the text I am looking for. Now I just need the syntax for the OR....

Skiffy

Take another look at my script.  

 

The first request is one set of criteria:

type of doc=passport, work status= active, and expiry date<$today .  

This will find all the passports (of active employees) that have expired.

 

Then there is the New Record/Request step, and another set of criteria: 

type of doc=drivers license, work status= active, and expiry date<$today .  

This request will find all the drivers licenses that have expired.

 

The found set will include both requests.

Thank you again. The find is now working correctly. Now my layout does not work and I don't see how to eliminate the display of the other types of documents. So while the find correctly finds the records where there are expired ID and only for active employees, the layout is still showing the other types of ID such as utility bills etc. I've tried lists, I've tried portals, but I'm not getting what I need. I know I'm missing something obvious, but I can't see it.

 

Many thanks,

Skiffy

Without seeing the layout/script, it is hard to say.

My apologies. An updated zip file is attached. The layout in question is AML Report and the script is Report of Expired AML Docs.

 

Skiffy New HR.fmp12.zip

The find layout was based on HR when it should have been based on Documents.  When you switch to the Report layout based on Documents,  the found set was the entire table.

 

The calculation in the Set Field[expiry date] was wrong.  It should be "<" & $today instead of "<$today".  Apologies.

 

New HR_dh.fmp12.zip

Thank you so much! I do appreciate the guidance and assistance.

Skiffy