1 Reply Latest reply on Mar 5, 2012 2:47 PM by philmodjunk

    Taking it to a new level -- Compiling totals



      Taking it to a new level -- Compiling totals


      I have amazed myself with what I have accomplished (and surprised my managers).  I am not a member of IT but Accounts payable.  I started my databases to supplement some deficiencies from our accounts payable software.  I cannot change the software so I have to work around the limitations.

      At the end of each day I read a text file (report) from which I parse out the report into usable data, such as invoice number, date, amount, batch number, voucher number (these last two assigned by the AP software) posting date, and vendor number. 

      I summarize this into another table:  batch info-APUser, batch number, date, number of invoices keyed, total dollar amount.  This is where I got the attention of management because now I know how many items are being processed daily by each AP Processor.  Each person will have a variable number of batches per day; typically two but more if they do imports of batches from certain vendors.

      I have built a table that summarizes each person per week.  I added a week number calculation field.  What I am trying to do is expand this to show how many per Monday, Tuesday etc of the week each person does.  I realized I needed to tag each batch appropriately.  To get weekly totals I had a "key" field of week.number  and person's initials.  The key would look like:  201201.th for TH's total of week 1 in 2012.  It worked find.

      To expand to days of the week I changed the calculation to 201201.02.th for week number - day number - for keyer.  I created a relation alias for each day of the week and created a calculation field for each day to sum approprately. 

      What I get is "index missing" and no sums.  There is a record for each keyer for each week.  I am generating keys on the summary record that should have matches in  the Batch.table for each keyer.  Manually I can copy a value from the summary record and use it to find values in the batch.table. 

      Any suggestions as to what I am missing?  I am sure it is something simple.  Solving this will move my database app from a nice tool to close end of day to a major analytical total regarding staffing and productivity metrics. 

      Thomas Hill

        • 1. Re: Taking it to a new level -- Compiling totals

          At risk of pointing out the obvious, you are missing an index for your calculation field. Open manage | database | fields, find this field and I predict that it will be listed as "unstored". You'll need to figure out why it is unstored. Most likely, your calculation refers to a field from a related table and by definition, such a calculation cannot be stored. If this is the case, you'll need to redesign things to get stored data so there can be an index to use in the relationship.

          Do you have a date field? If so, I don't see the need for this field at all, you can use an existing date field and either a name, iniitials or PersonID field in either a relationship or a sub summary part equipped summary report layout to get sub totals for each person broken down by day.