AnsweredAssumed Answered

Logic/Schema Problem...

Question asked by GeoffreyMartin on Oct 17, 2012
Latest reply on Nov 12, 2012 by philmodjunk

Like • Show 0 Likes0
Comment • 11

Title

Post

Hello all,

I'm trying to retrospectively (re)create a screen that is populated by a sql table (Activity_Log). I'm having some problems trying to figure out the logic/schema to make this work. I've come at this from a couple of angles and am stuck.

The screen is a vehicle resourse screen that shows where everyone is and what their status is currently. The fields are radio_name (the vehicle ID), activity (status), date_time (timestamp). I'm trying to capture this information at a particular point in time time_callenteredqueue. My problem is that the log table is a log and if I try to look at the data in the log at a particular point there may be one entry. I want to create something that shows what all of the units' statuses at that time.

10/10/2012 1:00:00 PM <- time_callenteredqueue

123 - Available - 12:50:00 PM (this is the time the unit went into the current status, but it is not necessary to show it)

456 - Enroute - 12:59:00 PM

789 - At Scene - 12:45:00 PM

Ideally these units would stay in this status until it changed in the log table. The units could always be there and only status(ed) when they had a status or a change. I've tried a portal of the activity_log table that looked back 15:00 but some of these units change status 2-4 times in 15:00.

Anyone have even a bit of guidance, I'm stuck.

philmodjunk Oct 17, 2012 2:59 PM

Correct Answer

This is not at all what I had in mind.

I am assuming a relationship such as:

Vehicles::VehicleID = ActivityLog::VehicleID ---> Not sure why you use a field named "radio name" for this?

The layout would be a list view layout based on Vehicles. Place a one row portal to ActivityLog, with the log sorted by the TimeStamp field in descending order and you'll get a list of all your vehicles showing their current status.

See the reply in context

No one else had this question

Outcomes

philmodjunk Oct 17, 2012 12:34 PM

I think a portal to your log table is the right way to go. You can sort the records shown in the portal to put the most recent log entry first and that should then show the current status on each vehicle.

Actions

GeoffreyMartin Oct 17, 2012 12:44 PM

I guess I should have gone a bit further. The eventual goal is to have FM count the resources in certain statuses at the snapshot time. I've tried tried that with the portal, but because a unit can change status within a couple of minutes or less, the counting thing doesn't really work, it's on the portal twice or three times within the filter of 15 minutes that I had built. I think I've got the counting part under control once I can create a table that works.

I realize this may be out of the scope of the forum, I just thought I'd put it out there.

Actions

philmodjunk Oct 17, 2012 2:00 PM

I don't quite get what you are trying to accomplish with the counting, but calculation fields can be defined with relationships in stead of portal filters to match to the desired set of records and count them. This should update quite smoothly.

Actions

GeoffreyMartin Oct 17, 2012 2:25 PM

A marked up picture is worth a thousand emails...The attached snapshot is only the first of many pages of data in that portal, but it'll give you an idea of what I am doing.

Actions

philmodjunk Oct 17, 2012 2:59 PM

This is not at all what I had in mind.

I am assuming a relationship such as:

Vehicles::VehicleID = ActivityLog::VehicleID ---> Not sure why you use a field named "radio name" for this?

Actions

GeoffreyMartin Oct 17, 2012 3:23 PM

Radio_name came from the sql source. That worked pretty well. So now I have what the unit's statuses are closest to now (portal sorted in decending order on the timestamp). If I wanted to look at this for a particular date/time, I'd use the portal filter?

Actions

philmodjunk Oct 17, 2012 3:31 PM

Both a portal filter or a relationship level filter can be used to specify a specific time period.

Actions

GeoffreyMartin Oct 17, 2012 3:42 PM

You've been a great help (as always). I put this layout in the schedule table because I could like by unit very easily there. It looks great. Can you nest portals inside portals? I wanted to put this in a different layout/table and use the date/timestamp to filter via the relationship. Because at this point this is a portal inside a different table.

Actions

philmodjunk Oct 17, 2012 4:09 PM

You cannot nest portals inside of portals. Why would you need that to filter by a time interval for a copy of the portal on a different layout?

You can set up a "master-slave" pair of portals where clicking a portal row in the "master portal" displays related detail records in the "slave" portal.

Actions

GeoffreyMartin Nov 9, 2012 5:42 PM

Rather than create the same post over again, I had another question regarding this same post. For each row, I've catagorized each status and given them either a O or 1 status in a Case arguement in another field. Now I'd like to try to count all of the portal rows that have value of 1. I'm having trouble summarizing this in the right place, every row is a single portal row. I've included a picture. Any ideas?

Actions

philmodjunk Nov 12, 2012 1:22 PM

I am assuming that you want two totals at the bottom of this screen with the total number of rows with 1 in that column added up to give you a count.

Is the one row portal a filtered portal?

If so, you'll need to change to a filtered relationship instead.

Then you can combine a calculation field and a summary field to get the needed totals.

If you need help setting up a relationship to take the place of the portal filter expression, please post your filter expression and then also describe the relatioship used for this portal.

Actions

11 Replies

philmodjunk Oct 17, 2012 12:34 PM
Mark Correct
Correct Answer
I think a portal to your log table is the right way to go. You can sort the records shown in the portal to put the most recent log entry first and that should then show the current status on each vehicle.
Like • Show 0 Likes0
Actions
GeoffreyMartin Oct 17, 2012 12:44 PM
Mark Correct
Correct Answer
I guess I should have gone a bit further. The eventual goal is to have FM count the resources in certain statuses at the snapshot time. I've tried tried that with the portal, but because a unit can change status within a couple of minutes or less, the counting thing doesn't really work, it's on the portal twice or three times within the filter of 15 minutes that I had built. I think I've got the counting part under control once I can create a table that works.

I realize this may be out of the scope of the forum, I just thought I'd put it out there.
Like • Show 0 Likes0
Actions
philmodjunk Oct 17, 2012 2:00 PM
Mark Correct
Correct Answer
I don't quite get what you are trying to accomplish with the counting, but calculation fields can be defined with relationships in stead of portal filters to match to the desired set of records and count them. This should update quite smoothly.
Like • Show 0 Likes0
Actions
GeoffreyMartin Oct 17, 2012 2:25 PM
Mark Correct
Correct Answer
A marked up picture is worth a thousand emails...The attached snapshot is only the first of many pages of data in that portal, but it'll give you an idea of what I am doing.
Like • Show 0 Likes0
Actions
philmodjunk Oct 17, 2012 2:59 PM
Unmark Correct
Correct Answer
     This is not at all what I had in mind.

     I am assuming a relationship such as:

     Vehicles::VehicleID = ActivityLog::VehicleID    ---> Not sure why you use a field named "radio name" for this?

     The layout would be a list view layout based on Vehicles. Place a one row portal to ActivityLog, with the log sorted by the TimeStamp field in descending order and you'll get a list of all your vehicles showing their current status.
Like • Show 0 Likes0
Actions
GeoffreyMartin Oct 17, 2012 3:23 PM
Mark Correct
Correct Answer
Radio_name came from the sql source. That worked pretty well. So now I have what the unit's statuses are closest to now (portal sorted in decending order on the timestamp). If I wanted to look at this for a particular date/time, I'd use the portal filter?
Like • Show 0 Likes0
Actions
philmodjunk Oct 17, 2012 3:31 PM
Mark Correct
Correct Answer
Both a portal filter or a relationship level filter can be used to specify a specific time period.
Like • Show 0 Likes0
Actions
GeoffreyMartin Oct 17, 2012 3:42 PM
Mark Correct
Correct Answer
You've been a great help (as always). I put this layout in the schedule table because I could like by unit very easily there. It looks great. Can you nest portals inside portals? I wanted to put this in a different layout/table and use the date/timestamp to filter via the relationship. Because at this point this is a portal inside a different table.
Like • Show 0 Likes0
Actions
philmodjunk Oct 17, 2012 4:09 PM
Mark Correct
Correct Answer
You cannot nest portals inside of portals. Why would you need that to filter by a time interval for a copy of the portal on a different layout?

You can set up a "master-slave" pair of portals where clicking a portal row in the "master portal" displays related detail records in the "slave" portal.
Like • Show 0 Likes0
Actions
GeoffreyMartin Nov 9, 2012 5:42 PM
Mark Correct
Correct Answer
Rather than create the same post over again, I had another question regarding this same post. For each row, I've catagorized each status and given them either a O or 1 status in a Case arguement in another field. Now I'd like to try to count all of the portal rows that have value of 1. I'm having trouble summarizing this in the right place, every row is a single portal row. I've included a picture. Any ideas?
Like • Show 0 Likes0
Actions
philmodjunk Nov 12, 2012 1:22 PM
Mark Correct
Correct Answer
     I am assuming that you want two totals at the bottom of this screen with the total number of rows with 1 in that column added up to give you a count.

     Is the one row portal a filtered portal?

     If so, you'll need to change to a filtered relationship instead.

     Then you can combine a calculation field and a summary field to get the needed totals.

     If you need help setting up a relationship to take the place of the portal filter expression, please post your filter expression and then also describe the relatioship used for this portal.
Like • Show 0 Likes0
Actions

Logic/Schema Problem...

Title

Post

Outcomes

Related Content

Recommended Content