How to create a summary field to summarize filtered data?

[nareyramos]

Hi guys. I am working on a database about change orders and I have the following fields in each record:

 

Change Order Name, Description, Contract No., etc. (this is not relevant to my question)

Contract Original Sum (coming from a related Contracts database. No problem)

Prior Change Orders

Prior Contract Amount

This Change Order

New Contact Amount

 

Since this is a database to store data about change orders, I have info about change orders for many different contracts. I have a problem with the field "Prior Change Orders" because I don't know how to summarize all the prior change orders that belong to a specific contractor, so that when I imput a new change order for that contractor, I automatically get the correct amount for "Prior Change Orders", which is the sum of all the prior change orders that belong to that contractor and nobody else. Please, forgive me if I am not making myself clear. If somebody needs any clarifications in order to give me a hand, please ask!!! Thank you so much!

 

NR

[Brooks]

TO [ ChangesTable::id_Contractor ] = TO [ ChangeOrders::id_Contractor ]

and TO [ ChangesTable::id_NewChangeOrder ] <> TO [ ChangeOrders::id ]

 

ChangesTable Create field Calc unstored sum ( ChangeOrdersTable::Amount ]

 

You are creating a relationship between your change order changes table and the change orders table on the Contractor id field. Assuming you don't want to include the new PO add the second line to remove it from the contractors change orders TO ( Table Occurrence ). Create a calculation field in the changes table then that sums all the amounts from the the Change order in the Change Orders TO. This field needs to be un stored so it evaluates each time you look at the data. 

[nareyramos]

Brooks,

 

Thank you very much for your reply, but I still don't get it. If you can post a small example with a couple of fields that would be great. Please forgive my ignorance. Thanks.

 

NR

[Brooks]

Post a copy of your file and I will modify it for you.

[nareyramos]

I'm sorry but I can't. It says that I am not permitted to upload "this kind of file", meaning my fm12 file. I sent a message a couple days ago to FMForums asking to clarify this for me and have gotten nothing back so far. I guess I'm running out of options here...

[steigrafx]

Zip the file prior to uploading.

[Lee Smith]

Hi NR, and welcome to the Forums,

 

Here are the steps to add a file, How to add an attachment to a reply

 

Please let me know if you have a problem (via private message). 

 

Also, point me to the post that you referred to in your post here about needing help.

 

TIA

 

Lee

[nareyramos]

First off, thanks for the tip, Steigrafx. Here is the FM file. The change order TO includes a field called Prior_COs that has to be a summary of the field CO_Amount not including the last (current) record. The problem is that it has to summarize only those CO_Amount values that belong to a specific Contract Number (Contract_No), in a way that when I input a new record for any goven contract, the total amount for Prior Change Orders (Prior_COs) automatically pops up.

Change Orders.zip

[nareyramos]

Hi Lee. I saw your message after I sent mine (attachment included!) but thank you very much. My original post is called "How to create a summary field to summarize filtered data?". I don't know if the title makes sense or not, but the post itself has an explanation of my dilemma. Also, the last post with the attachment gives a little bit more info. Please let me know if you need anything else. Thanks again.

 

 

NR

[Brooks]

Here you go

Change Orders.fmp12.zip

[Lee Smith]

Hi NR,

 

I thought you meant a different thread. 

 

Glad you figured out how to attach a file.

[nareyramos]

Brooks, I tested it with two records for the same Contract_No, and what happens is that after I input record #2, the CO_Amount for that second record is added as Prior_COs in the first record, when in reality the amount for Prior_COs in record 1 should remain blank (zero). If you keep adding records selecting the same Contract_No, the values for Prior_COs keep changing in all records. Prior_COs only summarizes prior records (not including current) and once it is summarized for a specific record, its value shall remain the same forever.

[Brooks]

Change the field type for Prior_CO to Number

Click Options

On Auto Enter Tab click Calculated Value

The calculation should already be there, just click OK

Make sure to keep leave Do not replace existing value checked so it does not change the data once stored.

[nareyramos]

Brooks, it works fine now! Thank you so much for your help.

 

NR