All things TrackVia!

Read, write, comment.

This is a public Category  publicRSS

Customer Story

    • Using logic formulas to calculate a sum of...
      Customer Story posted Mar 06 by tnelson
      436 Views, 4 Comments
      Title:
      Using logic formulas to calculate a sum of multiple records in one table and populate the result in another table
      Summary:

      Here's the setup:

      • One table (database) is titled "Equipment".  The purpose of this table is to list pieces of rental equipment for various programs along with their associated costs.  The fields include the program number, the equipment description, cost/ea, qty, # of days, and total for that particular record.
      • Another table is titled "Overview".  The purpose of this table is to list all of the programs (each program is a single record).  In this table there would be a totals column that would provide the sum total of the cost of all equipment from the "Equipment" table.

      What I'm trying to accomplish is to create a formula for the totals column in the "Overview" table.  This formula would check for certain conditions then calculate a result.  Here's how the logic might be described:

      • Does the program number for the record in the "Overview" table equal the program number for multiple records in the "Equipment" table?
      • If so, then sum the "Total" column of all the matching records in the "Equipment" table and return the result to the "Total" column of the "Overview" table.
      • If there is no match, then return "0".

      So, essentially, I would like to be able to view the total cost of rental equipment for a particular program, and then be able to use that total for other purposes within the database (meaning, that the "Statistics" format view of TrackVia that summarizes some data for me won't work for this purpose).

      Any ideas on how to structure a logical formula for this, if even possible?  Hopefully this was clearly described.  Thanks in advance.

    Comments

    • Although TrackVia's statistics mode can be used to return these results there is not currently a way to then use that total, average, etc in a field.  You would have to run a view in the Equipment database to show all pieces that were involved with that program in statistics mode and then manually insert the total into your record in the Overview database.  We are working on a child "roll-up" function that will allow for this and this is expected to become available during the 2nd quarter of 2009

    • Great!  I'll be looking forward to it.  On a related note, and perhaps this is a feature request, I don't know if any type of pivot table functionality is in the plans, but it would be an extremely helpful tool to assist in parsing and cross-cutting the data.  I've seen this in another online database and it was pretty fantastic.

      Thanks again...

    • It's Q309... Any update on the Child Roll Up function. I haven't been able to find it.

    • We left you a voicemail today indicating that this is something that is still a very high priority and will be available this fall.  We will be making an announcement in this forum once this feature is released.