10 Replies Latest reply on Feb 14, 2012 3:43 PM by philmodjunk

# Simple Calculations not working

### Title

Simple Calculations not working

### Post

Ok I am lost as to why filemaker is doing this but in Table 1 I have

(Record #)     (Value)       (Record 1)         (Record 2)      (Record 3)

1               2066              0                       0                    1

2               2065              0                       0                    1

3               2064              1                       1                    0

4               2063              0                       1                    1

And I am making a second table with the the same field names but different calculations, and the two tables are related by (Record #). When I make Table 2 Value = Table 1::Value it returns the numbers in the right order from 2066,2065,2064,and 2063. However when I go to make the calculation  (Table 1::Record 1 + Table 1::Record 2) for Table 2 Record 1 then it return a list of 1's, and when I do the calculation (Table 1::Record 2 + Table 1::Record 3) it returns a list of 2's. So I went to see when I make  Table 2 Record 1 = Table 1::Record 1 , Table 2 Record 2 = Table 1::Record 2 , and Table 2 Record 3 = Table 1::Record 3

Table 2 Results

(Record #)     (Value)       (Record 1)         (Record 2)      (Record 3)

1               2066              0                       1                    1

2               2065              0                       1                    1

3               2064              0                       1                    1

4               2063              0                       1                    1

Does anyone know what is happening

• ###### 1. Re: Simple Calculations not working

In your chart of sample data you appear to be confusing the terms "record" and "field". Shouldn't your three right hand fields be named "Field 1", "field 2" and "field 3"? IT makes it tricky to analyze your description of the problem.

I think you are trying to combine the values of multiple records in a single calculation. When you set up this kind of expression:

Related Table::Field 1 + Related Table::Field 2, the expression only accesses data in a single record, the first such related record. "First" is defined as either the first related record to be created, or if a sort order was specified for the relationship, the related record that sorts to be first as specified by that sort order.

To combine data from other related records requires changes in your expression. Your expression can use aggregate functions such as Sum ( Related Table::field) or a summary field defined in the related table to access all related records. Your expression can use GetNthRecord to access specific related records such as retrieving values from the 4th related record.

• ###### 2. Re: Simple Calculations not working

I know the record thing may be a bit confusing but that is what the fields are labled. I thought that that might have been why it wasn't working but I even changed the fields names but I get the same error. But the main thing that is weird is that when I simply make say Field 1 from Table 2 to equal Field 1 from Table 1 it doesnt return the right numbers but it does when I make Values from Table 2 equal Values from Table 1

The Calculation used for Table 1 (Field 1) =If(GetNthRecord(Record Value;1)=GetNthRecord(Record Value;Record Number+1);1;0)

The Calculation used for Table 1 (Field 2) =If(GetNthRecord(Record Value;2)=GetNthRecord(Record Value;Record Number+2);1;0)

The Calculation used for Table 1 (Field 3) =If(GetNthRecord(Record Value;3)=GetNthRecord(Record Value;Record Number+3);1;0)

Which returns

Field 1             Field 2           Field 3

0                       0                    1

0                       0                    1

1                       1                    0

0                       1                    1

1                       0                    1

The Calculation used for Table 2 (Field 1) =Table 1::Field 1

The Calculation used for Table 2 (Field 2) =Table 1::Field 2

The Calculation used for Table 2 (Field 3) =Table 1::Field 3

Field 1             Field 2           Field 3

0                       1                    1

0                       1                    1

0                       1                    1

0                       1                    1

0                       1                    1

• ###### 3. Re: Simple Calculations not working

Field names will not affect how a calculation evaluates. It just makes it harder for others to understand what you are trying to accomplish.

Can you copy and paste the original expressions that didn't work for you here?

Can you confirm that there is only one record in table 1 for any given value in the value field?

• ###### 4. Re: Simple Calculations not working

Table 1 Calculation

• ###### 5. Re: Simple Calculations not working

Table 1

• ###### 6. Re: Simple Calculations not working

Table 2 Calculation

• ###### 7. Re: Simple Calculations not working

Table 2

• ###### 8. Re: Simple Calculations not working

Thanks, that provides an excellent view of the calculations and the data involved. I'm assuming from what is shown that "first" is the actual name of Table1 or at least the name of one table occurrence of it.

Some observations on your first calculation field:

If ( GetNthRecord ( Value ; 1 ) = GetNthRecord ( Value ; RecordNumber + 1 ) ; 1 ; 0 )

All references in this calculation refer to  records, fields in table 1, there are no references to table 2 in this calculation. GetNthRecord, when it refers to records in the same table like this, refers to records as controlled by the current sort order. GetNthRecord ( value ; 1 ) thus refers to the value field in the first record in your current found set. From your screen shot, that would currently be 12 for every field in the table. Change the found set by performing a find or by sorting your records in a different order and this part of the expression returns a different value. Working with the found set shown in the screen shot, this means that your calculation compares 12 to 41 in record 1, 12 to 18 in record 2, 12 to 19 in record 3. Since none of these pairings are equal, 0 is returned.

Note that you can simplify this expression to just:

GetNthRecord ( Value ; 1 ) = GetNthRecord ( Value ; RecordNumber + 1 )

and it will return the same values. (True = 1 and False = 0)

This is further complicated by the fact that this expression appears to be defined as a stored calculation. It should be unstored in order for it to correctly update as found set modfications change the found set. Otherwise, the value shown is reflects the found set that was current at the time the record was created and when a record was created, the Record + 1 record does not yet exist.

Given that info, what is it that you really want this expression to compute? Which values from which fields in which records?

• ###### 9. Re: Simple Calculations not working

Actually the data that you see from Table 1 (First) is how it is supposed to be. It is supposed to compare the first value to every value following it down the list. So that is why you get a "1" when (Record Number)=18. The first comparison is 12-41,then 12-18, then 12-19....and the 18th comparison is 12-12 which return a "1".

But what I didn't show it that for every field I went and changed the calculation.

The Calculation used for Table 1 (Field) Record 1 =If(GetNthRecord(Record Value;1)=GetNthRecord(Record Value;Record Number+1);1;0)

The Calculation used for Table 1 (Field) Record 2 =If(GetNthRecord(Record Value;2)=GetNthRecord(Record Value;Record Number+2);1;0)

The Calculation used for Table 1 (Field) Record 3 =If(GetNthRecord(Record Value;3)=GetNthRecord(Record Value;Record Number+3);1;0)

So when you look at Field (Record 3) which referes to the 3rd value which equals 18. The first five numbers in this field are 00001. Meaning that from the 3rd value which equals 18, another 18 doesn't show up for another 5 records down the list.

Comparison 1: (18-19) =0

Comparison 2: (18-17) =0

Comparison 3: (18-16) =0

Comparison 4: (18-4) =0

Comparison 5: (18-18) =1

The problem was that in Table 2 when I were to go and add the fields from table 1 together it would return the wrong values with a list of 4's and then 5's . Table 2::Record 1 should of read like this:

Table 2 (Field) Record 1

0

0

0

0

1

0

1.....

For some reason I have concluded that it is because I was referencing a calculation field that contained GetNthRecord. But I somewhat solved this by creating an additional calculation table (That became Table 2) that used the lookup function on Table 1. And what now is Table 3(Previously Table 2) is able to add the fields together from Table 2 without the error of a list of 4's and then lists of 5's.

• ###### 10. Re: Simple Calculations not working

I understood that this example was for just the first field. But I was not clear on whether this calculation was actually evaluating the way you thought it was. My intent was to confirm that this first calculation was correct before attempting to determine what was needed for the calculations in table 2.

This all looks very strange to me as I do not have any idea why you are setting this up in this manner. I can't help but think there would be a much, much simpler method that could be used if I had a more complete picture of this process.

Both this post and your other thread look like you are attempting to use calculations where a relationship would do the same job of matching values in much simpler fashion.