Order of records

[montenegro]

Hello,

this is my first project using FileMaker so I am facing various beginner's difficulties.

One of the is following:

I want to record vehicle activities at Entrance/Gate. I created a file Employee_Info where I stored basic info for each employee (Decal, Name, Vehicle Type, Vehicle Tag) I also created Employee_IN file/form, where user types a Decal and after hitting Tab all other information (from Employee_Info)about that particular employee fills out autamatically. Additionaly Day and Time IN gets populated. This works great.

I also have a file Employee_OUT which records day and time an employee left the company gate. I created a relationship between Employee_OUT and Employee_IN, where Employee_OUT form takes all info from Employee_IN for that specific employee (including Day and Time IN). User types in a Decal and hit Tab, the rest of fields gets populated adding Day and Time OUT.

The problem I am having is:

For example, I have 3 records(for 3 days) for one particular employee in Employee_IN field, i.e. one record for each day. When that employee is leaving I want to record his Day and Time OUT, so I must use info from Employee_IN field. Ideally, when I type a decal in Employee_OUT form, the rest of the fields should get populated with the very last record of that specific Decal, since that is the "freshest" recors, i.e. record that has info when that employee entered gate the last time.(The record with the latest Day and Time IN for that specific decal)

Instead of that I always get info from the very first record of that specific Decal from Employee_IN.

I tried to use some sorting actions, but it did not seem to work.

Any suggestions how to design/sort records so info of the very last record will show up when that specific Decal is called.

Thanks

[Kundinger]

Hello 'montenegro',

Welcome to the FMForum... the greatest FileMaker resource available!!! And, of course... welcome to FileMaker... an experience you'll come to love and hate!

That said... I would recommend that you spend time learning the 'ins & outs' of FMP relationships... take some of the sample solutions you recieved on the FMP CD and 'tear' them apart to understand them. Focus on learning...

- Parent-Child Relationships

- Trigger Fields

- Portals

What you are working on should be a 'simple' enough system for you to 'cut your teeth on'! I would start simple and add functionality and features as you learn more and more. Please ARCHIVE each version of your system as you expand it.

About your current system... "Vehicle Logging System"

Fundamentally, you are on the right track with separating your 'vehicle info' from your 'in/out info'. When a vehicle pulls up to the gate... the system user enters the DecalID and enters IN or OUT. The issue you are experiencing seems to be how you do the 'IN & OUT'. You could have a portal on the Vehicle screen that shows all related 'In/Out' records... thus displaying a history of that vehicle. Then you click on a portal row that then displays the details to the system user for editing.

The key to this working is understanding relations... the Parent file (left-side) must have a 'trigger' to show ONLY related records from the Child file (right-side).

Some other 'points to ponder' about your system...

- is it employee info or vehicle info, do you track each employee IN/OUT or each vehicle IN/OUT

- can an employee have more than one vehicle

- what happens when more than one employee rides-in, in one vehicle, and leaves separately

- what happens if the vehicle doesn't leave today, but tomorrow, or out another gate

- how do you handle visitors, visitor vehicles, employees with new vehicles, in a timely manner

I'm not sure how 'secure' your installation needs to be... is it private, government, or military. Having grown up in the military and working with government organizations, I understand some of the issues with installation security.

Hope this helps... Good Luck! Let us know how you are progressing!

Bob Kundinger

[email protected]

[montenegro]

Hello Bob,

thank you for your time and help.

I read some basic stuff and did a CD tutorial before this little project. The best way to learn all features and options, for me, is to do a simple project and face with actual problems.

Regarding your questions/comments about my system:

- this is a very simple exercise project that I already did in MS Access long time ago, and my goal is to do it in FileMaker to compare Access and FileMaker and decide which way to go for a final version.

- this desing should record everything based on vehicle info, not employee info, so it does not matter how many employees are in a car at the gate

- The base for all info is a Decal Number. If an employee has more than one car he has more then one decals.

- visitors, contractor and others are handeled in separate files.

- we only have one gate, so it makes it easier

- if vehcle does not leave the same day, no problem, we simply record the day it left

As you can see the project is pretty simple and I almost finished it. I need to find and play with options to create something as a main menu, so user can access all needed files from there. I also want to explore option for a web published / intranet use of this program.

While I am reading more about other relationsips and portals, I hope you will have a answer on my original question:

Is there a way to access the very last record of a related file based on specific info, i.e. when I am pulling info from Employee_IN file in Employee_OUT file, I need to get the very last record/instance of that particular Decal requested from Employee_IN.

Currently I ALWAYS pull out only the very first record of that specific Decal recorded in Employee_IN.

If an employee entered a gate this morning user/gate guard created a record of that in Employee_IN file with all emloyee's info. The main/relation field of each record is Decal number. For example, This specific employee also entered and left a gate every day for past one month. When this employee leaves a gate today afternoon user will type in Decal number in Employee_OUT file, and basic info from Employee_IN file about this employee will automatically populate. This works fine. Problem is that instead of the latest info (from this morning) I always get the info when that employee entered a gate the very first time. (in this case a month ago).

How to control this?

I hope my explanation was not too detailed and boring. I feel there is a very simple solution to this.

Thanks

Srdjan

[BobWeaver]

There are two ways to access the last related record.

The first method is to go into the relationship definition, and select the sort option. Then sort in *descending* order by date and then time. The related record that is returned will be the first one in the sort order that you have chosen. So, it will be the latest one in this case.

The second method is to use the Last() function. Although the manual lists this function for use only with repeating fields, it will return the last related record from a relationship. Use it with this syntax:

Last(RelationshipName::FieldX)

[montenegro]

Bob,

thank you for your help.

The first method worked fine.

I did not know there is a sort option under the Define Relationship menu. In my earlier attempts to solve the problem,I was performing sort of records on an Employee IN file, not under Define Relationship. My records were sorted in Employee_IN file the way I wanted them to be, but that did not have any influence on what will Employee_OUT see/pull out as a requested info.

Thanks again

Srdjan