SImple arithmetical error

[jdjd]

I've spent 10 hours (including reading posts in the forums) trying to work out why my sums don't work.

Forumistas have been generous in the past; perhaps you can also spare the time now to solve

what must be a very simple error.

I have a database of Fellows of an Oxford college, 1437-2012. There are 2,400 records.

Fields include:

Date of birth This is a calculated field: if we don't know the dob we use YearIn minus the average age at YearIn

of fellows whose dob we do know : until about 1850 this is 22.

YearIn a simple 4 digit year from records: when the person was admitted to the college

YearOut a simple 4 digit year from records: when the person left college.

$$QYear The user inputs a year which then populates that field in all the records.

Custom Dialog ["Get a year"; Please enter the year:"; Fellowships::$$QYear] ---this

does work as required ---fills $$QYear in each record.

I can calculate the years a person was in fellowship (YearOut-YearIn). But all calculations using the global

$$QYear go haywire. A Find for instance

Fellowships::YearIn: [<-fellowships::$$QYear] AND Fellowships::YearOut:[>-fellowships::$$Qyear]

first says no records match, but after a sort produces all 2440 records. While a further calculation, for instance

Age in Qyear = $$Qyear - Date of Birth

produces an inexplicable nonsense. The wizard for constructing

Is there some special characteristic of $$ variables in calculations? (They work well for me if they are Text).

And (as a matter of interest) I can't find any explanation in the support docs nor in The Missing Manual for why

Enter Find Picks up the criteria for the find, I think from Perform Find. Why do I need both?

All and Every help most gratefully received.

[Vaughan]

Why are you using global variables for this?

This expression "fellowships::$$QYear" does not make sense. That to me suggests "a field named $$QYear in the fellowships table". But a field cannot be named $$QYear.

Sorry to be blunt.

Instead of a global variable use a global field for QYear, and use this in the calculations.

For the finds it may get a bit trickier because the calculation engine does not work for finds. The find that you use as an example won't work. You'll need to create a calculation field that works out the age (or whatever it is possibly using the global field) and search that.

Can you explain what it is you want to achieve, not how you think it should be done.

[jdjd]

Thanks. I'm grateful for bluntness ---subtlety often confusing.

What I'm trying to do in this case is work on a population which enters and exits for short periods over the years 1437-2012.

So: who was in in any given single year? how old was each individual in that year? what was the average age in

that year?---and so on.

The global variable I referred to so innocently is the content of a global field.

Maybe if I called the field QYear? But as soon as I click it for global storage (to populate each record)

it turns up as $$QYear. I can use $$QName to find all the fellows ever called Smith or Jones. That is

text. As you say, using $$Qyear (number) creates trickiness, and I'd be grateful to find ways around that.

Where is the documentation on limitations of $$ fields? FMP help says: "A global variable can be used in a calculation

or script anywhere in a file ..."

Thanks again, and I hope in the future.

[comment]

This part makes no sense:

But as soon as I click it for global storage (to populate each record)

it turns up as $$QYear.

In Filemaker, a field and a variable are two different things. Using the "$" character in a field name is not legal (although Filemaker will allow it, if you insist). It would probably be best to name your field simply gYear.

[jdjd]

So, what is the right way to input a value for use in a script?

Let me recap, and be as clear as I'm capable of:

I've got a table with a population of 2400 people, who were all members of a college between 1437 and 2010 (continuing ..)

The table includes information such as:

Name (text)

Date of birth (number: 4 digits for the year)

Year of entry (number: 4 digits for the year)

Year of exit (number: 4 digits for the year)

I can let users put in a name (Custom Dialogue), and Filemaker will find all members with that name.

The procedure is to assign a value (query name) to a field QName, which is global and is then compared

with Name for each record. In my version of FMPro (11.4) I was taught to populate that field by making

it global: Smith (e.g.) in every record. When I say it should be global in the wizard box, Filemaker automatically

turns the name of the field into $$QName. It then compares all Names with $$QName, and returns the

ones that match. Lovely.

What I can't do, and what is holding me up and frustrating me, is use the same procedure for QYear (number --4 digits

representing a year). The custom dialogue is the same (Put in a Year); and it does assign global QYear ($$QYear) to

the table: so far, so like finding instances of $$QName in Name. What this procedure won't do is compare $$QYear

with Date of Birth, YearIn or YearOut. Horrid.

What I want to do is, answer the questions:

How many people were members in a particular year? QYear compared with YearIn, YearOut

How old were those people in that year? Qyear minus Date of Birth

What was the average age of those people in that year? (av of answer to How old question)

And so on. Clearly those kind forumistas who say that $$QYear can't be used that way are

perfectly right. But what is the correct way to specify a query year, and to use it in calculations?

Help will not only solve my problem (which I find no guidance on in the support documentation

nor in the Missing Manual) but will perhaps allow me to sleep better at night.

Many thanks

jd

[comment]

I would put aside all your other questions and concentrate on this part only:

When I say it should be global in the wizard box, Filemaker automatically

turns the name of the field into $$QName.

I am not sure what you are describing here (wizard box?). Certainly, changing a field's storage to global should NOT affect its name.