I have a general design question (FM7, MacOSX).  I don�t have any formal
database programming training so please bear with me if these questions seem
obvious.  I did have a Fortran 77 class my 1st semester in college which is
where I learned my basic programming skills (if you want to call them
skills)

I learned filemaker 6 by practice and just upgraded to 7.

Is it considered a bad practice to, for organizational reasons, create a
separate table for all global fields in a multiple table database?

If I am using globals as counters and fields to display results of looping
calculations, is it more efficient to define the globals in the table that
contains the records I am looping thru? What if they are relational?

For example, consider a table of companies with multiple contacts stored in
a separate table:

Table 1 - (company name, address, zip, companyID)
Table 2 - (contact name, phone number, companyID)
Table 3 - globalList, globalCount

If I wanted to print labels for all the contacts for a certain zip range, I
would have to set up a layout that is based on the contacts table, which is
related to the companies table by companyID (1:1 I guess)

I also need the quantities of each zip in the found set of contacts.

I created a script that sorts and loops thru the records in the contact
table and counts the number of each zip and sets the globalList field like
this:
Zip - qty
19000 - 25
19001 - 101
19004 - 3
....
19099 - 21

My old file (FM6) was not relational, ie each company record had one contact
listed.  Two contacts, two separate company records.  I had a similar script
setup in the old file and it ran much faster than the one in FM7.

Is the slowness a result of...
A) the relationship between the companies and contacts?
or
B) the globals being on a different table?
Or
C) both

Also, in FM6, it seems like scripts were based-in/tied-to a specific file,
where now you can reference any field on any layout in any table with one
script.  Could this account for anything speedwise?


Geoff

I use globals in an admin table in the user file (in data separation 
model) wherever possible.

Being globals, you can pass data into them from any table, without a 
relationship path to the table containing the global 9the globale table 
TOc has to be on teh rel graph of course).

The thing to be aware of is that you have to navigate to a layout 
belonging to the 'global table' , to validate rels that pass stored data 
FROM the globals.

Similarly, to navigate to related records for example. Might be on a 
Company layout, with a portal to People. You want to select a person, 
and go to their People record. You can use a global in Admin TBL, to 
hold the People_ID , and a rel from Admin to People (using 
g_ID::PeopleID). You  pass the a portal row entity ID to a global in 
Admin table, your script must then go to that table layout, then go 
related record person record.

I prefix my Admin tables aa_Admin... so they all sort to the top of 
popup lists. I use 'global' tables for general admin stuff, hilites, 
layout nav..., and Search fields, and  new record data when I use 
globals in   'pending' or edit roles (before script passing to 
appropriate tables).


Keeping as many globals as possible out of the actual data files is a 
bit cleaner I suppose. You can also reuse them for multiple situations, 
label them g_text1, g_text2... rather than create dedicated globals for 
each script pass...

Chris Brown
Neurosurgery
University of Adelaide



G.Miller wrote:
> I have a general design question (FM7, MacOSX).  I don�t have any formal
> database programming training so please bear with me if these questions seem
> obvious.  I did have a Fortran 77 class my 1st semester in college which is
> where I learned my basic programming skills (if you want to call them
> skills)
> 
> I learned filemaker 6 by practice and just upgraded to 7.
> 
> Is it considered a bad practice to, for organizational reasons, create a
> separate table for all global fields in a multiple table database?
> 
> If I am using globals as counters and fields to display results of looping
> calculations, is it more efficient to define the globals in the table that
> contains the records I am looping thru? What if they are relational?
> 
> For example, consider a table of companies with multiple contacts stored in
> a separate table:
> 
> Table 1 - (company name, address, zip, companyID)
> Table 2 - (contact name, phone number, companyID)
> Table 3 - globalList, globalCount
> 
> If I wanted to print labels for all the contacts for a certain zip range, I
> would have to set up a layout that is based on the contacts table, which is
> related to the companies table by companyID (1:1 I guess)
> 
> I also need the quantities of each zip in the found set of contacts.
> 
> I created a script that sorts and loops thru the records in the contact
> table and counts the number of each zip and sets the globalList field like
> this:
> Zip - qty
> 19000 - 25
> 19001 - 101
> 19004 - 3
> ...
> 19099 - 21
> 
> My old file (FM6) was not relational, ie each company record had one contact
> listed.  Two contacts, two separate company records.  I had a similar script
> setup in the old file and it ran much faster than the one in FM7.
> 
> Is the slowness a result of...
> A) the relationship between the companies and contacts?
> or
> B) the globals being on a different table?
> Or
> C) both
> 
> Also, in FM6, it seems like scripts were based-in/tied-to a specific file,
> where now you can reference any field on any layout in any table with one
> script.  Could this account for anything speedwise?
> 
> 
> Geoff
> 

"Chris Brown" wrote:

> The thing to be aware of is that you have to navigate to a layout
> belonging to the 'global table' , to validate rels that pass stored data
> FROM the globals.

In relation to the above, if I am passing data into globals, I don�t need to
switch the layout, right?.  Or if I am using a global in a loop as a counter
as in:

Loop
If (some_field = some other field)
Set field (global_table::global_1, global_table::global_1 + 1)
End if
Go to record next, exit after last
End loop

The question is, why are my loops so slow now that I am using FM7?
Is it because I am looping thru records on one table and comparing related
fields on those records (from my original post)?

Thanks,

Geoff

to pass data into globals, you don't need to be on a layout owned by the 
global's parent Table, in order to pass data into the global.

As for your speed issue, I would suggest it is a product of the nature 
of the count you are doing. To establish this is the culprit, you could 
restructure how you are counting. I think I would create a rel between 
g_zip and Zip, and simply count that.  Zip will need to be indexed. You 
can set up a loop to loop through your dedicated Zip table records.

<<Also, in FM6, it seems like scripts were based-in/tied-to a specific file,
where now you can reference any field on any layout in any table with one
script.  Could this account for anything speedwise?..

Not quite. Globals can be referenced without rel paths, no globals must 
have a valid rel path, or the script must navigate to a layout the field 
in on,  to use its value...


Chris



G.Miller wrote:
> "Chris Brown" wrote:
> 
> 
>>The thing to be aware of is that you have to navigate to a layout
>>belonging to the 'global table' , to validate rels that pass stored data
>>FROM the globals.
> 
> 
> In relation to the above, if I am passing data into globals, I don�t need to
> switch the layout, right?.  Or if I am using a global in a loop as a counter
> as in:
> 
> Loop
> If (some_field = some other field)
> Set field (global_table::global_1, global_table::global_1 + 1)
> End if
> Go to record next, exit after last
> End loop
> 
> The question is, why are my loops so slow now that I am using FM7?
> Is it because I am looping thru records on one table and comparing related
> fields on those records (from my original post)?
> 
> Thanks,
> 
> Geoff
> 

"Chris Brown" wrote:

> I think I would create a rel between
> g_zip and Zip, and simply count that.  Zip will need to be indexed. You
> can set up a loop to loop through your dedicated Zip table records.

I'm sorry Chris but I don�t understand how to set up the relationship and
where to place the fields.  It makes sense to do it that way instead of
looping and counting...

Table (fields)
companies (CompanyID, Company Name, City, State, Zip)
contacts (CompanyID, Name)

Relationship companyID::companyID

I perform a find for a range of zips in companies::zip while in the contacts
layout.  That makes the found set a set of contacts.

I created a global_zip field in the contacts table and set up a relationship
between contacts::global_zip and companies::zip.

Then I entered '19000' into the global and tried in a script:
Set field (global_list, count(companies::zip))  but I only get 1 as a result
(although there are 53).

It seems like I should be counting from the companies table but the number
of contacts that have the zip code is more than the number of companies that
have the same zip because there is more than one contact for each company.

ok, no problem.

You have created a g_Zip in Contacts, and the rel is right (g_Zip::Zip). 
For a QAD (quick and dirty) test, add a portal using the rel , so that 
you can confirm the rel demonstrates what you think it should.
the real issue I think,  is  have your script count related CompanyID, 
not zip.

I would suggest that you might benefit from having a dedicated Zip 
table. You could populate it with a download from the PostOffice or 
whatever lists that kind of data in your area. You could then uses that 
for lookups to auto enter suburbs, rather than typing (avoids typo's 
too); but if your solution is small it may be gilding the lily a bit.

You want to count Companies by zip. If you parsed out the zips you have 
already, into a Zip table, you could create a count field using a rel 
from Zip::CompanyZip...

regards

Chris




G.Miller wrote:
> "Chris Brown" wrote:
> 
> 
>>I think I would create a rel between
>>g_zip and Zip, and simply count that.  Zip will need to be indexed. You
>>can set up a loop to loop through your dedicated Zip table records.
> 
> 
> I'm sorry Chris but I don�t understand how to set up the relationship and
> where to place the fields.  It makes sense to do it that way instead of
> looping and counting...
> 
> Table (fields)
> companies (CompanyID, Company Name, City, State, Zip)
> contacts (CompanyID, Name)
> 
> Relationship companyID::companyID
> 
> I perform a find for a range of zips in companies::zip while in the contacts
> layout.  That makes the found set a set of contacts.
> 
> I created a global_zip field in the contacts table and set up a relationship
> between contacts::global_zip and companies::zip.
> 
> Then I entered '19000' into the global and tried in a script:
> Set field (global_list, count(companies::zip))  but I only get 1 as a result
> (although there are 53).
> 
> It seems like I should be counting from the companies table but the number
> of contacts that have the zip code is more than the number of companies that
> have the same zip because there is more than one contact for each company.
> 
> 

"Chris Brown" wrote:

> ok, no problem.
> 
> You have created a g_Zip in Contacts, and the rel is right (g_Zip::Zip).
> For a QAD (quick and dirty) test, add a portal using the rel , so that
> you can confirm the rel demonstrates what you think it should.
> the real issue I think,  is  have your script count related CompanyID,
> not zip.

I confirmed that the rel is correct.

> I would suggest that you might benefit from having a dedicated Zip
> table. You could populate it with a download from the PostOffice or
> whatever lists that kind of data in your area. You could then uses that
> for lookups to auto enter suburbs, rather than typing (avoids typo's
> too); but if your solution is small it may be gilding the lily a bit.

My solution is small.  I'm having enough trouble as is.

> You want to count Companies by zip. If you parsed out the zips you have
> already, into a Zip table, you could create a count field using a rel
> from Zip::CompanyZip...

Actually I want to count contacts by zip, (or zips by contact is probably a
more accurate description).  When I take a mailing to the post office, they
want a list of all zips and their quantities attached to the paperwork.  For
example, if I was mailing out 19000...19050 they desire:

19001 - 23
19002 - 35
19003 - 34
....
19050 - 53

I think that looping through and counting zips vai a rel is the best way to
do it as you suggested, but I don�t understand how you can count a related
field.  

The script would be like:
Go to record (first) in contacts table sorted by related zip
Set a global to the current zip
Count the number of contacts that have that zip
Set a global_list to the current zip & the count
Go to next record
Loop
If (the global zip = the current zip)
 Do nothing
Else
 Count the number of contacts that have that zip
 set a global_list to current zip & count
 Set a global to the current zip
End if
Go to record next exit after last
End loop

Is this a sound approach?

I think this would be faster then using a global counter as I did before but
I still am not sure as to how to count the number of contacts that have that
zip.

Thanks for your help!

Geoff

If you need a list of all the "counts" of a specific zip code, there is a
simple approach:

In the contacts database add a field zip_count(summary) = Count of zip

I understand you have a found set. Thats just what we need :)

Sort your found set by zip

Create a layout and add a subsummary part "when sorted by:" zip
place your ZIP and the zip_count field in it

Set up LIST viewing mode.

Enter PREVIEW MODE

Print the list

Exit preview mode

and you're done

Regards, Wolf



"G.Miller" <sorry@idontwantspam.com> schrieb im Newsbeitrag
news:BDC06AF6.2B53%sorry@idontwantspam.com...
> "Chris Brown" wrote:
>
> > ok, no problem.
> >
> > You have created a g_Zip in Contacts, and the rel is right (g_Zip::Zip).
> > For a QAD (quick and dirty) test, add a portal using the rel , so that
> > you can confirm the rel demonstrates what you think it should.
> > the real issue I think,  is  have your script count related CompanyID,
> > not zip.
>
> I confirmed that the rel is correct.
>
> > I would suggest that you might benefit from having a dedicated Zip
> > table. You could populate it with a download from the PostOffice or
> > whatever lists that kind of data in your area. You could then uses that
> > for lookups to auto enter suburbs, rather than typing (avoids typo's
> > too); but if your solution is small it may be gilding the lily a bit.
>
> My solution is small.  I'm having enough trouble as is.
>
> > You want to count Companies by zip. If you parsed out the zips you have
> > already, into a Zip table, you could create a count field using a rel
> > from Zip::CompanyZip...
>
> Actually I want to count contacts by zip, (or zips by contact is probably
a
> more accurate description).  When I take a mailing to the post office,
they
> want a list of all zips and their quantities attached to the paperwork.
For
> example, if I was mailing out 19000...19050 they desire:
>
> 19001 - 23
> 19002 - 35
> 19003 - 34
> ...
> 19050 - 53
>
> I think that looping through and counting zips vai a rel is the best way
to
> do it as you suggested, but I don�t understand how you can count a related
> field.
>
> The script would be like:
> Go to record (first) in contacts table sorted by related zip
> Set a global to the current zip
> Count the number of contacts that have that zip
> Set a global_list to the current zip & the count
> Go to next record
> Loop
> If (the global zip = the current zip)
>  Do nothing
> Else
>  Count the number of contacts that have that zip
>  set a global_list to current zip & count
>  Set a global to the current zip
> End if
> Go to record next exit after last
> End loop
>
> Is this a sound approach?
>
> I think this would be faster then using a global counter as I did before
but
> I still am not sure as to how to count the number of contacts that have
that
> zip.
>
> Thanks for your help!
>
> Geoff
>
>
>
>
>
>

Hi Geoff,

why not take a different approach?
add a calculated field, dataformat number, set to value "1" in your
contacts table
add a relation g_zip in companies to zip in contacts
add a calculated field in companies, that summarizes the "1" based on
the relation between g_zip and zip
Holger

"G.Miller" <sorry@idontwantspam.com> wrote in message news:<BDC0229F.29F7%sorry@idontwantspam.com>...
> "Chris Brown" wrote:
> 
> > I think I would create a rel between
> > g_zip and Zip, and simply count that.  Zip will need to be indexed. You
> > can set up a loop to loop through your dedicated Zip table records.
> 
> I'm sorry Chris but I don?t understand how to set up the relationship and
> where to place the fields.  It makes sense to do it that way instead of
> looping and counting...
> 
> Table (fields)
> companies (CompanyID, Company Name, City, State, Zip)
> contacts (CompanyID, Name)
> 
> Relationship companyID::companyID
> 
> I perform a find for a range of zips in companies::zip while in the contacts
> layout.  That makes the found set a set of contacts.
> 
> I created a global_zip field in the contacts table and set up a relationship
> between contacts::global_zip and companies::zip.
> 
> Then I entered '19000' into the global and tried in a script:
> Set field (global_list, count(companies::zip))  but I only get 1 as a result
> (although there are 53).
> 
> It seems like I should be counting from the companies table but the number
> of contacts that have the zip code is more than the number of companies that
> have the same zip because there is more than one contact for each company.

"Wolf" wrote:

> If you need a list of all the "counts" of a specific zip code, there is a
> simple approach:
> 
> In the contacts database add a field zip_count(summary) = Count of zip

I tried to do this but the problem is the zip field is located in the
companies table.  When I try and create the summary field, the zip field is
not listed as it is a related.

Geoff

I have fiddles around with my loop and found something interesting (to me)
with regards to the speed. (My machine is a 12" PowerBook 1.33MHz with
768RAM)

I have a found set of 1000 records.

IN FM7 I did a simple loop with no calculations:

Loop
Go to record next, exit after last
End loop

It took 17 seconds.

In FMPro6, the same loop took less than 1 second.

I wonder why FM7 is so much slower?

Geoff

I have fiddles around with my loop and found something interesting (to me)
with regards to the speed. (My machine is a 12" PowerBook 1.33MHz with
768RAM)

I have a found set of 1000 records.

IN FM7 I did a simple loop with no calculations:

Loop
Go to record next, exit after last
End loop

It took 17 seconds.

In FMPro6, the same loop took 5 seconds.

I wonder why FM7 is so much slower?

Geoff

This doesn't sound right.  Is this the same exact script and performed 
on the same exact layout, and both starting from the first record?  Try 
adding Freeze Window as the first step.

In my tests (granted, this is on a Windows XP box), your script takes 
2.5 seconds to run.  If I add a Freeze Window at the beginning, the 
script runs instantaneously.

G.Miller wrote:
> I have fiddles around with my loop and found something interesting (to me)
> with regards to the speed. (My machine is a 12" PowerBook 1.33MHz with
> 768RAM)
> 
> I have a found set of 1000 records.
> 
> IN FM7 I did a simple loop with no calculations:
> 
> Loop
> Go to record next, exit after last
> End loop
> 
> It took 17 seconds.
> 
> In FMPro6, the same loop took less than 1 second.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

> Try adding Freeze Window as the first step.

Wow, I added freeze window and it is very fast now. I didn't realize that
freezing the window would have that much of an effect.

Thank you very much. I guess its another mountain out of a mole hill on my
part.

Geoff