Forum Discussion

JoshCollins's avatar
JoshCollins
Qrew Assistant Captain
5 years ago

Can I Summarize a Numeric Field Within Its Own Table?

Hello,

Is it possible to summarize a numeric field within its own table?

For instance, I have an "Properties" table with fields [Build Cost] and [SqFt]. Build Cost is currency and Sqft is standard numeric. 

I have another Formula - Numeric field called [Build Cost Per SqFt] which uses the formula [Build Cost] / [SqFt].

This formula calculates the Build Cost per SqFt for each Property record.  However, I need to calculate an average Build Cost Per SqFt for all records in the table...not individually. This new "global" field will be used in calculations to create projections, etc.

Am I overlooking a really simple solution to this?



------------------------------
Josh Collins
------------------------------
Relationships
  • OanaWhalen's avatar
    OanaWhalen
    Qrew Cadet
    5 years ago
    Josh,

    The best solution I've found for this is to create a dummy parent table with a single record and create a relationship with Properties with some new [Related Parent] field. Attach all your Properties to that parent record by grid editing the [Related Parent] field to all read 1 (or whatever the RID ends up being). You could also use an automation for this if you set it up before adding your new record.

    You can then create a summary field on the dummy parent table summarizing all your data, then do a look up in your Properties table to pull that aggregated value back down. Not the most straightforward solution, but it works.

    I've also solved a similar problem through using javascript on page load, but that functionality is no longer supported.

    ------------------------------
    Oana Toma
    ------------------------------