 | |
calculation fields not calculating
comp.databases.filemaker comp.databases.filemaker
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
calculation fields not calculating -
03-30-2007
, 02:51 PM
#2
| |||
| |||
|
|
Hi everyone. I am using FMP 8.5. I am trying to calculate a sum from a field that repeats in a portal. Basically, whenever certain values are entered into a field in my portal, the value of another field outside the portal is increased. My problem is this... the field that is receiving the calculated total has a question mark (?) in it instead of a number. I am assuming the calculation doesn't work because the ? mark can be added to. Unfortunately, I can't figure out how to set the ? to a 0 and then follow that with an if statement to begin the calculations. Any recommendations would be greatly appreciated!!!! Thanks in advance, STabit |
Typically, the ? appears when the field is not large enough to display the results of the calculation.
Look at the calc field in its own table. Is the calculation correct there?
Matt
#3
| |||
| |||
|
|
Hi everyone. I am using FMP 8.5. I am trying to calculate a sum from a field that repeats in a portal. Basically, whenever certain values are entered into a field in my portal, the value of another field outside the portal is increased. My problem is this... the field that is receiving the calculated total has a question mark (?) in it instead of a number. I am assuming the calculation doesn't work because the ? mark can be added to. Unfortunately, I can't figure out how to set the ? to a 0 and then follow that with an if statement to begin the calculations. Any recommendations would be greatly appreciated!!!! Thanks in advance, STabit |
display the number, for example trying to display the number 18,645 in
a field that's only one character wide obviously isn't possible. Try
making the field wider (even if only temporarily) and see if you then
get a number displayed.
It could also be that the field is formatted to display the number to a
set number of decimal places, which again would make the number too
long to fit in the field's width. Changing the Format -> Number of the
field can help solve this.
As for displaying a 0, there's two parts to this and you shouldn't need
an If statement to fix it. If the sum of the field totals to 0 (eg. 5,
-4, and -1) then a 0 will be displayed, UNLESS you've set the Format ->
Number option to not display 0 results for the field.
Also, by default a Calculation field that is based on fields with no
data in them will not display 0 and will instead remain empty until at
least one of the fields has numerical data in it. You can change this
option in the field Define Calculation window at the bottom - turn off
the "Do not evaluate if all referenced fields are empty" option.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
#4
| |||
| |||
|
|
In article <1175284291.143667.94420 (AT) n59g2000hsh (DOT) googlegroups.com>, "stabit" <sharon.tabit (AT) gmail (DOT) com> wrote: My problem is this... the field that is receiving the calculated total has a question mark (?) in it instead of a number. A ? in a number field usually means the field is not wide enough to display the number, for example trying to display the number 18,645 in a field that's only one character wide obviously isn't possible. Try making the field wider (even if only temporarily) and see if you then get a number displayed. |
help that I had forgotten that that field was calculated; I couldn't
understand at first why FMP wouldn't let me enter data into it. :-)
Then I wondered why there was a "?" where there should have been
numbers. On a hunch, I went into Layout mode and dragged the field
longer, and when I went back to Browse mode, the number was there.
Patty
#5
| |||
| |||
|
|
On 03/30/2007 15:51:31 "stabit" <sharon.ta... (AT) gmail (DOT) com> wrote: Hi everyone. I am using FMP 8.5. I am trying to calculate a sum from a field that repeats in a portal. Basically, whenever certain values are entered into a field in my portal, the value of another field outside the portal is increased. My problem is this... the field that is receiving the calculated total has a question mark (?) in it instead of a number. I am assuming the calculation doesn't work because the ? mark can be added to. Unfortunately, I can't figure out how to set the ? to a 0 and then follow that with an if statement to begin the calculations. Any recommendations would be greatly appreciated!!!! Thanks in advance, STabit First, what do you mean by "...the ? mark can be added to?" You can't add anything to a calculation. FM simply does not permit it: you'll get an error saying "This action cannot be performed because this field is not modifiable." Typically, the ? appears when the field is not large enough to display the results of the calculation. Look at the calc field in its own table. Is the calculation correct there? Matt |
I will give hopefully a clearer example. I have a portal that has a
repeating field. This field contains different fish species. IF the
species entered is a native to the drainage, then the field located
outside the portal (called number of natives) will increase by 1. I
have increased the size of my "number of natives" field located
outside the portal and the "?" still remains. My calculation for the
"number of natives" field is the following:
numNatives =
__________________________________________________ _______________________________
0 or
If ( GenusSpecies:
rigin = "N-Tallapoosa" and Drainage ="Tallapoosa";numNatives + 1; numNatives) or
If ( GenusSpecies:
rigin = "N-Chattahoochee" and Drainage ="Chattahoochee";numNatives + 1; numNatives)
Being new to FMP this may be WAY off base.
I really appreciate all the quick responses!!
Stabit
#6
| |||
| |||
|
|
I apologize, the "can" was a typo. I meant it could NOT be added to. I will give hopefully a clearer example. I have a portal that has a repeating field. This field contains different fish species. IF the species entered is a native to the drainage, then the field located outside the portal (called number of natives) will increase by 1. I have increased the size of my "number of natives" field located outside the portal and the "?" still remains. My calculation for the "number of natives" field is the following: numNatives = 0 or If ( GenusSpecies: rigin = "N-Tallapoosa" and Drainage ="Tallapoosa";numNatives + 1; numNatives) or If ( GenusSpecies: rigin = "N-Chattahoochee" and Drainage ="Chattahoochee";numNatives + 1; numNatives) Being new to FMP this may be WAY off base. I really appreciate all the quick responses!! Stabit |
)This Calculation suffers from three big problems:
- Using the Or function in the manner "0 or ..." means that
FileMaker will try to give you a Boolean logic answer (similar
to performing an If test) which means the result will only ever
be 0 or 1, if the calculation could work at all.
- A Calculation field can not reference itself, so "numNatives +1"
is never going to work. In fact, FileMaker Pro probably
complained about a Circular Reference when you tried to
click on the OK button of the Define Calculation window. This
is the problem that is causing the ? to be deisplayed because
FileMaker cannot calculate any result at all.
- You're also trying to obtain a total across all the related
records, but an If statement using a Related field will
only ever test the data in the FIRST related record, not all
the related records. This is true for many functions when
referencing a related field. Try putting a related field
outside the Portal and you'll see that in Browse mode it
displays just the first related record data.
What you appear to be trying to do is obtain a count of how many
related records in the Portal have their Origin field matching the
Drainage field in the main Table (Parent Table).
Unfortunately it's going to be impossible to give you a correct answer
without knowing more about the structure of your database - what data
is stored, how it is linked between tables, etc.
For example, you might have a Lakes Table that is linked to a Fish
Table. Each record in the Lake Table may use a unique LakeID field to
link it to muliple Fish Table records. These Fish Table records might
be linked from only one Lake each (eg. there are five Trout records
because five lakes have trout in them), OR there might be Fish Table
records that are linked from many different Lake records (eg. one Trout
record, but linked from various Lake records).
The first structure is easy to calculate your wanted number, but the
second structure is going to be impossible without using a script and
button.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
#7
| |||
| |||
|
|
In article <1175300310.871567.119... (AT) l77g2000hsb (DOT) googlegroups.com>, "stabit" <sharon.ta... (AT) gmail (DOT) com> wrote: I apologize, the "can" was a typo. I meant it could NOT be added to. I will give hopefully a clearer example. I have a portal that has a repeating field. This field contains different fish species. IF the species entered is a native to the drainage, then the field located outside the portal (called number of natives) will increase by 1. I have increased the size of my "number of natives" field located outside the portal and the "?" still remains. My calculation for the "number of natives" field is the following: numNatives = 0 or If ( GenusSpecies: rigin = "N-Tallapoosa" and Drainage ="Tallapoosa";numNatives + 1; numNatives) or If ( GenusSpecies: rigin = "N-Chattahoochee" and Drainage ="Chattahoochee";numNatives + 1; numNatives) Being new to FMP this may be WAY off base. I really appreciate all the quick responses!! Stabit This Calculation is definitely "WAY off base". )This Calculation suffers from three big problems: - Using the Or function in the manner "0 or ..." means that FileMaker will try to give you a Boolean logic answer (similar to performing an If test) which means the result will only ever be 0 or 1, if the calculation could work at all. - A Calculation field can not reference itself, so "numNatives +1" is never going to work. In fact, FileMaker Pro probably complained about a Circular Reference when you tried to click on the OK button of the Define Calculation window. This is the problem that is causing the ? to be deisplayed because FileMaker cannot calculate any result at all. - You're also trying to obtain a total across all the related records, but an If statement using a Related field will only ever test the data in the FIRST related record, not all the related records. This is true for many functions when referencing a related field. Try putting a related field outside the Portal and you'll see that in Browse mode it displays just the first related record data. What you appear to be trying to do is obtain a count of how many related records in the Portal have their Origin field matching the Drainage field in the main Table (Parent Table). Unfortunately it's going to be impossible to give you a correct answer without knowing more about the structure of your database - what data is stored, how it is linked between tables, etc. For example, you might have a Lakes Table that is linked to a Fish Table. Each record in the Lake Table may use a unique LakeID field to link it to muliple Fish Table records. These Fish Table records might be linked from only one Lake each (eg. there are five Trout records because five lakes have trout in them), OR there might be Fish Table records that are linked from many different Lake records (eg. one Trout record, but linked from various Lake records). The first structure is easy to calculate your wanted number, but the second structure is going to be impossible without using a script and button. Helpful Harry Hopefully helping harassed humans happily handle handiwork hardships ;o) |
trying to count within one record. Each fish collection is a record.
I may have 100 fish in one record. Unfortunately, I have not figured
out how to total columns in a portal within a record. I want to know,
withing one record, how many of my fish I caught are bottom feeders.
With that total number, I can calculate "benthic fluvial specialists".
Any help would be great! Is this even possible within one record?
STabit
#8
| |||
| |||
|
|
Hi again. I am not trying to count across records. I am actually trying to count within one record. Each fish collection is a record. I may have 100 fish in one record. Unfortunately, I have not figured out how to total columns in a portal within a record. I want to know, withing one record, how many of my fish I caught are bottom feeders. With that total number, I can calculate "benthic fluvial specialists". Any help would be great! Is this even possible within one record? STabit |
against yusing them, but in this case it might just do what you need.
Create a number field many repetitions as you need. MyField
<Number;repeating>
Create a calculation cMyTotal <number ; Sum ( MyField ) >
when you have filled all the repetitions you need you see the total of all
the fish in the cMyTotal field
Hpe this helps
Keep well, Ursus
#9
| |||
| |||
|
|
Hi again. I am not trying to count across records. I am actually trying to count within one record. Each fish collection is a record. I may have 100 fish in one record. Unfortunately, I have not figured out how to total columns in a portal within a record. I want to know, withing one record, how many of my fish I caught are bottom feeders. With that total number, I can calculate "benthic fluvial specialists". Any help would be great! Is this even possible within one record? STabit Perhaps in this case a repeating field can help. Normally I would advise against yusing them, but in this case it might just do what you need. Create a number field many repetitions as you need. MyField Number;repeating Create a calculation cMyTotal <number ; Sum ( MyField ) when you have filled all the repetitions you need you see the total of all the fish in the cMyTotal field Hpe this helps Keep well, Ursus |
#10
| |||
| |||
|
|
On Mar 30, 9:49 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com wrote: In article <1175300310.871567.119... (AT) l77g2000hsb (DOT) googlegroups.com>, "stabit" <sharon.ta... (AT) gmail (DOT) com> wrote: I apologize, the "can" was a typo. I meant it could NOT be added to. I will give hopefully a clearer example. I have a portal that has a repeating field. This field contains different fish species. IF the species entered is a native to the drainage, then the field located outside the portal (called number of natives) will increase by 1. I have increased the size of my "number of natives" field located outside the portal and the "?" still remains. My calculation for the "number of natives" field is the following: numNatives = 0 or If ( GenusSpecies: rigin = "N-Tallapoosa" and Drainage ="Tallapoosa";numNatives + 1; numNatives) or If ( GenusSpecies: rigin = "N-Chattahoochee" and Drainage ="Chattahoochee";numNatives + 1; numNatives) Being new to FMP this may be WAY off base. I really appreciate all the quick responses!! Stabit This Calculation is definitely "WAY off base". )This Calculation suffers from three big problems: - Using the Or function in the manner "0 or ..." means that FileMaker will try to give you a Boolean logic answer (similar to performing an If test) which means the result will only ever be 0 or 1, if the calculation could work at all. - A Calculation field can not reference itself, so "numNatives +1" is never going to work. In fact, FileMaker Pro probably complained about a Circular Reference when you tried to click on the OK button of the Define Calculation window. This is the problem that is causing the ? to be deisplayed because FileMaker cannot calculate any result at all. - You're also trying to obtain a total across all the related records, but an If statement using a Related field will only ever test the data in the FIRST related record, not all the related records. This is true for many functions when referencing a related field. Try putting a related field outside the Portal and you'll see that in Browse mode it displays just the first related record data. What you appear to be trying to do is obtain a count of how many related records in the Portal have their Origin field matching the Drainage field in the main Table (Parent Table). Unfortunately it's going to be impossible to give you a correct answer without knowing more about the structure of your database - what data is stored, how it is linked between tables, etc. For example, you might have a Lakes Table that is linked to a Fish Table. Each record in the Lake Table may use a unique LakeID field to link it to muliple Fish Table records. These Fish Table records might be linked from only one Lake each (eg. there are five Trout records because five lakes have trout in them), OR there might be Fish Table records that are linked from many different Lake records (eg. one Trout record, but linked from various Lake records). The first structure is easy to calculate your wanted number, but the second structure is going to be impossible without using a script and button. Hi again. I am not trying to count across records. I am actually trying to count within one record. Each fish collection is a record. I may have 100 fish in one record. Unfortunately, I have not figured out how to total columns in a portal within a record. I want to know, withing one record, how many of my fish I caught are bottom feeders. With that total number, I can calculate "benthic fluvial specialists". Any help would be great! Is this even possible within one record? |
different Fish records - and this means you are trying to count across
all the related records to get a total of the portal records within the
"Fish Collection" record.
Totalling Portal columns is easy. All you need is a Calculation field
that uses the Sum function or Count function.
eg.
Sum(Relationship::Field)
or Count(Relationship::Field) as long as each field has data in it
Your problem appears to be that your trying to count only specific rows
where the Portal row's Origin basically matches the Fish Collection
record's Drainage (ignoring the extra "N").
This isn't going to be possible in the Fish Collection record unless:
- each portal row Fish record is unique to one Fish
Collection parent record.
or - you use a script to loop through the portal counting
only the appropriate rows.
But since we don't know the structure of your database we can't really
give you the answer. Is each Fish record unique to one Fish Collection
record, or can one Fish record linked from many different Fish
Collection records. ie. Trout may appear in five different Fish
Collection records, but in the Fish table are there five different
"Trout" records or only one??
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.