3 Replies Latest reply on Jun 16, 2015 2:04 PM by philmodjunk

    Report Drill Down recommendations

    jcondon

      Title

      Report Drill Down recommendations

      Post

       

       

       

      Hello,

      I am looking for some feedback on a database we are setting up.

      The database is an equipment tracking database tracking about 25 pieces of equipment.  We have a table for equipment, transactions, and other required information.  Data entry is performed on a List view where at piece of equipment has its own entry with 5 portal fields.  The user selects a date that populates the transaction dates in each of the 5 portals.  The user can then select a job and status.  

      Each piece of equipment will have a an transaction for each day of the week.

      I wa trying to have essentially a drill down screen, where the each month has a button.  If all of the entries were complete, the button would be green.  If not, the button would be red.  When the user hit a red button, they would be taken to a listing by week, where each week would be a button and a similar red/green conditional format would be used.  If a week were selected, the user would be taken to the data entry screen.

      I am trying to figure out the test for the conditional formatting.  I have a calculated field to determine if a record is complete.  I just need to check for each piece of equipment on each day in the date range (excluding weekends) there is a completed flag.  If all dates and equipment have a completed flag, the button is green.  If not, the button is red.

      I have a tendency to overthink my databases and was wondering if anyone had any simple ideas.  I was thinking of a filter based on the date range.  If all pieces of equipment had a flag for each day, I would set a flag to 1.  Any range with a 1 would be green, 0 would be red.

       

       

       

        • 1. Re: Report Drill Down recommendations
          philmodjunk

          But your equipment table should only have one record for each different piece of equipment, so any flags would need to be in a record of a different table and I don't see one where you could set such a flag as a stored indexed value as you'd need a table with one record for each piece of equipment for each day. With such a table, what you describe then becomes possible, but it's not your only option. I'd also consider whether an ExecuteSQL query might be used to not only check completeness for a given item for a given day but also over a range of dates such as over a week or month...

          • 2. Re: Report Drill Down recommendations
            jcondon

             My transaction table records the equipment used.  Per the way they wanted the system setup, they wanted a transaction for each piece of equipment each day.  

            • 3. Re: Report Drill Down recommendations
              philmodjunk

              Then that would be the table were you would flag a record as "complete" or not. Depending on how you set this up, you may or may not have to allow for a case where the transaction record for a given piece of equipment is incomplete due to that record not existing at all.