Database based on task completion.

[luiscovar]

First of all, the actual task is for the employees to go around the campus and hold down the water fountain for 30 seconds first thing in the morning.

 

I created a database and a table has all the water fountains, with fields, location, fountain number, and description. I created a separate table called tasks, that has fields flushed(drop down menu with yes or no, and a notes section). The third table has fields including, employee first and last name, and their employee number. 

 

The idea is to match all of these together in order to know which employee did the task and where they completed it or not. Also to somehow search it by date period. So maybe from 2/2/2014 to 3/1/2014. I have no clue how to do that one. 

 

I would like to somehow add a script where if i push a button a countdown begins and it beeps at the end. However, different fountains have different amount of spouts so one would have to be 90 seconds, another 30 seconds, another 60 seconds. Deepening on each. Any help would be very much appreciated.

 

I attached a picture of my relationships for criticism/advice. This is barely my 3rd day using it and it is proving to be difficult. 

[comment]

I think you have two separate issues here:

 

1. How to organize your database in terms of tables, fields and relationships;

2. How to build the user interface for creating the records.

 

Regarding the first issue:

How does an employee know which fountains to handle (and on which date)? If there are assignments of employees to fountains (or fountains to employees), you need to record these somewhere.  Also, one would assume that these assignments will change from time to time; will you need to keep the record of previous assignments? Or is it enough to keep record of completed tasks only?

 

It's also not clear what does the Tasks table actually record: is it possible for an employee to try and accomplish the task without succeeding? or is it strictly a yes/no affair?

[luiscovar]

There is only one employee through out the day that should be doing the work. However, different employees may be chosen to do the task. I don't know how to do this yet but it should be a fixed global thing as soon as they sign in. So when they sign in they see their name and it is unmodifiable. 

 

You're right about those issues. I am having trouble creating the data base. Ideally, I'd like it for them employee to type in the fountain number and then have the Description/Location of it to come out when they do. When they're are done holding it down they have a drop down menu asking if it was flushed with the values Yes, No, and Edit. And that answer should link to the fountain number and the date that it was done too.

 

I'm really new to this so sorry if it seems like I'm asking like too much.

[comment]

I think your structure should look more like this:

 

 

 

In terms of workflow: when the employee selects a fountain - either by clicking on it in a list view of the Fountains table, or by entering a unique number into a global field - have a script run and create a new record in the Tasks table, populating the following fields:

 

TaskID (auto-entered serial number);

Date (auto-entered creation date);

FountainID - set this field to the selected fountain's ID;

EmployeeID - set this field to the currently logged-in employee's ID.

 

Now you have the record ready and waiting for the employee to enter the result of the action.

 

--

P.S. Instead of EmployeeID, you could use their account name, which is easy to get through the Get(AccountName) function.

[luiscovar]

In the picture you attached, what's in the middle is the field that is being linked, right? Not a separate table?

 

Ok, I'm starting to understand the bigger picture now. Currently, I do now know how to create scripts but I can learn it pretty quick.

 

Where would the employee view the information of the fountain? Since a script is going to run when a fountain number is inputted and go into the task layout should the description be under the task table too?

[comment]

what's in the middle is the field that is being linked, right?

 

Right (assuming you mean in the middle of a line; Tasks, in the middle, is definitely a table).

 

 

Where would the employee view the information of the fountain? Since a script is going to run when a fountain number is inputted and go into the task layout should the description be under the task table too?

 

Once a task is created as described above, it is linked to the selected fountain record. You can simply place fields from the Fountains table on the layout of Tasks, and they will display the data from the related record in Fountains. Make these fields non-enterable in Browse mode, so that they cannot be modified accidentally.

[luiscovar]

Hello, I have a couple questions still and i hope you dont mind helping me out a little more. So, i created a a relationship like you advised and I'll attach it here for further criticism. 

 

So, i have the list like of fountains there but I would like to make them clickable so that when the user finds the correct one it automatically goes to the task option and matches the ID. 

 

In the pictures i attached I did what you suggested and created a button that automatically goes to the task section. However, when i click on the button and run the script it goes to the Task layout and creates a new record but never with the matching one, its just increments the fountain ID to whatever it was before.

 

 

Thanks in advance.

[luiscovar]

Anyone?

[comment]

In the pictures i attached I did what you suggested and created a button that automatically goes to the task section.

 

I am not quite sure what I am looking at. The script especially is not clear to me. Is that the script you run when clicking on a task? If so, that is not what I suggested.

 

Assuming the button is on a layout of Fountains*, your script should look something like this:

Set Variable [ $fountainID ; Fountains::FountainID ]
Go to Layout { Tasks ]
New Record
Set Field [ Tasks::FountainID ; $fountainID ]
Set Field [ Tasks::EmployeeID ; SomeTable::gEmployeeID ]
Commit Records

This is assuming you have populated a global SomeTable::gEmployeeID field with the currently logged in employee's ID on startup.

 

 

Note that context is very important in Filemaker. The same script, if called from a layout of another table, will do something else altogether (something you most likely don't want to be done). That's also why your screenshots are rather meaningless; you can't tell what a layout does if you don't know which table (or rather which table occurrence) it is associated with.