3 Replies Latest reply on Aug 15, 2012 9:27 PM by philmodjunk

    Structure: Actions Journal w/ many details tables

    SkipperID

      Title

      Structure: Actions Journal w/ many details tables

      Post

      Trying to figure out the best structure for a property management db.

      I have Assets (Parcels, Buildings and Units) that I want to create a journal of activities/actions for.

      Each category of ACTIONS has enough specific field information that I have divided them into their own tables: Real Estate, Maintenance, Correspondence, Notes.

      For Example, the fields in Real Estate are:

      Type (Listing/Inquiry/Offer/Counteroffer/Sale/Purchase)
      Date
      ExpireDate
      Buyer
      Seller
      Amount (of offer/listing/etc.)
      Realtor
      ...etc

      In order to connect those activities with their details, I am considering an ACTIVITY table which connects the ASSET to ACTION DETAILS

      So the ACTIONS Table is: ACTIONID, ASSETID, ActionCategory, ActionDetailsID, CreatedDate

      *** Which table those actiondetails are in is dependant upon the ActionCategory.

       

      Here's how I will use the data:

      1. Imagine now that I inputing a transaction.

      A. I create a new record in the Actions table.

      B. based on the Category of the action, I know the which action details table to reference (which will open on the form for input) 

       

      2. Imagine now I am creating a report of all ACTIONS for the past week.

      A. I search the ACTIONS table by a date range.

      B. From those records i somehow pull the details from their Details using ActionCategory and ActionDetailsID

      C. Sort/Group by Date/Category

       

      - I'm trying to model this on an order/orderdetails set up, but the trick seems to be that there are many different orderdetails tables because the products have such different specs.

      Is there a better way to approach this?

      Thanks!

       

        • 1. Re: Structure: Actions Journal w/ many details tables
          philmodjunk

          Unless an ActionDetails record should be linked to more than one asset you do not need the actions table to serve as a join table between assets and action details. The category and date fields can be moved to the actionDetails record.

          One way to manage the multiple action tables is to add a tab control with a different portal to a different actionDetails type of record on each tab.

          • 2. Re: Structure: Actions Journal w/ many details tables
            SkipperID

            Thanks for the quick response.

            That makes sense to me as far as getting information in, but how do I pool it all together to get a report of all actions regardless of type by date/asset?

            The only field that each details table will have in common is that they all link to a record in the assets table.

            • 3. Re: Structure: Actions Journal w/ many details tables
              philmodjunk

              Ok, that starts to explain the thought behind the actions table.

              There are two basic approaches to this type of issue:

              1) Create a mega table with all the fields for all the different types of actions. Any given type of actrion only uses a small sub set of the total fields in your table.

              2) Use your Actions table as a "backbone" table and link your different details tables to it as you have described.

              The end result, when you set up your layouts for each action detail looks and functions much the same. And reporting can be a real challenge either way.

              There are a number of features you can play with in a number of different combinations. Here's one possibility:

              Set up a list view layout based on Actions.

              Arrange the layout body with horizontal rows of records, one row from each related table. When you view this in browse mode, only one row of data contains data. By setting the fields to slide up and "resize" enclosing part, the empty rows of fields disappear when you print, preview or PDF your report layout.

              The challenge is how to handle the field labels on the layout so that you only see layout text for the specific "detail row" that contains data.

              Option1: use calculation fields defined in the detail table to produce the lable text and add them to your layout with the other detail fields.
              Option 2: If you set up a single row of lables above your row of fields, you can use conditional formatting to set the font size to 500 for labels for the empty detail rows and can stack multiple layers of layout text on top of each other. (one label in each stack of text is visible for any given activity record.)