11 Replies Latest reply on Nov 12, 2012 1:22 PM by philmodjunk

    Logic/Schema Problem...

    GeoffreyMartin
    GeoffreyMartin Oct 17, 2012 12:19 PM

      Title

      Logic/Schema Problem...

      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.  

      • 112 Views
      • Tags:
        • 1. Re: Logic/Schema Problem...
          philmodjunk
          philmodjunk Oct 17, 2012 12:34 PM (in response to GeoffreyMartin)

               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.

          • 2. Re: Logic/Schema Problem...
            GeoffreyMartin
            GeoffreyMartin Oct 17, 2012 12:44 PM (in response to GeoffreyMartin)

                 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.

            • 3. Re: Logic/Schema Problem...
              philmodjunk
              philmodjunk Oct 17, 2012 2:00 PM (in response to GeoffreyMartin)

                   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.

              • 4. Re: Logic/Schema Problem...
                GeoffreyMartin
                GeoffreyMartin Oct 17, 2012 2:25 PM (in response to GeoffreyMartin)

                     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.

                • 5. Re: Logic/Schema Problem...
                  philmodjunk
                  philmodjunk Oct 17, 2012 2:59 PM (in response to GeoffreyMartin)

                       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.

                  • 6. Re: Logic/Schema Problem...
                    GeoffreyMartin
                    GeoffreyMartin Oct 17, 2012 3:23 PM (in response to GeoffreyMartin)

                          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?

                    • 7. Re: Logic/Schema Problem...
                      philmodjunk
                      philmodjunk Oct 17, 2012 3:31 PM (in response to GeoffreyMartin)

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

                      • 8. Re: Logic/Schema Problem...
                        GeoffreyMartin
                        GeoffreyMartin Oct 17, 2012 3:42 PM (in response to GeoffreyMartin)

                             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.

                        • 9. Re: Logic/Schema Problem...
                          philmodjunk
                          philmodjunk Oct 17, 2012 4:09 PM (in response to GeoffreyMartin)

                               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.

                          • 10. Re: Logic/Schema Problem...
                            GeoffreyMartin
                            GeoffreyMartin Nov 9, 2012 5:42 PM (in response to GeoffreyMartin)

                                 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? 

                            • 11. Re: Logic/Schema Problem...
                              philmodjunk
                              philmodjunk Nov 12, 2012 1:22 PM (in response to GeoffreyMartin)

                                   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.