SQL SELECT statement equivelent 
Author Message
 SQL SELECT statement equivelent

Another question from the FileMaker newbie.  I am using the evaluation
copy of FileMaker Pro 5.5, the one with NO help file.  I need to find
out what is the FileMaker equivelent of the SQL SELECT statement.  For
example:  I have a salesmen file.  Where I have the salesmen name,
sale date, and sale amount fields.  How can I wirte a script to select
all the salesman named John Smith and total all his sales?  I know I
can do this with a column break report, however I need to script this
so I can build on it.  I've searched the group archives for an answer
but no luck.  Please help.


Tue, 11 Nov 2003 02:20:57 GMT
 SQL SELECT statement equivelent

There are essentially two ways to gather records-- dynamically, via searches
(Finds), or relationally, using GTRR (Go to related records). Finds use
Summary fields to return aggregate data, and relationships use the Aggregate
functions for the same purpose.

As you say, finds will not stay put, and if you want to "build on" the data,
you'll need to create useful relationships.The most basic relationship would
be using the Salesman ID as match fields on both sides. Thus:

Sum(Salesman::Invoice_Total)

would return the total of all the invoices totals for that salesman, where
"Salesman" is the name of a relationship that uses the salesman ID as the
match field on both sides of the relationship (you can use the name, but
it's not very discrete).

You can add filters to that relationship by creating concatenated match
fields on both sides, which will slice the view as you like, say for a given
month per given salesman, etc.

You may also collect the data dynamically, back to using searches, by
posting the collected values to a global number field created for the
purpose. Thus, if you perform the appropriate find, in a script, you can
then

Set Field["gTotal", "Sum_Total"]

where "gTotal" is a global number field, and "Sum_Total" is the summary
field that, temporarily, holds the result of the search.

--

John Weinshel
Datagrace
Associate Member, Filemaker Solutions Alliance
Vashon Island, WA
(206) 463-1634


Quote:
> Another question from the FileMaker newbie.  I am using the evaluation
> copy of FileMaker Pro 5.5, the one with NO help file.  I need to find
> out what is the FileMaker equivelent of the SQL SELECT statement.  For
> example:  I have a salesmen file.  Where I have the salesmen name,
> sale date, and sale amount fields.  How can I wirte a script to select
> all the salesman named John Smith and total all his sales?  I know I
> can do this with a column break report, however I need to script this
> so I can build on it.  I've searched the group archives for an answer
> but no luck.  Please help.



Tue, 11 Nov 2003 02:34:47 GMT
 SQL SELECT statement equivelent
John thank you very much for your reply.  I am sure the answer to my
question lies in your text, however it's still a few notches above my
FileMaker knowledge, so allow me to share with you a bit more detail
and see if you can do the same.

First of all I am only using one data file, (I didn't design it that
way, and that's my excuse) so creating relationships sounds a bit
wierd, with one table if you will.  Unless that's how FileMaker
behaves to get more functionality "aggregate functions" ?

However you introduced the global variable or field to me, for which I
am greatful.

Quote:

> There are essentially two ways to gather records-- dynamically, via searches
> (Finds), or relationally, using GTRR (Go to related records). Finds use
> Summary fields to return aggregate data, and relationships use the Aggregate
> functions for the same purpose.

> As you say, finds will not stay put, and if you want to "build on" the data,
> you'll need to create useful relationships.The most basic relationship would
> be using the Salesman ID as match fields on both sides. Thus:

> Sum(Salesman::Invoice_Total)

> would return the total of all the invoices totals for that salesman, where
> "Salesman" is the name of a relationship that uses the salesman ID as the
> match field on both sides of the relationship (you can use the name, but
> it's not very discrete).

> You can add filters to that relationship by creating concatenated match
> fields on both sides, which will slice the view as you like, say for a given
> month per given salesman, etc.

> You may also collect the data dynamically, back to using searches, by
> posting the collected values to a global number field created for the
> purpose. Thus, if you perform the appropriate find, in a script, you can
> then

> Set Field["gTotal", "Sum_Total"]

> where "gTotal" is a global number field, and "Sum_Total" is the summary
> field that, temporarily, holds the result of the search.

> --

> John Weinshel
> Datagrace
> Associate Member, Filemaker Solutions Alliance
> Vashon Island, WA
> (206) 463-1634



> > Another question from the FileMaker newbie.  I am using the evaluation
> > copy of FileMaker Pro 5.5, the one with NO help file.  I need to find
> > out what is the FileMaker equivelent of the SQL SELECT statement.  For
> > example:  I have a salesmen file.  Where I have the salesmen name,
> > sale date, and sale amount fields.  How can I wirte a script to select
> > all the salesman named John Smith and total all his sales?  I know I
> > can do this with a column break report, however I need to script this
> > so I can build on it.  I've searched the group archives for an answer
> > but no luck.  Please help.



Tue, 11 Nov 2003 08:28:50 GMT
 SQL SELECT statement equivelent
Yes, a relationship can be a self-join (not the same thing as a join file)--
a relationship between a file and itself.  You can create a relationship,
using Define Relationships, using the Salesman ID (within the one file) as
the match field on both sides.

--

John Weinshel
Datagrace
Associate Member, Filemaker Solutions Alliance
Vashon Island, WA
(206) 463-1634


Quote:
> John thank you very much for your reply.  I am sure the answer to my
> question lies in your text, however it's still a few notches above my
> FileMaker knowledge, so allow me to share with you a bit more detail
> and see if you can do the same.

> First of all I am only using one data file, (I didn't design it that
> way, and that's my excuse) so creating relationships sounds a bit
> wierd, with one table if you will.  Unless that's how FileMaker
> behaves to get more functionality "aggregate functions" ?

> However you introduced the global variable or field to me, for which I
> am greatful.




Quote:
> > There are essentially two ways to gather records-- dynamically, via
searches
> > (Finds), or relationally, using GTRR (Go to related records). Finds use
> > Summary fields to return aggregate data, and relationships use the
Aggregate
> > functions for the same purpose.

> > As you say, finds will not stay put, and if you want to "build on" the
data,
> > you'll need to create useful relationships.The most basic relationship
would
> > be using the Salesman ID as match fields on both sides. Thus:

> > Sum(Salesman::Invoice_Total)

> > would return the total of all the invoices totals for that salesman,
where
> > "Salesman" is the name of a relationship that uses the salesman ID as
the
> > match field on both sides of the relationship (you can use the name, but
> > it's not very discrete).

> > You can add filters to that relationship by creating concatenated match
> > fields on both sides, which will slice the view as you like, say for a
given
> > month per given salesman, etc.

> > You may also collect the data dynamically, back to using searches, by
> > posting the collected values to a global number field created for the
> > purpose. Thus, if you perform the appropriate find, in a script, you can
> > then

> > Set Field["gTotal", "Sum_Total"]

> > where "gTotal" is a global number field, and "Sum_Total" is the summary
> > field that, temporarily, holds the result of the search.

> > --

> > John Weinshel
> > Datagrace
> > Associate Member, Filemaker Solutions Alliance
> > Vashon Island, WA
> > (206) 463-1634



> > > Another question from the FileMaker newbie.  I am using the evaluation
> > > copy of FileMaker Pro 5.5, the one with NO help file.  I need to find
> > > out what is the FileMaker equivelent of the SQL SELECT statement.  For
> > > example:  I have a salesmen file.  Where I have the salesmen name,
> > > sale date, and sale amount fields.  How can I wirte a script to select
> > > all the salesman named John Smith and total all his sales?  I know I
> > > can do this with a column break report, however I need to script this
> > > so I can build on it.  I've searched the group archives for an answer
> > > but no luck.  Please help.



Tue, 11 Nov 2003 09:46:46 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Is there an equivelent to the TRANSFORM statement from Acess in T-SQL

2. USING A FIELD FROM MAIN SELECT STATEMENT IN SUB SELECT STATEMENT

3. Customer Statement SQL Select statement???

4. SQL Select From Select Statement

5. the MOD equivelent in SQL Server

6. HEX2DEC - is there equivelent in SQL server?

7. datevalue() equivelent in sql?

8. Help me convert a SELECT statement to an UPDATE statement

9. insert statement using values from a select statement

10. IF STATEMENTS IN SELECT STATEMENTS

11. Running SELECT statement within EXECUTE statement

12. Sleeping SELECT statement is blocking another statement


 
Powered by phpBB® Forum Software