Brain Fart on this one

[Darrell]

I have developed a brain fart on this one. I think it is a simple solution but I just can't think of it right now. Here is it: I am devoleping a Dispatch database for a small ambulette company. What I am trying to accomplish is this. When a person clicks on a the unit number to assign the call I want the driver of that unit to appear on the main screen. I.E. Click on unit field, select unit number and in a box next to the unit field the drivers name appears that is assigned to that unit. I have created a relational data base with the field unit 1...unit 2.. and so on, with the driver name in that field. Now as I have said in the main database when I click on the unit number field I want the drivers name to appear along with the unit number. Now how hard can that be? An example would help greatly.

Thanks.

[MoonShadow]

If there is only one driver per unit ...

Create a relationship. Select File > Define Relationships. Select Dispatch Unit# in the left pane and Drivers Unit# in the right.

Place your Drivers::DriverName field directly on the Dispatch layout next to the Dispatch Unit #.

If it is necessary to store the Drivers Name along with the Unit# in your Dispatch table (sometimes necessary for history tracking if you change a unit's driver), then create a field in Dispatch for the name also. On this field, use Auto-Enter by Lookup Drivers::Name. And then just place this new DriverName field on your Dispatch form.

I would tend to want to use the second method - or have an additional file of the Drivers with unique IDs, which would never change. Then assign the DriverID to each Unit. In this way, you can track which specific driver was handling each incident back through time without adding a redundant DriverName field in Dispatch.

[Darrell]

I am still a liitle confused yet here. I will show the database I have;

I have the main database and In it I made a field called Key, I made that file a constant with the number 1. I made the other databse called driver and made the following fields Key: made that a constant number 1 then the following fields with drop down list of the drivers name

Unit 1 driver

Unit 2 driver

Unit 3 driver

Unit 4 driver

Unit 5 driver

I based the relaiontship on each key field

So would I recreate a relationship. And do the following like you have shown or something total different? Select File > Define Relationships. Select Dispatch Unit# in the left pane and Drivers Unit# in the right.

Place your Drivers::DriverName field directly on the Dispatch layout next to the Dispatch Unit #.

If it is necessary to store the Drivers Name along with the Unit# in your Dispatch table (sometimes necessary for history tracking if you change a unit's driver), then create a field in Dispatch for the name also. On this field, use Auto-Enter by Lookup Drivers::Name. And then just place this new DriverName field on your Dispatch form.

I would tend to want to use the second method - or have an additional file of the Drivers with unique IDs, which would never change. Then assign the DriverID to each Unit. In this way, you can track which specific driver was handling each incident back through time without adding a redundant DriverName field in Dispatch, and yes there is only one driver per unit sao I think that makes it easyer to figure out.

Driver.zip

[MoonShadow]

I might suggest a few changes, ie, your Units really should be records and not fields because every time you add a Unit, you will need to add a field. There are other snags you will hit with your current design also.

I've attached a down-and-dirty example of a relational structure which will give you everything you wish. If you open Dispatch, you will see it in action. You would need to review and duplicate four specific things in each file:

Any Auto-Enter calculations or lookups

Any Relationships

Any Value Lists

Any Value Lists assigned to each popup field

This type of structure will allow total flexibility for you. It will also 'hard code' the EmployeeID into the Dispatch record so that, a year from now if you need to find out which employee was driving a specific rig during an incident it can be retrieved directly from the Employee file. Employees should never be deleted but rather flagged with a status, such as Active or Terminated. If you always leave their records, there will be no need to pass their full name (or other information) into your other files. It helps keep file size down.

I hope the sample files (and structure) help you here. If you have questions please ask or, if this won't work for you, maybe we can figure something else out.

Wow. This is the first time I've gone back and created relationships in vs. 6! I felt blind!! And I couldn't simply pass the data through (grandchild style) like I can in 7. I had a difficult time switching my mind back! Those of us using vs. 7 are truly spoiled!

Dispatch.zip

[Darrell]

Thanks for the reply I am looking it right now and I know it will work. Now can I bother you for one more thing. I have a field called Status and I use the if calc (If(inservice = "","Pending","Completed") inservice is a time field that when I click on a button it closes out the call or when I create a new call it puts Pending in the Status field. I have other time fields like dispatch, enroute, onscene, transport, arrival. What I want to do is show (in the status field) what the unit is doing after the user clicks on one of the time fields. I think I have to use if then calcs but not sure. Can you show me an example?

Thanks so much

[MoonShadow]

You've sort of contradicted yourself, ie, you want Pending or Completed in Status but go on to say you want it to say where it is in the process (which could be in any one of 6 stages); so I assume you want to change what you have (from Pending/Completed) to a more descriptive Status?

Your Status field must then be a calculation and not Auto-Enter (text) field or it won't change for you. Or you could simply set the Status field depending upon the button selected, ie, Set Field [ OnScene, Status(CurrentTime) and Set Field [status, "On Scene"]. In vs. 7 you could use Auto-Enter (Replace) on a standard text field. Oh well, here's a calculation which doesn't need to be unstored so it's as resource safe and you won't have to mess with your scripts ...

Case(

Arrival, "Completed",

Transport, "Transporting",

OnScene, "On Scene",

Enroute, "Enroute",

Dispatch, "Dispatched",

InService, "In Service",

"Pending"

)

[Darrell]

You've sort of contradicted yourself

Yes I guess I did, very tired on this end trying to figure this out. Let me try again. I have fields that I time stamp (Dispatch,Enroute,OnScene, Departed, Arrival) I have setup buttons for each field that all I do is click on it and it time stamps the current time. What I also want is a Status field to show what is happening (I.E. When I click on the Dispatch button and timestamp the Dispatch Field I want to the status Field to show Dispatch Same thing for enroute, Onscene...etc. Hope that explains it better. Thanks once again for the help as the other item works now.

[MoonShadow]

Wonderful! Then the above calculation will work for you. Time is a number and will create a boolean result of 1 (or true) if the field isn't empty. It doesn't matter whether your time fields are time or text; although time is best to use because then you can compare a dispatch time start to finish.

So this creates your multiple If/Then which reads as:

If Arrival contains a value (is true) then display "Completed" ... etc.

So just create a Status field (type calculation, text) and insert the above calc. Then place this Status field on your form. It will change according to which time field you set.

I noticed I didn't have Departure - so just add that line like the others wherever it would go. The sequence should be listed in reverse starting with the end result (Completed) first and ending with the start (In Service). New records will automatically display "Pending" until In Service is given a time.

And hey ... I've been where you are at (overly tired and too much pressure) too many times to count; in fact, I'm still there most times.

[Darrell]

First of all I want to thank you Moonshadow for all the help that has been provided in getting this database up and running. I have one other thing I have run into. In the main page that all the users will be viewing I want to show all available Units that are not assigned to a call. A unit gets assigned to a call when the user clciks on the time field that says dispatch and assignes a unit. Do I need two fields One to show the unit and the other to show available or assigned? Thanks for the help in advance. I have attached tha screen shot of the main screen.

main.pdf

[MoonShadow]

You need a portal which will display only Units available. Create a Constant calculation (number) in Dispatch and put 1 in the calc box. In Units (or also in Dispatch if only one file), create a calculation (number) called Available with: IsEmpty(Dispatch). This will produce a 1 if no time exists in the Dispatch field.

Create a relationship from Dispatch to itself (if you didn't create a Units file) or to Units (if you did) by selecting File > Define Relationship > New. Call it Available Units. Join Constant (in the left pane) to Available Units in the right.

Now on your main layout in Dispatch, select Insert > Portal and show records from Available Units. Then Insert > Field and select Available Units relationship from the popup before selecting your Unit Number field (7 is nicer about this because it allows you to select your fields at the same time).

Place this Unit Number field in the top portal row. Keep it away from the portal boundaries (particularly the top and left edges). Turn off field entry (right-click field, field format, Behavior, unclick Allow Entry). You don't want Users changing this data manually.

You can attach script to the portal Unit field or place a transparent rectangle over the row with script Go To Related Record [ ] with a Layout switch, so Users can jump directly to that Unit Number for assignment. There are a few other tricks to making the portal prettier, ie, so it won't turn black if clicked by a User but this will get you up and running.

[Darrell]

Ran into a problem. My user assigns a single unit to a more then one run so. IN the avalibale field the unit is show up twice. How do I work around that? I have attached a screen shot.

list.pdf

[MoonShadow]

Any assigned Unit shouldn't show in the portal at all.

Your portal appears to be producing all records so something is wrong in Pleasantville!! Here's a quick demo to show you what it should look like. I used a self-join for the example to show it shouldn't make a difference in your portal display.

You didn't join the Constant to the Constant for this relationship did you? Hmmmmm. Well, we'll pinpoint it whatever it is ...

test.zip

[Darrell]

I sent the test DB back showing what happens to the available untis field. You will see that I assigned unit 317 to 3 different calls before dispatching one of the calls to unit 317. Unit 371 is still showing up as available even though they have been dispatched on another call. What my user wants is the list of waiting calls and assigns that unit to the next call even though the first call is not done. Hey thanks for the time fields script, they are better then mine.

test.zip

[MoonShadow]

Hmmmm, the logic escapes me, I'm afraid ...

Why is the User assigning several calls to the same Unit number? Why not wait to assign a Unit Number until it is dispatched? Or once User clicks 'Dispatch', you can script removal of the other 317 assignments. If your User only wants Units available to assign to appear in the portal, how could she assign 317 three times? It's chicken vs. egg circular illogic.

Since 317 is listed in three records in Dispatch, 317 is treated as three different units!! You would need to *see* all 317 records within Dispatch, which would require an unstored aggregate function such as Max(Dispatch).

Did you create a Units file as per my example, that is related? If so, you can store the times within your Units file (which is more accurate - you are timing your Units movement) so that, when 317 is dispatched it will no longer be available to assign and could disappear from the availability list. Or can one unit be dispatched to multiple calls? In that case, you wouldn't want 317 to disappear from availability!

With your existing solution, you will have to deal with 317 that was never dispatched which currently is entered in the other two records. What do you do with them after 317 is displatched? Well, I've attached the demo back. It simply displays ALL dispatch records but highlights in red any calls that have been dispatched.

If you can explain the logic behind this inconsistency and/or tell me you have a Units file, I'm at a loss as to what else to suggest. But I will continue to work with you on it, if you wish.

test2.zip

[Darrell]

Why is the User assigning several calls to the same Unit number

Because sometimes there are multipal pcikups going to the same destination. Aslo in the mornng when a driver comes in there is a printout of a list of the days current calls that the dispatcher wnats this driver to take.

Did you create a Units file as per my example, that is related? If so, you can store the times within your Units file (which is more accurate - you are timing your Units movement) so that, when 317 is dispatched it will no longer be available to assign and could disappear from the availability list.

Yea I did and I creataed a units file but I made the relationship with itself in the dispacth file, amybe I should make it within the units file. If Unit 317 is assigned to 1 call and that unit is dispatched it does disappear from the availability list.

But I will continue to work with you on it, if you wish

Oh yes please, you don't know how much this is helping me. I am getting more and more cofident in figuring out little things that improve the program. I do think I may run into one more problem so I will chew on tyour ear and see what you think. This program is goinig to run on Filemaker server 6 with 5 clients hitting the database at one time. What I am concerened about is this. Say one user dispatches a call, another user is looking at the wating list Now will the record update on that second useres screen to show the call has been dispatched? If it does show up and the second user goes into the record that the first user was looking at to put the unit on scene because the first user became busy or went to a different record, will the second user's screen show the dispatch time on there screen? I understand that the first user has to be out of the record because there cannot be 2 users in the same record. I hope I haven't confused you to much. Thanks once again for the download.

[MoonShadow]

Good morning!

Before I networked the first time, I couldn't conceive how it could work and update. And truthfully, I have no idea how prior versions (before vs. 7) work but I assume it's the same as far as User displaying when networked.

If a User is in a record (but doesn't have their cursor in the record (claiming it) then they will see a momentary screen flash (at least on Windows) and the data will change. I can sit and watch portal items come and go also. The example I provided stacks a calculation on top of the regular Unit Number in the portal and it's unstored. I'm confident it will change for you.

However, I would NOT answer a network question (and many other questions) because I don't feel I know enough about the subject. You should start a new thread for each new problem or question for three reasons: 1) I may not be available whenever you need answers. 2) There are thousands of people smarter than I who may have better suggestions and 3) If each different question/subject is another post, then other people seeking the same answers can find them easily by their categories and proper forums. Attempt to keep one thread to addressing the general problem which started it.

Networking is cool! I suggest you start studying the posts in that forum. It is particularly important that you learn how to properly use globals in a network environment and you SHOULD take advantage of globals - they are our workhorses, particularly when networked.

And I'm glad I've helped. I've been (and still am) where you are - asking questions and attempting to provide a great solution for Users. FM Forums is truly a godsend to all of us.

[Darrell]

I will look around for the Networking problem, I don't think it will be to much to figure out. I have attached a screen shot of the main page and you will see that I added a protal to show a list of all runs but I ran into problem. I use the relationship that was used to showing avaliable units (haven't put that on the main page yet but will) anyway when a run completes or is cancelled it is omited. As you can see by the Status field it shows the cancelled run, I don't want any of the completed runs to show in this portal or cancelled runs. Basiclly I just wnat pending runs or current runs that are dispatched to show. How would I go about this?

Thanks

main.pdf