updating activity Portal

[Sergeant Ron]

As you may have guess by my screen name Im in law enforcement. In regards to this I'm trying to develop a more complex type of portal than I'm currently using to check Officers thru a dispatch database. Currently I have a portal setup (which is a self join relationship that basically will show which officers are on a particular call based on either their "Time of Arrival" (Time field) and a "Time Cleared" (Time Field). When the officer is dispatched to a call the call entry in the database is assigned to a particular officer and the Time of arrival is empty thus creating an entry in the portal which shows the location (or destination) the officer is going to and that their status is "En Route". Once the officer arrives a time is entered in the Time of Arrival field and the status then changes to "On Scene". This works fine but I need to take it a step further. What I need to do is somehow incorperate another table which I will call Officer on Duty. Somehow in addition to the Officers that are assigned calls and either EnRoute or On Scene I need to show in the same portal which officers are still availible. My thought was that this could be done thru a Officers working table that Officers would enter their names at the begining of their shift and then sign off of when their shift ends. This Im sure I could accomplish by way of adding another field to the Officers on Duty table such as On-Duty Off Duty. Can anyone suggest how I can somehow combine this table of officers working with the basic concept that I currently have in place or if someone can suggest a better way from scratch I'd love to hear about it. Thanks, Ron

[CobaltSky]

Hi Ron,

Given the change you are contemplating, the portal will no longer be filtered according to dispatch status, so the relationship the portal is based on will have to be changed so that it shows all the officers in the current shift.

If you have a procedure whereby the officers sign on for a shift, then perhaps the simplest way to do this would be to set up a calc field (in the table where the sign-on and sign-off occurs)that returns a constant value ("X") when an officer is signed on, then use this calc field as the foreign key for the portal relationship. :)

[Sergeant Ron]

Thanks...

I did as you suggested. I added a table called "OnOffDuty" and bases on wheter the officer is on duty or off duty their name will appear in the portal. Now the thing I still need to do is associate those officers that are On duty with the calls being dispatched which are dispatched in a different table caled "Dispatch". As stated prior I did this by using an empty field to indicate where they were en route or on scene. how can I make this now have the officers status change from availible to on scene and still give me the information about the call (such as location)the officer is on in the same portal. I cant seem to figure out how to make that connection. Something like this i need displayed.

Status Officer Location

Availible Ron Main Street

En Route Bob South Street

That is the current way it displayes and is quite useful, however as I've said I need to inculde the Officers on duty, have them listed in the portal as availible if they arent on a call, and have their status change when they are dispatched based on an empty time field.

Thanks Cobalt Sky..Ron

[CobaltSky]

  Quote

how can I make this now have the officers status change from availible to on scene and still give me the information about the call (such as location)the officer is on in the same portal.

I assume that your dispatch table has a record for each dispatch event, which indicates who has been sent and where etc.

That being the case, if you set up a relationship from your personnel table (the one the portal is based on) to the dispatch table (and sort it descending on dispatch timestamp) it will pick up the most recent dispatch event for each officer.

Assuming that you have a dispatch status field and a destination field in the dispatch table, you'll then be able to display them in the portal. The relationships and relationship sort will then ensure that the view in the portal shows the staff on duty and their current (most recent) dispatch status.

[Sergeant Ron]

ok, im a little confused. The dispatch table is where I want the portal placed, the portal is based on the personell being onduty/offduty. How can I get the informtion from the dispatch table into the personell portal. This is where I'm having a problem, or I just dont seem to understand something. Regards, Ron

[CobaltSky]

Hi Ron,

What I was suggesting is that you use a portal based on the Personnel table as a *filter* of sorts for the data which resides in the Dispatch table.

That way you can view a single portal row for each officer who is on duty, and within that row you will be able to see (sourced from a table occurrence one step further away) the most recent dispatch status/location details for that person.

I've assembled a very quick demo of the rudiments of such a set-up so you can have a play with it (see attached). ;)

DispatchDemo.zip

[Sergeant Ron]

oh, ok. Thank you. I'll give it a try later on today. Much appreciated!

Ron.

[Sergeant Ron]

Again, Thanks Ray. This is EXACTLY what I needed!

[Sergeant Ron]

Ray, there is one more thing I was hoping I could do with this solution. The way that it was set up prior to this change I had the portal sorting by a simple self join relationship that evaluated based on wheather or anot a time field was empty or not. While this would give me the results I needed like you suggestion did there was one thing that I like about it. The portal refreshed ( and changed the officer's status in the portal) everytime I input or removed a time from either of the time fields (Time Arrived or Time Cleared)

When Time arrived is was filled in it refrehed the portal to show the status as "ON SCENE". This now requires a screen refresh. I added that to a script and attached it to a button along with "Input current time". But I was hopeing I could somehow have the portal auto refresh when the times are input and removed like it did before. Im thinking that the reason is that the fact that the portal is now being sorted by a timestamp rather than a field that was a calculation, thus forcing a re-evaluation of the fields contents and causing the refresh. I hope I've made this somewhat clear. Any suggestions.

[CobaltSky]

Hi Ron,

I'm afraid your explanation is rather hard to follow.

You say "prior to this change I had the portal sorting by a simple self join relationship". But portals don't sort by relationships, they sort by field values - and you've given no clue as to which field you were sorting on, how or for what purpose.

Then you say "When Time arrived is was filled in it refrehed the portal to show the status as "ON SCENE". This now requires a screen refresh". Presumably what you mean is that entering a time into a 'time arrived' field triggered reevaluation of a calculation field (which in turn was showing in the portal) to show 'On Scene" - but I'm guessing here. You haven't told us how or where the time arrived was filled in, nor how that action was being translated into the data showing in the portal.

As to the refresh issue that seems to be the nub of your current problem, I had a look at a copy of the example file I posted above and I added the ShiftData::DispatchTime field to the portal on the DispatchRef layout. With that change in place, I then tested to see if the portal was updating to show changes to the dispatch time on the most recent dispatch record for each person on duty. It was; as soon as I clicked out of a dispatch record after making a change, the updated timestamp value appeared in the portal.

I then tried adding a ShiftStartTimestamp field to the Personnel table and redefining the ShiftStatus field as an auto enter (replace) calc with thte formula If(IsEmpty(ShiftStartTime); "Off"; "On"). Again, entering or deleting a value in the Personnel ::ShiftStartTimestamp field was instantly reflected in the list of records displaying in the portal on the DispatchRef layout.

Obviously there are some other factors contributing to the problem you are seeing. But it is not clear from your post what they might be.

I suggest that you work with the example file and see if you can replicate the problem in it. If you can, perhaps post it here with some details about what fields and layouts you are modifying when the problem occurs. :

[Sergeant Ron]

Sorry I didnt explain this very well. In your example you have the dispatcher selecting the status, which does update in the portal. In my solution I instead use a calculation (Officer Status) based on two time fields to set the status of the officer. the calc. reads:

Officer Status:

If (IsEmpty (Time arrived) and IsEmpty (Time cleared); "En Route";

If (not IsEmpty(Time arrived) and IsEmpty (Time cleared); "On Scene";

If (not IsEmpty (Time cleared); "Available")))

based on the time fields being filled in the status will change, but this is what is puzzleing, the last IF statement will cause a screen refreshment when I click out of the Time cleared field, the Time arrived field causes no such action and required me to force a window refresh in order to update the window.

Also, I sort the same way the demo indicated, by the timestamp field, however the officer status field is in one table and the shift status in in the personnel table. I've read something about portals not refreshing when the field contents change when the portal is based on one particular relationship and the and the other fields in the portal are from a different relationship (I see that this works in your demo, so I'm not sure what the problem is). In the prior solution I sorted by a third time field called "Time of call", which was an auto created time field. the relationship in the portal was a selfjoin that had a field set as (SelfjoinOfficerStatus)"1" = to (SelfjoinOfficerStatuscalc)defined as IsEmpty(Time Cleared).

All the fields were from the same portal thus there was no problem.

I hope this somehow clears up my explaination, sorry I was in Disney world all week, brain isnt working like it should be yet.

Thanks, Ron

Edited November 20, 2005 by Guest