Retrofit: Sum previous records only

[esegal]

Sorry if this is a really newbie question - I have a calculation I am working on that requires me to calculate the sum of a field entry for all previous records for a particular company excluding the same field in the current record. How do I get a sum for previous records only?

Also, I already have many records in place and I need to implement this from the first record, so I have have to summarize previous records by date since they were created chronologically.

Thanks.

[-Queue-]

If your serial numbers are in incremental order, then you could create a relationship where

CompanyID = CompanyID AND

Serial > Serial

and then make your calculation Sum(newRelationship::fieldToBeSummed)

You can do something similar, using DateEntry instead of Serial, if each company only has one record per date.

[esegal]

What you describe is what I set up, the problem is that the sum I get is for all records related to the company either before or after the current record, not just the records entered prior. There can be more than one record per day per company so date won't work. I don't know if a date & time-of-day reference would be the right way to go, or how to do that if it is the right way to go.

What might work is to sum only records related to said company with a serial number "less than" the current serial number. I just don't know how to do that yet.

[-Queue-]

How is your Serial field defined? The setup above should work if it is incremental, because the records following the current one will be greater than the current Serial, and so will not be included in the > relationship.

You could also use a timestamp field, if one exists for each record.

[esegal]

Ah - I missed your "<" sign. Well, it still doesn't work for me.

Here's what I need to calculate:

1) Original contract amount (from "A" below)

2) Sum of all prior COs (change orders) (trying to use "D" below.)

3) Contract sum including prior COs (calculation)

4) New contract sum including all COs (calculation)

I have #1 & #4 down, but can't get #2 & 3 until I resolve #2.

Relationships between tables:

A) proj_company = proj_company

co_no = co_no

C) proj_company = proj_company AND co_no = co_no

D) proj_company = proj_company AND co_no < co_no

I know there are too many relationships, I will get rid of those I don't need. I've just set them up to accomplish things along the way. If I try to combine them they don't work the same so I keep them separate. Now it's a mess. I'm not good at relationships yet.

[esegal]

to answer your other question, the serial is set with Get From Previous Record + 1. This is in order to be able to revise a CO by adding & "R" through a revise script, so they are not auto-enter serials, per se.

[-Queue-]

Well, it would be a > sign, if the left hand side is the original TO and the right is the newly-created TO for the self-relationship. Otherwise, it will only pull records that are greater than the current co_no. However, if some include 'R', then this will not work well anyway. You should have a true serial field, which is auto-entered and non-modifiable. Users never even need see this field and it should be used for relationships, so that they aren't broken when you add an 'R' to the co_no field.

It looks like you will also need a relationship which uses >= to include the current record with the previous ones.

[esegal]

I'm just not getting results and I'm trying my best.

Questions:

I have my MASTER table and my CHANGE ORDER table. Every record has unique auto serials. The MASTER table is where the bulk of my data is currently entered/stored. The fields in my newly created CHANGE ORDER table populate from the MASTER via LOOKUP.

I cannot figure out which table to enter info and which table to calculate it.

I need a fresh set of eyes because I'm mired down with the way I've been trying to do this and am totally frustrated with myself.

[-Queue-]

Try posting a stripped-down sample of your file for us to investigate.