calculating certain rows in a portal

[CBL]

I have a database of bills and expenses that I record every month. I have the various bills in a portal with the date they are due and the amounts. What I would like to be able to do is to select only certain rows (or ID's) from the portal and then get a summary of the amounts from all of those rows only. Then, I would like to take this total and divide it by 3 to get a new total. Can anyone help me with this?

[RalphL]

Welcome to the Forum,

Without knowing your table structure, I will have to make a guess. Add a number field in the related table. Auto enter 0 in this field. Write a script like this.

If [NewField = 0]

Set field [NewField, 1]

Else

Set field [NewField, 0]

End If

Commit record

Add a button in the portal row that preforms the following script.

Go to relaated record [Relationship]

Preform Script [new script]

Refresh window

Make a new relationship to the related table. This relationship should include the dates (first and last days of month) aand the new field.

Use the Sum function with this relationship to get the total for the selected rows.

If this doesn't help you, post a sample of your file and I will make the changes to get this to work.

[-Queue-]

A simpler script for changing the value of NewField:

Set Field [NewField; Abs(NewField - 1)]

Commit Records/Requests

[CBL]

Thank you for your quick reply. I am new to this so I may need a little more explanation. First, what is the SetField doing in the script? Is it making some entries equal to 1 instead of 0? If so, how does this help? Won't it make each line equal to 1 since they are all auto entered as 0? Or does this allow me to select which entries become 1 or 0? Second, when I create the relationship to another table, how to I relate it to the beginning and end of the months? Do I have to manually type in the beginning and ending dates of each and every month? Thanks again for your help.

[CBL]

Also, the SetField won't let me enter it the way that you suggested on either example. It says that it is missing an operator value. I am using FileMaker 7 if that helps.

[-Queue-]

Actually, I don't think you need the Go to Related Record step. A button in a portal will act on only the current portal row (related record). So your script only need be

Set Field [relationship::NewField; Abs(relationship::Newfield - 1)]

Commit Records/Requests

This will change the selected portal row's NewField to 1 or zero, appropriately.

You could also put the related NewField in the portal and format it as a checkbox using a value list of 1. Then you wouldn't need a script at all.

The new relationship should be the same as your original one, with the addition of a calculated number field equal to 1 being mapped to the related NewField. That way it will include only the related records with NewField marked as 1 for your Sum calculation.

[-Queue-]

The semicolon in the Set Field step separates the target field, relationship::NewField, from the calculation to specify, Abs(relationship::NewField - 1). It should not be included in the actual calculation.

[Søren Dyhr]

We had a very similar problem up her in this thread, which solved it for version 6, so something needs to be done different:

http://www.fmforums.com/threads/showflat...true#Post134856

--sd

[CBL]

Thanks for your quick reply. This is going to help me tremendously. I have one more question to make sure I understand this right. Let's say that I do set the NewField equal to 1 for certain rows. At that point, what would the sum function look like to get a sum of all the specific amounts in the selected rows?

[-Queue-]

Sum(NewRel::Amount), where NewRel is the duplicate relationship with the additional mapping from a calculation of 1 to NewField.